动态SQL
什么是动态SQL:动态SQL就是根据不同的条件生成不同的SQL语句
所谓的动态SQL,本质上还是SQL语句,只是我们可以在SQL层面,去执行一个逻辑代码
动态 SQL 是 MyBatis 的强大特性之一。如果你使用过 JDBC 或其它类似的框架,你应该能理解根据不同条件拼接 SQL 语句有多痛苦,例如拼接时要确保不能忘记添加必要的空格,还要注意去掉列表最后一个列名的逗号。利用动态 SQL,可以彻底摆脱这种痛苦。
动态SQL元素和STL或基于类似XML的文本处理器相似。在MyBatis之前的版本中,有很多元素需要花时间了解。
MyBatis3 大大精简了元素种类,现在只需学习原来一半的元素便可。MyBatis采用功能强大的基于OGNL的表达式来淘汰具它大部分无素。
if
choose (when,otherwise)
trim (where,set)
foreach
搭建环境
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>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.2</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.13</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.16</version>
</dependency>
</dependencies>
<build>
<resources>
<resource>
<directory>src/main/resources</directory>
<includes>
<include>**/*.properties</include>
<include>**/*.xml</include>
</includes>
</resource>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.properties</include>
<include>**/*.xml</include>
</includes>
<filtering>false</filtering>
</resource>
</resources>
</build>
</project>
2.编写配置文件
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis?userSSL=true&useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
username=root
password=root
<?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核心配置文件-->
<configuration>
<!--引入外部配置文件-->
<properties resource="application.properties"/>
<!--标准的日志工厂实现-->
<settings>
<setting name="logImpl" value="STDOUT_LOGGING"/>
<!--是否开启自动驼峰命名规则(camel case)映射-->
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
<!--给实体类起别名-->
<typeAliases>
<package name="com.cy.pojo"/>
</typeAliases>
<!--配置环境-->
<environments default="mysql">
<!--配置mysql环境-->
<environment id="mysql">
<!--配置事务的类型-->
<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.cy.dao.BlogMapper"/>
</mappers>
</configuration>
创建工具类
package com.cy.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;
//SqlSessionFactory --> sqlSession
public class MybatisUtils {
static SqlSessionFactory sqlSessionFactory = null;
static {
try {
//使用Mybatis第一步 :获取sqlSessionFactory对象
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
} catch (IOException e) {
e.printStackTrace();
}
}
public static SqlSession getSqlSession(){
//提交事务
return sqlSessionFactory.openSession(true);
}
}
创建工具类,生成随机的id
package com.cy.utils;
import org.junit.Test;
import java.util.UUID;
//用UID方法生成随机的ID
@SuppressWarnings("all")//预制警告(让警告不显示)
public class IDutils {
public static String getID(){
return UUID.randomUUID().toString().replaceAll("-"," ");
}
//测试
@Test
public void test(){
System.out.println(IDutils.getID());
}
}
3.编写实体类
import lombok.Data;
import java.util.Date;
@Data
public class Blog {
private String id;
private String title;
private String author;
private Date createTime;//属性名和字段名不一致
private int views;
}
4.编写实体类对应Mapper接口和Mapper.xml文件
package com.cy.dao;
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.cy.dao.BlogMapper">
<insert id="addBlog" parameterType="blog">
insert into blog (id, title,author,create_time,views)
values (#{id},#{title}, #{author}, #{createTime}, #{views})
</insert>
</mapper>
创建一个测试类:
插入数据
public class MyTest {
@Test
public void addInitBlog(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
Blog blog=new Blog();
blog.setId(IDutils.getID());
blog.setTitle("Mybatis");
blog.setAuthor("淡若清风");
blog.setCreateTime(new Date());
blog.setViews(9999);
mapper.addBlog(blog);
blog.setId(IDutils.getID());
blog.setTitle("JAVA");
mapper.addBlog(blog);
blog.setId(IDutils.getID());
blog.setTitle("Spring5");
mapper.addBlog(blog);
blog.setId(IDutils.getID());
blog.setTitle("微服务");
mapper.addBlog(blog);
}
}
1.动态SQL IF
概述:
使用动态 SQL 最常见情景是根据条件包含 where 子句的一部分。比如:
<select id="findActiveBlogWithTitleLike"
resultType="Blog">
SELECT * FROM BLOG
WHERE state = ‘ACTIVE’
<if test="title != null">
AND title like #{title}
</if>
</select>
这条语句提供了可选的查找文本功能。如果不传入 “title”,那么所有处于 “ACTIVE” 状态的 BLOG 都会返回;如果传入了 “title” 参数,那么就会对 “title” 一列进行模糊查找并返回对应的 BLOG 结果(细心的读者可能会发现,“title” 的参数值需要包含查找掩码或通配符字符)。
如果希望通过 “title” 和 “author” 两个参数进行可选搜索该怎么办呢?首先,我想先将语句名称修改成更名副其实的名称;接下来,只需要加入另一个条件即可。
<select id="findActiveBlogLike"
resultType="Blog">
SELECT * FROM BLOG WHERE state = ‘ACTIVE’
<if test="title != null">
AND title like #{title}
</if>
<if test="author != null and author.name != null">
AND author_name like #{author.name}
</if>
</select>
看完概述开始编写:
把刚刚插入的JAVA views值改为1000
查询博客
<?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.cy.dao.BlogMapper">
<insert id="addBlog" parameterType="blog">
insert into blog(id, title, author, create_time, views)
values (#{id},#{title},#{author},#{createTime},#{views});
</insert>
<select id="queryBlogIF" parameterType="map" resultType="blog">
<!--1=1是为了where后所有判断条件都不符合时,依然可以执行查询而不报错-->
select * from blog where 1=1
<!--判断是否为空,不为空就执行拼接-->
<!--sql语句: select * from blog where 1=1 and title ="java"-->
<if test="title != null">
and title = #{title}
</if>
<if test="author != null">
and author =#{author}
</if>
</select>
</mapper>
package com.cy.dao;
import com.cy.pojo.Blog;
import java.util.List;
import java.util.Map;
public interface BlogMapper {
//插入数据
int addBlog(Blog blog);
//查询博客
List<Blog> queryBlogIF(Map map);
}
测试:
@Test
public void queryBlogIF(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap map = new HashMap();
//map.put("title","JAVA");
map.put("author","淡若清风");
List<Blog> blogs=mapper.queryBlogIF(map);
for (Blog blog:blogs){
System.out.println(blog);
}
sqlSession.close();
}
2.动态SQL choose、when、otherwise
概述:
有时候,我们不想使用所有的条件,而只是想从多个条件中选择一个使用。针对这种情况,MyBatis 提供了 choose 元素,它有点像 Java 中的 switch 语句。
还是上面的例子,但是策略变为:传入了 “title” 就按 “title” 查找,传入了 “author” 就按 “author” 查找的情形。若两者都没有传入,就返回标记为 featured 的 BLOG(这可能是管理员认为,与其返回大量的无意义随机 Blog,还不如返回一些由管理员精选的 Blog)。
<select id="findActiveBlogLike"
resultType="Blog">
SELECT * FROM BLOG WHERE state = ‘ACTIVE’
<choose>
<when test="title != null">
AND title like #{title}
</when>
<when test="author != null and author.name != null">
AND author_name like #{author.name}
</when>
<otherwise>
AND featured = 1
</otherwise>
</choose>
</select>
3.动态SQL trim、where、set
概述:
前面几个例子已经方便地解决了一个臭名昭著的动态 SQL 问题。现在回到之前的 “if” 示例,这次我们将 “state = ‘ACTIVE’” 设置成动态条件,看看会发生什么。
<select id="findActiveBlogLike"
resultType="Blog">
SELECT * FROM BLOG
WHERE
<if test="state != null">
state = #{state}
</if>
<if test="title != null">
AND title like #{title}
</if>
<if test="author != null and author.name != null">
AND author_name like #{author.name}
</if>
</select>
如果没有匹配的条件会怎么样?最终这条 SQL 会变成这样:
SELECT * FROM BLOG
WHERE
这会导致查询失败。如果匹配的只是第二个条件又会怎样?这条 SQL 会是这样:
SELECT * FROM BLOG
WHERE
AND title like ‘someTitle’
这个查询也会失败。这个问题不能简单地用条件元素来解决。这个问题是如此的难以解决,以至于解决过的人不会再想碰到这种问题。
MyBatis 有一个简单且适合大多数场景的解决办法。而在其他场景中,可以对其进行自定义以符合需求。而这,只需要一处简单的改动:
<select id="findActiveBlogLike"
resultType="Blog">
SELECT * FROM BLOG
<where>
<if test="state != null">
state = #{state}
</if>
<if test="title != null">
AND title like #{title}
</if>
<if test="author != null and author.name != null">
AND author_name like #{author.name}
</if>
</where>
</select>
where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且,若子句的开头为 “AND” 或 “OR”,where 元素也会将它们去除。
如果 where 元素与你期望的不太一样,你也可以通过自定义 trim 元素来定制 where 元素的功能。比如,和 where 元素等价的自定义 trim 元素为:
<trim prefix="WHERE" prefixOverrides="AND |OR ">
...
</trim>
编写代码分析:
choose 条件查询
接口
//查询博客Choose
List<Blog> queryBlogChoose(Map map);
<select id="queryBlogChoose" parameterType="map" resultType="blog">
select * from blog
<where><!--根据传入的值,判断执行条件-->
<choose>
<!--when:什么时候-->
<when test="title != null">
title=#{title}
</when>
<when test="author != null">
and author= #{author}
</when>
<!--如果都不满足,就执行:-->
<otherwise>
and views =#{views}
</otherwise>
</choose>
</where>
</select>
测试:
@Test
public void queryBlogChoose(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap map = new HashMap();
// map.put("title", "java");
map.put("author", "淡若清风");
map.put("views", 9999);
List<Blog> blogs=mapper.queryBlogChoose(map);
for (Blog blog:blogs){
System.out.println(blog);
}
sqlSession.close();
}
update更新博客( trim)
接口
//更新博客
int updateBlog(Map map);
<update id="updateBlog" parameterType="map">
update blog
<set><!--和where差不多,set是发现 ,就给你去掉-->
<if test="title !=null">
title = #{title},
</if>
<if test="author != null">
author=#{author}
</if>
</set>
where id= #{id}
</update>
测试:
@Test
public void updateBlog(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap map = new HashMap();
map.put("title", "java2");
map.put("author", "淡若清风2");
map.put("id", "80ae0f8e fd78 41bf bc9e 97ec174aee46");
//map.put("views", 9999);
mapper.updateBlog(map);
sqlSession.close();
}
所谓的动态SQL,本质还是SQL语句,只是我们可以在SQL层面,去执行一个逻辑代码
这个例子中,set 元素会动态地在行首插入 SET 关键字,并会删掉额外的逗号(这些逗号是在使用条件语句给列赋值时引入的)。
注意事项:
◆ 最好基于单标来定义SQL片段
◆ 不要存在where标签
动态SQL就是在拼接SQL语句,我们只要保证SQL的正确性,按照SQL的格式,去排列组合就可以了
建议:
◆ 先在Mysql中写出完整的SQL,再对应的去修改成我们的动态SQL实现通用即可。
SQL片段
有的时候,我们可能会将一些功能的部分抽取出来,方便复用!
1.使用SQL标签抽取公共的部分
<sql id="if-title-author">
<if test="title != null">
and title = #{title}
</if>
<if test="author != null">
and author =#{author}
</if>
</sql>
2.在需要使用的地方 使用include标签即可
<select id="queryBlogIF" parameterType="map" resultType="blog">
select * from blog
<where>
<include refid="if-title-author"></include>
</where>
</select>
注意事项:
◆ 最好基于单标来定义SQL片段
◆ 不要存在where标签
Foreach
参考:
//select * from user where 1=1 and (id=1 or id=2 or id=3)
select * from user where 1=1 and
<foreach item="id" in collection="ids"
open="(" separator="or" close=")">
</foreach>
查询第1-2-3号记录的博客
为了测试方便:我们先把博客的id改为1 2 3 4
接口
//查询第1-2-3号记录的博客
List<Blog> queryBlogForeach(Map map);
<!--
select * from user where 1=1 and (id=1 or id=2 or id=3)
我们现在传递一个map,这个map中可以存一个集合
-->
<select id="queryBlogForeach" parameterType="map" resultType="blog">
select * from blog
<where>
<!--collection="ids"集合 item="id"从集合中遍历出来每一项的名字为id
遍历:
open=""以什么开始,close=""关闭,separator=""分隔符
-->
<foreach collection="ids" item="id" open="and (" close=")" separator="or">
id= #{id}
</foreach>
</where>
</select>
测试:
@Test
public void queryBlogForeach(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap map = new HashMap();
ArrayList<Integer> ids = new ArrayList<>();
ids.add(1);
ids.add(2);
ids.add(3);
map.put("ids", ids);
List<Blog> blogs = mapper.queryBlogForeach(map);
for (Blog b1og : blogs) {
System.out.println(b1og);
}
sqlSession.close();
}
动态SQL就是在拼接SQL语句,我们只要保证SQL的正确性,按照SQL的格式,去排列组合就可以了
建议:
◆ 先在Mysql中写出完整的SQL,再对应的去修改成我们的动态SQL实现通用即可