第七章动态SQL

target

掌握 if 标签的使用
掌握 choose、when、otherwise的使用
了解 trim标签的使用
了解 where 标签的使用
掌握 set 标签的使用
掌握 foreach标签的使用
了解 bind 标签的使用

开发人员通常根据需求手动拼接 SQL 语句，这是一个极其麻烦的工作，而 MyBatis 提供了对 SQL 语句动态组装的功能，恰能解决这一问题。

MyBatis 的动态 SQL 元素与 JSTL或 XML 文本处理器相似，常用<if>、<choose>、<when>、<otherwise>、<trim>、<where>、<set>、<foreach> 和 <bind> 等元素。

新建动态Web项目MyBatis-07，用来演示本章的内容。

① 导入jar

将以下jar包复制到lib目录下

log4j-1.2.17.jar
log4j-api-2.3.jar
log4j-core-2.3.jar
mybatis-3.4.5.jar
mysql-connector-java-8.0.15.jar

② 创建实体类

在com.mybatis.po包下新建一个Employee类：

public class Employee {

    private Integer id;
    private String name;
    private Integer gender;
    private String email;
}

③ 建表

在MySQL数据库中新建employee表：

CREATE TABLE employee(
  id int PRIMARY key,
  name VARCHAR(30),
  gender int(2),
  email VARCHAR(30)
);

④ 创建映射接口
在com.mybatis.mapper包下创建EmployeeMapper.java接口：

public interface EmployeeMapper {

}

⑤ 创建映射文件

在com.mybatis.mapper包下创建EmployeeMapper.xml映射文件：
namespace是映射接口的全类名。

<?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.mybatis.mapper.EmployeeMapper">

</mapper>

⑥ 创建mybatis全局配置文件

在src目录下创建mybat-config.xml文件，并配置数据库信息和别名，mapper等。

<?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>
    <properties resource="dbconf.properties"></properties>
    <settings>
        <setting name="logImpl" value="LOG4J" />
    </settings>
    <typeAliases>
        <package name="com.mybatis.po" />
    </typeAliases>
    <!-- 配置mybatis运行环境 -->
    <environments default="development">
        <environment id="development">
            <!-- 使用JDBC的事务管理 -->
            <transactionManager type="JDBC" />
            <dataSource type="POOLED">
                <!-- MySQL数据库驱动 -->
                <property name="driver" value="${mysql.driver}" />
                <!-- 连接数据库的URL -->
                <property name="url" value="${mysql.url}" />
                <property name="username" value="${mysql.username}" />
                <property name="password" value="${mysql.password}" />
            </dataSource>
        </environment>
    </environments>

    <!-- 将mapper文件加入到配置文件中 -->
    <mappers>
        <mapper resource="com/mybatis/mapper/EmployeeMapper.xml" />
    </mappers>
</configuration>

在src目录下创建dbconf.properties文件，用来加载数据库连接信息。

# MySQL environment
mysql.driver=com.mysql.cj.jdbc.Driver
mysql.url=jdbc:mysql://localhost:3306/mybatis
mysql.username=root
mysql.password=lixintao

# Oracle Environment
oracle.driver=com.jdbc.OracleDriver
oracle.url=jdbc:oracle:thin:@localhost:1521:orcl
oracle.username=root
oracle.password=lixintao

创建log4j.properties，配置日志相关：

# Global logging configuration
log4j.rootLogger=ERROR,stdout
# MyBatis logging configuration...
log4j.logger.com.mybatis=DEBUG
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

⑦ 创建测试类

在com.mybatis.test包下，新建测试类MybatisTest.java：

public class MybatisTest {

    SqlSession session = null;

    @Before
    public void init() {
        InputStream in = null;
        try {
            in = Resources.getResourceAsStream("mybatis-config.xml");
        } catch (IOException e) {
            e.printStackTrace();
        }
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(in);
        session = sessionFactory.openSession();
    }
    
    
    @After
    public void destory() {
        if (session!=null) {
            session.close();
        }
    }
}

1. if标签

动态 SQL 通常要做的事情是有条件地包含 where 子句的一部分，所以在 MyBatis 中 <if> 元素是最常用的元素，它类似于 Java 中的 if 语句。在应用中测试 <if> 元素，具体过程如下：

1）添加 SQL 映射语句

在 com.mybatis.mapper 包的 EmployeeMapper.xml 文件中添加如下 SQL 映射语句：

