动态 SQL
官方描述
动态 SQL 是 MyBatis 的强大特性之一。如果你使用过 JDBC 或其它类似的框架，你应该能理解根据不同条件拼接 SQL 语句有多痛苦，例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态 SQL，可以彻底摆脱这种痛苦。
使用动态 SQL 并非一件易事，但借助可用于任何 SQL 映射语句中的强大的动态 SQL 语言，MyBatis 显著地提升了这一特性的易用性。
如果你之前用过 JSTL 或任何基于类 XML 语言的文本处理器，你对动态 SQL 元素可能会感觉似曾相识。在 MyBatis 之前的版本中，需要花时间了解大量的元素。借助功能强大的基于 OGNL 的表达式，MyBatis 3 替换了之前的大部分元素，大大精简了元素种类，现在要学习的元素种类比原来的一半还要少。
- if
- choose (when, otherwise)
- trim (where, set)
- foreach
动态 SQL 指的是根据不同的查询条件 , 生成不同的 Sql 语句。
在实际工作中，我们会遇到很多比较复杂的业务，需要构建更为复杂的 SQL 语句，比如多条件组合查询、in 比较等。为此 Mybatis 提供了一套标签来解决我们的问题，其中包括 if, choose, when, otherwise, trim, where, set, foreach等标签 ，方便我们构建安全可靠的 SQL 语句，提高开发效率。
环境搭建
1、创建一个 blog 数据表
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
2、创建一个 Maven 项目
3、IDutils 工具类，用来生成 UUID，作为博客 id 唯一值。
public class IDutils {
public static String getId(){
return UUID.randomUUID().toString().replace("-","");
}
}
4、MybatisUtil 工具类，获取 SqlSession
public class MybatisUtil {
private static SqlSessionFactory sqlSessionFactory;
static {
String resource = "mybatis-config.xml";
try {
InputStream inputStream = Resources.getResourceAsStream(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
} catch (IOException e) {
e.printStackTrace();
}
}
public static SqlSession getSqlSession(){
return sqlSessionFactory.openSession(true);
}
}
5、Blog.java
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Blog {
private String id;
private String title;
private String author;
private Date createTime;
private int view;
}
6、编写 Mapper 相关文件
public interface BlogMapper {
int addBlog(Blog blog);
}
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.msdn.dao.BlogMapper">
<insert id="addBlog" parameterType="Blog">
insert into mybatis.blog(id, title, author, create_time, view)
values (#{id}, #{title}, #{author}, #{createTime}, #{view})
</insert>
</mapper>
7、db.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis?useUnicode=true&useSSL=true&serverTimezone=UTC&characterEncoding=utf-8
username=root
password=1234567
8、mybatis-config.xml
<?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" />
<typeAliases>
<package name="com.msdn.bean"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${driver}"/>
<!--jdbc.url=jdbc:mysql://localhost:3306/oto?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC-->
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="com/msdn/dao/BlogMapper.xml"/>
</mappers>
</configuration>
9、新增数据
@Test
public void add(){
SqlSession sqlSession = MybatisUtil.getSqlSession();
BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
Blog blog = new Blog();
blog.setId(IDutils.getId());
blog.setAuthor("hresh");
blog.setTitle("Spring IoC学习系列一");
blog.setCreateTime(new Date());
blog.setView(1234);
blogMapper.addBlog(blog);
blog.setId(IDutils.getId());
blog.setTitle("Spring IoC学习系列二");
blogMapper.addBlog(blog);
blog.setId(IDutils.getId());
blog.setTitle("Spring IoC学习系列三");
blogMapper.addBlog(blog);
sqlSession.close();
}
if 语句
根据作者名和博客标题进行查询，如果某一条件为空，则只根据另一条件进行查询。
1、编写接口类
List<Blog> selectByIf(Map<String,Object> map);
2、Mapper 配置文件
<select id="selectByIf" parameterType="map" resultType="blog">
select * from mybatis.blog where 1=1
<if test="title != null">
and title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</select>
3、测试
@Test
public void select(){
SqlSession sqlSession = MybatisUtil.getSqlSession();
BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
HashMap<String, Object> m = new HashMap<String, Object>();
// m.put("title","Spring IoC学习系列一");
m.put("author","hresh2");
List<Blog> blogs = blogMapper.selectByIf(m);
for (Blog blog : blogs) {
System.out.println(blog);
}
sqlSession.close();
}
其中 m 集合中可以存放键值对，也可以为空，执行都不会报错。
Where
修改上述的 SQL 语句：
<select id="selectByIf" parameterType="map" resultType="blog">
select * from mybatis.blog
<where>
<if test="title != null">
and title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</where>
</select>
where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且，若子句的开头为 “AND” 或 “OR”，where 元素也会将它们去除。
Set
1、编写接口方法
int update(Map<String,Object> map);
2、Mapper 配置文件
<update id="update" parameterType="map">
update mybatis.blog
<set>
<if test="title != null">
title = #{title},
</if>
<if test="author != null">
author = #{author}
</if>
</set>
where id = #{id}
</update>
3、测试
@Test
public void updateTest(){
SqlSession sqlSession = MybatisUtil.getSqlSession();
BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
HashMap<String, Object> m = new HashMap<String, Object>();
m.put("title","Spring IoC学习系列1");
// m.put("author","hresh2");
m.put("id","8b42c64435a047458d2867fad347e8d6");
blogMapper.update(m);
sqlSession.close();
}
当 SQL 语句中的 author 条件不成立时，set 标签会将 title 条件后带的逗号自动去除掉。
choose语句
有时候，我们不想用到所有的查询条件，只想选择其中的一个，查询条件有一个满足即可，使用 choose 标签可以解决此类问题，类似于 Java 的 switch 语句。
1、编写接口方法
List<Blog> queryByChoose(Map<String,Object> map);
2、Mapper 配置文件
xml <select id="queryByChoose" parameterType="map" resultType="blog"> select * from mybatis.blog where 1=1 <choose> <when test="title != null"> and title = #{title} </when> <when test="author != null"> and author = #{author} </when> <otherwise> and id = #{id} </otherwise> </choose> </select>
3、测试
@Test
public void queryByChoose(){
SqlSession sqlSession = MybatisUtil.getSqlSession();
BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
HashMap<String, Object> m = new HashMap<String, Object>();
m.put("title","Spring IoC学习系列1");
m.put("author","hresh22");
m.put("id","8b42c64435a047458d2867fad347e8d6");
List<Blog> blogs = blogMapper.queryByChoose(m);
for (Blog blog : blogs) {
System.out.println(blog);
}
sqlSession.close();
}
当 title 属性值不为空时，SQL 语句则执行 title = #{title}条件，其他条件不予处理。
SQL片段
在 if 语句中如果条件过多，导致 SQL 语句变得臃肿，为了增加代码的重用性，可以考虑将部分代码提取出来，提高复用性。
同 if 语句中的案例效果一致，我们换用一种实现方式。
<sql id="wherSql" >
<if test="title != null">
and title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</sql>
<select id="selectByIf2" parameterType="map" resultType="blog">
select * from mybatis.blog
<where>
<!-- 引用 sql 片段，如果refid 指定的不在本文件中，那么需要在前面加上 namespace -->
<include refid="wherSql"></include>
</where>
</select>
测试
@Test
public void select(){
SqlSession sqlSession = MybatisUtil.getSqlSession();
BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
HashMap<String, Object> m = new HashMap<String, Object>();
m.put("title","Spring IoC学习系列1");
// m.put("author","hresh2");
// List<Blog> blogs = blogMapper.selectByIf(m);
List<Blog> blogs = blogMapper.selectByIf2(m);
for (Blog blog : blogs) {
System.out.println(blog);
}
sqlSession.close();
}
结果为：
2020-03-20 23:40:58,135 DEBUG [com.msdn.dao.BlogMapper.selectByIf2] - ==> Preparing: select * from mybatis.blog WHERE title = ?
2020-03-20 23:40:58,169 DEBUG [com.msdn.dao.BlogMapper.selectByIf2] - ==> Parameters: Spring IoC学习系列1(String)
2020-03-20 23:40:58,190 DEBUG [com.msdn.dao.BlogMapper.selectByIf2] - <== Total: 1
Blog(id=8b42c64435a047458d2867fad347e8d6, title=Spring IoC学习系列1, author=hresh2, createTime=Wed Mar 04 19:49:04 CST 2020, view=1234)
注意：
提取的 SQL 片段中的 and 连接符会被自动处理掉。
trim
如果 where 元素与你期望的不太一样，你也可以通过自定义 trim 元素来定制 where 元素的功能。比如，和 where 元素等价的自定义 trim 元素为：
<trim prefix="WHERE" prefixOverrides="AND |OR ">
...
</trim>
Mapper 配置文件
<select id="queryByChoose2" parameterType="map" resultType="blog">
select * from mybatis.blog
<trim prefix="where" prefixOverrides="and |or">
<if test="title != null">
title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</trim>
</select>
测试
@Test
public void queryByChoose(){
SqlSession sqlSession = MybatisUtil.getSqlSession();
BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
HashMap<String, Object> m = new HashMap<String, Object>();
// m.put("title","Spring IoC学习系列1");
m.put("author","hresh2");
List<Blog> blogs = blogMapper.queryByChoose2(m);
for (Blog blog : blogs) {
System.out.println(blog);
}
sqlSession.close();
}
prefixOverrides 属性会忽略通过管道符分隔的文本序列。
Foreach
1、编写接口方法
List<Blog> queryByForeach(Map<String,Object> map);
2、Mapper 配置文件
<select id="queryByForeach" parameterType="map" resultType="blog">
select * from mybatis.blog
<where>
<!--
collection:指定输入对象中的集合属性
item:每次遍历生成的对象
open:开始遍历时的拼接字符串
close:结束时拼接的字符串
separator:遍历对象之间需要拼接的字符串
select * from blog where 1=1 and (id=1 or id=2 or id=3)
-->
<foreach collection="authors" item="author" open="(" close=")" separator="or">
author = #{author}
</foreach>
</where>
</select>
3、测试
@Test
public void queryByForeach(){
SqlSession sqlSession = MybatisUtil.getSqlSession();
BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
HashMap<String, Object> m = new HashMap<String, Object>();
List<String> authors = new ArrayList<String>();
authors.add("hresh2");
authors.add("hresh");
m.put("authors",authors);
List<Blog> blogs = blogMapper.queryByForeach(m);
for (Blog blog : blogs) {
System.out.println(blog);
}
sqlSession.close();
}
当 authors 列表为空时，执行该代码将查询出所有的结果集。