掌握Mybatis动态SQL写法，告别根据不同条件拼接SQL语句的痛苦

最新推荐文章于 2023-05-22 21:09:00 发布

JavaCoder567

最新推荐文章于 2023-05-22 21:09:00 发布

阅读量2.5k

点赞数 1

分类专栏：个人笔记文章标签： java mybatis

本文链接：https://blog.csdn.net/qq_40151840/article/details/104697465

版权

个人笔记专栏收录该内容

31 篇文章 0 订阅

订阅专栏

掌握Mybatis动态 SQL 的写法，告别根据不同条件拼接 SQL 语句的痛苦

本文基于Mybatis官方文档，根据我自己的学习情况进行整理记录。

一、前言

MyBatis 的强大特性之一便是它的动态 SQL。如果你有使用 JDBC 或其它类似框架的经验，你就能体会到根据不同条件拼接 SQL 语句的痛苦。例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态 SQL 这一特性可以彻底摆脱这种痛苦。

虽然在以前使用动态 SQL 并非一件易事，但 MyBatis 提供了可以被用在任意 SQL 映射语句中的强大的动态 SQL 语言改善了这种情形。 MyBatis 采用功能强大的基于 OGNL 的表达式来实现动态 SQL 的使用。

二、动态SQL支持的标签

1. 标签总览

元素	作用	备注
if	根据条件语句决定是否拼接 SQL	单条件分支
choose(when,otherwise)	从多个条件语句中选择一个执行，类似 Java 中的 if else语句	多条件分支
trim,where,set	辅助元素	用于处理 SQL 拼接问题
foreach	对一个集合进行遍历，通常是在构建 IN 条件语句的时候	批量插入，更新，查询时经常用到
script	在带注解的映射器接口类中使用动态 SQL	在@Select等注解上使用
bind	创建一个变量，并绑定到上下文中	用于兼容不同的数据库，防止 SQL 注入等

2. If 标签

动态 SQL 通常要做的事情是根据条件包含 where 子句的一部分。If 标签必须结合 test 属性联合使用，比如：

<select id="findActiveBlogWithTitleLike" resultType="Blog">
  SELECT * FROM BLOG
  WHERE state = ‘ACTIVE’
  <if test="title != null">
    AND title like #{title}
  </if>
</select>

这条语句提供了一种可选的查找文本功能。如果没有传入“title”，那么所有处于“ACTIVE”状态的BLOG都会返回；反之若传入了“title”，那么就会对“title”一列进行模糊查找并返回 BLOG 结果（细心的读者可能会发现，“title”参数值是可以包含一些掩码或通配符的）。

如果希望通过“title”和“author”两个参数进行可选搜索该怎么办呢？首先，改变语句的名称让它更具实际意义；然后只要加入另一个条件即可。

<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>

3. Choose 标签

有时我们不想应用到所有的条件语句，而只想从中择其一项。针对这种情况，MyBatis 提供了 choose 元素，它有点像 Java 中的 if else语句（也可以类比 switch 语句，但我个人觉得在 switch 语句中直到 break 语句出现才会跳出，不太满足）。

还是上面的例子，但是这次变为提供了“title”就按“title”查找，提供了“author”就按“author”查找的情形，若两者都没有提供，就返回所有符合条件的 BLOG（实际情况可能是由管理员按一定策略选出 BLOG 列表，而不是返回大量无意义的随机结果）。

<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>

一个 choose 标签至少有一个 when，最多一个otherwise。同样的，when 也必须结合 test 属性联合使用。

4. Trim,where,set 标签

4.1 Where标签

前面几个例子已经解决了一个臭名昭著的动态 SQL 问题。现在回到“if”示例，这次我们将“ACTIVE = 1”也设置成动态的条件，看看会发生什么。

<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>

如果这些条件没有一个能匹配上会发生什么？最终这条 SQL 会变成这样：

SELECT * FROM BLOG
WHERE

这会导致查询失败。如果仅仅第二个条件匹配又会怎样？这条 SQL 最终会是这样:

