MyBatis动态SQL

MyBatis的动态SQL通过if、choose、when、otherwise、trim、where、set、foreach等标签，可组合成非常灵活的SQL语句，从而在提高SQL语句准确性的同时，大大提高开发人员的效率

1 if判断 & OGNL

可在Mapper xml配置文件使用if标签根据接口参数动态确定最终执行的SQL语句，

<select id="getEmployeeByCond" resultType="com.lnhoo.dao.Employee">
    select * from employees where
    <if test="id!=null">
        id=#{id}
    </if>
    <if test="lastName!=null and lastName!=''">
        and last_name like #{lastName}
    </if>
</select>

if标签的test属性遵循OGNL语法，可以参考：OGNL - Apache Commons OGNL - Language Guide

2 where

上面if标签的用法，如果第一个test不通过，后续执行的sql语句where后面就是and，不符合sql语法导致报错
比较简单粗暴的解决方式是直接在where后面加个1=1，

<select id="getEmployeeByCond" resultType="com.lnhoo.dao.Employee">
    select * from employees where 1=1
    <if test="id!=null">
        and id=#{id}
    </if>
    <if test="lastName!=null and lastName!=''">
        and last_name like #{lastName}
    </if>
</select>

也可以使用where标签将所有查询条件包括在内，自动将多出来的and和or去掉，

<select id="getEmployeeByCond" resultType="com.lnhoo.dao.Employee">
    select * from employees
    <where>
        <if test="id!=null">
            id=#{id}
        </if>
        <if test="lastName!=null and lastName!=''">
            and last_name like #{lastName}
        </if>
    </where>
</select>

使用where标签的时候，and应该放在条件语句之前，比如这里“and”就放在“last_name like #{lastName}”之前，如果放在后面拼接出的sql就不符合语法规则

3 trim自定义字符串前后缀

可以使用trim标签自定义字符串的前后缀，

<select id="getEmployeeByCondTrim" resultType="com.lnhoo.dao.Employee">
    select * from employees
    <trim prefix="where" suffixOverrides="and">
        <if test="id!=null">
            id=#{id} and
        </if>
        <if test="lastName!=null and lastName!=''">
            last_name like #{lastName} and
        </if>
    </trim>
</select>

在这个例子中，trim标签设置所有if条件的前缀是“where”，去掉if条件末尾的“and”

trim标签的属性包括，

属性	作用
prefix	给拼装后的字符串加一个前缀
prefixOverrides	去掉整个字符串前面的字符
suffix	给拼装后的字符串加一个后缀
suffixOverrides	去掉整个字符串后面的字符

4 choos分支选择

choose标签可以实现类似Java中switch-case结构的功能，

<select id="getEmployeeByAnyCond" resultType="com.lnhoo.dao.Employee">
    select * from employees
    <where>
        <choose>
            <when test="id!=null">
                id=#{id}
            </when>
            <when test="lastName!=null">
                last_name like #{lastName}
            </when>
            <otherwise>
                1=1
            </otherwise>
        </choose>
    </where>
</select>

类比Java的switch-case结构，choose标签相当于switch关键字，表示开始一个分支选择；when标签相当于case关键字，表示其中的一个分支；otherwise相当于default，表示其余分支条件都不成立时的默认操作

choose标签中的各个分支是自上而下做判断的，只要其中一个条件满足，就执行操作，然后退出分支选择

5 set与if结合的动态更新

set标签和if标签结合可以实现动态更新的效果，

<update id="updateById">
    update employees
    <set>
        <if test="id!=null">
            id=#{id},
        </if>
        <if test="lastName!=null">
            last_name=#{lastName},
        </if>
        <if test="gender!=null">
            gender=#{gender},
        </if>
        <if test="email!=null">
            email=#{email},
        </if>
        <if test="deptId!=null">
            dept_id=#{deptId}
        </if>
    </set>
    where id=#{id};
</update>

只有当传递了值的时候才会更新数据库表中对应得字段

set标签会自动处理出现在末尾多余的逗号
同样的功能也可以用trim实现，此时prefix=”set”，suffixOverrides=”,”

6 foreach遍历集合

6.1 拼接条件

有时候需要根据传递给接口的参数拼接出条件，

<select id="getEmployeeForEach" resultType="com.lnhoo.dao.Employee">
    select * from employees where id in
    <foreach collection="ids" item="eid" open="(" close=")" separator=",">
        #{eid}
    </foreach>
</select>

接口声明中用@Param注解集合参数，

1	List<Employee> getEmployeeForEach(@Param("ids") List<Integer> ids);

