深入了解 MyBatis :动态 SQL

这是我的 MyBatis 学习笔记的第四篇:深入了解 MyBatis 的动态 SQL 。

相信自己使用过拼接字符串的方式来实现动态 SQL 的读者都知道,那简直是个噩梦,因为要处理很多边缘逻辑。

例如当没有条件的时候要消除 “WHERE” ,第一个被查询的条件不能拼接 “AND “ | “OR “ ,拼接一个列表的时候要消除最后一个 “,” … 但是使用 MyBatis 却十分简单。


if

使用动态 SQL 最常见情景是根据条件包含 WHERE 子句的一部分。比如:

1
2
3
4
5
6
7
8
<select id="listGoodsWithTitle" resultType="Goods">
SELECT *
FROM t_goods
WHERE is_deleted = 0
<if test="title != null">
AND title = #{title}
</if>
</select>

这条语句提供了可选的查找文本功能。

如果不传入 “title”,那么所有处于 is_deleted = 0 状态的 goods 都会返回;

如果传入了 “title” 参数,那么就会对 “title” 一列进行查找并返回对应的 goods 结果。

当然也支持多条件查询:

1
2
3
4
5
6
7
8
9
10
11
<select id="listGoodsWithTitleAndSeller" resultType="Goods">
SELECT *
FROM t_goods
WHERE is_deleted = 0
<if test="title != null">
AND title = #{title}
</if>
<if test="seller != null and seller.name != null">
AND seller_name = #{seller.name}
</if>
</select>

这时候两个参数同样都是可选的,如果只有 “title” 或者 “seller” 则只会对传入 “title” 或者 “seller” 进行查询,如果同时传入的则会同时作为查询条件。


choose (when, otherwise)

有时候,我们不想使用所有的条件,而只是想从多个条件中选择一个使用。

针对这种情况,MyBatis 提供了 choose 元素,它有点像 Java 中的 switch 语句。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<select id="listGoodsWithTitleAndSeller" resultType="Goods">
SELECT *
FROM t_goods
WHERE is_deleted = 0
<choose>
<when test="title != null">
AND title = #{title}
</when>
<when test="seller != null and seller.name != null">
AND seller_name = #{seller.name}
</when>
<otherwise>
AND featured = 1
</otherwise>
</choose>
</select>

这时候策略变为:传入了 “title” 就按 “title” 查找,传入了 “seller” 就按 “seller” 查找的情形。若两者都没有传入,就返回标记为 featured 的 goods。


trim (where, set)

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<select id="listGoodsWithTitleAndSeller" resultType="Goods">
SELECT *
FROM t_goods
WHERE
<if test="is_deleted != null">
is_deleted = #{is_deleted}
</if>
<if test="title != null">
AND title = #{title}
</if>
<if test="seller != null and seller.name != null">
AND seller_name = #{seller.name}
</if>
</select>

如果没有匹配的条件会怎么样?最终这条 SQL 会变成这样:

1
2
3
SELECT * 
FROM t_goods
WHERE

这会导致查询失败。如果匹配的只是第二个条件又会怎样?这条 SQL 会是这样:

1
2
3
4
SELECT * 
FROM t_goods
WHERE
AND title = 'someTitle'

这个查询也会失败。这个问题不能简单地用 if 条件元素来解决。这就是我们刚开始提到的字符串拼接动态 SQL 的问题。

MyBatis 有一个简单且适合大多数场景的解决办法:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<select id="listGoodsWithTitleAndSeller" resultType="Goods">
SELECT *
FROM t_goods
<where>
<if test="is_deleted != null">
is_deleted = #{is_deleted}
</if>
<if test="title != null">
AND title = #{title}
</if>
<if test="seller != null and seller.name != null">
AND seller_name = #{seller.name}
</if>
</where>
</select>

WHERE 元素只会在子元素返回任何内容的情况下才插入 WHERE 子句。而且,若子句的开头为 “AND” 或 “OR”,WHERE 元素也会将它们去除。

如果 WHERE 元素与你期望的不太一样,你也可以通过自定义 trim 元素来定制 WHERE 元素的功能。比如,和 WHERE 元素等价的自定义 trim 元素为:

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

可以解读为如果 trim 里面有内容返回则先插入一个 WHERE ,然后将第一个返回的内容头部的 “AND “ 或者 “OR “ 删除(注意此例中的空格是必要的)。

用于动态更新语句的类似解决方案叫做 setset 元素可以用于动态包含需要更新的列,忽略其它不更新的列。比如:

