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

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

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

#{} 与 ${}

区别

写在前面，mybatis 的 SQL 传参，可以使用#和$两种符号来绑定，但两者之间还是有区别的：

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

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

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

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

  String name = "Tom";
  name = "'" + name + "'"
  

  通常用在需要动态传入表名 或 列名 时使用（不可变参数）。例如，分表时，需要根据业务系统动态指定所要查询的表，或在order by ${columnName}语句中使用。

强调：${ } 的变量的替换阶段是在动态 SQL 解析阶段（Mybatis处理），而 #{ }的变量的替换是在 DBMS（由JDBC处理） 中。

SQL注入

如下 SQL：

select * from ${tableName} where name = ${name}

入参 tableName：user; delete user; --

SQL 动态解析之后，预编译之前的SQL 为：

select * from user; delete user; -- where name = ?;

这问题就坑大了，直接删除表了，原来的 Where 条件被注释掉了。

动态表名

<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. 非空及空字符串判断

   <if test="null != title and '' != title"> </if>
   

2. 集合或数组非空判断

   <if test="null != list and list.size() > 0"> </if>
   

3. 数值相等比较

   <if test=" null != score and score == 90"> </if>
   

4. 字符串相等判断

   <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 用法示例

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

<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 WHERE，SELECT * FROM BLOG WHERE AND title like 'someTitle'，这类问题可通过使用where 标签解决。或拼接 where 1 = 1，后面再拼条件判断。

where

where 元素知道只有在一个以上的if条件有值的情况下才去插入 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

<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

<!-- where定制 -->
<trim prefix="WHERE" prefixOverrides="AND |OR ">
  ... 
</trim>

<!-- set定制 -->
<trim prefix="SET" suffixOverrides=",">
  ...
</trim>

<!-- 修改用户信息 -->
<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 条件语句的时候。

<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 是值。