MyBatis 动态 SQL 详解（以后写 SQL 爽多了）

向右看齐 2021-09-07 06:14 306阅读 0赞

![format_png][]

动态 SQL 是 MyBatis 的强大特性之一。如果你使用过 JDBC 或其它类似的框架，你应该能理解根据不同条件拼接 SQL 语句有多痛苦，例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态 SQL，可以彻底摆脱这种痛苦。

01. 环境搭建

在正式开始学习之前，我们先搭建一下此次项目的环境，首先创建数据库和数据表：

CREATE DATABASE mybatis;
    
    CREATE TABLE tbl_employee(
     id INT(11) PRIMARY KEY AUTO_INCREMENT,
     last_name VARCHAR(255),
     gender CHAR(1),
     email VARCHAR(255)
    );
    
    INSERT INTO tbl_employee VALUES('tom',0,'tom@qq.com');

接下来我们创建一个maven项目，引入MyBatis的依赖：

<dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.4.1</version>
    </dependency>
    
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.37</version>
    </dependency>

接着创建数据表对应的Bean类：

@Data
    @ToString
    public class Employee {
    
        private Integer id;
        private String lastName;
        private char gender;
        private String email;
    }

在类路径下编写一个XML文件(mybatis-config.xml)来完成SqlSessionFactory的构建：

<?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE configuration
            PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
            "http://mybatis.org/dtd/mybatis-3-config.dtd">
    <configuration>
        <environments default="development">
            <environment id="development">
                <transactionManager type="JDBC"/>
                
                <dataSource type="POOLED">
                    <property name="driver" value="com.mysql.jdbc.Driver"/>
                    <property name="url" value="jdbc:mysql:///mybatis"/>
                    <property name="username" value="root"/>
                    <property name="password" value="123456"/>
                </dataSource>
            </environment>
        </environments>
    
        
        <mappers>
            <mapper resource="EmployeeMapper.xml"/>
        </mappers>
    </configuration>

最后创建 Mapper 接口和接口对应的映射文件。

02. if

先来看看第一个动态SQL标签——if，通过if标签，我们能够根据我们传递的参数进行动态查询，比如：根据id查询员工信息，若是携带了哪个参数sql的条件就要带上这个参数对应的字段的值。

<select id="getEmpsByConditionIf" resultType="com.wwj.mybatis.bean.Employee">
      select *
      from tbl_employee
      where
      <if test="id != null">
        id=#{id}
      </if>
      <if test="lastName != null and lastName != ''">
        and last_name like #{lastName}
      </if>
      <if test="email != null and email.trim() != ''">
        and email = #{email}
      </if>
      <if test="gender == 0 or gender == 1">
        and gender = #{gender}
      </if>
    </select>

测试代码：

EmployeeDynamicSqlMapper mapper = sqlSession.getMapper(EmployeeDynamicSqlMapper.class);
    Employee employee = new Employee(1, "%c%", null, "jack@qq.com");
    List<Employee> emps = mapper.getEmpsByConditionIf(employee);
    for (Employee emp : emps) {
        System.out.println(emp);
    }

我们来看看它生成的sql：

==> Preparing: select * from tbl_employee where id = ? and last_name like ? and email = ? 
    ==> Parameters: 1(Integer), %c%(String), jack@qq.com(String)
    <== Columns: id, last_name, gender, email, d_id
    <== Row: 1, jack, 1, jack@qq.com, 1
    <== Total: 1
    Employee(id=1, lastName=null, gender=1, email=jack@qq.com)

然而这样的代码是有问题的，如果我们的条件是这样的：

Employee employee = new Employee(null, "%c%", null, "jack@qq.com");

来看看生成的sql：

==>  Preparing: select * from tbl_employee where and last_name like ? and email = ? 
    ==> Parameters: %c%(String), jack@qq.com(String)

因为id为null，所以后面的and被直接拼在了where的后面，此时sql语法就会出错，为此我们需要使用另一个标签——where，它能够完美地解决这一问题。

03. where

<select id="getEmpsByConditionIf" resultType="com.wwj.mybatis.bean.Employee">
      select *
      from tbl_employee
      <where>
        <if test="id != null">
          id = #{id}
        </if>
        <if test="lastName != null and lastName != ''">
          and last_name like #{lastName}
        </if>
        <if test="email != null and email.trim() != ''">
          and email = #{email}
        </if>
        <if test="gender == 0 or gender == 1">
          and gender = #{gender}
        </if>
      </where>
    </select>

只需将判断条件全部放在where标签内，MyBatis就会自动将where标签拼接的sql中多出来的and和or删掉，然而where标签依然有弊端，比如这样拼接sql：

