动态sql在xml中支持的几种标签：

if
choose（when、otherwise）
trim（where、set）
foreach

bind

4.1 if用法

4.1.1 在where条件中使用if

  /**
   * 根据动态条件查询用户信息
   * @param user
   * @return
   */
  List<SysUser> selectByUser(SysUser user);

  <select id="selectByUser" resultType="com.ql.simple.model.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>

if中必填的属性时test，test是一个符合OGNL要求的判断表达式

注意sql中where关键字后面的条件
注意条件中的and或or

测试：

@Test
    public void testSelectByUser() {
        SqlSession sqlSession = getSqlSession();
        try {
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            // 只查询用户名
            SysUser user = new SysUser();
            user.setUserName("ad");
            List<SysUser> userList = userMapper.selectByUser(user);
            Assert.assertTrue(userList.size() > 0);
            // 只查询邮箱
            SysUser user2 = new SysUser();
            user2.setUserEmail("test@mybatis.tk");
            List<SysUser> userList2 = userMapper.selectByUser(user2);
            Assert.assertTrue(userList2.size() > 0);
            // 同时查询用户名和邮箱
            SysUser user3 = new SysUser();
            user3.setUserName("ad");
            user3.setUserEmail("test@mybatis.tk");
            List<SysUser> userList3 = userMapper.selectByUser(user3);
            Assert.assertTrue(userList3.size() == 0);
        }finally {
            sqlSession.close();
        }
    }

4.1.2 在update更新列中使用if

    /**
     * 根据主键更新
     * @param sysUser
     * @return
     */
    int updateByIdSelective(SysUser sysUser);

    <update id="updateByIdSelective">
        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>
            <if test="headImg != null">
                head_img = #{headImg, jdbcType=BLOB},
            </if>
            <if test="createTime != null">
                create_time = #{createTime,jdbcType=TIMESTAMP},
            </if>
            id = #{id}
        where id = #{id}
    </update>

注意 id = #{id}条件，防止if全部不成立时，sql语法错误

每个if查询中都是有“，”的，所以最后id = #{id}省略的话，会导致sql语法错误

4.1.3 在insert动态插入列中使用if

  /**
   * 添加用户，使用useGenerateKeys方式
   * @param user
   * @return
   */
  int insert2(SysUser user);

  <insert id="insert2" useGeneratedKeys="true" keyProperty="id">
      insert into sys_user(id,
                           user_name,
                           user_password,
                           <if test="userEmail != null and userEmail != ''">
                               user_email,
                           </if>
                           user_info,
                           head_img,
                           create_time)
      values ( #{id}, #{userName}, #{userPassword},
              <if test="userEmail != null and userEmail != ''">
                  #{userEmail},
              </if>
              #{userInfo}, #{headImg, jdbcType=BLOB}, #{createTime, jdbcType=TIMESTAMP} )
  </insert>

4.2 choose用法

  /**
   * 根据用户id或用户名查询
   * @param sysUser
   * @return
   */
  SysUser selectByIdOrUserName(SysUser sysUser);

<select id="selectByIdOrUserName" resultType="com.ql.simple.model.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>

4.3 wherre、set、trim用法

4.3.1 where用法

  <select id="selectByUser" resultType="com.ql.simple.model.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>

也就是sql中where语句的作用，但是这里的where标签会把语句后面and或or开头的单词去掉，这样就不会有sql异常。这样也不需要像之前那样添加1 = 1 这样的临时条件

4.3.2 set用法


  <update id="updateByIdSelective">
      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>
          <if test="headImg != null">
              head_img = #{headImg, jdbcType=BLOB},
          </if>
          <if test="createTime != null">
              create_time = #{createTime,jdbcType=TIMESTAMP},
          </if>
          id = #{id},
      </set>
      where id = #{id}
  </update>

set的作用和update语句中set语句的作用一致，只是如果该标签后面的字符串是以逗号结尾的，就将这个逗号剔除

4.3.3 trim的用法

where和set标签的功能都可以用trim标签来实现，底层就是通过TrimSqlNode来实现的
where标签对应的trim的实现：

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

set标签对应的trim实现：

<trim prefix="SET" suffixOverrides="，">
...
</trim>

4.4 foreach用法

4.4.1 foreach实现in集合

  /**
   * 根据用户id集合查询
   * @param idList
   * @return
   */
  List<SysUser> selectByIdList(List<Long> idList);

  <select id="selectByIdList" resultType="com.ql.simple.model.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>
  </select>

foreach包含的属性：

collection:必填，值为要迭代循环的属性名
item：变量名
index：索引的属性名
open：整个循环内容开头的字符串
close：整个循环内容结尾的字符串
separator

collection属性的设置：

只有一个数组或集合参数

collection=”list” or collection=”array”

有多个参数

用@Param指定参数，将collection设为param注解指定的名字

参数是Map类型

与param注解相似，将collection指定为对应Map中的key即可，默认为_parameter

参数是一个对象

指定为对象的属性名即可

4.4.2 foreach实现批量插入

    /**
     * 批量插入用户信息
     * @param userList
     * @return
     */
    int insertList(List<SysUser> userList);

    <insert id="insertList">
        insert into sys_user(user_name, user_password, user_email, user_info, head_img, create_time)
        values
        <foreach collection="list" item="user" separator=",">
            (#{user.userName}, #{user.userPassword}, #{user.userEmail}, #{user.userInfo}, #{user.headImg,jdbcType=BLOB},
             #{user.createTime,jdbcType=TIMESTAMP})
        </foreach>
    </insert>

如果要在MySQL中实现批量插入返回自增主键值：

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

4.4.3 foreach实现动态update

    /**
     * 通过Map更新列
     * @param map
     * @return
     */
    int updateByMap(Map<String, Object> map);

    <update id="updateByMap">
        update sys_user
        set
        <foreach collection="_parameter" item="val" index="key" separator=",">
            ${key} = #{val}
        </foreach>
        where id = #{id}
    </update>

index对应的值为map的key

4.5 bind用法

bind标签主要是用来避免由于更换数据库带来的一些麻烦

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

该语句在mysql中正常，但是换到oracle会报错

<if test="userName != null and userName != ''">
    <bind name="userNameLike" value="'%' + userName + '%'"/>
    and user_name like #{userNameLike}
</if>

Java 从入门到进阶

第四章 MyBatis动态SQL