<!--使用 if 元素根据条件动态查询用户信息 -->
<select id="selectUserByIf" resultType="employee" parameterType="employee">
  select * from employee where 1=1
  <if test="name!=null and name!=''">
    and name like concat('%',#{name},'%')
  </if>
  <if test="gender !=null and gender !=''">
    and gender=#{gender}
  </if>
</select>

2）添加数据操作接口方法

在 com.mybatis.mapper 包的 Employee 接口中添加如下数据操作接口方法：

public List<Employee> selectUserByIf(Employee emp);

3）测试

@Test
public void selectUserByIf() {
  EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class);
  Employee emp = new Employee(null,"e",1,null);
  List<Employee> emps = employeeMapper.selectUserByIf(emp );
  System.out.println(emps);
}

2. choose、when、otherwise标签

有些时候不想用到所有的条件语句，而只想从中择取一二，针对这种情况，MyBatis 提供了 <choose> 元素，它有点像 Java中的 switch 语句。在应用中测试 <choose> 元素，具体过程如下：

1）添加 SQL 映射语句

在 com.mybatis.mapper 包的 EmployeeMapper.xml 文件中添加如下 SQL 映射语句：

<!--使用choose、when、otherwise元素根据条件动态查询用户信息 -->
<select id="selectEmployeeByChoose" resultType="com.mybatis.po.Employee" parameterType="com.mybatis.po.Employee">
  select * from employee where 1=1
  <choose>
    <when test="name!=null and name!=''">
      and name like concat('%',#{name},'%')
    </when>
    <when test="gender==0 or gender==1">
      and gender=#{gender}
    </when>
    <otherwise>
      and id > 10
    </otherwise>
  </choose>
</select>

如果第一个when符合条件，则choose结束，只对第一个条件进行过滤；否则执行第二个when，如果所有的when都不符合，则执行otherwise条件。

2）添加数据操作接口方法

在 com.mybatis.mapper 包的 Employee 接口中添加如下数据操作接口方法：

public List<Employee> selectEmployeeByChoose(Employee user);

3）测试

@Test
public void selectEmployeeByChoose() {
  EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class);
  Employee emp = new Employee(1,null,1,"aa");
  List<Employee> emps = employeeMapper.selectEmployeeByChoose(emp );
  for (Employee employee : emps) {
    System.out.println(employee);
  }
}

执行的SQL是：select * from employee where 1=1 and gender=?

3. trim标签

<trim> 元素的主要功能是可以在自己包含的内容前加上某些前缀，也可以在其后加上某些后缀，与之对应的属性是 prefix 和 suffix。

可以把包含内容的首部某些内容覆盖，即忽略，也可以把尾部的某些内容覆盖，对应的属性是 prefixOverrides 和 suffixOverrides。正因为 <trim> 元素有这样的功能，所以也可以非常简单地利用<trim> 来代替 <where> 元素的功能。

1）添加 SQL 映射语句

在 com.mybatis.mapper 包的 EmployeeMapper.xml 文件中添加如下 SQL 映射语句：

<!--使用trim元素根据条件动态查询用户信息 -->
<select id="selectEmployeeByTrim" resultType="employee" parameterType="employee">
  select * from employee
  <trim prefix="where" prefixOverrides="and | or">
    <if test="name!=null and name!=''">
      and name like concat('%',#{name},'%')
    </if>
    <if test="gender == 0 or gender == 1 ">
      and gender = #{gender}
    </if>
  </trim>
</select>

2）添加数据操作接口方法

在 com.mybatis.mapper 包的 Employee 接口中添加如下数据操作接口方法：

public List<Employee> selectEmployeeByTrim(Employee emp);

3）测试

@Test
public void selectEmployeeByTrim() {
  EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class);
  Employee emp = new Employee(1,"e",0,"aa");
  List<Employee> emps = employeeMapper.selectEmployeeByTrim(emp );
  for (Employee employee : emps) {
    System.out.println(employee);
  }
}

4. where标签

<where> 元素的作用是会在写入 <where> 元素的地方输出一个 where 语句，另外一个好处是不需要考虑 <where> 元素里面的条件输出是什么样子的，MyBatis 将智能处理。如果所有的条件都不满足，那么 MyBatis 就会查出所有的记录，如果输出后是以 and 开头的，MyBatis 会把第一个 and 忽略。

当然如果是以 or 开头的，MyBatis 也会把它忽略；此外，在 <where> 元素中不需要考虑空格的问题，MyBatis 将智能加上。

1）添加 SQL 映射语句

在 com.mybatis.mapper 包的 EmployeeMapper.xml 文件中添加如下 SQL 映射语句:

<!--使用where元素根据条件动态查询用户信息-->
<select id="selectEmployeeByWhere" resultType="employee" parameterType="employee">
  select * from employee
  <where>
    <if test="name != null and name != ''">
      and name like concat('%',#{name},'%')
    </if>
    <if test=" gender == 0 or gender == 1 ">
      and gender = #{gender}
    </if >
  </where>
</select>

2）添加数据操作接口方法

在 com.mybatis.mapper 包的 Employee 接口中添加如下数据操作接口方法：

public List<Employee> selectEmployeeByWhere(Employee emp);

3）测试

@Test
public void selectEmployeeByWhere() {
  EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class);
  Employee emp = new Employee(1,"e",0,"aa");
  List<Employee> emps = employeeMapper.selectEmployeeByWhere(emp );
  for (Employee employee : emps) {
    System.out.println(employee);
  }
}

