mybatis动态SQL

一、简介

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.

MyBatis最强大的特性之一就是它的动态SQL功能。如果您有JDBC或任何类似框架的经验，您就会理解有条件地将SQL字符串连接在一起是多么痛苦，确保不要忘记空格或省略列列表末尾的逗号。动态SQL处理起来非常痛苦。

MyBatis的动态SQL技术就是为了解决该问题而生。

测试前的准备

在mysql中有一张名为t_emp的表，各字段如下：

我们根据该表格生成了对应的实体类Emp

在mybatis的全部配置文件中设置了字段与实体类的对应规则

	<settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

创建了对应的Mapper接口以及Mapper.xml

二、if和where

• where标签会自动去掉“标签体内前面、后面多余的and/or
• 使用if标签，让我们可以有选择的加入SQL语句的片段。这个SQL语句片段是否要加入整个SQL语句，就看if标签判断的结果是否为true
• 在if标签的test属性中，可以访问实体类的属性，不可以访问数据库表的字段

<select id="selectEmpByCondition" resultType="com.zengchuiyu.mybatis.entity.Emp">
        select emp_id,emp_name,emp_salary 
        from t_emp
        <where>
            <if test="empName != null">
            <!-- 在if标签内部，需要访问接口的参数时还是正常写#{} -->
                or emp_name = #{empName}
            </if>
            <if test="empSalary &gt; 2000">
                or emp_salary > #{empSalary}
            </if>
        </where>
 </select>

结果：
第一种情况：所有条件都满足 WHERE emp_name=? or emp_salary>?
第二种情况：部分条件满足 WHERE emp_salary>? 或 where emp_name = ?
第三种情况：所有条件都不满足 没有where子句

三、set标签

使用场景：

在实际开发过程中，对一个实体类的更新往往不是更新所有的字段，而是更新一部分字段。此时页面上的表单往往不会提供不修改字段的表单项。
所以此时需要我们在Mapper配置文件中，对update语句的set子句进行定制，此时就可以使用动态SQL的set标签。

配置方式：

<update id="updateEmpDynamic">
        update t_emp
        <!--set emp_name = #{empName},emp_salary = #{empSalary}-->
        <set>
            <if test="empName != null">
                emp_name = #{empName},
            </if>
            <if test="empSalary &lt; 3000">
                emp_salary = #{empSalary},
            </if>
        </set>
        where emp_id = #{empId}
    </update>

• 使用set标签动态管理set子句，并且动态去掉两端多余的逗号

第一种情况：所有条件都满足 SET emp_name=?, emp_salary=?
第二种情况：部分条件满足 SET emp_salary=? 或 set emp_name = ?
第三种情况：所有条件都不满足 update t_emp where emp_id=?
没有set子句的update语句会导致SQL语法错误

四、trim标签

使用trim标签控制条件部分两端是否包含某些字符

• prefix属性：指定要动态添加的前缀

• suffix属性：指定要动态添加的后缀

• prefixOverrides属性：指定要动态去掉的前缀，使用“|”分隔有可能的多个值

• suffixOverrides属性：指定要动态去掉的后缀，使用“|”分隔有可能的多个值

<select id="selectEmpByConditionByTrim" resultType="com.zengchuiyu.mybatis.entity.Emp">
        select emp_id,emp_name,emp_salary 
        from t_emp
        <trim prefix="where" suffixOverrides="and|or">
            <if test="empName != null">
                emp_name = #{empName} and
            </if>
            <if test="empSalary &gt; 3000">
                emp_salary > #{empSalary} and
            </if>
        </trim>
    </select>
    <!-- 当前例子用where标签实现更简洁，但是trim标签更灵活，可以用在任何有需要的地方 -->

五、choose/when/otherwise

这组标签的作用是在多个分支条件中，仅执行一个。（可以类比java中的switch结构）

<select id="selectEmpByConditionByChoose" resultType="com.zengchuiyu.mybatis.entity.Emp">
        <include refid="mySelectSql"/>
        where
        <choose>
            <when test="empName != null">emp_name = #{empName}</when>
            <when test="empSalary &lt; 3000">emp_salary &lt; 3000</when>
            <otherwise>1 = 1</otherwise>
        </choose>
    </select>

结果：
第一种情况：第一个when满足条件 where emp_name=?
第二种情况：第二个when满足条件 where emp_salary < 3000
第三种情况：两个when都不满足 where 1=1 执行了otherwise

六、foreach标签

foreach标签是用来批量操作SQL语句，它有以下属性：

• collection属性：要遍历的集合
• item属性：遍历集合的过程中能得到每一个具体对象，在item属性中设置一个名字，将来通过这个名字引用遍历出来的对象
• separator属性：指定当foreach标签的标签体重复拼接字符串时，各个标签体字符串之间的分隔符
• open属性：指定整个循环把字符串拼好后，字符串整体的前面要添加的字符串
• close属性：指定整个循环把字符串拼好后，字符串整体的后面要添加的字符串
• index属性：这里起一个名字，便于后面引用
  遍历List集合，这里能够得到List集合的索引值
  遍历Map集合，这里能够得到Map集合的key

以批量插入为例：

<insert id="batchInsertEmp">
        insert into t_emp(emp_name,emp_salary)
        <foreach collection="empList" item="emp" separator="," open="values" index="index" close=";">
            (#{emp.empName},#{emp.empSalary})
        </foreach>
</insert>

注意：在foreach标签内部如果需要引用遍历得到的具体的一个对象，需要使用item属性声明的名称

拼装效果：

Preparing: insert into t_emp(emp_name,emp_salary) values (?,?) , (?,?) , (?,?) , (?,?) , (?,?) , (?,?) , (?,?) , (?,?) , (?,?) , (?,?) ;

批量更新时需要注意点

上面批量插入的例子本质上是一条SQL语句，而实现批量更新则需要多条SQL语句拼起来，用分号分开。也就是一次性发送多条SQL语句让数据库执行。此时需要在数据库连接信息的URL地址中设置

url=jdbc:mysql://localhost:3306/mybatis-example?allowMultiQueries=true

对应的foreach标签如下：

<update id="updateEmplBatch">
        <foreach collection="empList" item="emp" separator=";">
            update t_emp
            set emp_name = #{emp.empName},emp_salary = #{emp.empSalary}
            where emp_id = #{emp.empId}
        </foreach>
    </update>

效果：

Preparing: update t_emp set emp_name = ?,emp_salary = ? where emp_id = ? ; update t_emp set emp_name = ?,emp_salary = ? where emp_id = ? ;

关于foreach标签的collection属性

如果没有给接口中List类型的参数使用@Param注解指定一个具体的名字，那么在collection属性中默认可以使用collection或list来引用这个list集合。这一点可以通过异常信息看出来：

Cause: org.apache.ibatis.binding.BindingException: Parameter ‘empList’ not found. Available parameters are [arg0, collection, list]

七、sql标签

1. 抽取重复的SQL片段

<!-- 使用sql标签抽取重复出现的SQL片段 -->
<sql id="mySelectSql">
      select emp_id,emp_name,emp_salary 
      from t_emp
</sql>

2. 引用已抽取的SQL片段

 <!-- 使用include标签引用声明的SQL片段 -->
<include refid="mySelectSql"/>