MyBatis（五）MyBatis动态SQL

MyBatis 目录

• MyBatis（一）MyBatis初识 - 青杉 - 博客园 (cnblogs.com)
• MyBatis（二）MyBatis标签 - 青杉 - 博客园 (cnblogs.com)
• MyBatis（三）MyBatis resultMap和注解 - 青杉 - 博客园 (cnblogs.com)
• MyBatis（四）MyBatis关联（级联）查询 - 青杉 - 博客园 (cnblogs.com)
• MyBatis（五）MyBatis动态SQL - 青杉 - 博客园 (cnblogs.com)

MyBatis 动态SQL

​ 动态 SQL是 MyBatis 的强大特性之一。在 JDBC 或其它类似的框架中，开发人员通常需要手动拼接 SQL语句。根据不同的条件拼接 SQL语句是一件极其痛苦的工作。例如，拼接时要确保添加了必要的空格，还要注意去掉列表最后一个列名的逗号。而动态 SQL恰好解决了这一问题，可以根据场景动态的构建查询。

​ 动态 SQL只有几个基本元素，与 JSTL 或 XML 文本处理器相似，十分简单明了，大量的判断都可以在 MyBatis 的映射 XML 文件里配置，以达到许多需要大量代码才能实现的功能。

​ 动态 SQL大大减少了编写代码的工作量，更体现了 MyBatis 的灵活性、高度可配置性和可维护性。

MyBatis 也可以在注解中配置 SQL，但是由于注解功能受限，且对于复杂的 SQL 语句来说可读性差，所以使用较少。本教程不对它们进行介绍。

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

元素	作用	备注
if	判断语句	单条件分支判断
choose（when、otherwise）	相当于 Java 中的 switch case 语句	多条件分支判断
trim、where	辅助元素	用于处理一些SQL拼装问题
foreach	循环语句	在in语句等列举条件常用
bind	辅助元素	拼接参数

数据结构

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(20),
  `sex` varchar(20),
	`birthday` date,
	`address` varchar(100),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

添加一些数据

INSERT INTO user(username,sex,birthday,address) value("张三","男","2000-10-10","四川成都");
INSERT INTO user(username,sex,birthday,address) value("李四","男","2000-10-10","四川成都");
INSERT INTO user(username,sex,birthday,address) value("王五","男","2000-10-10","四川成都");

User类

package org.mybatis.example;

import lombok.Data;
import lombok.ToString;

import java.util.List;

@Data
@ToString
public class User {
    private int id;
    private String username;
    private String sex;
    private String birthday;
    private String address;
}

resultMap

<resultMap type="org.mybatis.example.User" id="myResult">
        <id property="id" column="id" />
        <result property="username" column="username" />
        <result property="sex" column="sex" />
        <result property="birthday" column="birthday" />
        <result property="address" column="address" />
    </resultMap>

if标签

MyBatis if 类似于 Java 中的 if 语句，是 MyBatis 中最常用的判断语句。使用 if 标签可以节省许多拼接 SQL 的工作，把精力集中在 XML 的维护上。

if 语句使用方法简单，常常与 test 属性联合使用。语法如下。

<if test="判断条件">
    SQL语句
</if>

当判断条件为 true 时，才会执行所包含的 SQL 语句。

最常见的场景是在 if 语句中包含 where 子句，例如。

<select id="selectUser" resultMap="myResult">
    select * from user
    <if test="name != null">
        where name like #{name}
    </if>
</select>

以上表示如果传入的name不为空就执行where name like #{name}语句 如果为空就只执行select * from user

可多个 if 语句同时使用。

<select id="selectUserByUsernameAndSex"  parameterType="org.mybatis.example.User" resultMap="myResult">
    select * from user where
        <if test="username != null">
           username=#{username}
        </if>
         
        <if test="username != null">
           and sex=#{sex}
        </if>
</select>

这样写我们可以看到，如果 sex 等于 null，那么查询语句为 select * from user where username=#{username},但是如果usename 为空呢？那么查询语句为 select * from user where and sex=#{sex}，这是错误的 SQL 语句，如何解决呢？请看下面的 where 语句

两种方式

1、 万能1=1 where 后面加上 1=1

2、if+where 语句

where 标签

<select id="selectUserByUsernameAndSex"  parameterType="org.mybatis.example.User" resultMap="myResult">
    select * from user
    <where>
        <if test="username != null">
           username=#{username}
        </if>
         
        <if test="username != null">
           and sex=#{sex}
        </if>
    </where>
