[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>
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语句。