第三版：mybatis 动态sql的实现

if的使用

/**
 * 根据姓名和email查询user集合 if的使用
 * @param sysUser
 * @return
 */
List<SysUser> selectUserByNameAndEmail(SysUser sysUser);

<select id="selectUserByNameAndEmail" resultType="SysUser">
    select id,user_name,user_password,user_email,user_info,head_img,create_time
    from sys_user where 1=1
    <if test="userName!=null and userName!=''">
        and user_name like concat('%',#{userName},'%')
    </if>
    <if test="userEmail!=null and userEmail!=''">
        and user_email = #{userEmail}
    </if>
</select>

@Test
public void selectUserByNameAndEmail(){
    SqlSession sqlSession = ## 标题getSqlSession();
    try {
        SysUserMapper sysUserMapper = sqlSession.getMapper(SysUserMapper.class);
        SysUser sysUser = new SysUser();
        sysUser.setUserName("te");
        sysUser.setUserEmail("test1@123");
        List<SysUser> sysUsers = sysUserMapper.selectUserByNameAndEmail(sysUser);
        Assert.assertTrue(sysUsers.size()>0);
    }finally {
        sqlSession.rollback();
        sqlSession.close();
    }
}

choose 的使用

choose 元素中包含 when和otherwish两个元素
一个choose中至少有一个when，有0个或者1个otherwish元素
（相当于switch case defult）

/**
 * 根据id或者name 查询user集合  choose的使用
 * @param sysUser
 * @return
 */
List<SysUser> selectUserByidandName(SysUser sysUser);

<select id="selectUserByidandName" resultType="SysUser">
    select id,user_name,user_password,user_email,user_info,head_img,create_time
    from sys_user where 1=1
    <choose>
        <when test="id != null">
            and id = #{id}
        </when>
        <when test="userName != null and userName != ''">
            and user_name = #{userName}
        </when>
        <otherwise>
            and 1=2
        </otherwise>
    </choose>
</select>

@Test
public void selectUserByidandName(){
    SqlSession sqlSession = getSqlSession();
    try {
        SysUserMapper sysUserMapper = sqlSession.getMapper(SysUserMapper.class);
        SysUser sysUser = new SysUser();
        sysUser.setId(1l);
        sysUser.setUserName("admin");
        List<SysUser> sysUsers = sysUserMapper.selectUserByidandName(sysUser);
        Assert.assertTrue(sysUsers.size()>0);
    }finally {
        sqlSession.rollback();
        sqlSession.close();
    }
}

where的用法

与单独使用if的不同在于省略了 “where 1=1”

/**
 * 根据姓名和email查询user集合  where的使用
 * @param sysUser
 * @return
 */
List<SysUser> selectwhere(SysUser sysUser);

<select id="selectwhere" resultType="SysUser">
    select id,user_name,user_password,user_email,user_info,head_img,create_time
    from sys_user
    <where>
        <if test="userName!=null and userName!=''">
            and user_name like concat('%',#{userName},'%')
        </if>
        <if test="userEmail!=null and userEmail!=''">
            and user_email = #{userEmail}
        </if>
    </where>
</select>

@Test
public void selectWhere(){
    SqlSession sqlSession = getSqlSession();
    try {
        SysUserMapper sysUserMapper = sqlSession.getMapper(SysUserMapper.class);
        SysUser sysUser = new SysUser();
        sysUser.setUserName("te");
        sysUser.setUserEmail("test1@123");
        List<SysUser> sysUsers = sysUserMapper.selectwhere(sysUser);
        Assert.assertTrue(sysUsers.size()>0);
    }finally {
        sqlSession.rollback();
        sqlSession.close();
    }
}

set的用法

set的作用：如果set面字符串是以逗号结尾的，就将这个逗号删除
（避免全部的值都为null的情况）

/**
 * 根据Id修改user对象 set的使用
 * @param sysUser
 * @return
 */
int updataset(SysUser sysUser);

<update id="updataset">
      UPDATE `sys_user`
      <set>
          <if test="userName != null and userName != ''">
               user_name = #{userName},
          </if>
          <if test="userPassword != null and userPassword != ''">
               user_password = #{userPassword},
          </if>
          <if test="userEmail != null and userEmail != ''">
               user_email = #{userEmail},
          </if>
          <if test="userInfo != null and userInfo != ''">
               user_info =## 标题 @{userInfo},
          </if>
      </set>
    WHERE (`id`=#{id});
</update>

@Test
public void updateset(){
    SqlSession sqlSession = getSqlSession();
    try {
        SysUserMapper sysUserMapper = sqlSession.getMapper(SysUserMapper.class);
        SysUser sysUser = new SysUser();
        sysUser.setId(1l);
        sysUser.setUserName("admin");
        int updataset = sysUserMapper.updataset(sysUser);
        Assert.assertTrue(updataset>0);
        System.out.println(sysUser.toString());
    }finally {
        sqlSession.commit();
        sqlSession.close();
    }
}

trim的用法

where和set标签的功能都可以使用trim标签来实现，并且在底层就是通过TrimSqlNode实现的

where标签对应的trim的实现如下：

<trim prefix="WHERE" prefixOverrides="AND |OR ">
    ...
</trim>

set标签对应的trim的实现如下：

<trim prefixOverrides="SET" suffixOverrides=",">
    ...
</trim>

foreach in

/**
 * 根据ids查询user集合  foreach in
 * @param idList
 * @return
 */
List<SysUser> selectByIdlist(List<String> idList);

<select id="selectByIdlist" resultType="SysUser">
     select id,
  user_name,
  user_password,
  user_email,
  user_info,
  head_img,
  create_time
  from sys_user where id in
  <foreach collection="list" open="(" close=")" separator="," item="id" index="i">
    #{id}
  </foreach>
  /* collection 必填 值为迭代循环的属性名。
   item 变量名，值为迭代对象中取出的每一个值
   index 索引的属性名，在集合数组情况下的值为 当前索引值，当前迭代循环的对象是map类型时，这个值为map的key
   open 整个循环内容开头的字符串
   close 整个循环内容结尾的字符串。
   separator 每次村换的分隔符
*/
</select>

@Test
public void selectByIdlist(){
    SqlSession sqlSession = getSqlSession();
    try {
        SysUserMapper sysUserMapper = sqlSession.getMapper(SysUserMapper.class);
        List<String> list = new ArrayList<>();
        list.add("1");
        list.add("2");
        List<SysUser> sysUsers = sysUserMapper.selectByIdlist(list);
        Assert.assertNotNull(sysUsers);
        Assert.assertTrue(sysUsers.size()==2);
    }finally {
        sqlSession.rollback();
        sqlSession.close();
    }
}

实现批量增加的时候，只需在原来的代码基础上添加

<insert id="XXXX" useGeneratedKeys="true" keyProperty="id">

foreach 实现动态update

/**
 * 修改对象 通过map foreach
 * @param map
 * @return
 */
int updateByMap(Map<String,Object> map);

<update id="updateByMap" >
     UPDATE `sys_user` SET
     <foreach collection="_parameter" index="key" item="val" separator=",">
         ${key} = #{val}
     </foreach>
   WHERE (`id`=#{id});

</update>

@Test
public void updateByMap(){
    SqlSession sqlSession = getSqlSession();
    try {
        SysUserMapper sysUserMapper = sqlSession.getMapper(SysUserMapper.class);

        Map<String,Object> map = new HashMap<>();
        map.put("id",1l);
        map.put("user_name","asdasdas");
        map.put("user_email","asdasd");
        int i = sysUserMapper.updateByMap(map);
        Assert.assertEquals(1,i);
    }finally {
        sqlSession.rollback();
        sqlSession.close();
    }
}

bind 用法

<if test="userName!=null and userName!=''">
    and user_name like concat('%',#{userName},'%')
</if>

改为： 防止sql注入，适用于多个数据库的语法
<if test="userName!=null and userName!=''">
    <bind name="userNameLike" value="'%' + userName + '%'"/>
    and user_name like #{userNameLike}
</if>