（五）MyBatis学习笔记-动态SQL

1. 概述

如果使用JDBC或者类似于Hibernate的其他框架，很多时候要根据需要去拼装SQL。比如我要查询角色，可以根据角色名称或者备注等信息查新，但是当我们不输入名称时原本使用名称做查询条件的情况就不适用了。而MaBatis提供了对SQL语句动态的组装能力，MyBatis 采用功能强大的基于 OGNL 表达式来简化操作。

MyBatis的动态SQL包括以下几种元素，如下所示：

元素	作用	备注
if	判断语句	单条件复分支判断
choose( when, otherwise)	相当于Java中的switch和case语句	多条件分支判断
tirm ( where, set)	辅助元素，用于处理特定的SQL拼装问题，比如去掉多余的and、or等	用于处理SQL拼装问题
foreach	循环语句	在in语句等列举条件常用

2. if 元素

需求：根据员工对象Employee来查询对应的员工，传入的员工对象Employee带有哪个属性，就将其作为查询条件。

1. 我们先在映射接口EmployeeMapperDynamicSQL定义如下方法

   public List<Employee> getEmpsByConditionIf(Employee employee);

2. 然后在映射器文件EmployeeMapperDynamicSQL.xml中配置如下信息

   <select id="getEmpsByConditionIf" resultType="com.atguigu.mybatis.bean.Employee">
       select id,last_name lastName, gender, email from tbl_employee
       where
       <!-- test中的值必须符合OGNL规范 
   	在OGNL中and与&&是一样的意思，但是&&是特殊符号，而应该换成其转义字符，即&amp;&amp
   	同理，''可以换成转义字符&quot;&quot
   	-->
       <if test="id!=null">
           id = #{id}
       </if>
       <if test="lastName!=null and lastName!=''">
           and last_name like #{lastName}
       </if>
       <!-- gender的类型为字符串，这里的test没有写成gender=='0'的格式
   	是因为OGNL会进行字符串与数字的判断转换 -->
       <if test="gender==0 or gender==1">
           and gender = #{gender}
       </if>
       <if test="email!=null and email!=''">
           and email = #{email}
       </if>
   </select>

3. 测试代码
   
   这里在Employee对象中设置了id，lastName，gender和email属性，那么数据库会将这些属性作为条件来查询符合的员工。我们可以从运行结果输出的信息看出来，如下所示：
   
   当我们对email传入null时，是不会将email作为查询条件的，因为我们使用if 元素进行了判断。测试代码如下：
   
   输出的SQL信息
   

   3. where 元素

   在刚才演示if 元素的例子的基础上，假设我们传入的员工id值为null，测试代码如下示：
   
   这时如果还是如下的SQL配置，那么就会发生错误。
   
   输出的错误的SQL信息如下：
   

第一种解决办法是在where语句后面加上1=1，后面if元素中要拼装成SQL语句的内容全部写成and xxx的内容，如下所示：

第二种解决方法是通过where元素，MyBatis使用where标签来将所有的查询条件包括在内，将标签中的内容拼装成sql，其会把多出来的and或者or自动去掉。使用where元素的映射器中配置如下：

再次测试传入的员工id为null时，则不会引发错误。输出的SQL语句信息为：

select id,last_name lastName, gender, email from tbl_employee WHERE last_name like ? and gender = ?

4. trim 元素

上面演示where标签时，if 标签内的语句都是以and开头的，那么现在将and 放在尾部，且传入的email为null时，则又会发生错误。

输出的错误SQL语句信息为：

明显SQL语句中多了一个and，这就说明where标签只能去掉头部的and或者or，却不能去掉尾部的and或者or。 下面我们通过trim元素来解决这个问题。

我们另外写一个方法来演示该标签的使用，映射接口的方法为：

public List<Employee> getEmpsByConditionTrim(Employee employee);

映射器文件中的SQL配置，这里通过Trim元素来处理;

这时即便email的传入的值为null也不会发生错误。不过这里还是推荐使用where标签并将and置于前面来解决这个问题，where标签更加明了

5. choose 、when、otherwise元素

需求：假设现在传入的Employee对象如果有id值，那么就将其作为查询条件，如果有lastName值，那么就将lastName作为查询条件，且只使用其中一个作为查询条件。如果都为null的话就查询所有。

1. 在映射接口中编写如下方法，用来演示这些元素的使用：

   public List<Employee> getEmpsByConditionChoose(Employee employee);

2. 映射器文件进行如下配置

   <select id="getEmpsByConditionChoose" resultType="com.atguigu.mybatis.bean.Employee">
           select id,last_name lastName, gender, email from tbl_employee
           <where>
               <choose>
                   <when test="id!=null">
                       id = #{id}
                   </when>
                   <when test="lastName!=null and lastName!=''">
                       last_name like #{lastName}
                   </when>
                   <otherwise>
                       1=1
                   </otherwise>
               </choose>	
           </where>
       </select>