</select>

​ 这个where标签会知道如果它包含的标签中有返回值的话，它就插入一个where。此外，如果标签返回的内容是以AND 或OR 开头的，则它会剔除掉。

choose、when和otherwise标签

MyBatis 中动态语句 choose-when-otherwise 类似于 Java 中的 switch-case-default 语句。由于 MyBatis 并没有为 if 提供对应的 else 标签，如果想要达到<if>...<else>...</else>`` </if> 的效果，可以借助 <choose>、<when>、<otherwise> 来实现。

动态语句 choose-when-otherwise 语法如下。

<choose>
    <when test="判断条件1">
        SQL语句1
    </when >
    <when test="判断条件2">
        SQL语句2
    </when >
    <when test="判断条件3">
        SQL语句3
    </when >
    <otherwise>
        SQL语句4
    </otherwise>
</choose>

choose 标签按顺序判断其内部 when 标签中的判断条件是否成立，如果有一个成立，则执行相应的 SQL 语句，choose 执行结束；如果都不成立，则执行 otherwise 中的 SQL 语句。这类似于 Java 的 switch 语句，choose 为 switch，when 为 case，otherwise 则为 default。

<select id="selectUserByChoose" parameterType="org.mybatis.example.User" resultMap="myResult">
      select * from user
      <where>
          <choose>
              <when test="id !='' and id != null">
                  id=#{id}
              </when>
              <when test="username !='' and username != null">
                  and username=#{username}
              </when>
              <otherwise>
                  and sex=#{sex}
              </otherwise>
          </choose>
      </where>
  </select>

　　也就是说，这里我们有三个条件，id,username,sex，只能选择一个作为查询条件

　　　　如果 id 不为空，那么查询语句为：select * from user where id=?

　　　　如果 id 为空，那么看username 是否为空，如果不为空，那么语句为 select * from user where username=?;

　　　　　　　　　　如果 username 为空，那么查询语句为 select * from user where sex=?

set标签

在 Mybatis 中，update 语句可以使用 set 标签动态更新列。set 标签可以为 SQL 语句动态的添加 set 关键字，剔除追加到条件末尾多余的逗号。

<!-- 根据 id 更新 user 表的数据 -->
<update id="updateUserById" parameterType="org.mybatis.example.User">
    update user u
        <set>
            <if test="username != null and username != ''">
                u.username = #{username},
            </if>
            <if test="sex != null and sex != ''">
                u.sex = #{sex}
            </if>
        </set>
     
     where id=#{id}
</update>

　这样写，如果第一个条件 username 为空，那么 sql 语句为：update user u set u.sex=? where id=?

　　　　　　如果第一个条件不为空，那么 sql 语句为：update user u set u.username = ? ,u.sex = ? where id=?

foreach标签

对于一些 SQL 语句中含有 in 条件，需要迭代条件集合来生成的情况，可以使用 foreach 来实现 SQL 条件的迭代。

Mybatis foreach 标签用于循环语句，它很好的支持了数据和 List、set 接口的集合，并对此提供遍历的功能。语法格式如下。

<foreach item="item" index="index" collection="list|array|map key" open="(" separator="," close=")">
    参数值
</foreach>

foreach 标签主要有以下属性，说明如下。

• item：表示集合中每一个元素进行迭代时的别名。
• index：指定一个名字，表示在迭代过程中每次迭代到的位置。
• open：表示该语句以什么开始（既然是in条件语句，所以必然以(开始）。
• separator：表示在每次进行迭代之间以什么符号作为分隔符（既然是 in 条件语句，所以必然以,作为分隔符）。
• close：表示该语句以什么结束（既然是 in 条件语句，所以必然以)结束）。

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

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

示例

需求：我们需要查询 user 表中 id 分别为1,2,3的用户

sql语句：select * from user where id=1 or id=2 or id=3

　　　　 select * from user where id in (1,2,3)

当前数据如下

id	username	sex	birthday	address
1	张三	男	2000-10-10	四川成都
2	李四	男	2000-10-10	四川成都
3	王五	男	2000-10-10	四川成都

UserMapper.xml