SELECT * FROM BLOG
WHERE
AND title like ‘someTitle’

这个查询也会失败。很显然，我们要解决两个问题：

当条件都不满足时：此时 SQL 中应该要不能有 where ，否则导致出错
当 if 有条件满足时： SQL 中需要有 where，且第一个成立的 if 标签下的 and | or 等要去掉

MyBatis 有一个简单的处理，能在 90% 的情况下都会有效，那就是使用 where 标签。而在不能使用的地方，你可以自定义处理方式来令其正常工作。在if 语句外包上元素就能达到目的，如下：

<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 元素只会在至少有一个子元素的条件返回 SQL 子句的情况下才去插入“WHERE”子句。而且，若语句的开头为“AND”或“OR”，where 元素也会将它们去除。

如果 where 元素没有按正常套路出牌，我们可以通过自定义 trim 元素来定制 where 元素的功能。比如，和 where 元素等价的自定义 trim 元素为：

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

prefixOverrides 属性会忽略通过管道分隔的文本序列（注意此例中的空格也是必要的!）。它的作用是移除所有指定在 prefixOverrides 属性中的内容，并且插入 prefix 属性中指定的内容。

4.2 Set标签

类似的用于动态更新语句的元素叫做 set。**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 关键字，同时也会删掉无关的逗号，因为用了条件语句之后很可能就会在生成的 SQL 语句的后面留下这些逗号。（译者注：因为用的是“if”元素，若最后一个“if”没有匹配上而前面的匹配上，SQL 语句的最后就会有一个逗号遗留）

若你对 set 元素等价的自定义 trim 元素的代码感兴趣，那这就是它的真面目：

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

注意这里我们删去的是后缀值，同时添加了前缀值。

4.3 Trim 标签

set 和 where 其实都是 trim 标签的一种类型，该两种功能都可以使用 trim 标签进行实现。

4.3.1 trim 来表示 where

如以上的 where 标签，我们也可以写成

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

表示当 trim 中含有内容时，添加 where，且第一个为 and 或 or 时，会将其去掉。而如果没有内容，则不添加 where。

4.3.2 trim 来表示 set

相应的， set 标签可以如下表示

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

表示当 trim 中含有内容时，添加 set，且最后的内容为 , 时，会将其去掉。而没有内容，不添加 set

4.3.3 trim 的几个属性

prefix: 当 trim 元素包含有内容时，增加 prefix 所指定的前缀
prefixOverrides: 当 trim 元素包含有内容时，去除 prefixOverrides 指定的前缀
suffix: 当 trim 元素包含有内容时，增加 suffix 所指定的后缀
suffixOverrides：当 trim 元素包含有内容时，去除 suffixOverrides 指定的后缀

5. 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>

foreach 元素的功能非常强大，它允许你指定一个集合，声明可以在元素体内使用的集合项（item）和索引（index）变量。它也允许你指定开头与结尾的字符串以及在迭代结果之间放置分隔符。这个元素是很智能的，因此它不会偶然地附加多余的分隔符。

注意你可以将任何可迭代对象（如 List、Set 等）、Map 对象或者数组对象传递给 foreach 作为集合参数。

当使用可迭代对象或者数组时，index 是当前迭代的次数，item 的值是本次迭代获取的元素。
当使用 Map 对象（或者 Map.Entry 对象的集合）时，index 是键，item 是值。

6. Script 标签

要在带注解的映射器接口类中使用动态 SQL，可以使用 script 元素。比如:

    @Update({"<script>",
      "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}",
      "</script>"})
    void updateAuthorValues(Author author);

7. Bind 标签

bind 元素可以从 OGNL 表达式中创建一个变量并将其绑定到上下文。比如：

<select id="selectBlogsLike" resultType="Blog">
  <bind name="pattern" value="'%' + _parameter.getTitle() + '%'" />
  SELECT * FROM BLOG
  WHERE title LIKE #{pattern}
</select>