【MyBatis】学习纪要六：动态SQL

引言

动态SQL：Dynamic SQL。

本节我们来通过 MyBatis 的官方文档进行学习。

Description（描述）

官方描述如下：

One of the most powerful features of MyBatis has always been its Dynamic SQL capabilities. If you have any experience with JDBC or any similar framework, you understand how painful it is to conditionally concatenate strings of SQL together, making sure not to forget spaces or to omit a comma at the end of a list of columns. Dynamic SQL can be downright painful to deal with.

While working with Dynamic SQL will never be a party, MyBatis certainly improves the situation with a powerful Dynamic SQL language that can be used within any mapped SQL statement.

The Dynamic SQL elements should be familiar to anyone who has used JSTL or any similar XML based text processors. In previous versions of MyBatis, there were a lot of elements to know and understand. MyBatis 3 greatly improves upon this, and now there are less than half of those elements to work with. MyBatis employs powerful OGNL based expressions to eliminate most of the other elements:

● if
● choose (when, otherwise)
● trim (where, set)
● foreach

My View

上面这段话，总结起来就三点：

• MyBatis 在动态SQL这块很强大。

• 你需要会 OGNL 表达式

• if/choose/trim/foreach

OGNL

用法看这里：OGNL指南

另外，支持转义符，所以，看一下转义：

if/where/trim/choose/foreach

• if 表示 判断

  <if test="title != null">
    AND title like #{title}
  </if>

• where

我们总会在if标签里写AND，那么当第一个不存在时就会出问题：
sql: …WHERE AND …

因此 where 是解决这个问题的

  <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>

• trim

有人会说了，既然写在前面有问题，那可不可以写在后面呢？当然可以。

来看些 trim 的用法：

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

-----

<trim prefix="SET" suffixOverrides=",">
  ...
</trim>

总结一下：
prefix:前面加什么
prefixOverrides:前面忽略掉什么
suffix:后缀
suffixOverrides:后面忽略掉什么

• choose

  <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>

• foreach

遍历一个集合。

collection：指定要遍历的集合：
list类型的参数会特殊处理封装在map中，map的key就叫list
item：将当前遍历出的元素赋值给指定的变量
separator:每个元素之间的分隔符
open：遍历出所有结果拼接一个开始的字符
close:遍历出所有结果拼接一个结束的字符
index:索引。遍历list的时候是index就是索引，item就是当前值

  WHERE ID in
  <foreach item="item" index="index" collection="list"
      open="(" separator="," close=")">
        #{item}
  </foreach>

set

    <update id="testUpdate">
        UPDATE
          user
        <set>
            <if test="user.name != null">name = #{user.name},</if>
            <if test="user.age != null and user.age >= 0">age = #{user.age}</if>
        </set>
        <where>
            <if test="user.id != null and user.id > 0">id = #{user.id}</if>
        </where>
    </update>

批量插入

• 方法一：

    <insert id="testBatchInsert1">
        INSERT INTO
          user(name, age)
        VALUES
        <foreach collection="users" index="i" item="k" separator=",">
            (
            <if test="k.name != null">#{k.name}</if>
            ,
            <if test="k.age != null and k.age >= 0">#{k.age}</if>
            )
        </foreach>
    </insert>

• 方法二：

    <insert id="testBatchInsert2">

        <foreach collection="users" index="i" item="k" separator=";">
            INSERT INTO
              user(name, age)
            VALUES(
            <if test="k.name != null">#{k.name}</if>
            ,
            <if test="k.age != null and k.age >= 0">#{k.age}</if>
            )
        </foreach>
    </insert>

注意

1、数据库的url应该如下配置：

spring.datasource.url=jdbc:mysql://localhost:3306/mybatis_demo?allowMultiQueries=true

2、此方法返回的影响行数不正确。

==> Preparing: INSERT INTO user(name, age) VALUES( ? , ? ) ; INSERT INTO user(name, age) VALUES( ? , ? )
==> Parameters: n3(String), 11(Integer), n4(String), 12(Integer)
<== Updates: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@64fc097e]
rs num : 1

_parameter and _databaseId

_parameter，参数
_databaseId，当前数据库的Id

bind

关于数据绑定，MyBatis官网提供的例子如下：

<select id="selectBlogsLike" resultType="Blog">
  <bind name="pattern" value="'%' + _parameter.getTitle() + '%'" />
  SELECT * FROM BLOG
  WHERE title LIKE #{pattern}
</select>

引用sql

有些重复的，可以抽取出来

后记

测试代码：DynamicSQL-Demo

需要说明的是：为了保持测试风格，有些返回值类型不正确，导致测试报错，但这并不影响着一节的学习，因为你能够看到sql和结果。