3. 在id为空，lastName不为null的条件下，测试代码如下：
   

4. 输出的结果为：

   DEBUG 01-19 13:54:09,600 ==>  Preparing: select id,last_name lastName, gender, email from tbl_employee WHERE last_name like ?   (BaseJdbcLogger.java:145) 
   DEBUG 01-19 13:54:09,657 ==> Parameters: %张%(String)  (BaseJdbcLogger.java:145) 
   DEBUG 01-19 13:54:09,693 <==      Total: 2  (BaseJdbcLogger.java:145) 
   Employee [id=4, lastName=张三, gender=1, email=zhangsan@gmail.com]
   Employee [id=5, lastName=张三, gender=1, email=zhangsan@gmail.com]

6. set 元素

需求：假设现在对于传入的Employee对象的属性，如果属性值非空则进行更新操作，否则不进行更新。

1. 首先依旧在映射接口文件中定义方法

   public boolean updateEmpByConditonSet(Employee employee);

2. 然后在映射器文件中进行如下配置，注意：这里没有使用set标签

   <update id="updateEmpByConditonSet">
       update tbl_employee
       set
           <if test="lastName!=null">
               last_name = #{lastName},
           </if>
           <if test="gender!=null">
               gender = #{gender},
           </if>
           <if test="email!=null">
               email = #{email}
           </if>
           where id = #{id}
       </update>

3. 运行测试，传入的Employee对象的lastName属性的值为”zs”，gender和email为null，这时会输出如下错误的SQL信息。
   
   if标签中的sql语句实现了拼接，但是后面却带有，的分隔符。这是我们可以使用set标签来处理这个问题，映射器文件的配置信息如下：

   <update id="updateEmpByConditonSet">
           update tbl_employee
           <set>
               <if test="lastName!=null">
                   last_name = #{lastName},
               </if>
               <if test="gender!=null">
                   gender = #{gender},
               </if>
               <if test="email!=null">
                   email = #{email}
               </if>
           </set>
           where id = #{id}
       </update>

   当然我们也可以使用tirm元素来处理这个问题，以下为核心的映射器配置信息：

   <update id="updateEmpByConditonSet">
           update tbl_employee
           <trim prefix="set" suffixOverrides=",">
               <if test="lastName!=null">
                   last_name = #{lastName},
               </if>
               <if test="gender!=null">
                   gender = #{gender},
               </if>
               <if test="email!=null">
                   email = #{email}
               </if>
           </trim>
           where id = #{id}
       </update>

7. foreach 元素

foreach元素是一个循环语句，它的作用是遍历集合，它能够很好地支持数组和List、Set接口的集合，对比提供遍历功能。它往往用于SQL中的in关键字。

7.1 批量查询

我们如果需要根据多个员工id去查询多个对应的员工信息，原始的SQL语句可以以下面的方式来编写：

select id,last_name lastName, gender, email from tbl_employee where id in(1,2,3) 

但是查询的id是固定的，我们可以利用foreach标签来实现根据id的List集合来动态的查询获取员工的信息。

1. 在映射接口中定义如下方法：

   //这里使用注解给List集合命名为idList (List集合的系统自定义别名为list)
   public List<Employee> getEmpsByIdForeach(@Param("idList")List<Integer> idList);

2. 然后在映射器文件中进行如下配置：

   <!-- 原始查询语句：select id,last_name lastName, gender, email
   	from tbl_employee where id in(1,2,3) 
   
   	collection属性的内容为要遍历的集合，
   	List集合类型的参数MyBatis会进行特殊处理，会将List集合封装在Map集合中，
   	Map的key就叫list。但是我们在定义getEmpsByIdForeach方法时给参数命名为idList
   	所以在这里collection属性的内容就为idList
   	
   	item指当前遍历出来的元素赋值给定的变量，也就指当前的元素。
   	separator指各个元素的分隔符，即where id in(1,2,3) 中1,2,3之间的逗号
   	open和close表示是什么符号将这些集合元素包装起来
   	index配置的是当前元素在集合的位置的下标
   -->
   <select id="getEmpsByIdForeach" resultType="com.atguigu.mybatis.bean.Employee">
       select id,last_name lastName, gender, email from tbl_employee where id in
       <!-- 下面foreach的部分相当于(1,2,3)这一部分，不过里面的值是可变的 -->
       <foreach collection="idList" item="item_id" separator="," open="(" close=")">
           #{item_id}
       </foreach>
   </select>

