Mybatis中动态SQL详解

以前在使用JDBC操作数据时，如果查询条件特别多，将条件串联成SQL字符串是一件痛苦的事情。通常的解决方法是写很多的if-else条件语句对字符串进行拼接，并确保不能忘了空格或在字段的最后省略逗号。MyBatis使用动态SQL来改善这种情形，动态SQL是基于OGNL的表达式，可方便我们在SQL语句中实现某些逻辑，简单说主要是为了解决参数不固定情况。用于实现动态SQL的元素如下。

• if：利用if实现简单的条件选择
• choose（when，otherwise）：相当于Java中的switch语句，通常与when和otherwise搭配使用
• set：解决动态更新语句
• trim：可以灵活的去除多余的关键字
• foreach：迭代一个集合，通常用于in条件

set 标签

使用set标签可以将动态的配置 SET 关键字，并剔除追加到条件末尾的任何不相关的逗号。使用 if+set 标签修改后，在进行表单更新的操作中，哪个字段中有值才去更新，如果某项为 null 则不进行更新，而是保持数据库原值。

1 2 3 4 5 6 7 8 9 10 11 12

<update id="updateSet" parameterType="com.dl.POJO.User">      update user      <set>          <if test="name != null and name != ''">              name = #{name},          </if>          <if test="county != null and county != ''">              county = #{county},          </if>      </set>      where id = #{id}  </update>

大多数标签我们都是结合在一起使用的，常见主要是为了解决参数不固定情况。

2.1 if 和 where 标签

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

<sql id="params">   id, name,country,sex,birth,tel </sql> <select id="selectByConditions"  resultType="com.dl.POJO.User">   select <include refid="params"/> from user   <where>       <if test="id != null and id != 0">           AND id = #{id}       </if>       <if test="name != null and name != ''">           AND name = #{name}       </if>       <if test="county != null and county != ''">           AND county = #{county}       </if>   </where> </select>

<include refid="params"/>其实就是你需要查询的sql字段，用一个sql标签封装起来，以后直接引用即可。

一般开发列表业务的查询条件时，参数通常不固定，如果有多个查询条件，通常会使用<where> <if>标签来进行控制。

where标签会自动标识其标签内是否有返回值，若有，就插入一个where。 <where>标签可以

自动的将第一个满足的条件前面的逻辑运算符 (or ,and) 去掉，什么意思呢，我们简单看一下：

假设此时我们传入的参数为：id=0 name="童话" country="中国"

观察传入的参数，id传入0，此条件不会执行。name传入一个合法值，此时name条件成立，但是前面多了一个and关键字，此时<where>标签会自动去掉这个and关键字。

当然，有的人可能会说，后面直接给个where 1=1不就可以不用<where>标签了么。如下所示：

1 2 3 4 5 6 7

<select id="findActiveBlogLike" resultType="Blog">     select * from blog     where 1=1    <if test="state != null">      and state = #{state}    </if> </select>

当然，这样肯定是能实现你所想的功能的做法，但这个东西也会带来一个巨坑，但是这种做法有一个最大的弊端，如果表有索引的话，会导致数据表上的索引失效。这样查询得效率显然就会很低下，就像 ${} 和 #{} 多数情况下可以通用，但是${}就是不能防止SQL注入问题。因此我们还是采用 <where> 标签比较舒适。

如果 where 元素不能满足你的要求，你也可以通过自定义 trim 元素来定制 where 元素的功能。

2.2 trim和if标签

mybatis的trim标签一般用于去除sql语句中多余的and关键字，逗号，或者给sql语句前拼接 where 、 set 以及 values( 等前缀，或者添加“)“等后缀，可用于选择性插入、更新、删除或者条件查询等操作。

主要有4个属性：

属性	描述
prefix	给sql语句拼接的前缀
suffix	给sql语句拼接的后缀
prefixOverrides	去除sql语句前面的关键字或者字符，该关键字或者字符由prefixOverrides属性指定，假设该属性指定为"AND"，当sql语句的开头为"AND"，trim标签将会去除该"AND"
suffixOverrides	去除sql语句后面的关键字或者字符，该关键字或者字符由suffixOverrides属性指定

举个栗子，和 where 元素等价的自定义 trim 元素为：

1 2 3 4 5 6 7 8 9 10 11 12 13 14

<select id="getUserList" resultType="com.dl.POJO.User">   select <include refid="params"/> from user   <trim prefix="where" prefixOverrides="and | or" suffix="order by id">       <if test="id != null and id != 0">           AND id = #{id}       </if>       <if test="name != null and name != ''">           AND name = #{name}       </if>       <if test="county != null and county != ''">           AND county = #{county}       </if>   </trim> </select>

上面我们多添加了一个 suffix="order by id" 来实现按照某种规则排序，下面再看一个稍微复杂一点的栗子。

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

