动态 SQL

一、学习目的

在实际项目的开发中，开发人员在使用 JDBC 或其他持久层框架进行开发时，经常需要根据不同的条件拼接 SQL 语句，拼接 SQL 语句时还要确保不能遗漏必要的空格、标点符号等，这种编程方式给开发人员带来极大的不便，而 MyBatis 提供的 SQL 语句动态组装功能，恰能很好的解决这一问题，提高了 SQL 语句的复用性，本章将对 MyBatis 框架的动态 SQL 进行详细讲解。

二、动态 SQL 中的元素

三、条件查询操作

① if 元素

在 MyBatis 中，if 元素是最常用的判断元素，它类似于 Java 中的 if 语句，主要用于实现某些简单的条件判断。在实际应用中，我们可能会通过某个条件查询某个数据。例如，要查找某个客户的信息，可以通过姓名或者年龄来查找客户，也可以不填写年龄直接通过姓名来查找，还可以都不填写而查询出所有客户，此时姓名和年龄就是非必须条件。类似于这种情况的，我们都可以通过 if 元素来实现。

/*CustomerMapper.xml*/
<select id="findCustomer" parameterType="customer" resultType="customer">
  select * from customer where 1=1
    
    <if test="username!=null and username!=''">
      and username like concat('%',#{username},'%')
    </if>
    
    <if test="jobs!=null and jobs!=''">
      and jobs=#{jobs}
    </if>
    
</select>

//test.java
Customer customer = new Customer();
customer.setUsername("jack");
customer.setJobs("teacher");
List<Customer> customers = session.selectList("com.tyut.mapper.CustomerMapper.findCustomer",customer);
for (Customer c:customers) {
  System.out.println(c);
}
session.close();

为什么要使用 where 1=1？
第一个条件成立时，加不加 1=1 都是没有影响的，但是如果说我们的第一个语句中的条件并不成立，这时候肯定需要跳过它再去执行下一个语句，但是这时会报错，因为 where 后直接就跟了 and，这是不允许的，为了解决这个问题，我们使用 where 1=1。

当二者都为空时，查询到的结果则为整个表！

② choose、when 和 otherwise 元素

在使用 if 元素时，只要 test 属性中的表达式为 true，就会执行元素中的条件语句，但是在实际应用中，有时只需要从多个选项中选择一个去执行。例如，当客户名称不为空，则只根据客户名称进行客户筛选，与客户职业无关，当客户名称为空，而客户职业不为空，则只根据客户职业进行客户筛选，当客户名称和客户职业都为空，则要求查询出所有电话不为空的客户信息。针对这种情况，使用 if 元素进行处理显然是不合理的，MyBatis 提供了 choose、when、otherwise 元素进行处理，这三个元素往往组合在一起使用，作用相当于 Java 语言中的 if…else。

/*CustomerMapper.xml*/
<select id="findCustomer" parameterType="customer" resultType="customer">
  select * from customer where 1=1
    <choose>
    
      <when test="username!=null and username!=''">
        and username like concat('%',#{username},'%')
      </when>
      
      <when test="jobs!=null and jobs!=''">
        and jobs=#{jobs}
      </when>

      <otherwise>
        and phone is not null
      </othewrwise>
      
    </choose>
</select>

if 元素不管后面有多少语句，只要条件为 true 则全部执行，而 choose 语句是从上到下找第一个条件为 true 的语句去执行，至于后面的各种语句一律不执行！

③ where 和 trim 元素

在映射文件中，编写的 SQL 语句后面加入了 “where 1=1” 的条件的话，既保证了 where 后面的条件成立，又避免了 where 后面第一个词是 and 或者 or 之类的关键字。其实除了 where 1=1 外，我们还可以使用 MyBatis 提供的 where 元素和 trim 元素解决这个问题。

/*CustomerMapper.xml*/
<select id="findCustomer" parameterType="customer" resultType="customer">
  select * from customer
    <where>
    
      <if test="username!=null and username!=''">
        and username like concat('%',#{username},'%')
      </if>
    
      <if test="jobs!=null and jobs!=''">
        and jobs=#{jobs}
      </if>
      
    </where>
</select>

where 元素会自动判断由组合条件拼装的 SQL 语句，只有 where 元素内的某一个或多个条件成立时，才会在拼装 SQL 中加入 where 关键字，否则将不会添加，即使 where 之后的内容有多余的 and 或 or，where 元素也会自动地将它们去除。

除了 where 外，trim 元素也可以用于删除多余的关键字，它可以直接实现 where 元素的功能，trim 元素包含四个属性。

/*CustomerMapper.xml*/
<select id="findCustomer" parameterType="customer" resultType="customer">
  select * from customer
    <trim prefix="where" prefixOverrides="and">
    
      <if test="username!=null and username!=''">
        and username like concat('%',#{username},'%')
      </if>
    
      <if test="jobs!=null and jobs!=''">
        and jobs=#{jobs}
      </if>
      
    </trim>
</select>

增加 where，剔除 and！

四、更新操作

实际应用中，大多数情况下我们都是只更新某一个或几个字段，如果更新的每一条数据都要将其所有的属性都更新一遍，那么执行效率是非常差的，为了解决更新数据的效率问题，MyBatis 提供了 set 元素，set 元素主要用于更新操作，它可以在动态 SQL 语句前输出一个 SET 关键字，并将 SQL 语句中最后一个多余的逗号去掉，set 元素与 if 元素结合可以实现只更新需要更新的字段。

/*CustomerMapper.xml*/
<update id="updateCustomerByset" parameterType="customer">
  update customer 
  <set>
  
    <if test="username!=null and username!=''">
      username=#{username},
    </if>
    
    <if test="jobs!=null and jobs!=''">
      jobs=#{jobs},
    </if>

    <if test="phone!=null and phone!=''">
      phone=#{phone},
    </if>
    
  </set>
  where id=#{id}
</update>

//test.java
Customer customer = new Customer();
customer.setId(3);
customer.setPhone("85785785785");
int rows = session.update("com.tyut.mapper.CustomerMapper.updateCustomerById",customer);
if(rows>0) {
  System.out.println("修改了"+"rows"+"条数据！");
} else {
  System.out.println("数据修改失败！");
}
session.commit();
session.close();

① 在映射文件中使用 set 元素和 if 元素组合进行 update 语句动态 SQL 组装时，如果 set 元素内包含的内容都为空，则会出现 SQL 语法错误，因此，在使用 set 元素进行字段信息更新时，要确保传入的更新字段不能都为空；
② 除了使用 set 元素外，还可以通过 trim 元素来实现更新操作，其中 trim 元素的 prefix 属性指定要添加的 trim 元素所包含内容的前缀为 set，suffixOverrides 属性指定去除的 trim 元素所包含内容的后缀为逗号。

注意不要忘记 session.commit() 手动提交事务，否则我们对数据库所做的更改是不会永久性保存的！

五、复杂查询操作

1.foreach 元素中的属性

在遍历参数时，collection 属性的值是必须指定的，不同情况下，该属性的取值也是不一样的，主要有以下三种情况：

① 若遍历的参数是单个参数且参数类型是一个 List，collection 属性值为 list；
② 若遍历的参数是单个参数且参数类型是一个数组，collection 属性值为 array；
③ 若传入的参数为多个参数，就需要把参数封装为一个 Map 进行处理，collection 属性值为 Map。

2.foreach 元素迭代数组

例如要从数据表中查询出 id 为 1、2、3 的客户信息，就可以利用数组作为参数，存储 id 的属性值 1、2、3，并通过 foreach 元素迭代数组完成客户信息的批量查询操作。

/*CustomerMapper.xml*/
<select id="selectByArray" resultType="customer">
  select * from customer where id in
  <foreach collection="array" item="id" open="(" close=")" separator=",">
    #{id}
  </foreach>
</select>

//test.java
Integer[] ids = {2,3};
List<Customer> customers = session.selectList("com.tyut.mapper.CustomerMapper.selectByArray",ids);
for (Customer c:customers) {
  System.out.println(c);
}
session.close();

parameterType 属性可写可不写，所以完全可以不写！

3.foreach 元素迭代 List

/*CustomerMapper.xml*/
<select id="selectByList" resultType="customer">
  select * from customer where id in
  <foreach collection="list" item="id" open="(" close=")" separator=",">
    #{id}
  </foreach>
</select>

//test.java
List<Integer> ids = new ArrayList<>();
ids.add(2);
ids.add(3);
List<Customer> customers = session.selectList("com.tyut.mapper.CustomerMapper.selectByList",ids);
for (Customer c:customers) {
  System.out.println(c);
}
session.close();

4.foreach 元素迭代 Map

当 session 的 API 需要传入多个参数时，我们可以将多个参数封装到一个 Map 集合里面。

/*CustomerMapper.xml*/
<select id="selectByMap" resultType="customer">
  select * from customer where jobs=#{jobs} and id in
  <foreach collection="id" item="roleMap" open="(" close=")" separator=",">
    #{roleMap}
  </foreach>
</select>

//test.java
Map<String,Object> map = new HashMap<>();
List<Integer> ids = new ArrayList<>();
ids.add(1);
ids.add(2);
ids.add(3);
map.put("id",ids);
map.put("jobs","teacher");
List<Customer> customers = session.selectList("com.tyut.mapper.CustomerMapper.selectByMap",map);
for (Customer c:customers) {
  System.out.println(c);
}
session.close();

Okey，到此为止我们的动态 SQL 就已讲解完毕，试着自己写一下学生信息查询系统，以便更好地巩固以上知识点。>_<