<?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="org.mybatis.example.UserMapper">
    <!-- 一对多 根据id查询用户及其关联的订单信息：级联查询的第一种方法（分步查询） -->
    <resultMap type="org.mybatis.example.User" id="myResult">
        <id property="id" column="id" />
        <result property="username" column="username" />
        <result property="sex" column="sex" />
        <result property="birthday" column="birthday" />
        <result property="address" column="address" />
    </resultMap>
    <select id="selectUserByListId" parameterType="List" resultMap="myResult">
        select * from user
        <where>
            <!--
                collection:指定输入对象中的集合属性
                item:每次遍历生成的对象
                open:开始遍历时的拼接字符串
                close:结束时拼接的字符串
                separator:遍历对象之间需要拼接的字符串
                select * from user where 1=1 and id in (1,2,3)
              -->
            <foreach collection="ids" item="id" open="and id in (" close=") " separator=",">
                #{id}
            </foreach>
        </where>
    </select>
</mapper>

UserMapper 接口

package org.mybatis.example;

import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface UserMapper {
    List<User> selectUserByListId(@Param("ids") List<Integer> ids);
}

测试

package org.mybatis.example;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

public class MainApplication {
    public static void main(String[] args) throws IOException {
        InputStream config = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(config);
        SqlSession ss = ssf.openSession();
        List<Integer> ids = new ArrayList<>();
        ids.add(1);
        ids.add(2);
        List<User> orderList = ss.getMapper(UserMapper.class).selectUserByListId(ids);
        for (User or : orderList) {
            System.out.println(or);
        }
    }
}

运行如下

==>  Preparing: select * from user WHERE id in ( ? , ? )
==> Parameters: 1(Integer), 2(Integer)
<==    Columns: id, username, sex, birthday, address
<==        Row: 1, 张三, 男, 2000-10-10, 四川成都
<==        Row: 2, 李四, 男, 2000-10-10, 四川成都
<==      Total: 2
User(id=1, username=张三, sex=男, birthday=2000-10-10, address=四川成都)
User(id=2, username=李四, sex=男, birthday=2000-10-10, address=四川成都)

Process finished with exit code 0

生成的sql语句为select * from user WHERE id in ( ? , ? )

bind标签

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

bind 标签可以通过 OGNL 表达式自定义一个上下文变量。

<select id="selectUser" resultMap="myResult">
        <bind name="username" value="'%'+_parameter+'%'" />
        select * from user where username like #{username}
    </select>

bind 元素属性如下。

• value：对应传入实体类的某个字段，可以进行字符串拼接等特殊处理。
• name：给对应参数取的别名。

以上代码中的“_parameter”代表传递进来的参数，它和通配符连接后，赋给了 pattern，然后就可以在 select 语句中使用这个变量进行模糊查询，不管是 MySQL 数据库还是 Oracle 数据库都可以使用这样的语句，提高了可移植性。

trim标签

在 MyBatis 中除了使用 if+where 实现多条件查询，还有一个更为灵活的元素 trim 能够替代之前的做法。

trim 一般用于去除 SQL 语句中多余的 AND 关键字、逗号，或者给 SQL 语句前拼接 where、set 等后缀，可用于选择性插入、更新、删除或者条件查询等操作。trim 语法格式如下。

<trim prefix="前缀" suffix="后缀" prefixOverrides="忽略前缀字符" suffixOverrides="忽略后缀字符">
    SQL语句
</trim>

trim 中属性说明如下。

属性	描述
prefix	给SQL语句拼接的前缀，为 trim 包含的内容加上前缀
suffix	给SQL语句拼接的后缀，为 trim 包含的内容加上后缀
prefixOverrides	去除 SQL 语句前面的关键字或字符，该关键字或者字符由 prefixOverrides 属性指定。
suffixOverrides	去除 SQL 语句后面的关键字或者字符，该关键字或者字符由 suffixOverrides 属性指定。

　①、用 trim 改写 where 标签

<select id="selectUserByUsernameAndSex" resultMap="myResult" parameterType="org.mybatis.example.User">
        select * from user
        <trim prefix="where" prefixOverrides="and | or">
            <if test="username != null">
               and username=#{username}
            </if>
            <if test="sex != null">
               and sex=#{sex}
            </if>
        </trim>
    </select>

　②、用 trim 改写的 set 标签

<!-- 根据 id 更新 user 表的数据 -->
    <update id="updateUserById" parameterType="org.mybatis.example.User">
        update user u
            <trim prefix="set" suffixOverrides=",">
                <if test="username != null and username != ''">
                    u.username = #{username},
                </if>
                <if test="sex != null and sex != ''">
                    u.sex = #{sex},
                </if>
            </trim>
         
         where id=#{id}
    </update>