MyBatis:动态SQL与参数绑定(#{} 和 ${})
MyBatis 的强大特性之一便是它的动态 SQL,Mybatis 在对 SQL 语句进行预编译之前,会对 SQL 进行动态解析,解析为一个 BoundSql 对象,同时对动态 SQL 进行处理。
动态 SQL 主要通过OGNL
表达式实现 SQL 可以动态判断和选择参数。
#{} 与 ${}
区别
写在前面,mybatis 的 SQL 传参,可以使用#
和$
两种符号来绑定,但两者之间还是有区别的:
#{}
:解析为一个 JDBC 预编译语句(prepared statement)的参数占位符,使用?
来标识,再将值设置进去。可以较大程度防止
sql
注入,能用#{}
就不要用${}
。${}
:仅仅为一个简单的 String 替换,在动态 SQL 解析阶段进行变量替换。将传入的参数直接显示拼装成 SQL,如果传入字符串,需要在参数传入前加上引号。如下:
1
2String name = "Tom";
name = "'" + name + "'"通常用在需要动态传入表名 或 列名 时使用(不可变参数)。例如,分表时,需要根据业务系统动态指定所要查询的表,或在
order by ${columnName}
语句中使用。
强调:${ }
的变量的替换阶段是在动态 SQL 解析阶段(Mybatis处理),而 #{ }
的变量的替换是在 DBMS(由JDBC处理) 中。
SQL注入
如下 SQL:
1 | select * from ${tableName} where name = ${name} |
入参 tableName:user; delete user; --
SQL 动态解析之后,预编译之前的SQL 为:
1 | select * from user; delete user; -- where name = ?; |
这问题就坑大了,直接删除表了,原来的 Where 条件被注释掉了。
动态表名
1 | <select id="getPayOrder" resultType="payOrder" statementType="STATEMENT"> |
要实现动态调用表名和字段名,就不能使用预编译了,需指定 statementType 类型,即添加 statementType="STATEMENT"
。
statementType:告诉 MyBatis 具体使用 Statement,PreparedStatement 或者 CallableStatement
- STATEMENT:非预编译
- PREPARED:默认,预编译 或 CALLABLE 中的任意一个
动态SQL
if 判断
动态 SQL 通常要做的事情是有条件地包含 where 子句的一部分。
非空及空字符串判断
1
<if test="null != title and '' != title"> </if>
集合或数组非空判断
1
<if test="null != list and list.size() > 0"> </if>
数值相等比较
1
<if test=" null != score and score == 90"> </if>
字符串相等判断
1
2
3
4
5
6
7<if test=" 'Y'.toString() == flag"> </if>
<if test=" '0'.toString() == state"> </if>
<if test=' delFlag == "1" '> </if>
不能使用下面方式
<if test=" delFlag == '2' ">
Mybatis会将 "2" 解析为字符(java 强类型语言, '2' char 类型),而非字符串,不能做到判断的效果。if 用法示例
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16<select id="findActiveBlogWithTitleLike" resultType="Blog">
SELECT * FROM BLOG
WHERE state = ‘ACTIVE’
<if test="null != title and '' != title">
AND title like #{title}
</if>
</select>
<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>
choose(when,otherwise)
从条件查询中,只选择其中几项;类似Java中的switch
语句。
1 | <select id="findActiveBlogLike" resultType="Blog"> |
trim(where,set)
多条件下如果用 if
判断,会存在当没有条件满足时,或where
后面的条件不满足时,就会出现错误的SQL
拼接:SELECT * FROM BLOG WHERE
,SELECT * FROM BLOG WHERE AND title like 'someTitle'
,这类问题可通过使用where
解决。
where
where
元素知道只有在一个以上的if条件有值的情况下才去插入 WHERE 子句。而且,若最后的内容是 AND
或 OR
开头的,where 元素也知道如何将他们去除。
1 | <select id="findActiveBlogLike" resultType="Blog"> |
set
1 | <update id="updateAuthorIfNecessary"> |
trim
1 | <!-- where定制 --> |
1 | <!-- 修改用户信息 --> |
foreach遍历
动态 SQL 的另外一个常用的必要操作是需要对一个集合进行遍历,通常是在构建 IN
条件语句的时候。
1 | <select id="selectPostIn" resultType="domain.blog.Post"> |
foreach
元素的功能是非常强大的,它允许你指定一个集合,声明可以用在元素体内的集合项和索引变量。它也允许你指定开闭匹配的字符串以及在迭代中间放置分隔符。这个元素是很智能的,因此它不会偶然地附加多余的分隔符。
注意 你可以将任何可迭代对象(如列表、集合等)和任何的字典或者数组对象传递给 foreach
作为集合参数。当使用可迭代对象或者数组时,index
是当前迭代的次数,item
的值是本次迭代获取的元素。当使用字典(或者Map.Entry
对象的集合)时,index
是键,item
是值。
MyBatis:动态SQL与参数绑定(#{} 和 ${})