Mybatis入门-06-动态SQL(if、choose、when、otherwise、set、sql、foreach)
一、前言
关于动态SQL，官方文档有很详细的总结。
一句话，拼接SQL很麻烦，甚至会因为缺少空格出错。利用动态SQL可以摆脱这种痛苦。
版本相关：
- MySQL 8.0.19
- MyBatis 3.5.5
参考视频：【狂神说Java】Mybatis最新完整教程IDEA版
二、搭建环境
2.1 创建表并插入数据
Create Table `blog`(
`id` varchar(50) not null comment '博客id',
`title` varchar(100) not null comment '博客标题',
`author` varchar(30) not null comment '博客作者',
`create_time` datetime not null comment '创建时间',
`views` int(30) not null comment '浏览量'
)Engine=Innodb default charset = utf8;
insert into `blog`(`id`,`title`,`author`,`create_time`,`views`)
values
('97560394a5414655a571df20792c86cc','Mybatis入门-01-第一个程序','Hanxi','2020-09-10 00:31',25),
('9e328886846c4107a45ade2ed7259600','Mybatis入门-02-增删改查及配置（属性、别名、映射器）','Duzhuan','2020-09-11 14:08',5),
('57d837764f714efbbff4fcd335f2ddb1','日志工厂 ','Duzhuan','2020-09-11 22:49',12),
('f35f55cb414f4d3aadd75b8021e1b34e','Mybatis入门-04-多对一 ','Duzhuan','2020-09-19 20:49',5),
('c6f82f2bc929417c83736c28b307aa19','Mybatis入门-05-一对多','Duzhuan','2020-09-20 08:50',2);
2.2 依赖
对于不需要使用日志的，可以JUnit下面的关于日志的依赖删除。
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.duzhuan</groupId>
<artifactId>MyBaitsLearn</artifactId>
<packaging>pom</packaging>
<version>1.0-SNAPSHOT</version>
<properties>
<maven.compiler.source>11</maven.compiler.source>
<maven.compiler.target>11</maven.compiler.target>
</properties>
<dependencies>
<!--jdbc-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.19</version>
</dependency>
<!--mybatis-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.5</version>
</dependency>
<!--JUnit-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
<!--使用slf4j 作为日志门面-->
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>1.7.25</version>
</dependency>
<!--使用 log4j2 的适配器进行绑定-->
<dependency>
<groupId>org.apache.logging.log4j</groupId>
<artifactId>log4j-slf4j-impl</artifactId>
<version>2.12.1</version>
<scope>test</scope>
</dependency>
<!--log4j2 日志门面-->
<dependency>
<groupId>org.apache.logging.log4j</groupId>
<artifactId>log4j-api</artifactId>
<version>2.12.1</version>
</dependency>
<!--log4j2 日志实现-->
<dependency>
<groupId>org.apache.logging.log4j</groupId>
<artifactId>log4j-core</artifactId>
<version>2.12.1</version>
</dependency>
</dependencies>
<build>
<resources>
<resource>
<directory>src/main/resource/</directory>
<includes>
<include>**/*.properties</include>
<include>**/*.xml</include>
</includes>
<filtering>true</filtering>
</resource>
<resource>
<directory>src/main/java/</directory>
<includes>
<include>**/*.properties</include>
<include>**/*.xml</include>
</includes>
<filtering>true</filtering>
</resource>
</resources>
</build>
</project>
2.3 mybatis配置文件
路径
文件
mybatis-config.xml
请按需要设置<setting>里的日志，不需要日志可以删除<settings>及其子标签；
请按需要设置别名，即 <package name="com.duzhuan.pojo"/>；
请按照自己的Maven路径设置好<mappers>。
同时关于mapUnderscoreToCamelCase,具体可看官方文档:
| 设置名 | 描述 | 有效值 | 默认值 |
|---|---|---|---|
| mapUnderscoreToCamelCase | 是否开启驼峰命名自动映射，即从经典数据库列名 A_COLUMN 映射到经典 Java 属性名 aColumn。 | true | false | False |
<?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="db.properties"></properties>
<settings>
<setting name="logImpl" value="SLF4J"/>
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
<typeAliases>
<package name="com.duzhuan.pojo"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${driver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper class="com.duzhuan.dao.BlogMapper"/>
</mappers>
</configuration>
db.properties
自行设置好JDBC配置，以下仅供参考：
driver = com.mysql.jdbc.Driver
url = jdbc:mysql://localhost:3306/mybatis?useSSL=true&useUnicode=true&characterEncoding=UTF8&serverTimezone=UTC
username = root
password = qq123456
log4j2.xml
日志相关的配置，这里由于是SLF4J日志门面+LOG4J实现，因此在mybatis-config.xml中设置了SLF4J，具体看Mybatis入门-03-日志工厂。
<?xml version="1.0" encoding="UTF-8"?>
<!--
status="debug" 日志框架本身的级别
configuration还有个属性是 monitorInterval = 5,自动加载配置文件的最小间隔时间，单位是秒
-->
<configuration status="debug">
<!--
集中配置属性进行管理，使用时通过：${}
-->
<properties>
<property name="LOG_HOME">./logs</property>
</properties>
<!--日志处理器-->
<!--先定义所有的appender -->
<appenders>
<!--这个输出控制台的配置 -->
<Console name="Console" target="SYSTEM_OUT">
<!-- 控制台只输出level及以上级别的信息（onMatch），其他的直接拒绝（onMismatch） -->
<ThresholdFilter level="trace" onMatch="ACCEPT" onMismatch="DENY"/>
<!-- 这个都知道是输出日志的格式 -->
<PatternLayout pattern="%d{yyyy.MM.dd 'at' HH:mm:ss z} [%-5level] %class{36} %L %M - %msg%xEx%n"/>
</Console>
<!--文件会打印出所有信息，这个log每次运行程序会自动清空，由append属性决定，这个也挺有用的，适合临时测试用 -->
<!--append为TRUE表示消息增加到指定文件中，false表示消息覆盖指定的文件内容，默认值是true -->
<File name="log" fileName="${LOG_HOME}/mybatis-log.log" append="false">
<PatternLayout pattern="%d{yyyy.MM.dd 'at' HH:mm:ss z} [%-5level] %class{36} %L %M - %msg%xEx%n"/>
</File>
<!--
添加过滤器ThresholdFilter,可以有选择的输出某个级别以上的类别
onMatch="ACCEPT" onMismatch="DENY"意思是匹配就接受,否则直接拒绝
-->
<File name="ERROR" fileName="${LOG_HOME}/mybatis-error.log">
<ThresholdFilter level="error" onMatch="ACCEPT" onMismatch="DENY"/>
<PatternLayout pattern="%d{yyyy.MM.dd 'at' HH:mm:ss z} [%-5level] %class{36} %L %M - %msg%xEx%n"/>
</File>
<!--
使用随机读写流的日志文件输出appender，性能提高
-->
<RandomAccessFile name="accessFile" fileName="${LOG_HOME}/mybatis-access.log">
<PatternLayout pattern="%d{yyyy.MM.dd 'at' HH:mm:ss z} [%-5level] %class{36} %L %M - %msg%xEx%n"/>
</RandomAccessFile>
<!--
这个会打印出所有的信息，每次大小超过size，
则这size大小的日志会自动存入按年份-月份建立的文件夹下面并进行压缩，
作为存档
-->
<RollingFile name="RollingFile" fileName="${LOG_HOME}/mybatis-web.log"
filePattern="logs/$${date:yyyy-MM}/web-%d{MM-dd-yyyy}-%i.log.gz">
<PatternLayout pattern="%d{yyyy-MM-dd 'at' HH:mm:ss z} [%-5level] %class{36} %L %M - %msg%xEx%n"/>
<SizeBasedTriggeringPolicy size="2MB"/>
</RollingFile>
</appenders>
<!--然后定义logger，只有定义了logger并引入的appender，appender才会生效 -->
<loggers>
<!--使用rootLogger配置 日志级别level="trace" -->
<root level="trace">
<!--制定日志使用的处理器-->
<appender-ref ref="log"/>
<appender-ref ref="ERROR" />
<appender-ref ref="Console"/>
<appender-ref ref="accessFile"/>
<appender-ref ref="RollingFile"/>
</root>
</loggers>
</configuration>
三、创建实体类
路径
代码
Blog.java
package com.duzhuan.pojo;
import java.util.Date;
/**
* @Autord: HuangDekai
* @Date: 2020/9/20 10:30
* @Version: 1.0
* @since: jdk11
*/
public class Blog {
private String id;
private String title;
private String author;
private Date createTime;
private int views;
public Blog() {
}
public Blog(String id, String title, String author, Date createTime, int views) {
this.id = id;
this.title = title;
this.author = author;
this.createTime = createTime;
this.views = views;
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getAuthor() {
return author;
}
public void setAuthor(String author) {
this.author = author;
}
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
public int getViews() {
return views;
}
public void setViews(int views) {
this.views = views;
}
@Override
public String toString() {
return "Blog{" +
"id='" + id + '\'' +
", title='" + title + '\'' +
", author='" + author + '\'' +
", createTime=" + createTime +
", views=" + views +
'}';
}
}
四、创建常用工具类
路径
代码
MyBatisUtils
package com.huangdekai.utils;
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;
/**
* @Autord: HuangDekai
* @Date: 2020/9/20 10:36
* @Version: 1.0
* @since: jdk11
*/
public class MyBatisUtils {
private static SqlSessionFactory sqlSessionFactory;
static {
try {
String config = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(config);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
} catch (IOException e) {
e.printStackTrace();
}
}
public static SqlSession getSqlSession(){
return sqlSessionFactory.openSession();
}
}
IdUtils
package com.duzhuan.utils;
import java.util.UUID;
/**
* @Autord: HuangDekai
* @Date: 2020/9/20 12:00
* @Version: 1.0
* @since: jdk11
*/
public class IdUtils {
public static String getId(){
return UUID.randomUUID().toString().replace("-","");
}
}
五、Mapper
路径
六、IF标签
BlogMapper
package com.duzhuan.dao;
import com.duzhuan.pojo.Blog;
import java.util.List;
/**
* @Autord: HuangDekai
* @Date: 2020/9/20 10:58
* @Version: 1.0
* @since: jdk11
*/
public interface BlogMapper {
List<Blog> getBlogList(Blog blog);
}
BlogMapper.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="com.duzhuan.dao.BlogMapper">
<select id="getBlogList" resultType="Blog">
select * from mybatis.blog where 1=1
<if test="title != null">
and `title` like concat('%',#{title},'%')
</if>
<if test="author != null">
and `author` = #{author}
</if>
<if test="views != -1">
and `views` >= #{views}
</if>
</select>
</mapper>
- 只是增加了使用if语句
test里放的是判断语句，多嘴一句，里面的如author != null中的author是Blog实体类的属性名，而不是数据库的字段名。- 实质上，
select * from mybatiswhere 1=1中where 1=1并不是一种规范的写法，甚至说不应该出现这种写法，这里仅仅用于说明<if>的使用，后面会使用where标签解决要使用where 1=1这种问题。
测试类
package com.duzhuan.dao;
import com.duzhuan.pojo.Blog;
import com.duzhuan.utils.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;
/**
* @Autord: HuangDekai
* @Date: 2020/9/20 15:11
* @Version: 1.0
* @since: jdk11
*/
public class BlogMapperTest {
@Test
public void getBlogListTest(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
Blog blog1 = new Blog(null,"Mybatis",null,null,5);
List<Blog> blogs = mapper.getBlogList(blog1);
for (Blog blog : blogs) {
System.out.println(blog);
}
sqlSession.close();
}
}
结果：
七、where标签
如果注意的话，可以看到上面的SQL语句有一个十分多余的东西，where 1=1。
但是如果想要完成上面的功能，不用where 1=1这样类似的句子还不行，MyBatis的解决方法就是使用where标签，实质上和在SQL里使用where仅仅有一点点细微差别。
这方面官方文档-动态SQL中就写得十分翔实。
这里就对BlogMapper.xml做一点修改，尝试使用<where>标签去替代原来的句子：
<?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.duzhuan.dao.BlogMapper">
<select id="getBlogList" resultType="Blog">
select * from mybatis.blog
<where>
<if test="title != null">
and `title` like concat('%',#{title},'%')
</if>
<if test="author != null">
and `author` = #{author}
</if>
<if test="views != -1">
and `views` >= #{views}
</if>
</where>
</select>
</mapper>
结果：
没错，真的只有一点点修改而已。
这是由于<where>的特性，下面是官方文档的说法：
where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。
而且，若子句的开头为 “AND” 或 “OR”，where 元素也会将它们去除。
八、choose-when-otherwise标签
这方面官方文档-动态SQL中写得十分翔实。
有时候，我们不想使用所有的条件，而只是想从多个条件中选择一个使用。针对这种情况，MyBatis 提供了 choose 元素，它有点像 Java 中的 switch 语句。
这里就对BlogMapper和BlogMapper.xml做一点添加，尝试使得：
-
有title时候返回搜索title的数据
-
有author无title时候返回搜索author的数据
-
仅有views时候返回大于等于views的数据，views默认输入0
BlogMapper中添加方法：
List<Blog> getBlogListByChoose(Blog blog);
BlogMapper.xml中在根标签<mapper></mapper>中添加：
<select id="getBlogListByChoose" resultType="Blog">
select * from mybatis.blog
<where>
<choose>
<when test="title != null">
`title` like concat('%',#{title},'%')
</when>
<when test="title == null and author != null">
`author` like concat('%',#{author},'%')
</when>
<otherwise>
`views` >= #{views}
</otherwise>
</choose>
</where>
</select>
测试样例：
@Test
public void getBlogListByChooseTest(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
Blog blog1 = new Blog(null, "日志", "hanxi", null, 10);
Blog blog2 = new Blog(null, null, "hanxi", null, 10);
Blog blog3 = new Blog(null, null, null, null, 10);
List<Blog> blogListByChoose1 = mapper.getBlogListByChoose(blog1);
List<Blog> blogListByChoose2 = mapper.getBlogListByChoose(blog2);
List<Blog> blogListByChoose3 = mapper.getBlogListByChoose(blog3);
System.out.println("blog1------->"+blogListByChoose1);
System.out.println("blog2------->"+blogListByChoose2);
System.out.println("blog3------->"+blogListByChoose3);
sqlSession.close();
}
输出结果：
九、set标签
上面的都是查询语句，那么动态的插入语句呢？
<update id="updateAuthorIfNecessary">
update Author
<set>
<if test="username != null">username=#{username},</if>
<if test="password != null">password=#{password},</if>
<if test="email != null">email=#{email},</if>
<if test="bio != null">bio=#{bio}</if>
</set>
where id=#{id}
</update>
这是官方文档的一个例子。
可以看到，如果不使用<set>标签，一旦<if test="bio != null">bio=#{bio}</if>不成立而前面的SQL有成立的，那么就会多一个逗号，导致出错。
对于set，官方文档中有解释：
set 元素会动态地在行首插入 SET 关键字，并会删掉额外的逗号（这些逗号是在使用条件语句给列赋值时引入的）。
set使用方法与where类似，可以自行增加样例。
十、SQL标签
假如有很多个SQL，而且很多重复的，那么为了实现SQL的复用，就可以用到<SQL>标签。
BlogMapper.xml:
将getBlogList的<where>标签内的东西移出，放入<sql>标签内：
<?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.duzhuan.dao.BlogMapper">
<!--=================add======================-->
<sql id="title-author-views">
<if test="title != null">
and `title` like concat('%',#{title},'%')
</if>
<if test="author != null">
and `author` = #{author}
</if>
<if test="views != -1">
and `views` >= #{views}
</if>
</sql>
<!--==========================================-->
<select id="getBlogList" resultType="Blog">
select * from mybatis.blog
<where>
<!--=====================update=========================-->
<include refid="title-author-views"/>
<!--====================================================-->
</where>
</select>
<select id="getBlogListByChoose" resultType="Blog">
select * from mybatis.blog
<where>
<choose>
<when test="title != null">
`title` like concat('%',#{title},'%')
</when>
<when test="title == null and author != null">
`author` like concat('%',#{author},'%')
</when>
<otherwise>
`views` >= #{views}
</otherwise>
</choose>
</where>
</select>
</mapper>
再用原来对这个方法的测试案例测试（BlogMapperTest的getBlogListTest方法）：
没问题。
- 尽量基于单表
- 不要带
<where>标签
十一、foreach
动态 SQL 的另一个常见使用场景是对集合进行遍历（尤其是在构建 IN 条件语句的时候）。
为了方便讲解，将数据库中的id改为1~5:
比如实质要执行的语句：
select * from blog where id in (1,2,3,8,10)
BlogMapper.java中添加方法：
List<Blog> getBlogListByForeach(List numList);
BlogMapper.xml中添加：
<select id="getBlogListByForeach" resultType="Blog">
select * from mybatis.blog
where id in
<foreach collection="list" item="item" index="index" open="(" close=")" separator=",">
#{item}
</foreach>
</select>
结果：