<insert id="insertSelective" parameterType="com.dl.blog.pojo.Blog">   insert into blog   <trim prefix="(" suffix=")" suffixOverrides=",">     <if test="id != null">       id,     </if>     <if test="title != null">       title,     </if>     <if test="views != null">       views,     </if>     <if test="createTime != null">       create_time,     </if>   </trim>   <trim prefix="values (" suffix=")" suffixOverrides=",">     <if test="id != null">       #{id,jdbcType=INTEGER},     </if>     <if test="title != null">       #{title,jdbcType=VARCHAR},     </if>     <if test="views != null">       #{views,jdbcType=INTEGER},     </if>     <if test="createTime != null">       #{createTime,jdbcType=TIMESTAMP},     </if>   </trim> </insert>

其中最重要的就是 suffixOverrides="," ,表示去除sql语句结尾多余的逗号。 假设上述条件只匹配上前三个，可以看到我们的sql将变成

1	insert into blog(id,title,views,) values(1,"标题",22,)

此时插入将会失败。使用trim标签就可以轻松解决此问题。

2.3 foreach 标签

foreach标签主要有以下参数：

属性	描述
item	循环体中的具体对象。支持属性的点路径访问，如item.age,item.info.details，在list和数组中是其中的对象，在map中是value。
index	在list和数组中,index是元素的序号，在map中，index是元素的key，该参数可选。
open	表示该语句以什么开始
close	表示该语句以什么结束
separator	表示元素之间的分隔符，例如在in()的时候，separator=","会自动在元素中间用“,“隔开，避免手动输入逗号导致sql错误，如in(1,2,)这样。该参数可选。

栗子1：进行list批量插入

1 2 3 4 5 6 7

<insert id="dynamicSqlInsertList" useGeneratedKeys="true" keyProperty="id" parameterType="java.util.List">     insert into users (name, age, county, date)     values     <foreach collection="list" item="user" separator="," >         (#{user.name}, #{user.age}, #{user.county}, #{user.date})     </foreach> </insert>

特别注意：Mysql默认接受sql的大小是 1048576(1M)， 即第三种方式若数据量超过1M会报如下异常：（可通过调整MySQL安装目录下的 my.ini 文件中 [mysqld] 段的 max_allowed_packet = 1M ）

useGeneratedKeys="true" keyProperty="id" 是用于插入之后返回自增主键的id的

从结果可以看出，我们一下插入了两条数据，每条数据之间使用 “，” 逗号进行分割， separator="," 的作用就是如此。其中 < foreach > 标签内部的属性务必加上 item。

与上面批量插入对应的mapper代码如下：

1	int insert(List<User> userList);

当然，其实说到批量插入，最粗暴方法就是for循环直接插入，但是数据量越多,耗费的时间将是巨大的。 

记录条数	普通for循环	Mybatis batch	foreach标签
500条	7742	7388	622
1000条	15290	15078	746
5000条	78011	177350	1172
10000条	397472	201180	1205

栗子2：list集合参数

1 2 3 4 5 6

<select id="dynamicSqlSelectList" resultType="com.dl.POJO.User">      SELECT * from user WHERE id in      <foreach collection="list" item="id" open="(" close=")" separator="," >          #{id}      </foreach>  </select>

可以看出我们的 SQL 语句新增了：( ? , ? ) ，前后的括号由 open="(" close=")" 进行控制，用 “？” 占位符占位，并通过separator以：“，”隔开，内部两个循环遍历出的元素。

栗子3：map参数 < map> 标签需要结合MyBatis的参数注解 @Param() 来使用，需要告诉Mybatis配置文件中的 collection="map" 里的map是一个参数：

1 2 3 4 5 6

<select id="dynamicSqlSelectMap" resultType="com.lks.bean.User">      select * from users WHERE      <foreach collection="map" index="key" item="value"  separator="=">          ${key} = #{value}      </foreach>  </select>

2.4 choose、when、otherwise 标签

这三个标签需要组合在一起使用，类似于 Java 中的 switch、case、default。只有一个条件生效，也就是只执行满足的条件 when，没有满足的条件就执行 otherwise，表示默认条件。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

<select id="dynamicSql2" resultType="com.lks.domain.User">     select * from users     <where>         <choose>             <when test="name != null and name != ''">                 AND name = #{name}             </when>             <when test="county != null and county != ''">                 AND county = #{county}             </when>             <otherwise>                 AND id = #{id}             </otherwise>         </choose>     </where> </select>

即使同时添加name和county的值，最终的sql也只会添加第一个属性值。

三、总结

使用批量插入执行的SQL语句应该等价于：

1	insert into users(name, age, county, date) values (?,?,?,? ),(?,?,?,? ),(?,?,?,? ),(?,?,?,?)

在使用foreach的时候最关键的也是最容易出错的就是collection属性，该属性是必须指定的，但是在不同情况 下，该属性的值是不一样的，主要有一下3种情况：

1.如果传入的是单参数且参数类型是一个List的时候，collection属性值为list

2.如果传入的是单参数且参数类型是一个array数组的时候，collection的属性值为array

3.如果传入的参数是多个的时候，我们就需要把它们封装成一个Map了，当然单参数也可以封装成map