Mybatis(三)动态SQL
动态 SQL 是 MyBatis 的强大特性之一。
1、主要的动态SQL元素
| 元素 | 作用 | 备注 |
|---|---|---|
| if | 判断语句 | 单条件分支判断 |
| choose（when、otherwise） | 选择语句，类似case when语句 | 多条件分支判断 |
| trim（where、set） | 辅助元素 | 处理SQL拼装问题 |
| foreach | 循环控制语句 | 在in语句等列举条件差用 |
2、if 元素
使用动态 SQL 最常见情景是根据条件包含 where 子句的一部分。还是以 EmployeeMapper.xml文件为例：
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ssm.web.demo.dao.emp.EmployeeMapper">
<select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultType="com.ssm.web.demo.entity.emp.Employee">
select
emp_id as empId, emp_name as empName,email as email
from tbl_emp
where 1=1
<if test="empName != null and empName!='' ">
and emp_name like #{empName}
</if>
</select>
</mapper>test="empName != null and empName!=''如果不传入 “empName”，那么所有Employee都会返回；如果传入了 “empName” 参数不为空且不等于空字符串，那么就会对 “empName” 一列进行模糊查找并返回对应的结果。
test属性比较常见的是判空，如要判固定值或字符串，则可以这样：
<if test="sex == '1' or sex =='MALE' ">
...
</if>你以为这样就可以了吗？看着可以，实际测试过程中还是不行的。
需要这样才可以：
<if test="sex == '1'.toString() or sex =='MALE'.toString() ">
...
</if>或者这样：
<if test=' sex == "1" or sex == "MALE" ' >
...
</if>2、choose-when-otherwise元素
示例如下：
<select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultType="com.ssm.web.demo.entity.emp.Employee">
select
emp_id as empId, emp_name as empName,email as email
from tbl_emp
where 1=1
<choose>
<when test="empId != null and empId!='' ">
and empId = #{empId}
</when>
<when test="empName != null and empName!='' ">
and emp_name like #{empName}
</when>
<otherwise>
AND emp_name is not null
</otherwise>
</choose>
</select>当编号不为空的时候使用编号查询，当编号为空，名称不为空就用名称查询，当编号和名称都为空，就默认查名称不为空的Employee的记录。
3、trim、where、set 元素
上面要写where 1=1这种条件，使用where元素就可以去掉这种条件。示例如下：
<select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultType="com.ssm.web.demo.entity.emp.Employee">
select emp_id as empId, emp_name as empName,email as email
from tbl_emp
<where>
<choose>
<when test="empId != null and empId!='' ">
and empId = #{empId}
</when>
<when test="empName != null and empName!='' ">
and emp_name like #{empName}
</when>
<otherwise>
AND emp_name is not null
</otherwise>
</choose>
</where>
</select>但是默认条件下 有个where AND emp_name is not null会多出一个and，那么使用trim就可以去掉这个多余的and，如：
<select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultType="com.ssm.web.demo.entity.emp.Employee">
select emp_id as empId, emp_name as empName,email as email
from tbl_emp
<trim prefix="where" prefixOverrides="and">
<choose>
<when test="empId != null and empId!='' ">
and empId = #{empId}
</when>
<when test="empName != null and empName!='' ">
and emp_name like #{empName}
</when>
<otherwise>
AND emp_name is not null
</otherwise>
</choose>
</trim>
</select>trim元素表示要去掉一些特殊的字符串。prefix代表语句前缀，prefixOverrides代表的是需要去掉的那种字符串。与where写法等效。
Set元素：动态地在行首插入 SET 关键字，并会删掉额外的逗号。常常用于不确定字段更新，可以只更新需要变动的字段而不是全部字段。例如：
<update id="updateByExampleSelective" parameterType="map">
update tbl_emp
<set>
<if test="record.empId != null">
emp_id = #{record.empId,jdbcType=INTEGER},
</if>
<if test="record.empName != null">
emp_name = #{record.empName,jdbcType=VARCHAR},
</if>
<if test="record.sex != null">
sex = #{record.sex,jdbcType=CHAR},
</if>
<if test="record.email != null">
email = #{record.email,jdbcType=VARCHAR},
</if>
</set>
<if test="_parameter != null">
<include refid="Update_By_Example_Where_Clause" />
</if>
</update>也可以使用trim的写法：
<trim prefix="set" prefixOverrides=",">
...
</trim>4、foreach
foreach是个循环语句，用于对集合进行遍历。
foreach允许指定一个集合，声明可以在元素体内使用的集合项（item）和索引（index）变量。它也允许你指定开头与结尾的字符串以及集合项迭代之间的分隔符。
<select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultType="com.ssm.web.demo.entity.emp.Employee">
select emp_id as empId, emp_name as empName,email as email
from tbl_emp
where emp_id in
<foreach item="item" index="index" collection="idList" open="(" separator="," close=")" >
#{item}
</foreach>
</select>属性说明：
collection 配置的是传进来的集合参数名称。可以是数组、List、Set、Map等集合。
item 配置的是循环中本次迭代的当期元素。如果是Map集合，item是Map的值value。
index 配置的是当前元素在集合的位置下标。如果是Map集合，index就是键，item是值。
open和close 配置以什么符号包装集合元素。
separator 配置的是各个元素的间隔符。
5、bind 元素
bind 元素允许你在 OGNL 表达式以外创建一个变量，并将其绑定到当前的上下文。
实际应用场景1
在实际应用中like查询，比如Mysql数据库，使用concat将参数和”%”拼接。但是Oracle和DB2数据库连接符是”||” ，SQL需要提供两种方式去实现。bind 元素可以很好的解决这种问题。比如以刚才的模糊查询为例：
<select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultType="com.ssm.web.demo.entity.emp.Employee">
<bind name="pattern" value="'%' + empName + '%'" />
select
emp_id as empId, emp_name as empName,email as email
from tbl_emp
where emp_name like #{pattern}
</select>empName就是实际传过来的参数，与通配符“%”连接之后，赋值给了pattern，下面就可以直接使用pattern变量。绑定多个同理，注意name不要重复即可。
实际应用场景2
如果配置了 databaseIdProvider，可以在动态代码中使用名为 “_databaseId” 的变量来为不同的数据库构建特定的语句。比如：
<insert id="insert">
<selectKey keyProperty="id" resultType="int" order="BEFORE">
<if test="_databaseId == 'oracle'">
select seq_users.nextval from dual
</if>
<if test="_databaseId == 'db2'">
select nextval for seq_users from sysibm.sysdummy1"
</if>
</selectKey>
insert into tbl_emp values (#{id}, #{empName},#{sex},#{email})
</insert>6、script
在带注解的映射器接口类中使用动态 SQL，可以使用 script元素。
@Update({"<script>",
"update tbl_emp",
" <set>",
" <if test='emp.empName != null'>emp_Name=#{emp.empName},</if>",
" <if test='emp.email != null'>email=#{emp.email},</if>",
" <if test='emp.sex != null'>sex=#{emp.sex}</if>",
" </set>",
"where empId=#{empId}",
"</script>"})
void updateEmployeeValues(Employee emp);7、动态 SQL 中的插入脚本语言
MyBatis 从 3.2 版本开始支持插入脚本语言，这允许你插入一种语言驱动，并基于这种语言来编写动态 SQL 查询语句。
这种情况目前没有遇到，如有需要参考Mybatis官网说明。
相关文章：
| 文章名称 |
|---|
| 《Mybatis(一)主要组件》 |
| 《Mybatis(二)配置》 |
| 《Mybatis(三)动态SQL》 |
| 《Mybtis(四)工作原理》 |
| 《Mybtis(五)Mapper映射器》 |
| 《Mybtis(六)Mapper级联》 |
