MyBatis:动态SQL与参数绑定(#{} 和 ${})

MyBatis 的强大特性之一便是它的动态 SQL,Mybatis 在对 SQL 语句进行预编译之前,会对 SQL 进行动态解析,解析为一个 BoundSql 对象,同时对动态 SQL 进行处理。

动态 SQL 主要通过OGNL表达式实现 SQL 可以动态判断和选择参数。

#{} 与 ${}

区别

写在前面,mybatisSQL 传参,可以使用#$两种符号来绑定,但两者之间还是有区别的:

  • #{}:解析为一个 JDBC 预编译语句(prepared statement)的参数占位符,使用 ?来标识,再将值设置进去。

    可以较大程度防止sql注入,能用 #{} 就不要用 ${}

  • ${}:仅仅为一个简单的 String 替换,在动态 SQL 解析阶段进行变量替换。

    将传入的参数直接显示拼装成 SQL,如果传入字符串,需要在参数传入前加上引号。如下:

    1
    2
    String 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
2
3
<select id="getPayOrder" resultType="payOrder" statementType="STATEMENT">
select ${columns} from ${tableName} where pay_id = #{payId}
</select>

要实现动态调用表名和字段名,就不能使用预编译了,需指定 statementType 类型,即添加 statementType="STATEMENT"

statementType:告诉 MyBatis 具体使用 Statement,PreparedStatement 或者 CallableStatement

  • STATEMENT:非预编译
  • PREPARED:默认,预编译 或 CALLABLE 中的任意一个

动态SQL

if 判断

动态 SQL 通常要做的事情是有条件地包含 where 子句的一部分。

  1. 非空及空字符串判断

    1
    <if test="null != title and '' != title"> </if>
  2. 集合或数组非空判断

    1
    <if test="null != list and list.size() > 0"> </if>
  3. 数值相等比较

    1
    <if test=" null != score and score == 90"> </if>
  4. 字符串相等判断

    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 类型),而非字符串,不能做到判断的效果。
  5. 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
2
3
4
5
6
7
8
9
10
11
12
13
14
<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>

trim(where,set)

多条件下如果用 if 判断,会存在当没有条件满足时,或where后面的条件不满足时,就会出现错误的SQL拼接:SELECT * FROM BLOG WHERESELECT * FROM BLOG WHERE AND title like 'someTitle',这类问题可通过使用where解决。

where

where 元素知道只有在一个以上的if条件有值的情况下才去插入 WHERE 子句。而且,若最后的内容是 ANDOR 开头的,where 元素也知道如何将他们去除。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<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

1
2
3
4
5
6
7
8
9
10
<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>

trim

1
2
3
4
5
6
7
8
9
<!-- where定制 -->
<trim prefix="WHERE" prefixOverrides="AND |OR ">
...
</trim>

<!-- set定制 -->
<trim prefix="SET" suffixOverrides=",">
...
</trim>
1
2
3
4
5
6
7
8
9
10
11
12
13
<!-- 修改用户信息 -->
<update id="updateUserInfo" parameterType="com.entity.UserInfo">
update userinfo
<trim prefix="set" suffixOverrides=",">
<if test="password != null and password != ''">
password = #{password},
</if>
<if test="userName != null and userName != ''">
userName = #{userName},
</if>
</trim>
where userUUID = #{userUUID}
</update>

foreach遍历

动态 SQL 的另外一个常用的必要操作是需要对一个集合进行遍历,通常是在构建 IN 条件语句的时候。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<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>

<insert id="insertAuthor" useGeneratedKeys="true" keyProperty="id">
insert into Author (username, password, email, bio) values
<foreach item="item" collection="list" separator=",">
(#{item.username}, #{item.password}, #{item.email}, #{item.bio})
</foreach>
</insert>

foreach 元素的功能是非常强大的,它允许你指定一个集合,声明可以用在元素体内的集合项和索引变量。它也允许你指定开闭匹配的字符串以及在迭代中间放置分隔符。这个元素是很智能的,因此它不会偶然地附加多余的分隔符。

注意 你可以将任何可迭代对象(如列表、集合等)和任何的字典或者数组对象传递给 foreach 作为集合参数。当使用可迭代对象或者数组时,index 是当前迭代的次数,item 的值是本次迭代获取的元素。当使用字典(或者Map.Entry对象的集合)时,index 是键,item 是值。

MyBatis:动态SQL与参数绑定(#{} 和 ${})

http://blog.gxitsky.com/2018/03/07/Mybatis-12-dynamic-sql/

作者

光星

发布于

2018-03-07

更新于

2022-06-17

许可协议

评论