1
2
3
4
5
6
7
8
9
<update id="updateAccount">
UPDATE t_account
<set>
<if test="username != null">username=#{username},</if>
<if test="password != null">password=#{password},</if>
<if test="email != null">email=#{email}</if>
</set>
WHERE id = #{id}
</update>

这个例子中,set 元素会动态地在行首插入 SET 关键字,并会删掉额外的逗号(这些逗号是在使用条件语句给列赋值时引入的)。

来看看与 set 元素等价的自定义 trim 元素吧:

1
2
3
<trim prefix="SET" suffixOverrides=",">
...
</trim>

注意,我们覆盖了后缀值设置,并且自定义了前缀值。


foreach

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

1
2
3
4
5
6
7
8
9
10
<select id="listGoodsWithTags" resultType="Goods">
SELECT *
FROM t_goods
WHERE tags
IN
<foreach item="item" index="index" collection="list"
open="(" separator="," close=")">
#{item}
</foreach>
</select>

foreach 元素可以使用任何可迭代对象,包括单列集合类(如 List、Set 等)和双列集合类(如 Map、 Map.Entry 对象的集合 )。

当使用单列集合类时:index 是当前迭代的序号,item 的值是本次迭代获取到的元素。

当使用双列集合类时:index 是键,item 是值。