3. 运行测试
   

   7.2 批量插入

   7.2.1 方式一

   第一种批量插入基于MySQL的如下插入语句，如

   insert into tbl_employee(last_name, gender, email, d_id) values
   ("钱七","1","qianqi@gmail.com",2),("郑一","0","zhengyi@gmail.com",1);

下面我们使用foreach标签来实现这个SQL语句的批量插入。

1. 在映射接口定义如下方法

   public void addEmps(@Param("empList")List<Employee> empList);

2. 在映射器文件中配置如下信息

   <select id="addEmps" resultType="com.atguigu.mybatis.bean.Employee">
           insert into tbl_employee(last_name, gender, email, d_id) 
           values
           <!-- 注意一下：emp.dept.id表示emp中的属性dept对象中的id属性 -->
           <foreach collection="empList" item="emp" separator=",">
               (#{emp.lastName},#{emp.gender},#{emp.email},#{emp.dept.id})
           </foreach>
   </select>

3. 测试代码

   SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
   SqlSession openSession = sqlSessionFactory.openSession(false);
   try {
       EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
       Employee emp1 = new Employee(null,"钱七","1","qianqi@gmail.com",new Department(2));
       Employee emp2 = new Employee(null,"郑一","0","zhengyi@gmail.com",new Department(1));
       List<Employee> empList = new ArrayList<>();
       empList.add(emp1);
       empList.add(emp2);
       mapper.addEmps(empList);
       openSession.commit();
   } finally {
       openSession.close();
   }

7.2.2 方式二

第二种批量插入基于MySQL的如下插入语句，如

insert into tbl_employee(last_name, gender, email, d_id) values ("钱七","1","qianqi@gmail.com",2);
insert into tbl_employee(last_name, gender, email, d_id) values ("郑一","0","zhengyi@gmail.com",1);

其中的映射器进行如下配置：

<select id="addEmps2" resultType="com.atguigu.mybatis.bean.Employee">
    <foreach collection="empList" item="emp" separator=";">
        insert into tbl_employee(last_name, gender, email, d_id) 
        values (#{emp.lastName},#{emp.gender},#{emp.email},#{emp.dept.id})
    </foreach>
</select>

另外，还需要在数据库的properties配置文件的url中添加allowMultiQueries=true以实现一次性执行多条插入语句。

8. 内置参数

8.1 _databaseId 参数

在配置了databaseIdProvider标签的情况下，_databaseId 代表当前数据库的别名。它可以实现在select等标签中根据不同的if条件种的不同数据库别名来指定不同SQL语句。

假设MySQL数据中有tbl_employee表，而Oracle数据库中的是EMP表。下面演示一下其具体的用法。

1. 先在映射接口中定义如下方法：

   public List<Employee> getEmpsByInnerParam(Employee employee);

2. 然后全局配置文件配置databaseIdProvider的信息：

   <!--  配置数据库厂商标识 -->
    	<databaseIdProvider type="DB_VENDOR">
    		<property name="MySQL" value="mysql"/>
    		<property name="Oracle" value="oracle"/>
    		<property name="DB2" value="db2"/>
    	</databaseIdProvider>

3. 接着在映射器文件中进行如下配置：

   <select id="getEmpsByInnerParam" resultType="com.atguigu.mybatis.bean.Employee">
       <if test="_databaseId=='mysql'">
           select id,last_name lastName, gender, email from tbl_empployee
       </if>
       <if test="_databaseId=='oracle'">
           select id,last_name lastName, gender, email from emp
       </if>
   </select>

   它实现如果当前数据库是MySQL时，会执行select * from tbl_empployee语句，而当前数据库是Oracle时则执行select * from emp。

8.2 _parameter 参数

上面我们利用_databaseId来查询对应数据库的所有员工数据，假如我们现在的需求是根据传递过来的员工Employee对象的lastName属性来查询获取对应的员工信息。但是假如现在传递过来的员工Employee对象是null时，我们则不用lastName做查询条件，而是查询所有员工。 我们可以使用_parameter参数来实现非空检查以处理这个问题。

我们将映射器文件的信息更改如下：

注意： 此时_parameter代表的是getEmpsByInnerParam方法传递过来的Employee对象。假设传递过来的是id和lastName，MyBatis会将这两个参数封装到一个Map中，而_parameter则代表这个Map集合。

测试代码：

9. bind 元素

bind元素可以将OGNL表达式的值绑定到一个变量中，方便后来引用这个变量的值。比如上面_parameter 参数的测试代码中，Employee的第二个参数为”%张%”，也就是按照lastName进行模糊查询。现在我只想传入参数”张”就能实现模糊查询，这时我就要在映射器文件中使用bind元素来实现变量lastName与%的绑定了。更改_parameter 参数那一部分的映射器配置信息，如下所示：

这时再进行测试：

不过不太推荐这种绑定方式，指定传入带%的参数更好些。