<select id="getEmpsByConditionIf" resultType="com.wwj.mybatis.bean.Employee">
      select *
      from tbl_employee
      <where>
        <if test="id != null">
          id = #{id} and
        </if>
        <if test="lastName != null and lastName != ''">
          last_name like #{lastName} and
        </if>
        <if test="email != null and email.trim() != ''">
          email = #{email} and
        </if>
        <if test="gender == 0 or gender == 1">
          gender = #{gender}
        </if>
      </where>
    </select>

并且传递的参数条件是这种情况：

Employee employee = new Employee(null, "%c%", null, null);

此时last\_name属性后面跟着一个and，但是接下来的属性都是null，所以得到了这样的一条sql：

==>  Preparing: select * from tbl_employee WHERE last_name like ? and 
    ==> Parameters: %c%(String)

可前面我们已经说到，where标签会自动去除多余的and和or呀，为什么还是报错呢？原来，where标签只能去除属性前多余的and和or，至于属性后的，它就无能为力了，所以要么就将and和or都写在属性的前面，要么就使用trim标签来解决这一问题。

04. trim

<select id="getEmpsByConditionIf" resultType="com.wwj.mybatis.bean.Employee">
      select *
      from tbl_employee
      <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>
        <if test="email != null and email.trim() != ''">
          email = #{email} and
        </if>
        <if test="gender == 0 or gender == 1">
          gender = #{gender}
        </if>
      </trim>
    </select>

trim标签中共有四个属性，分别介绍一下作用：

*  prefix：指定sql的前缀，该前缀会被放在trim标签拼接好的sql前面
 *  suffix：指定sql的后缀，该后缀会被放在trim标签拼接好的sql后面
 *  prefixOverrides：前缀覆盖，MyBatis会自动去除属性前的指定字符串
 *  suffixOverrides：后缀覆盖，MyBatis会自动去除属性后的指定字符串

在这里我们将前缀指定为where，并指定后缀覆盖为and，就能解决之前的问题了，执行结果：

==> Preparing: select * from tbl_employee where last_name like ? 
    ==> Parameters: %c%(String)
    <== Columns: id, last_name, gender, email, d_id
    <== Row: 1, jack, 1, jack@qq.com, 1
    <== Total: 1
    Employee(id=1, lastName=null, gender=1, email=jack@qq.com)

05. choose

该标签用于分支操作，与if不同的是，choose只有一个会被执行，若有一个choose满足条件，则其它choose都将失效，比如：根据传递过来的参数进行员工信息查询，如果携带id，则根据id查询，如果携带lastName，则根据lastName查询，实现如下：

List<Employee> getEmpsByConditionChoose(Employee employee);

<select id="getEmpsByConditionChoose" resultType="com.wwj.mybatis.bean.Employee">
      select *
      from tbl_employee
      <where>
        <choose>
          <when test="id != null">
            id = ${id}
          </when>
          <when test="lastName != null">
            last_name like #{lastName}
          </when>
          <otherwise>
          </otherwise>
        </choose>
      </where>
    </select>

choose标签类似于带break的switch语句，otherwise标签表示其它条件都不满足则执行该标签内容，编写业务代码：

Employee employee = new Employee(1, "%c%", null, null);
    List<Employee> emps = mapper.getEmpsByConditionChoose(employee);
    for (Employee emp : emps) {
        System.out.println(emp);
    }

这里同时携带了id和lastName，sql语句会如何拼接呢？因为id的判断在lastName之前，所以id分支已经满足条件，其它分支都会失效，故sql语句如下：

==> Preparing: select * from tbl_employee WHERE id = 1 
    ==> Parameters: 
    <== Columns: id, last_name, gender, email, d_id
    <== Row: 1, jack, 1, jack@qq.com, 1
    <== Total: 1

若是什么参数都不带，则执行otherwise，sql什么都不拼接，故查询所有数据。

06. set

学会了如何动态查询之后，我们来尝试一下动态更新，即：根据传递过来的参数进行员工信息更新，携带了什么参数就更新什么参数，实现如下：

void updateEmp(Employee employee);

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

这样实现的话，当参数均携带的时候不会出现问题，然而这种情况：

Employee employee = new Employee(1, "tom", null, null);
    mapper.updateEmp(employee);

因为lastName后面的参数都为null，导致  `，` 被直接拼在了id的前面，造成语法错误，来看看生成的sql：

==> Preparing: update tbl_employee set last_name = ?, WHERE id = ? 
    ==> Parameters: tom(String), 1(Integer)

为此，我们可以使用set标签，将这些动态更新的内容放在set标签内就能够解决这一问题：

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

这里也可以使用trim标签解决逗号拼接的问题，方式如下：

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

# 07. foreach    #

有时候我们需要同时查询多个值的数据，为此，可以使用foreach实现，比如 查询id为1、2、3的员工信息，实现如下：

