[05] 动态SQL

时间：2022-06-15 08:57:59

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>
7
 
1
<select id="findActiveBlogWithTitleLike" resultType="Blog">
2
    SELECT * FROM BLOG
3
    WHERE state = 'ACTIVE'
4
    <if test="title != null">
5
        AND title LIKE #{title}
6
    </if>
7
</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>
9
 
1
<select id="findActiveBlogLike" resultType="Blog">
2
    SELECT * FROM BLOG WHERE state = 'ACTIVE'
3
    <if test="title != null">
4
        AND title like #{title}
5
    </if>
6
    <if test="author != null and author.name != null">
7
        AND author_name like #{author.name}
8
    </if>
9
</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>
14
 
1
<select id="findActiveBlogLike" resultType="Blog">
2
    SELECT * FROM BLOG WHERE state = 'ACTIVE'
3
    <choose>
4
        <when test="title != null">
5
            AND title like #{title}
6
        </when>
7
        <when test="author != null and author.name != null">
8
            AND author_name like #{author.name}
9
        </when>
10
        <otherwise>
11
            AND featured = 1
12
        </otherwise>
13
    </choose>
14
</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>
13
 
1
<select id="findActiveBlogLike" resultType="Blog">
2
    SELECT * FROM BLOG
3
    WHERE
4
    <if test="state != null">
5
        state = #{state}
6
    </if>
7
    <if test="title != null">
8
        AND title like #{title}
9
    </if>
10
    <if test="author != null and author.name != null">
11
        AND author_name like #{author.name}
12
    </if>
13
</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>
14
 
1
<select id="findActiveBlogLike" resultType="Blog">
2
    SELECT * FROM BLOG
3
    <where>
4
        <if test="state != null">
5
            state = #{state}
6
        </if>
7
        <if test="title != null">
8
            AND title like #{title}
9
        </if>
10
        <if test="author != null and author.name != null">
11
            AND author_name like #{author.name}
12
        </if>
13
    </where>
14
</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>
10
 
1
<update id="updateAuthorIfNecessary">
2
    update Author
3
    <set>
4
        <if test="username != null">username=#{username},</if>
5
        <if test="password != null">password=#{password},</if>
6
        <if test="email != null">email=#{email},</if>
7
        <if test="bio != null">bio=#{bio}</if>
8
    </set>
9
    where id=#{id}
10
</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>
14
 
1
<select id="findActiveBlogLike" resultType="Blog">
2
    SELECT * FROM BLOG
3
    <trim prefix="WHERE" prefixOverrides="AND |OR">
4
        <if test="state != null">
5
            state = #{state}
6
        </if>
7
        <if test="title != null">
8
            AND title like #{title}
9
        </if>
10
        <if test="author != null and author.name != null">
11
            AND author_name like #{author.name}
12
        </if>
13
    </trim>
14
</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>
x
10
 
1
<update id="updateAuthorIfNecessary">
2
    update Author
3
    <trim prefix="SET" suffixOverrides=",">
4
        <if test="username != null">username=#{username},</if>
5
        <if test="password != null">password=#{password},</if>
6
        <if test="email != null">email=#{email},</if>
7
        <if test="bio != null">bio=#{bio}</if>
8
    </trim>
9
    where id=#{id}
10
</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>
 
1
<select id="selectPostIn" resultType="domain.blog.Post">
2
    SELECT *
3
    FROM POST P
4
    WHERE ID in
5
    <foreach item="item" index="index" collection="list" open="(" separator="," close=")">
6
        #{item}
7
    </foreach>
8
</select>

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

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


相关文章