多数据库支持

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<insert id="insert">
<selectKey keyProperty="id" resultType="int" order="BEFORE">
<if test="_databaseId == 'oracle'">
SELECT seq_users.nextval
FROM DUAL
</if>
<if test="_databaseId == 'mysql'">
SELECT nextval
FOR seq_users
FROM sysibm.sysdummy1
</if>
</selectKey>
INSERT INTO users VALUES (#{id}, #{name})
</insert>


使用注解

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

1
2
3
4
5
6
7
8
9
10
@Update({"<script>",
"update t_account",
" <set>",
" <if test='username != null'>username=#{username},</if>",
" <if test='password != null'>password=#{password},</if>",
" <if test='email != null'>email=#{email},</if>",
" </set>",
"where id = #{id}",
"</script>"})
void updateAccount(Account account);


在 Java 代码里嵌入 SQL

如果我们实在需要在 Java 中嵌入 SQL ,MyBatis也提供了支持。

先来看看不使用 MyBatis,我们会怎么做:

1
2
3
4
String sql = "SELECT account, email"
"FROM t_account" +
"WHERE is_deleted = 0" +
"AND balance > 100";

当 SQL 变复杂,涉及动态查询条件的时候还是会遇到我们刚开始说的各种问题,来看看如果是 MyBatis 该如何实现。

1
2
3
4
5
6
7
8
private String selectRichAccountSql() {
return new SQL(){{
SELECT("account, email, firstName, lastName");
FROM("t_account");
WHERE("is_deleted = 0");
WHERE("balance > 100");
}}.toString();
}

多个 WHERE 之前默认会添加 AND ,也可以在多个查询条件之间显示调用 AND 或者 OR 。这和使用 XML 动态 SQL 一样智能。

这是使用匿名内部类的方式,会创建一个 SQL 类的子类。除了使用匿名内部类的方式,还有以下几种方式:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
// 匿名内部类
public String deleteAccountSql(final Integer id) {
return new SQL() {{
DELETE_FROM("t_account");
WHERE("id = #{id}");
}}.toString();
}

// 由于执行的每个方法默认都会返回 this ,所以还可以使用链式调用风格
public String insertAccountSql(final Integer id,
final String firstName,
final String lastName) {
String sql = new SQL()
.INSERT_INTO("t_account")
.VALUES("id, firstName", "#{id}, #{firstName}")
.VALUES("lastName", "#{lastName}")
.toString();
return sql;
}

// 动态查询条件
public String selectAccountSql(final String email,
final String firstName,
final String lastName) {
return new SQL() {{
SELECT("account, email, firstName, lastName");
FROM("t_account");
WHERE("is_deleted = 0");
if (email != null) {
WHERE("email LIKE #{email}");
}
if (firstName != null) {
WHERE("firstName LIKE #{firstName}");
}
if (lastName != null) {
WHERE("lastName LIKE #{lastName}");
}
ORDER_BY("email");
}}.toString();
}

这里所调用的和 SQL 关键字同名的方法都是来自于 SQL 的父类 AbstractSQL :

方法 描述
SELECT(String)
SELECT(String...)
开始新的或追加到已有的 SELECT子句。
可以被多次调用,参数会被追加到 SELECT 子句。
参数通常使用逗号分隔的列名和别名列表。
SELECT_DISTINCT(String)
SELECT_DISTINCT(String...)
开始新的或追加到已有的 SELECT子句,并添加 DISTINCT 关键字到生成的查询中。
可以被多次调用,参数会被追加到 SELECT 子句。
参数通常使用逗号分隔的列名和别名列表。
FROM(String)
FROM(String...)
开始新的或追加到已有的 FROM子句。
可以被多次调用,参数会被追加到 FROM子句。
参数通常是一个表名或别名。
JOIN(String)
JOIN(String...)
INNER_JOIN(String)
INNER_JOIN(String...)
LEFT_OUTER_JOIN(String)
LEFT_OUTER_JOIN(String...)
RIGHT_OUTER_JOIN(String)
RIGHT_OUTER_JOIN(String...)
基于调用的方法,添加新的合适类型的 JOIN 子句。
参数可以包含一个由列和连接条件构成的标准连接。
WHERE(String)
WHERE(String...)
插入新的 WHERE 子句条件,并使用 AND 拼接。
可以被多次调用,对于每一次调用产生的新条件,会使用 AND 拼接起来。
要使用 OR 分隔,请使用 OR()
OR() 使用 OR 来分隔当前的 WHERE 子句条件。
可以被多次调用,但在一行中多次调用会生成错误的 SQL
AND() 使用 AND 来分隔当前的 WHERE子句条件。
可以被多次调用,但在一行中多次调用会生成错误的 SQL
由于 WHEREHAVING都会自动使用 AND 拼接, 因此这个方法并不常用,只是为了完整性才被定义出来。
GROUP_BY(String)
GROUP_BY(String...)
追加新的 GROUP BY 子句,使用逗号拼接。
可以被多次调用,每次调用都会使用逗号将新的条件拼接起来。
HAVING(String)
HAVING(String...)
追加新的 HAVING 子句,使用 AND 拼接。
可以被多次调用,每次调用都使用AND来拼接新的条件。要使用 OR 分隔,请使用 OR()
ORDER_BY(String)
ORDER_BY(String...)
追加新的 ORDER BY 子句,使用逗号拼接。
可以多次被调用,每次调用会使用逗号拼接新的条件。
LIMIT(String)
LIMIT(int)
追加新的 LIMIT 子句。
仅在 SELECT()、UPDATE()、DELETE() 时有效。
当在 SELECT() 中使用时,应该配合 OFFSET() 使用。
OFFSET(String)
OFFSET(long)
追加新的 OFFSET 子句。 仅在 SELECT() 时有效。
当在 SELECT() 时使用时,应该配合 LIMIT() 使用。
FETCH_FIRST_ROWS_ONLY(String)
FETCH_FIRST_ROWS_ONLY(int)
追加新的 FETCH FIRST n ROWS ONLY 子句。
仅在 SELECT() 时有效。 该方法应该配合 OFFSET_ROWS() 使用。
DELETE_FROM(String) 开始新的 delete 语句,并指定删除表的表名。
通常它后面都会跟着一个 WHERE 子句。
INSERT_INTO(String) 开始新的 insert 语句,并指定插入数据表的表名。
后面应该会跟着一个或多个 VALUES() 调用,或 INTO_COLUMNS() 和 INTO_VALUES() 调用。
VALUES(String, String) 追加数据值到 insert 语句中。
第一个参数是数据插入的列名,第二个参数则是数据值。
可以使用一个 VALUES 来插入多列数据,列名使用逗号分隔
e.g. VALUES(“id, account”, “#{id}, #{account}”)
INTO_COLUMNS(String...) 追加插入列子句到 insert 语句中。
应与 INTO_VALUES() 一同使用。
INTO_VALUES(String...) 追加插入值子句到 insert 语句中。
应与 INTO_COLUMNS() 一同使用。
UPDATE(String) 开始新的 update 语句,并指定更新表的表名。
后面都会跟着一个或多个 SET() 调用,通常也会有一个 WHERE() 调用。
SET(String)
SET(String...)
对 update 语句追加 “set” 属性的列表
ADD_ROW() 添加新的一行数据,以便执行批量插入。

注意:SQL 类将原样插入 LIMITOFFSETOFFSET n ROWS 以及 FETCH FIRST n ROWS ONLY 子句。

换句话说,类库不会为不支持这些子句的数据库执行任何转换。

因此,用户应该要了解目标数据库是否支持这些子句。如果目标数据库不支持这些子句,产生的 SQL 可能会引起运行错误。


相关阅读

MyBatis 快速入门

深入了解 MyBatis :详解配置

深入了解 MyBatis :ResultMap

深入了解 MyBatis :缓存

更值得其他语言开发者看的《阿里Java开发手册》 为什么说高并发越来越重要了

评论

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×