MyBatis动态SQL

前言

在前面，我们已经学习了MyBatis的单表操作以及多表操作，在体验了MyBatis提供的对于结果集的封装之后，我们接下来来学习MyBatis中的动态SQL。

动态SQL

所谓的动态SQL，就是根据不同的情况来产生不同的SQL，比如在查询用户的时候，我们可以根据用户的ID、名称、邮箱来查询，当然，这三者也可以进行组合，如果要为每一种情况写一个查询，那工作量还是比较恐怖的，动态SQL在这个时候就充分发挥作用了。

在MyBatis中，提供了一下几个标签来实现动态SQL

• if
• choose
  • when
  • otherwise
• where
• set
• trim
• foreach

下面我们就来详细学习这几个标签

if标签

if标签中包含一个必须具备的属性test=""，只有当test中的值为真时，if标签里面的内容才会被拼接到SQL语句中，如下代码所示。

<select id="selectByUser" resultType="domain.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 
    <!--注意这里，如果两者均为空，会出现 where之后啥都没有，SQL语法错误，不优雅，有更好地解决方案-->
    <!--如果userName非空，则使用userName来查询-->
    <if test="userName != null and userName != ''">
        and user_name like concat('%', #{userName}, '%')
    </if>
    <!--如果userEmail非空，则使用userEmail来查询-->
    <if test="userEmail != null and userEmail != ''">
        and user_email = #{userEmail}
    </if>
</select>

if语句的使用还是比较好理解的，需要注意的是，if语句可以用于select、update、insert中，后面两种情况的小例子如下所示

 <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>
        <!--注意这里，如果前面都为空，则会出现set where id = #{id}，SQL语法错误 -->
        <!--如果只有一个条件，会出现 user_name = #{userName}, where id = #{id}， SQL语法错误-->
        id = #{id} 
        where id = #{id}
</update>

<!--注意insert语句中，插入的值以及字段部分均需要进行测试-->
<insert id="insert2" 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 = TIMESTAMPS})
</insert>

choose标签

choose标签与if标签不同，多if标签的元素之间是没有关系的，而choose标签中的when以及otherwise则是互斥的关系，可以将choose及其子标签理解为if ... else if ... else....，其用法如下

<select id="selectByIdOrUserName" resultType="domain.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 != ''">
            and id = #{id}
        </when>
        <when test="userName != null and userName != '">
            and user_name = #{userName}
        </when>
        <otherwise>
            and 1 = 2
        </otherwise>
    </choose>
</select>

where、set、trim

在上面的if和choose标签中，我们都会根据情况加入以下辅助的操作，如1=1用于避免当所有的值都不存在的时候，拼接SQL出现错误，where以及set标签的出现，就是为了解决这样的问题

<select id="selectByUser" resultType="domain.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 user_name like concat('%', #{userName}, '%')
        </if>
        <if test="userEmail != null and userEmail != ''">
            and user_email = #{userEmail}
        </if>
    </where>
</select>

如果没有一个if匹配，则where也不会存在，解决了出现以where结尾的情况，如果有情况匹配，会自动去除where后面的and，避免出现where and的情况，比上面添加1 = 1的情况更加优雅一点.

如果set中的内容有值，就插入一个set，如果以逗号结尾，则删除该逗号。

<update id="updateByIdSelective">
    update sys_user
    <set>
        <if test="userName != null and userName != ''">
            user_name = #{userName},
        </if>
    </set>
    id = #{id}
    where id = #{id}
</update>

但是由于if中会以,结尾，所以感觉其实set标签没有什么用处，囧

where和set可以通过trim来实现，并且底层就是通过TrimSqlNode实现的

where版本的trim

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

set版本的trim

<trim prefix="SET" suffixOverrides=",">
</trim>

foreach标签

foreach可以对数组、map、或者实现了Iterable接口的对象进行遍历，然后将对应的内容拼接起来，就是通过循环来拼接SQL

<select id="selectByIdList" resultType="domain.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>

拼接之后的SQL为

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 ( ? , ? ) 

根据生成的SQL来解释foreach的使用，应该还是比较好理解的。

foreach属性

• collection，要迭代循环的属性名
  • 如果是数组，则是array
  • 列表，则是list
  • 其余为collection
  • 可以通过@Param("name")来指定，然后使用name
• item：变量名，值为从迭代对象中取出的每一个值
• index：索引，如果是map，则是key
• open：内容开头的字符串
• close：内容结束的字符串
• separator：拼接时候的分隔符

可以通过下面的方式来实现批量插入

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

当参数类型是map时，可以通过以下方式来动态操作，在迭代map时，item是map的值，index是key，需要注意这一点

<update id="updateByMap">
    update sys_user
    set
        <foreach colleciton="_parameter/使用@Param()指定的name" item="val" index="key" separator=",">
            ${key} = #{val}
        </foreach>
    where id = #{id}
</update>

总结

本小节主要学习了MyBaits中的动态SQL，包括了if、choose、when、otherwise、where、set、trim、foreach等标签，通过灵活组合这些标签，可以比较容易地实现根据不同的参数来拼接不同的SQL语句，不过，由于每次都需要拼接SQL，所以，对于性能还是有一定的影响的。