𝑬𝒏𝒅𝒘𝒂𝒔 -- 详解MyBatis动态SQL标签

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

if标签

使用方法

if标签
使用test = ？来约束传入值，然后在条件为true的时候跟上需要拼接的sql语句

详细用法：在传入的title值不为空的时候，SELECT * FROM BLOG WHERE state = ‘ACTIVE’ AND title like #{title};

在传入title为空的时候，SELECT * FROM BLOG WHERE state = ‘ACTIVE’;

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

where标签

where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且，若子句的开头为 “AND” 或 “OR”，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>

上面例子会在两种情况出现异常：

当state、title、author.name为空的时候，就会导致生成的sql语句为

SELECT * FROM BLOG
WHERE

这毫无疑问是会抛出异常的，所以是不允许的。

当state为空其他都非空有值的情况，生成的sql语句为

SELECT * FROM BLOG
WHERE
AND title like ‘Title’
AND author_name like ‘author_name’

这在Where语句后又接了个AND，毫无疑问也是会报错的。解决办法就是开头说的where标签

在无返回值的时候去除WHERE子句。
若子句的开头为 “AND” 或 “OR”，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>

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>

它的作用和where很相似，也是可以

剔除当bio不为空时候，末尾多余的符号“,”；
某列值为空时候，对数据库该列不进行操作保持原值；

trim标签

trim 是一个格式化标签，可以完成或者是标记的功能。主要有4个参数：

prefix：前缀
prefixOverrides：去掉第一个and或者是or
suffix：后缀
suffixOverrides：去掉最后一个逗号，也可以是其他的标记

<select id="dynamicSqlTrim" resultType="com.lks.domain.User">
	select * from users
	<trim prefix="where" suffix="order by age" prefixOverrides="and | or" suffixOverrides=",">
		<if test="name != null and name != ''">
			AND name = #{name}
		</if>
		<if test="county != null and county != ''">
			AND county = #{county}
		</if>
	</trim>
</select>

prefix：前缀将where插入 suffix：后缀order by age prefixOverrides：去掉第一个and或者是or suffixOverrides：去掉最后一个逗号，也可以是其他的标记

最后在name和county不为空的时候得到的sql语句为

select * from users 
WHERE name = 'NAME'
AND county = 'COUNTY'
order by age

choose、when、otherwise标签

有时候，我们不想使用所有的条件，而只是想从多个条件中选择一个使用。针对这种情况，MyBatis 提供了 choose 元素，它有点像 Java 中的 switch、case、default，只有一个生效！ 按照顺序来确定哪个生效。

还是上面的例子，但是策略变为：传入了 “title” 就按 “title” 查找，没有title若传入了 “author” 就按 “author” 查找的情形。若两者都没有传入，就返回标记为 featured 的 BLOG（这可能是管理员认为，与其返回大量的无意义随机 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>

foreach标签

动态 SQL 的另一个常见使用场景是对集合进行遍历（尤其是在构建 IN 条件语句的时候）。

foreach标签主要有以下参数：

item ：循环体中的具体对象。支持属性的点路径访问，如-item.age,item.info.details，在list和数组中是其中的对象，在map中是value。
index ：在list和数组中,index是元素的序号，在map中，index是元素的key，该参数可选。
open ：表示该语句以什么开始
close ：表示该语句以什么结束
separator ：表示元素之间的分隔符，例如在in()的时候，separator=","会自动在元素中间用“,“隔开，避免手动输入逗号导致sql错误，如in(1,2,)这样。该参数可选。

<!--动态Sql: foreach标签, 批量插入-->
<insert id="dynamicSqlInsertList" useGeneratedKeys="true" keyProperty="id">
	insert into users (name, age, county, date)
	values
	<foreach collection="list" item="user" separator="," >
		(#{user.name}, #{user.age}, #{user.county}, #{user.date})
	</foreach>
</insert>

相当于

insert into users(name, age, county, date) 
values
(user.name, user.age, user.county, user.date),
(user.name, user.age, user.county, user.date)

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

相当于

SELECT *
FROM POST P
WHERE ID in
(item1, item2, item3)

open、close则是在外侧包裹着每一项item 而插入语句则是需要每个item构建好（?,?,?,?），然后用“,”分割进行插入。

bind标签

bind 元素允许你在 OGNL 表达式以外创建一个变量，并将其绑定到当前的上下文。比如：

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

多数据支持

如果配置了 databaseIdProvider，你就可以在动态代码中使用名为 “_databaseId” 的变量来为不同的数据库构建特定的语句。比如下面的例子：

<insert id="insert">
  <selectKey keyProperty="id" resultType="int" order="BEFORE">
    <if test="_databaseId == 'oracle'">
      select seq_users.nextval from dual
    </if>
    <if test="_databaseId == 'db2'">
      select nextval for seq_users from sysibm.sysdummy1"
    </if>
  </selectKey>
  insert into users values (#{id}, #{name})
</insert>

参考：

https://mybatis.org/mybatis-3/zh/dynamic-sql.html

https://blog.csdn.net/lks1139230294/article/details/87957689

end

作者：Endwas（联系作者）

发表时间：2021-02-11 11:01

转载声明：如果是转博主转载的文章，请附上原文链接

公众号转载：请在文末添加作者名字和博客地址

详解MyBatis动态SQL标签