MyBatis-XML Schema

1. Brief Description - Differences from Notes

Execution efficiency: same

Speed of development: faster annotations

Easy maintenance: Notes are simpler.

Old company, old projects still use XML

 

 

2. Environmental Setup

1. Make sure the database has been created

2. Import jar package & core configuration file

3. (XML only) Create mapper package and import mapper.xml mapping file

 

4. Create a package package and import the MybatisUtils tool

5. Define JavaBean

User class:

public class User implements Serializable {
    private Integer uid;
    private String username;
    private Date birthday;
    private String sex;
    private String address;

    // get set Reference or no reference toString

}

 

6. (XML only) Define Dao & Set Mapper.xml & Register Mapper

 

 

 

 

3. Getting Started Cases - Query All

Code implementation'

Test class:

/**
 *Case 1: Query all
 */
@Test
public void run1(){
    //1. Get Dao Objects
    UserDao dao = MyBatisUtils.getMapper(UserDao.class);
    //2. Call Dao Object
    List<User> ulist =  dao.selectAll();
    System.out.println(ulist);
    //3. Close Session
    MyBatisUtils.close();
}

 

UserDao: (no SQL statement)

List<User> selectAll();

 

UserMapper.xml (unique to XML)

<mapper namespace="com.czxy.user.dao.UserDao">
   <!--
      In corresponding notes select
      id="Method name in interface"
      resultType="Return Value Type"
   -->
   <select id="selectAll" resultType="com.czxy.user.domain.User">
      select * from user
   </select>
</mapper>

 

 

Differences between annotations and XML styles (Understanding)

 

Annotation is an alternative to XML.

 

 

4. Advanced-Advanced [Key]

1. Query by ID

Test class:

/**
 *Case 2: Query by ID
 */
@Test
public void run2(){
    //1. Get Dao Objects
    UserDao dao = MyBatisUtils.getMapper(UserDao.class);
    //2. Call Dao Object
    User user = dao.selectUserByUid(6);
    System.out.println(user);
    //3. Close Session
    MyBatisUtils.close();
}

 

UserDao:

User selectUserByUid(@Param("uid") int i);

 

UserMapper.xml

<select id="selectUserByUid" resultType="com.czxy.user.domain.User">
   select * from user where uid = #{uid}
</select>

 

 

2. Add

Test class:

/**
 *Case 3: Add
 */
@Test
public void run3(){
    //1. Get Dao Objects
    UserDao dao = MyBatisUtils.getMapper(UserDao.class);
    //2. Call Dao Object
    User user = new User(null, "Brilliant sir", new Date(), "male", "Pandora Box");
    int row = dao.insert(user);
    System.out.println(row);
    //3. Commit the transaction before closing the session
    MyBatisUtils.commitAndClose();
}

UserDao:

int insert(User user);

 

UserMapper.xml

<!--
   In corresponding notes insert
   id="Method name in interface"
