Mybatis——动态SQL
一、概念
动态SQL就是指根据不同的条件生成不同的SQL语句。
官网上的解释:
二、搭建测试动态SQL的环境
数据库建表
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
创建一个基础工程
1.导入各种需要的包
<dependencies>
<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.4</version>
</dependency>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.18</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.8</version>
</dependency>
<!-- https://mvnrepository.com/artifact/log4j/log4j -->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
</dependencies>
2.编写基本的配置文件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"/>
<settings>
<setting name="mapUnderscoreToCamelCase" value="true"/>
<setting name="logImpl" value="LOG4J"/>
</settings>
<typeAliases>
<package name="com.cm.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>
<package name="com.cm.mapper"/>
</mappers>
</configuration>
3.编写实体类
这里采用注解的方式
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Blog {
private String id;
private String title;
private String author;
private Date createDate;
private int views;
}
4.编写实体类对应BlogMapper接口 和 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.cm.mapper.BlogMapper">
<!--基本格式-->
</mapper>
5.工具类
这次增加一个工具类,这个工具是为了产生随机的id字符串(现实中,序号其实是不连续的),我们通过这个工具类来模拟随机id序号。
import java.util.UUID;
public class IDUtils {
public static String getId() {
return UUID.randomUUID().toString().replaceAll("-","");
}
}
这个MybatisUtils接口有更改,加入了事务。
import java.io.IOException;
import java.io.InputStream;
public class MybatisUtils {
private static SqlSessionFactory sqlSessionFactory;
static {
try {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
//工厂模式
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
} catch (IOException e) {
e.printStackTrace();
}
}
//重写了getSession()方法,默认为不提交,要提交事务就传参数true即可。
public static SqlSession getSession() {
return getSession(false);
}
public static SqlSession getSession(boolean flag) {
//openSession(true) true表示提交事务,false为不提交。
return sqlSessionFactory.openSession(flag);
}
}
三、动态SQL
1.插入数据
接口
int addBlog(Blog blog);
SQL语句
<insert id="addBlog" parameterType="Blog">
insert into blog(id,title,author,create_time,views)
values (#{id}, #{title}, #{author}, #{createDate},#{views})
</insert>
测试
按图建立测试类
@Test//测试的注解,需要junit包,照着这么写就行
public void addBlogTest() {
BlogMapper mapper = MybatisUtils.getSession(true).getMapper(BlogMapper.class);
Blog blog = new Blog();
blog.setId(IDUtils.getId());
blog.setTitle("Mybatis");
blog.setAuthor("张三");
blog.setCreateDate(new Date());
blog.setViews(9999);
Blog blog1 = new Blog();
blog1.setId(IDUtils.getId());
blog1.setTitle("Java");
blog1.setAuthor("李四");
blog1.setCreateDate(new Date());
blog1.setViews(9899);
Blog blog2 = new Blog();
blog2.setId(IDUtils.getId());
blog2.setTitle("Spring");
blog2.setAuthor("王五");
blog2.setCreateDate(new Date());
blog2.setViews(9909);
System.out.println(mapper.addBlog(blog) + mapper.addBlog(blog1) + mapper.addBlog(blog2));
}
多次插入,并修改,可以得到下表:
2.if
官网
接口
List<Blog> getBlogByIf(Map map);
SQL语句
<select id="getBlogByIf" parameterType="map" resultType="Blog">
select * from blog where
<if test="title!=null">
title = #{title}
</if>
<if test="author!=author">
author = #{author}
</if>
</select>
测试1
@Test
public void getBlogByIfTest() {
BlogMapper mapper = MybatisUtils.getSession().getMapper(BlogMapper.class);
Map<String, String> map = new HashMap<String, String>();
map.put("title", "MyBatis");
List<Blog> blogs = mapper.getBlogByIf(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
}
运行结果
SQL语句加一个and
测试2
结果同测试1上
测试3
map为空
@Test
public void getBlogByIfTest() {
BlogMapper mapper = MybatisUtils.getSession().getMapper(BlogMapper.class);
Map<String, String> map = new HashMap<String, String>();
List<Blog> blogs = mapper.getBlogByIf(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
}
运行结果
但是使用where就不会报错了。
3.where
SQL语句
<select id="getBlogByIf" parameterType="map" resultType="Blog">
select * from blog
<where>
<if test="title!=null">
title = #{title}
</if>
<if test="author!=author">
and author = #{author}
</if>
</where>
</select>
测试
@Test
public void getBlogByIfTest() {
BlogMapper mapper = MybatisUtils.getSession().getMapper(BlogMapper.class);
Map<String, String> map = new HashMap<String, String>();
List<Blog> blogs = mapper.getBlogByIf(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
}
运行结果
where还有自动略过and的作用。
4.set
接口
int updateBlog(Map map);
SQL语句
<!--注意逗号不能省-->
<!--更新采用set-->
<update id="updateBlog" parameterType="map">
update blog
<set>
<if test="title!=null">
title = #{title},
</if>
<if test="author!=null">
author = #{author}
</if>
</set>
where id = #{id}
</update>
测试
@Test
public void testSet() {
//记得提交事务
BlogMapper mapper = MybatisUtils.getSession(true).getMapper(BlogMapper.class);
Map<String, String> map = new HashMap<String, String>();
map.put("id", "6d2a891d036c4748a81f23184cfce1cc");//id不能少
map.put("title", "Mybatis");
map.put("author", "cm");
System.out.println(mapper.updateBlog(map));
}
运行结果
数据表变为
另一种情况,看看set是怎么处理逗号的
@Test
public void testSet() {
//记得提交事务
BlogMapper mapper = MybatisUtils.getSession(true).getMapper(BlogMapper.class);
Map<String, String> map = new HashMap<String, String>();
map.put("id", "6d2a891d036c4748a81f23184cfce1cc");
map.put("title", "Mybatis");
//map.put("author", "cm");
System.out.println(mapper.updateBlog(map));
}
}
运行结果
总结trim, where, set
- 可以看出where自动略过and或者or。
-
可以看出set自动略过逗号。
-
where和set就是——被封装的trim。
-
trim为原型,where和set是封装。
查看官网,可以更深的理解trim, where, set。
5.choose (when, otherwise)
只要能够满足一个条件即可。
choose就好比Java中的switch。
接口
List<Blog> queryBlogByChoose(Map map);
SQL语句
<select id="queryBlogByChoose" parameterType="map" resultType="Blog">
select * from blog
<where>
<choose>
<when test="title != null">
title = #{title}
</when>
<when test="author !=null">
author = #{author}
</when>
<otherwise>
views = #{views}
</otherwise>
</choose>
</where>
</select>
测试
map为空
@Test
public void testChoose() {
BlogMapper mapper = MybatisUtils.getSession().getMapper(BlogMapper.class);
Map<String, String> map = new HashMap<String, String>();
List<Blog> blogs = mapper.queryBlogByChoose(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
}
运行结果
map不为空
@Test
public void testChoose() {
BlogMapper mapper = MybatisUtils.getSession().getMapper(BlogMapper.class);
Map<String, String> map = new HashMap<String, String>();
map.put("title", "Mybatis");
List<Blog> blogs = mapper.queryBlogByChoose(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
}
运行结果
传递两个参数
@Test
public void testChoose() {
BlogMapper mapper = MybatisUtils.getSession().getMapper(BlogMapper.class);
Map<String, String> map = new HashMap<String, String>();
map.put("title", "Mybatis");
map.put("author", "李四");
List<Blog> blogs = mapper.queryBlogByChoose(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
}
运行结果,注意传递参数的顺序改变,运行结果也是一样的
只传递第二个参数
@Test
public void testChoose() {
BlogMapper mapper = MybatisUtils.getSession().getMapper(BlogMapper.class);
Map<String, String> map = new HashMap<String, String>();
//map.put("title", "Mybatis");
map.put("author", "李四");
List<Blog> blogs = mapper.queryBlogByChoose(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
}
运行结果
6.foreach
官网解释
接口
List<Blog> queryBlogByForeach(Map map);
SQL语句
<!--就是sql的子查询 where in(1,2,3)-->
<!--
collection 输入的参数 map中的
item 遍历出来的每一项
通过item遍历出来的参数可以在foreach标签中使用
open="and (" close=")" 开始和结束符
separator="or" 拼接符
-->
<select id="queryBlogByForeach" parameterType="map" resultType="blog">
select * from blog
<where>
<foreach collection="ids" item="id" open="and (" close=")" separator="or">
id = #{id}
</foreach>
</where>
</select>
测试
@Test
public void testForeach() {
BlogMapper mapper = MybatisUtils.getSession().getMapper(BlogMapper.class);
Map<String, List> map = new HashMap<String, List>();
List<String> ids = new ArrayList<String>();
ids.add("a3b70e1bd6744359bdb82e6078932d3c");
ids.add("700d2bf0287d4b5e931ff18a38c5ea8a");
ids.add("6d2a891d036c4748a81f23184cfce1cc");
map.put("ids", ids);
List<Blog> blogs = mapper.queryBlogByForeach(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
}
运行结果
动态SQL加强了程序的健壮性。
所谓的动态SQL,本质还是SQL语句 , 只是我们可以在SQL层面,去执行一个逻辑代码
总代码
接口
public interface BlogMapper {
int addBlog(Blog blog);
List<Blog> getBlogByIf(Map map);
int updateBlog(Map map);
List<Blog> queryBlogByChoose(Map map);
List<Blog> queryBlogByForeach(Map map);
}
配置文件
<?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.cm.mapper.BlogMapper">
<insert id="addBlog" parameterType="Blog">
insert into blog(id,title,author,create_time,views)
values (#{id}, #{title}, #{author}, #{createDate},#{views})
</insert>
<select id="getBlogByIf" parameterType="map" resultType="Blog">
select * from blog
<where>
<if test="title!=null">
title = #{title}
</if>
<if test="author!=author">
and author = #{author}
</if>
</where>
</select>
<!--注意逗号不能省-->
<!--更新采用set-->
<update id="updateBlog" parameterType="map">
update blog
<set>
<if test="title!=null">
title = #{title},
</if>
<if test="author!=null">
author = #{author}
</if>
</set>
where id = #{id}
</update>
<select id="queryBlogByChoose" parameterType="map" resultType="Blog">
select * from blog
<where>
<choose>
<when test="title != null">
title = #{title}
</when>
<when test="author !=null">
author = #{author}
</when>
<otherwise>
views = #{views}
</otherwise>
</choose>
</where>
</select>
<!--就是sql的子查询 where in(1,2,3)-->
<!--
collection 输入的参数 map中的
item 遍历出来的每一项
通过item遍历出来的参数可以在foreach标签中使用
-->
<select id="queryBlogByForeach" parameterType="map" resultType="blog">
select * from blog
<where>
<foreach collection="ids" item="id" open="and (" close=")" separator="or">
id = #{id}
</foreach>
</where>
</select>
</mapper>
测试
public class BlogTest {
@Test
public void addBlogTest() {
BlogMapper mapper = MybatisUtils.getSession(true).getMapper(BlogMapper.class);
Blog blog = new Blog();
blog.setId(IDUtils.getId());
blog.setTitle("Mybatis");
blog.setAuthor("张三");
blog.setCreateDate(new Date());
blog.setViews(9999);
Blog blog1 = new Blog();
blog1.setId(IDUtils.getId());
blog1.setTitle("Java");
blog1.setAuthor("李四");
blog1.setCreateDate(new Date());
blog1.setViews(9899);
Blog blog2 = new Blog();
blog2.setId(IDUtils.getId());
blog2.setTitle("Spring");
blog2.setAuthor("王五");
blog2.setCreateDate(new Date());
blog2.setViews(9909);
System.out.println(mapper.addBlog(blog) + mapper.addBlog(blog1) + mapper.addBlog(blog2));
}
@Test
public void getBlogByIfTest() {
BlogMapper mapper = MybatisUtils.getSession().getMapper(BlogMapper.class);
Map<String, String> map = new HashMap<String, String>();
map.put("title", "MyBatis");
map.put("author", "张三");
//参数为空,就会报错,但用where就不会。
//map.put(null, null);
List<Blog> blogs = mapper.getBlogByIf(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
}
@Test
public void testSet() {
//记得提交事务
BlogMapper mapper = MybatisUtils.getSession(true).getMapper(BlogMapper.class);
Map<String, String> map = new HashMap<String, String>();
map.put("id", "6d2a891d036c4748a81f23184cfce1cc");
map.put("title", "Mybatis");
//map.put("author", "cm");
System.out.println(mapper.updateBlog(map));
}
@Test
public void testChoose() {
BlogMapper mapper = MybatisUtils.getSession().getMapper(BlogMapper.class);
Map<String, String> map = new HashMap<String, String>();
//map.put("author", "李四");
//map.put("title", "Mybatis");
List<Blog> blogs = mapper.queryBlogByChoose(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
}
@Test
public void testForeach() {
BlogMapper mapper = MybatisUtils.getSession().getMapper(BlogMapper.class);
Map<String, List> map = new HashMap<String, List>();
List<String> ids = new ArrayList<String>();
ids.add("a3b70e1bd6744359bdb82e6078932d3c");
ids.add("700d2bf0287d4b5e931ff18a38c5ea8a");
ids.add("6d2a891d036c4748a81f23184cfce1cc");
map.put("ids", ids);
List<Blog> blogs = mapper.queryBlogByForeach(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
}
}