List<Employee> getEmpsByConditionForeach(@Param("ids") List<Integer> ids);
    
    <select id="getEmpsByConditionForeach" resultType="com.wwj.mybatis.bean.Employee">
      select *
      from tbl_employee
      where id in(
      <foreach collection="ids" item="id" separator=",">
        #{id}
      </foreach>
      )
    </select>

首先collection属性指定需要遍历的集合，item属性指定遍历出的每个元素的名字，separator属性指定的是每个遍历出的元素的分隔符，一定不要忘记in后面的小括号，foreach也支持将小括号写在标签内：

<select id="getEmpsByConditionForeach" resultType="com.wwj.mybatis.bean.Employee">
      select *
      from tbl_employee
      where id in
      <foreach collection="ids" item="id" separator="," open="(" close=")">
        #{id}
      </foreach>
    </select>

编写业务代码：

List<Employee> emps = mapper.getEmpsByConditionForeach(Arrays.asList(1, 2, 3));
    for (Employee emp : emps) {
        System.out.println(emp);
    }

执行结果：

==> Preparing: select * from tbl_employee where id in ( ? , ? , ? ) 
    ==> Parameters: 1(Integer), 2(Integer), 3(Integer)
    <== Columns: id, last_name, gender, email, d_id
    <== Row: 1, tom, 1, jack@qq.com, 1
    <== Row: 3, tom, 1, tom@qq.com, 2
    <== Total: 2
    Employee(id=1, lastName=null, gender=1, email=jack@qq.com)
    Employee(id=3, lastName=null, gender=1, email=tom@qq.com)

借助foreach标签我们还能够实现批量插入，实现如下：

void addEmpBatch(@Param("emps") List<Employee> emps);

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

编写业务代码：

List<Employee> emps = new ArrayList<>();
    emps.add(new Employee(null, "jack", '1', "jack@qq.com"));
    emps.add(new Employee(null, "jerry", '1', "jerry@qq.com"));
    mapper.addEmpBatch(emps);

执行结果：

==> Preparing: insert into tbl_employee(last_name,gender,email) values (?,?,?) , (?,?,?) 
    ==> Parameters: jack(String), 1(String), jack@qq.com(String), jerry(String), 1(String), jerry@qq.com(String)
    <== Updates: 2

也可以通过执行多条sql来实现批量插入：

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

sql语句之间用 `;` 分隔，但是MySQL默认是不支持这种语法的，为此，需要设置一个参数使其支持该语法：

jdbc.url=jdbc:mysql:///mybatis?allowMultiQueries=true

MyBatis提供了两个内置参数用于辅助操作，也就是说，即使方法没有传递任何参数，我们也能够在操作标签中取出这两个参数进行处理：

*  \_parameter：所有参数，单个参数情况下，\_parameter就是该参数；多个参数情况下，\_parameter就是封装所有参数的Map集合
 *  \_databaseId：若配置了databaseIdProvider 标签，则该参数就是当前数据库的别名

08. bind

MyBatis提供了bind标签用于将表达式的值绑定到一个变量中，用法如下：

<select id="getEmpsByConditionChoose" resultType="com.wwj.mybatis.bean.Employee">
      <bind name="_lastName" value="'%'+ lastName + '%'"/>
      select *
      from tbl_employee
      <where>
        <choose>
          <when test="id != null">
            id = ${id}
          </when>
          <when test="lastName != null">
            last_name like #{_lastName}
          </when>
          <otherwise>
          </otherwise>
        </choose>
      </where>
    </select>

这里使用bind标签定义了一个变量\_lastName，其值为lastName值前后拼接 `%` ，接下来就可以引用该变量，此时调用方法进行模糊查询就不需要自己拼接 `%c%` 了，而是直接传入字符串 `c` 即可。

09. sql

该标签能够抽取可重用的sql片段，使Mapper文件更加简洁：

<sql id="insertColumn">
      last_name,gender,email
    </sql>

接下来若是想使用这一字符串，直接用include标签包含进来即可：

<insert id="addEmpBatch">
      insert into tbl_employee(
      <include refid="insertColumn"></include>
      )
      values
      <foreach collection="emps" item="emp" separator=",">
        (#{emp.lastName},#{emp.gender},#{emp.email})
      </foreach>
    </insert>

公众号运营至今，离不开小伙伴们的支持。为了给小伙伴们提供一个互相交流的平台，特地开通了官方交流群。关注公众号「Java后端」回复「进群」即可。

推荐阅读 
    1. 推荐几个好玩的 GitHub 项目
    2. 推荐几个程序员常用的软件
    3. 图解 Spring 解决循环依赖

[format_png]: /images/20210730/693660f81ec34d03b1ef17d18720c533.png