文章目录
6. 动态sql 总览
参考狂神说MyBatis课程笔记
参考黑马程序员MyBaits课程笔记
MyBatis笔记思维导图:
问题扩展与汇总:
1. 什么是动态sql
-
动态SQL指的是根据不同的查询条件 , 生成不同的Sql语句
-
如果有比较复杂的业务,我们需要写复杂的 SQL 语句,往往需要拼接,
- 而拼接 SQL ,稍微不注意,由于引号,空格等缺失可能都会导致错误。
-
使用 mybatis 动态SQL,
- 通过 if, choose, when, otherwise,trim, where, set, foreach等标签,
- 可组合成非常灵活的SQL语句,从而在提高 SQL 语句的准确性的同
时,也大大提高了开发人员的效率。
2. if 语句
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
创建实体类和工具类
package com.sheng.utils;
import java.util.UUID;
public class shengUtil {
// 生成随机字符串
public static String generateUUID() {
return UUID.randomUUID().toString().replace("-", "");
}
}
import java.util.Date;
public class Blog {
private String id;
private String title;
private String author;
private Date createTime;
private int views;
//set,get....
}
编写Mapper接口及xml配置文件
package com.sheng.dao;
public interface BlogMapper {
}
- \resources\com\sheng\dao\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.sheng.dao.BlogMapper">
</mapper>
myBatis核心配置文件
- 下划线驼峰自动转换
- 采用组注册,自动扫描包下的mapper进行注册
<!-- 映射的数据库字段名下划线驼峰自动转换-->
<settings>
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
<!-- 批量别名定义,扫描整个包下的类,别名为类名(首字母大写或小写都可以)-->
<typeAliases>
<package name="com.sheng.entity"></package>
</typeAliases>
<!-- 注册包下的每个mapper文件,前提是类名和路径均和接口保持一致-->
<mappers>
<package name="com.sheng.dao"/>
</mappers>
测试,添加初始数据
-
编写接口
// 新增一个博客 int addBlog(Blog blog);
-
sql配置文件
<!-- 定义全局变量--> <sql id="selectFields"> select id, title, author, create_time, views from blog </sql> <sql id="insertFields"> insert into blog(title, author, create_time, views) values(#{title}, #{author}, #{createTime}, #{views}) </sql> <!-- 添加数据--> <insert id="addBlog" parameterType="blog"> <include refid="insertFields"></include> </insert>
-
测试运行
public class BlogMapperTest { private SqlSession sqlSession; private BlogMapper blogMapper; @Before//用于在测试方法执行之前执行 public void init() throws Exception { // 获得SqlSession对象 sqlSession = MybatisUtils.getSqlSession(); //4.获取dao的代理对象 blogMapper = sqlSession.getMapper(BlogMapper.class); } @After//用于在测试方法执行之后执行 public void destroy() throws Exception { //提交事务 sqlSession.commit(); //6.释放资源 sqlSession.close(); } // 测试添加数据 @Test public void addInitBlog(){ Blog blog = new Blog(); blog.setId(ShengUtil.generateUUID()); blog.setTitle("Mybatis动态sql-1"); blog.setAuthor("sheng"); blog.setCreateTime(new Date()); blog.setViews(9999); blogMapper.addBlog(blog); blog.setId(ShengUtil.generateUUID()); blog.setTitle("动态sql-2"); blogMapper.addBlog(blog); blog.setId(ShengUtil.generateUUID()); blog.setTitle("动态sql-3"); blogMapper.addBlog(blog); blog.setId(ShengUtil.generateUUID()); blog.setTitle("动态sql-4"); blogMapper.addBlog(blog); } }
2)if语句需求及测试
需求
-
根据作者名字和博客名字来查询博客!
-
如果作者名字为空,那么只根据博客名字查询,反之,则根据作者名来查询
编写接口及sql语句
-
定义实现接口
// 需求1:通过作者名字和博客名字来查询博客 List<Blog> queryBlogIf(Map map);
-
动态sql语句编写
-
如果是传统的多条件查询,必须条件都不为null,否则,查询结构为null
<select id="queryBlogIf" parameterType="map" resultType="blog"> <include refid="selectFields"></include> where title = #{title} and author = #{author} </select>
-
使用if动态查询,如果一个为null,就会根据另一个条件查询
-
<!-- 需求1:通过作者名字和博客名字来查询博客 如果作者名字为空,那么只根据博客名字查询,反之,则根据作者名来查询--> <select id="queryBlogIf" parameterType="map" resultType="blog"> <include refid="selectFields"></include> where 1 = 1 <!--这样,下面条件一个为null,sql语句保证正确--> <if test="author != null"> and author = #{author} </if> <if test="title != null"> and title = #{title} </if> </select>
-
测试
// 需求1,通过作者名,博客名查询
@Test
public void testQueryBlogIf() {
HashMap<String, String> map = new HashMap<String, String>();
// map.put("title","Mybatis动态sql-1");
map.put("author","sheng");
List<Blog> blogs = blogMapper.queryBlogIf(map);
System.out.println(blogs);
}
}
Blog{id='50142417f09d468687d5bf1baebb5b47', title='Mybatis动态sql-1', author='sheng', createTime=Wed Feb 16 16:49:41 CST 2022, views=9999}
Blog{id='7fe738bc0d17441e94070b1978548f48', title='动态sql-4', author='sheng', createTime=Wed Feb 16 16:49:41 CST 2022, views=9999}
Blog{id='b29a4c242f99411fbdc9782bb780d8b6', title='动态sql-2', author='sheng', createTime=Wed Feb 16 16:49:41 CST 2022, views=9999}
Blog{id='fb92f0abf600411e9dc40ccfa2420ab9', title='动态sql-3', author='sheng', createTime=Wed Feb 16 16:49:41 CST 2022, views=9999}
3. where语句和set语句
where
- 为了简化上面 where 1=1 的条件拼装,我们可以采用标签来简化开发
- 这个“where”标签会知道如果它包含的标签中有返回值的话,它就插入一个‘where’。
- 此外,如果标签返回的内容是以AND 或OR 开头的,则它会剔除掉。【这是我们使用的最多的案例】
<!-- 需求1:通过作者名字和博客名字来查询博客
如果作者名字为空,那么只根据博客名字查询,反之,则根据作者名来查询-->
<select id="queryBlogIf" parameterType="map" resultType="blog">
<include refid="selectFields"></include>
<where>
<if test="author != null">
and author = #{author}
</if>
<if test="title != null">
and title = #{title}
</if>
</where>
</select>
set
-
上面的对于查询 SQL 语句包含 where 关键字,
-
如果在进行更新操作的时候,含有 set 关键词,需要使用set标签
// 需求2:更新作者名字和博客名字 int updateBlog(Map map);
-
注意:set是用逗号隔开
!-- 需求2:更新作者名字和博客名字 如果作者名字为空,那么只更新博客名字,反之,只更新作者名--> <update id="updateBlog" parameterType="map"> update blog <set> <if test="author != null"> author = #{author}, </if> <if test="title != null"> title = #{title} </if> </set> where id = #{id} </update>
-
测试:
// 需求2,更新作者名,博客名 @Test public void testUpdateBlogSet() { HashMap<String, String> map = new HashMap<String, String>(); map.put("title","动态sql-set"); // map.put("author","sheng-2"); map.put("id", "7fe738bc0d17441e94070b1978548f48"); blogMapper.updateBlog(map); }
4. choose语句、提取sql片段
choose
需求
- 有时候,我们不想用到所有的查询条件,只想选择其中的一个,查询条件有一个满足即可,
- 使用 choose标签可以解决此类问题,类似于 Java 的 switch 语句
- 即,只先进行前面的when标签查询,一旦查询到就返回结果,不会继续判断下面的when标签
接口定义、sql注入及测试
// 需求3:choose条件查询
List<Blog> queryBlogChoose(Map map);
<!--需求3:choose查询,只先进行前面的查询,一旦查到就中断返回结果-->
<select id="queryBlogChoose" parameterType="map" resultType="blog">
<include refid="selectFields"></include>
<where>
<choose>
<when test="title != null">
title = #{title}
</when>
<when test="author != null">
and author = #{author}
</when>
<otherwise>
and views = #{views}
</otherwise>
</choose>
</where>
</select>
// 需求3:choose条件查询
@Test
public void testQueryBlogChoose(){
HashMap<String, Object> map = new HashMap<String, Object>();
map.put("title","Mybatis动态sql-1");
map.put("author","sheng");
map.put("views",9999);
List<Blog> blogs = blogMapper.queryBlogChoose(map);
for(Blog blog : blogs) {
System.out.println(blog);
}
}
结果显示:
Blog{id='50142417f09d468687d5bf1baebb5b47', title='Mybatis动态sql-1', author='sheng', createTime=Wed Feb 16 16:49:41 CST 2022, views=9999}
Blog{id='7fe738bc0d17441e94070b1978548f48', title='Mybatis动态sql-1', author='sheng-2', createTime=Wed Feb 16 16:49:41 CST 2022, views=9999}
sql片段提取
需求
- 有时候可能某个 sql 语句我们用的特别多,为了增加代码的重用性,
- 简化代码,我们需要将这些代码抽取出来,然后使用时直接调用。
示例测试
-
提取的sql片段
<!-- 定义全局变量--> <sql id="selectFields"> select id, title, author, create_time, views from blog </sql> <sql id="insertFields"> insert into blog(id, title, author, create_time, views) values(#{id}, #{title}, #{author}, #{createTime}, #{views}) </sql>
-
引用sql片段
<!-- 添加数据--> <insert id="addBlog" parameterType="blog"> <include refid="insertFields"></include> </insert> <!-- 需求1:通过作者名字和博客名字来查询博客 如果作者名字为空,那么只根据博客名字查询,反之,则根据作者名来查询--> <select id="queryBlogIf" parameterType="map" resultType="blog"> <include refid="selectFields"></include> <where> <if test="author != null"> author = #{author} </if> <if test="title != null"> and title = #{title} </if> </where> </select>
4. foreach语句
需求
- 将数据库中前三个数据的id修改为1,2,3;
- 需求:我们需要查询 blog 表中 id 分别为1,2,3的博客信息
- 需要将需要的id放进一个集合,然后从集合中遍历拿到需要的数据
示例测试
// 需求4:遍历查询集合元素foreach
List<Blog> queryBlogForeach(Map map);
<!-- 需求4:遍历查询集合元素foreach-->
<select id="queryBlogForeach" parameterType="map" resultType="blog">
<include refid="selectFields"></include>
<where>
<!--
collection:指定输入对象中的集合属性
item:代表遍历集合的每个元素,生成的变量名
open:开始遍历时的拼接字符串
close:结束时拼接的字符串
separator:遍历对象之间需要拼接的字符串
id = #{id} 或者 #{id}:表示动态拼接部分的每一个变量表示方式
-->
<if test="ids != null and ids.size() > 0">
<!--select * from blog where 1=1 and (id=1 or id=2 or id=3)-->
<foreach collection="ids" item="id" open="(" close=")" separator="or">
id = #{id}
</foreach>
</if>
</where>
</select>
或者另一种拼接
<if test="ids != null and ids.size() > 0">
<!--select * from blog where 1=1 and id in(1,2,3)-->
<foreach collection="ids" item="id" open="id in(" close=")" separator=",">
#{id}
</foreach>
</if>
测试及结果:
// 需求4:遍历查询集合元素foreach
@Test
public void testQueryBlogForeach() {
HashMap map = new HashMap();
List<Integer> ids = new ArrayList<Integer>();
ids.add(1);
ids.add(2);
ids.add(3);
map.put("ids", ids);
List<Blog> blogs = blogMapper.queryBlogForeach(map);
for(Blog blog : blogs) {
System.out.println(blog);
}
}
Blog{id='1', title='Mybatis动态sql-1', author='sheng', createTime=Wed Feb 16 16:49:41 CST 2022, views=9999}
Blog{id='2', title='Mybatis动态sql-1', author='sheng-2', createTime=Wed Feb 16 16:49:41 CST 2022, views=9999}
Blog{id='3', title='动态sql-2', author='sheng', createTime=Wed Feb 16 16:49:41 CST 2022, views=9999}