比如上面的配置，“in”关键字后面跟的应该是“在一对括号内部用逗号间隔的数字”，用到foreach标签的属性作用分别是，

属性	作用
collection	要遍历集合的名称，使用@Param注解指定
index	遍历List的时候是索引；遍历Map的时候是entry的key
item	遍历List的时候是取出的元素；遍历Map的时候是entry的value
open	拼接字符串结果的前缀
close	拼接字符串结果的后缀
separator	每个元素之间的分隔符

6.2 MySQL批量插入

在有些场合需要定义1个方法实现批量插入记录，

EmployeeMapper mapper = session.getMapper(EmployeeMapper.class);
List<Employee> employees = new ArrayList<>();
employees.add(new Employee(null, "rita", "0", "rita@gmail.com", 2));
employees.add(new Employee(null, "michael", "1", "michael@gmail.com", 1));
Integer cols = mapper.batchAddEmployees(employees);
session.commit();
System.out.println("inserted cols: " + cols);

foreach标签也可以实现批量插入的功能，

<insert id="batchAddEmployees">
    insert into employees (last_name, gender, email, dept_id) values
    <foreach collection="emps" item="emp" separator=",">
        (#{emp.lastName}, #{emp.gender}, #{emp.email}, #{emp.deptId})
    </foreach>
</insert>

使用“.”操作符取出item内部的属性值

也可以把foreach标签放在整个insert语句的外部，

<insert id="batchAddEmployees">
    <foreach collection="emps" item="emp" separator=";">
        insert into employees (last_name, gender, email, dept_id) 
            values (#{emp.lastName}, #{emp.gender}, #{emp.email}, #{emp.deptId})
    </foreach>
</insert>

但是，此时需要在JDBC连接的时候配置allowMultiQueries选项为true，这样才能一次执行分号间隔的多条sql语句，

jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mybatis?allowMultiQueries=true
jdbc.username=root
jdbc.password=******

7 内置参数

7.1 _parameter

_parameter代表整个参数：

（1）单个参数：_parameter就是这个参数

（2）多个参数：参数会被封装成一个map，_parameter就是代表这个map

7.2 _databaseId

_databaseId代表当前数据库的别名，

<select id="getEmployeeById" resultType="com.lnhoo.dao.Employee">
    <if test="_databaseId=='mysql'">
        select * from employees
        <if test="_parameter!=null">
            where id=#{id}
        </if>
    </if>
    <if test="_databaseId=='oracle'">
        select * from tbl_employee
        <if test="_parameter!=null">
            where eid=#{id}
        </if>
    </if>
</select>

前提条件是需要在MyBatis全局文件中配置数据库别名，

<databaseIdProvider type="DB_VENDOR">
    <property name="MySQL" value="mysql"/>
    <property name="Oracle" value="oracle"/>
    <property name="SQL Server" value="sqlserver"/>
</databaseIdProvider>

8 bind

bind可以将OGNL表达式的值绑定到一个变量中，方便后来引用这个变量，

<select id="getEmployeeByName" resultType="com.lnhoo.dao.Employee">
    <bind name="_lastName" value="'%' + lastName + '%'"/>
    select * from employees where last_name like #{_lastName}
</select>

模糊查询无法通过“#”操作符取参数值，并且如果使用“$”操作符取参数值会有sql注入的风险

bind标签可以将OGNL表达式的值绑定到1个变量中，后续可以用”#”操作符引用

9 抽取可重用的sql片段

抽取可重用的sql片段，方便后续引用，

<select id="getEmployeeByName" resultType="com.lnhoo.dao.Employee">
    <bind name="_lastName" value="'%' + lastName + '%'"/>
    select
    <include refid="empCols"></include>
        from employees where last_name like #{_lastName}
</select>

<sql id="empCols">
    id, last_name, gender, email, dept_id
</sql>

使用include标签引用之前抽取的sql片段

sql标签可以引用include标签内定义的属性，

<select id="getEmployeeByName" resultType="com.lnhoo.dao.Employee">
    <bind name="_lastName" value="'%' + lastName + '%'"/>
    select
    <include refid="empCols">
        <property name="_lastName" value="#{_lastName}"/>
    </include>
        from employees where <include refid="nameCond"></include>
</select>

<sql id="empCols">
    id, last_name, gender, email, dept_id
</sql>

<sql id="nameCond">
    last_name like #{_lastName}
</sql>

比如在这个例子中，nameCond sql标签就引用了include标签使用property定义的属性_lastName