[05] 动态SQL


MyBatis的强大特性之一就是它的动态SQL,它可以根据不同的条件动态地组成SQL语句进行执行。为此,MyBatis提供了一系列强大的表达式,本章将就此进行学习,主要内容直接参考的是官方文档《动态 SQL》。

1、if

某些条件我需要时才出现,不需要时就不出现,这种需求常常出现在根据用户输入的条件进行搜索的场景,下面来看官方给出的例子:
<select id="findActiveBlogWithTitleLike" resultType="Blog">
    SELECT * FROM BLOG
    WHERE state = 'ACTIVE'
    <if test="title != null">
        AND title LIKE #{title}
    </if>
</select>

如果传入了title,那么就会对“title”进行模糊查询返回结果。你甚至可以多条件考虑:
<select id="findActiveBlogLike" resultType="Blog">
    SELECT * FROM BLOG WHERE state = 'ACTIVE'
    <if test="title != null">
        AND title like #{title}
    </if>
    <if test="author != null and author.name != null">
        AND author_name like #{author.name}
    </if>
</select>

2、choose / when / otherwise

choose / when / otherwise 三种元素结合起来使用,类似于Java中的switch语句,看了下面这个例子我想你一定能明白:
<select id="findActiveBlogLike" resultType="Blog">
    SELECT * FROM BLOG WHERE state = 'ACTIVE'
    <choose>
        <when test="title != null">
            AND title like #{title}
        </when>
        <when test="author != null and author.name != null">
            AND author_name like #{author.name}
        </when>
        <otherwise>
            AND featured = 1
        </otherwise>
    </choose>
</select>

3、where / set / trim

3.1 where

我们先看下面这个例子:
<select id="findActiveBlogLike" resultType="Blog">
    SELECT * FROM BLOG
    WHERE
    <if test="state != null">
        state = #{state}
    </if>
    <if test="title != null">
        AND title like #{title}
    </if>
    <if test="author != null and author.name != null">
        AND author_name like #{author.name}
    </if>
</select>

看似没有什么毛病,但假如所有的 if 条件都没有符合怎么办?SQL语句会变成这样:SELECT * FROM BLOG WHERE,这显然是个错误的语句。假设只匹配了第二个 if,那么语句又会变成这样:SELECT * FROM BLOG WHERE AND title like ‘someTitle’,这显然也是个错误的语句。

为了解决上面的问题,你可以使用 where 元素,就像下面这样:
<select id="findActiveBlogLike" resultType="Blog">
    SELECT * FROM BLOG
    <where>
        <if test="state != null">
            state = #{state}
        </if>
        <if test="title != null">
            AND title like #{title}
        </if>
        <if test="author != null and author.name != null">
            AND author_name like #{author.name}
        </if>
    </where>
</select>

有了 where 元素,你不必专门再写WHERE关键字,它会在至少一个子元素条件满足的情况下插入到SQL语句中,甚至你开头写的是 “AND” 或 “OR”,where 元素也会将它们去掉。

3.2 set

类似于 where 元素,用在 update 语句中的动态解决方案叫做 set,看如下例:
<update id="updateAuthorIfNecessary">
    update Author
    <set>
        <if test="username != null">username=#{username},</if>
        <if test="password != null">password=#{password},</if>
        <if test="email != null">email=#{email},</if>
        <if test="bio != null">bio=#{bio}</if>
    </set>
    where id=#{id}
</update>

如上例,set 元素会动态前置 SET 关键字,同时会删掉无关的逗号(比如只有第二个 if 满足条件的时候)

3.3 trim

trim 元素则是一个能够灵活设置的元素,你甚至可以通过 trim 实现和 where 或 set 元素相同的功能。trim 元素有四个属性:
  • prefix
  • prefixOverride
  • suffix
  • suffixOverride

prefix 表示 “在前置添加的内容”,prefixOverride 则表示 “覆盖掉前置内容并添加prefix的内容”,注意,这些属性的作用都是当trim标签内有SQL语句内容时才会触发。suffix 同理,不过是针对后置的。

多说无益,来看例子:
<select id="findActiveBlogLike" resultType="Blog">
    SELECT * FROM BLOG
    <trim prefix="WHERE" prefixOverrides="AND |OR">
        <if test="state != null">
            state = #{state}
        </if>
        <if test="title != null">
            AND title like #{title}
        </if>
        <if test="author != null and author.name != null">
            AND author_name like #{author.name}
        </if>
    </trim>
</select>

上例中trim的使用结果就是,它等同于 where 元素的效果,即:当有SQL语句出现(如上例有 if 满足),前置内容会添加 “WHERE”,如果遇到前置内容是 “AND ” 或者 “OR” 开头,则先覆盖掉前置内容(也即删掉),再添加 prefix 的内容 “WHERE”。

同理,我们也可以利用 trim 的 prefix 和 suffixOverride 来实现 set 元素的效果:
<update id="updateAuthorIfNecessary">
    update Author
    <trim prefix="SET" suffixOverrides=",">
        <if test="username != null">username=#{username},</if>
        <if test="password != null">password=#{password},</if>
        <if test="email != null">email=#{email},</if>
        <if test="bio != null">bio=#{bio}</if>
    </trim>
    where id=#{id}
</update>

4、foreach

动态SQL还可以对集合进行遍历,这在构建IN条件语句,或者是批量插入内容时极为常见:
<select id="selectPostIn" resultType="domain.blog.Post">
    SELECT *
    FROM POST P
    WHERE ID in
    <foreach item="item" index="index" collection="list" open="(" separator="," close=")">
        #{item}
    </foreach>
</select>

其中:
  • item - 当前迭代的元素
  • index - 当前迭代的次数(如果集合是Map,则index为键)
  • open - 开头字符串
  • separator - 元素间的分隔符
  • close - 结尾字符串

如上最终会形成诸如 “... WHERE ID in ( 10, 12, 23, 35, 99 )” 形式的SQL语句。 


posted @ 2018-07-20 12:00  Dulk  阅读(218)  评论(0编辑  收藏  举报