-->
<insert id="insert">
   insert into user values (#{uid},#{username},#{birthday},#{sex},#{address})
</insert>

 

 

3. Modification

Test class:

/**
 *Case 4: Modification
 */
@Test
public void run4(){
    //1. Get Dao Objects
    UserDao dao = MyBatisUtils.getMapper(UserDao.class);
    //2. Call Dao Object
    int row = dao.update("2010-01-01","female","Village",9);
    System.out.println(row);
    //3. Commit the transaction before closing the session
    MyBatisUtils.commitAndClose();
}

 

UserDao:

int update(@Param("birthday")String birthday, @Param("sex")String sex, @Param("address")String address, @Param("uid")int uid);

UserMapper:

<update id="update">
   update user set birthday=#{birthday},sex=#{sex},address=#{address} where uid=#{uid}
</update>

 

 

 

4. Delete

Test class:

/**
 *Case 5: Delete
 */
@Test
public void run5(){
    //1. Get Dao Objects
    UserDao dao = MyBatisUtils.getMapper(UserDao.class);
    //2. Call Dao Object
    int row = dao.deleteByUid(5);
    System.out.println(row);
    //3. Commit the transaction before closing the session
    MyBatisUtils.commitAndClose();
}

 

UserDao:

int deleteByUid(@Param("uid") int i);

UserMapper:

<delete id="deleteByUid">
   delete from user where uid=#{uid}
</delete>

 

 

5. Summary

annotation	XML Schema
@Select	<select id="method name"resultType="return value type"></select>
@Insert	<insert id="method name"></ Insert >
@Update	<update_id="method name"></ update >
@Delete	<delete id="method name"></ delete >
@Param	@Param Parameters are assigned and commented the same way

 

 

 

6. Total number of records queried

select count(*) from user;

Test class:

/**
 *Case 6: Total number of records queried
 */
@Test
public void run6(){
    //1. Get Dao Objects
    UserDao dao = MyBatisUtils.getMapper(UserDao.class);
    //2. Call Dao Object
    int count = dao.selectCount();
    System.out.println(count);
    //3. Close Session
    MyBatisUtils.close();
}

 

UserDao:

int selectCount();

 

UserMapper.xml:

<select id="selectCount" resultType="int">
   select count(*) from user
</select>

 

 

7. Conditional Query (Fixed SQL Statement)

Query: User name contains "ao" and all users with gender of "male"

Test class:

/**
 * Case 7: Query: User name contains "ao" and all users of gender "male"
 */
@Test
public void run7(){
    //1. Get Dao Objects
    UserDao dao = MyBatisUtils.getMapper(UserDao.class);
    //2. Call Dao Object
    List<User> ulist = dao.selectUlistByParam("%ao%","male");
    System.out.println(ulist);
    //3. Close Session
    MyBatisUtils.close();
}

 

UserDao:

List<User> selectUlistByParam(@Param("username") String username, @Param("sex") String sex);

 

UserMapper.xml:

<select id="selectUlistByParam" resultType="com.czxy.user.domain.User">
   select * from user where username like #{username} and sex = #{sex}
</select>

 

 

 

8. Conditional Query (Dynamic SQL) [Key]

Query: User name contains "ao" and all users with gender of "male"

1. If username is not passed, only gender is filtered
2. If gender is not passed in, filter user names
3. If neither user name nor gender is passed, query all

 

Dynamic SQL: Split different SQL statements according to different conditions

Modified UserMapper.xml:

<select id="selectUlistByParam" resultType="com.czxy.user.domain.User">
   select * from user
   <where>
      <if test="username!=null and username!=''">
         and username like #{username}
      </if>
      <if test="sex!=null and sex!=''">
         and sex = #{sex}
      </if>
   </where>
</select>

 

 

9. Conditional Query (Dynamic SQL) [Key]

Query: data with uid 1 or 3 or 4 or 6

uid in (1,3,4,6)

Test class:

/**
 * Case 8: Query: data with uid 1 or 3 or 4 or 6
 */
@Test
public void run8(){
    //1. Get Dao Objects
    UserDao dao = MyBatisUtils.getMapper(UserDao.class);
    //2. Call Dao Object
    List<Integer> ids = new ArrayList<>();
    Collections.addAll(ids,1,3,4,6);
    List<User> ulist = dao.selectUlistByUids(ids);
    System.out.println(ulist);
    //3. Close Session
    MyBatisUtils.close();
}

 

UserDao:

List<User> selectUlistByUids(@Param("ids") List<Integer> ids);

 

UserMapper.xml:

 

 

Extension: Dynamic SQL annotated

UserDao:

@Select("<script>select * from user\n" +
        "\t\t<where>\n" +
        "\t\t\t<if test=\"username!=null and username!=''\">\n" +
        "\t\t\t\tand username like #{username}\n" +
        "\t\t\t</if>\n" +
        "\t\t\t<if test=\"sex!=null and sex!=''\">\n" +
        "\t\t\t\tand sex = #{sex}\n" +
        "\t\t\t</if>\n" +
        "\t\t</where></script>")
List<User> selectUlistByParam(@Param("username") String username, @Param("sex") String sex);

 

@Select("<script>select * from user\n" +
        "\t\t<where>\n" +
        "\t\t\t<foreach collection=\"ids\" item=\"uid\" open=\"uid in (\" separator=\",\" close=\")\">\n" +
        "\t\t\t\t#{uid}\n" +
        "\t\t\t</foreach>\n" +
        "\t\t</where></script>")
List<User> selectUlistByUids(@Param("ids") List<Integer> ids);

The programmer opens two projects:

An XML way

A way to annotate

Write dynamic SQL in XML and migrate to annotation after testing.

 

 

Today's summary:

 

 

 

 

Please give yourself a compliment on your efforts!

Make a little progress every day `~~