MyBatis（四）动态SQL

在JDBC中，根据不同条件拼接SQL时，语句中的空格和逗号很容易出错，且代码很冗长。动态SQL是MyBatis的强大特性之一，可以很容易地规避这些问题。

MyBatis支持以下几种动态SQL相关的标签：if、choose（when、otherwise）、trim（where、set）、foreach、bind。

 

一、if用法

if标签通常用于WHERE语句中，通过判断参数值来决定是否使用某个查询条件，它也经常用于UPDATE语句中判断是否更新一个字段，还可以在INSERT语句中用来判断是否插入某个字段的值。

1、在WHERE条件中使用if

对于查询条件确定有值的SQL来说，可以通过日常查询的SQL那样直接将条件写在语句中。但是在程序实现中，当多个查询条件不全部有值时，非动态SQL会将null或空值''直接传入查询条件，导致查询结果与预期结果不符。针对这一情况，可以使用if标签来很好地应对这一问题。例如：

　　<select id="selectByUser" resultType="tk.mybatis.model.SysUser">
    　　select id,
        　　user_name userName,
        　　user_password userPassword,
        　　user_email userEmail,
        　　user_info userInfo,
        　　head_img headImg,
        　　create_time createTime
    　　from sys_user
    　　where 1 = 1
    　　<if test = "userName != null and userName != ''">
        　　and userName like concat('%', #{userName}, '%')
    　　</if>
    　　<if test = "userEmail != null and userEmail != ''">
        　　and userEmail like concat('%', #{userEmail}, '%')
    　　</if>
　　</select>

在实际使用场景中，如果我们想通过用户名或邮箱模糊匹配某个或某些用户信息，在不确定邮箱信息的情况下，我们可以只根据用户名来查询，而不希望将邮箱信息为null或空值''也携带作为查询条件之一。在使用if标签之后，如果两个条件都有传值，则根据两个信息来查询，如果是用户名或邮箱一个条件有传值，则根据有值得条件查询，如果两个条件都未传值，则查询所有记录。

if标签有一个必填属性test，其属性值为一个符合OGNL要求的判断表达式，结果可以为true或false，除此之外所有的非0值都为true，只有0为false。一般只用true或false作为结果。

在以上select语句中，包含了如下规则：

• property != null 或 property == null：适用于任何类型的字段，用于判断属性值是否为空。
• property != '' 或 property == ''：仅适用于String类型的字段，用于判断是否为空字符串。
• and和or：当有多个判断条件时，使用and或or进行连接，嵌套的判断可以使用小括号分组，and相当于Java中的与（&&），or相当于Java中的或（||）。

2、在UPDATE中使用if

如果需要只更新有变化的字段，不能将原来有值但没有变化的字段更新为空或null，可以通过if标签来实现。

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

3、在INSERT动态插入列中使用if

在数据库表中插入数据时，如果某一列的参数值不为空，就使用传入的值，如果传入参数为空，就使用数据库中的默认值，而不使用传入的空值。使用if就可以实现这种动态插入列的功能。

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

如果在建表时，对user_email字段设置了默认值，那么如果在调用insertSelective接口的用户对象中useEmail字段值为null或空，那么在插入时会将设置的默认值作为这一条记录的user_email属性值，如果字段值有赋值，则会将赋值的数据作为这条记录的user_email插入。从而，实现动态插入列。

在使用时，应注意若在列的部分增加if条件，则values的部分也要增加相同的if条件，保证列名与属性值一一对应。

 

二、choose用法

if标签提供了基本的条件判断，但是无法实现if...else、if...else...的逻辑，可以通过choose whern otherwise标签来实现。

    <select id="selectByIdOrUserName" resultType="tk.mybatis.simple.model.SysUser">
        select id,
        user_name userName,
        user_password userPassword,
        user_email userEmail,
        user_info userInfo,
        head_img headImg,
        create_time createTime
        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>
                1 = 2
            </otherwise>
        </choose>
    </select>

以上动态语句的逻辑为，当参数id有值的时候优先使用id查询，当id没有值时就去判断用户名是否有值，如果有值就用用户名查询，如果用户名也没有值，就使SQL查询无结果。

 

三、where、set、trim用法

1、where用法

where标签的作用：如果该标签包含的元素中有返回值，就插入一个where；如果where后面的字符串是以AND和OR开头的，就将他们剔除。

之前XML中的selectByuser方法在where条件部分通过在第一个where子句添加1 = 1保证当where的动态条件全部为空时，执行语句不会报错。现在，将其修改为where标签方式：

    <select id="selectByUser" resultType="tk.mybatis.simple.model.SysUser">
        select id,
        user_name userName,
        user_password userPassword,
        user_email userEmail,
        user_info userInfo,
        head_img headImg,
        create_time createTime
        from sys_user
        <where>
            <if test = "userName != null and userName != ''">
                and userName like concat('%', #{userName}, '%')
            </if>
            <if test = "userEmail != null and userEmail != ''">
                and userEmail like concat('%', #{userEmail}, '%')
            </if>
        </where>
    </select>

当if条件均不满足的时候，where元素中没有内容，所以在SQL中不会出现where。如果if条件满足，where元素的内容就是以and开头的条件，where会自动去掉开头的and。

2、set用法

set标签的作用：如果该标签中包含的元素有返回值，就插入一个set；如果set后面的字符串是以逗号结尾的，就将这个逗号剔除。

修改之前的updateByIdSelective方法：

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

3、trim用法

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

trim标签有如下属性：

• prefix：当trim元素内包含内容时，会给内容增加prefix指定的前缀。
• prefixOverrides：当trim元素内包含内容时，会把内容中匹配的前缀字符串去掉。
• suffix：当trim元素包含内容时，会给内容增加suffix指定的后缀。
• suffixOverrides：当trim元素内包含内容时，会把内容中匹配的后缀字符串去掉。

4、foreach用法

SQL语法中有时会使用IN关键字，例如id in (1,2,3)。可以使用${ids}方式直接获取值，但这种写法不能防止SQL注入，想避免SQL注入就需要用#{}的方式，这时需要使用foreach标签来满足。

foreach可以对数组、Map或实现了Iterable接口（如List、Set）的对象进行遍历。数组在处理时会转换为List对象，因此foreach遍历的对象可以分为两大类：Iterable类型和Map类型。

（1）foreach实现in集合

在UserMapper接口中添加方法：

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

在UserMapper.xml中添加：

    <select id="selectByIdList" resultType="tk.mybatis.simple.model.SysUser">
        select id, 
            user_name userName, 
            user_password userPassword, 
            user_email userEmail, 
            user_info userInfo, 
            head_img headImg, 
            create_time createTime
        from sys_user
        where id in
        <foreach collection="list" open="(" close=")" separator="," item="id" index="i">
            #{id}
        </foreach>
    </select>

foreach包含以下属性：

• collection：必填，值为要迭代循环的属性名。
• item：变量名，值为从迭代对象中取出的每一个值。
• index：索引的属性名，在集合数组情况下值为当前索引值，当迭代循环的对象时Map类型时，这个值为Map的key。
• open：整个循环内容开头的字符串。
• close：整个循环内容结尾的字符串。
• separator：每次循环的分隔符。

当参数类型为集合的时候，默认会转换成Map类型，并添加一个key为collection的值，如果参数类型是List集合，那么就继续添加一个key为list的值，当collection="list"时，就能得到这个集合，并对它进行循环操作。

当参数类型为数组的时候，也会转换成Map类型，默认的key为array。

上面提到的是数组或集合类型的参数默认的名字。推荐使用@Param注解来指定参数的名字，这时collection就设置为通过@Param注解指定的名字。

程序测试：

    @Test
    public void testSelectByIdList() {
        SqlSession sqlSession = getSqlSession();
        try {
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            List<Long> idList = new ArrayList<Long>();
            idList.add(1L);
            idList.add(1001L);
            List<SysUser> userList = userMapper.selectByIdList(idList);
            Assert.assertEquals(2, userList.size());
        } finally {
            sqlSession.close();
        }
    }

由控制台输出可以看到foreach内容已被转成in (?, ?)：

 

 （2）foreach实现批量插入

在MySQL中，也支持批量插入功能，在values后面通过将待批量插入的值以相同格式进行拼装，可以通过foreach来实现。

在UserMapper接口中添加方法：

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

在UserMapper.xml中添加：

    <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}, #{user.createTime})
        </foreach>
    </insert>