执行SQL：select * from employee WHERE name like concat('%',?,'%') and gender = ?

5. set标签

1）添加 SQL 映射语句

在 com.mybatis.mapper 包的 EmployeeMapper.xml 文件中添加如下 SQL 映射语句:

<!--使用set元素动态修改一个用户-->
<update id="updateEmployeeBySet" parameterType="employee">     
  update employee
  <set>
    <if test="name!=null">name= #{name},</if>
    <if test="gender!=null">gender = #{gender},</if>
  </set>
  where id= #{id}
</update>

注意：set的每一个if后面需要加一个逗号

2）添加数据操作接口方法

在 com.mybatis.mapper 包的 Employee 接口中添加如下数据操作接口方法：

public int updateEmployeeBySet(Employee emp);

3）测试

@Test
public void updateEmployeeBySet() {
  EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class);
  Employee emp = new Employee(1,"rose",0,"aa");
  int i = employeeMapper.updateEmployeeBySet(emp );
  session.commit();
  System.out.println("更新了" + i + "条记录！");
}

6. foreach标签

<foreach> 元素主要用在构建 in 条件中，它可以在 SQL 语句中迭代一个集合。

<foreach> 元素的属性主要有 item、index、collection、open、separator、close。

item 表示集合中每一个元素进行迭代时的别名。
index 指定一个名字，用于表示在迭代过程中每次迭代到的位置。
open 表示该语句以什么开始。
separator 表示在每次进行迭代之间以什么符号作为分隔符。
close 表示以什么结束。

在使用 <foreach> 元素时，最关键、最容易出错的是 collection 属性，该属性是必选的，但在不同情况下该属性的值是不一样的，主要有以下 3 种情况：

如果传入的是单参数且参数类型是一个 List，collection 属性值为 list。
如果传入的是单参数且参数类型是一个 array 数组，collection 的属性值为 array。
如果传入的参数是多个，需要把它们封装成一个 Map，当然单参数也可以封装成 Map。Map 的 key 是参数名，collection 属性值是传入的 List 或 array 对象在自己封装的 Map 中的 key。

1）collection为list

① 添加 SQL 映射语句

在 com.mybatis.mapper 包的 EmployeeMapper.xml 文件中添加如下 SQL 映射语句:

<!--使用foreach元素查询用户信息-->
    <select id="selectEmployeeByForeach" resultType="employee" parameterType="list">
        select * from employee where id in
        <foreach collection="list" item="item" index="index" open="(" separator="," close=")">
            #{item}
        </foreach>
    </select>

② 添加数据操作接口方法

在 com.mybatis.mapper 包的 Employee 接口中添加如下数据操作接口方法：

public List<Employee> selectEmployeeByForeach(List<Integer> list);

③ 测试

@Test
public void selectEmployeeByForeach() {
  EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class);
  List<Employee> emps = employeeMapper.selectEmployeeByForeach(Arrays.asList(1,2,3));
  for (Employee employee : emps) {
    System.out.println(employee);
  }
}

2）collection为map

① 添加 SQL 映射语句

在 com.mybatis.mapper 包的 EmployeeMapper.xml 文件中添加如下 SQL 映射语句:

<!--使用foreach元素查询用户信息:map-->
<select id="selectEmployeeByForeach2" resultType="employee" parameterType="map">
  select * from employee where id in
  <foreach collection="ids" item="id" index="index" open="(" separator="," close=")">
    #{id}
  </foreach>
</select>

② 添加数据操作接口方法

在 com.mybatis.mapper 包的 Employee 接口中添加如下数据操作接口方法：

public List<Employee> selectEmployeeByForeach2(Map<String,Object> map);

③ 测试

@Test
public void selectEmployeeByForeach2() {
  EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class);
  Map<String, Object> map = new HashMap<String,Object>();
  map.put("ids",Arrays.asList(1,2,3));
  List<Employee> emps = employeeMapper.selectEmployeeByForeach2(map);
  for (Employee employee : emps) {
    System.out.println(employee);
  }
}

3）批量插入

相当于执行：

insert into employee(name,gender,email) values(?,?,?),(?,?,?);

此种方式只适合MySQL，Oracle不支持。

① 添加SQL映射语句

在 com.mybatis.mapper 包的 EmployeeMapper.xml 文件中添加如下 SQL 映射语句:

<!-- 批量插入 -->
<insert id="insertEmpBatch" parameterType="list">
  insert into employee(name,gender,email) values
  <foreach collection="list" item="emp" separator=",">
    (#{emp.name},#{emp.gender},#{emp.email})
  </foreach>
</insert>

② 添加映射接口

在 com.mybatis.mapper 包的 Employee 接口中添加如下数据操作接口方法：

public int insertEmpBatch(List<Employee> list);

③ 测试

@Test
public void insertEmpBatch() {
  EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class);
  List<Employee> list = new ArrayList<Employee>();
  Employee emp1 = new Employee(null, "zhangsan", 1, "zhangsan@qq.com");
  Employee emp2 = new Employee(null, "lisi", 1, "lisi@qq.com");
  list.add(emp1);
  list.add(emp2);
  int i = employeeMapper.insertEmpBatch(list);
  session.commit();
  System.out.println("添加了：" + i + "条记录！");
}

4）批量插入2

相当于执行：

insert into employee(name,gender,email) values(?,?,?);
insert into employee(name,gender,email) values(?,?,?);

MySQL数据库需要打开批量添加：allowMultiQueries=true

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

① 添加SQL映射语句

在 com.mybatis.mapper 包的 EmployeeMapper.xml 文件中添加如下 SQL 映射语句:

<!-- 批量插入2 -->
<insert id="insertEmpBatch2" parameterType="list">
  <foreach collection="list" item="emp" separator=";">
    insert into employee(name,gender,email) values
    (#{emp.name},#{emp.gender},#{emp.email})
  </foreach>
</insert>

② 添加映射接口

在 com.mybatis.mapper 包的 Employee 接口中添加如下数据操作接口方法：

public int insertEmpBatch2(List<Employee> list);

③ 测试

@Test
public void insertEmpBatch2() {
  EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class);
  List<Employee> emps = new ArrayList<Employee>();
  Employee emp1 = new Employee(null, "zhangsan", 1, "zhangsan@qq.com");
  Employee emp2 = new Employee(null, "lisi", 1, "lisi@qq.com");
  emps.add(emp1);
  emps.add(emp2);
  int i = employeeMapper.insertEmpBatch2(emps);
  session.commit();
  System.out.println("发起了：" + i + "次查询！");
}

注意：这种情况返回的值是1，因为只发起一次SQL。

7. bind标签

在进行模糊查询时，如果使用${}拼接字符串，则无法防止 SQL 注入问题。如果使用字符串拼接函数或连接符号，但不同数据库的拼接函数或连接符号不同。

例如 MySQL的 concat 函数、Oracle 的连接符号“||”，这样 SQL 映射文件就需要根据不同的数据库提供不同的实现，显然比较麻烦，且不利于代码的移植。幸运的是，MyBatis 提供了 <bind> 元素来解决这一问题。

1）添加 SQL 映射语句

在 com.mybatis.mapper 包的 EmployeeMapper.xml 文件中添加如下 SQL 映射语句:

<!--使用bind元素进行模糊查询-->
<select id="selectEmployeeByBind" resultType="com.mybatis.po.Employee" parameterType= "com.mybatis.po.Employee">
  <!-- bind 中的 name 是随便起的，只要和#{ }中的值一样就行-->
  <bind name="para_name" value="'%' + name + '%'"/>
  select * from employee where name like #{para_name}
</select>

2）添加映射接口

在 com.mybatis.mapper 包的 Employee 接口中添加如下数据操作接口方法：

public List<Employee> selectEmployeeByBind(Employee emp);

3）测试

@Test
public void selectEmployeeByBind() {
  EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class);
  Employee emp = new Employee(null, "e", null, null);
  List<Employee> emps = employeeMapper.selectEmployeeByBind(emp);
  for (Employee employee : emps) {
    System.out.println(employee);
  }
}

第七章 动态SQL

1. if标签

1）添加 SQL 映射语句

2）添加数据操作接口方法

3）测试

2. choose、when、otherwise标签

1）添加 SQL 映射语句

2）添加数据操作接口方法

3）测试

3. trim标签

1）添加 SQL 映射语句

2）添加数据操作接口方法

3）测试

4. where标签

1）添加 SQL 映射语句

2）添加数据操作接口方法

3）测试

5. set标签

1）添加 SQL 映射语句

2）添加数据操作接口方法

3）测试

6. foreach标签

1）collection为list

① 添加 SQL 映射语句

② 添加数据操作接口方法

③ 测试

2）collection为map

① 添加 SQL 映射语句

② 添加数据操作接口方法

③ 测试

3）批量插入

① 添加SQL映射语句

② 添加映射接口

③ 测试

4）批量插入2

① 添加SQL映射语句

② 添加映射接口

③ 测试

7. bind标签

1）添加 SQL 映射语句

2）添加映射接口

3）测试

推荐阅读更多精彩内容

第七章动态SQL