文章目录
动态 SQL
1. 简介
1.1 什么是动态 SQL
- 动态 SQL 指:根据不同条件生成不同的 SQL 语句;
1.2 为什么要使用动态 SQL
-
如果你曾经使用过 JDBC 或其他类似框架的经验,你就可以体会到根据不同条件拼接 SQL 语句有多难;拼接的时候不能忘了必要的空格,还要注意省掉列表名后的逗号,利用动态 SQL 可以彻底摆脱这种麻烦。
-
动态 SQL 元素和使用 JSTL 或其他类似基于 XML 的文本处理器相似。
-
MyBatis 采用功能强大的基于 OGNL 的表达式来消除其他元素。
if
choose(when、ptherwise)
trim(where、set)
foreach
2. 实例
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
-
导包:
<dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.5.4</version> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.18.12</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.47</version> </dependency> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.12</version> </dependency>
-
编写配置文件:
<?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> <!--xml 中元素的标签上下位置是有要求的--> <!-- 将配置文件改为properties --> <properties resource="db.properties"/> <!-- settings,大小写一定不要写错 --> <settings> <setting name="logImpl" value="STDOUT_LOGGING"/> <!--开启驼峰命名规则--> <setting name="mapUnderscoreToCamelCase" value="true"/> </settings> <!--包的别名配置--> <typeAliases> <package name="com.su.pojo"/> <!--<typeAlias type="com.kuang.pojo.User" alias="User"/>--> </typeAliases> <!-- 一个environments 标签元素可以有多套配置 --> <environments default="development"> <!-- 里面的每一个environment代表一个具体的环境 --> <environment id="development"> <!--transactionManager 事务管理器 --> <transactionManager type="JDBC"/> <!-- dataSource 数据源配置 --> <dataSource type="POOLED"> <!-- 连接数据库的配置i--> <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.su.mapper"/> </mappers> </configuration>
db.properties
driver=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/mybatis?useSSL=true&useUnicode=true&characterEncoding=utf8 username=root password=123456
-
编写实体类:
package com.su.pojo; import lombok.Data; import java.util.Date; @Data public class Blog { private String id; private String title; private String author; private Date createDate; private int views; }
-
编写实体类对应Mapper接口:
package com.su.mapper; import com.su.pojo.Blog; public interface BlogMapper { // 添加博客 int addBlog(Blog blog); }
-
编写 Mapper接口对应的 Mapper.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"> <!-- 原来我们都是编写具体的执行sql --> <mapper namespace="com.su.mapper.BlogMapper"> <insert id="addBlog" parameterType="Blog"> insert into mybatis.blog (id,title,author,create_time,views) values (#{id}, #{title}, #{author}, #{createDate}, #{views}); </insert> </mapper>
2.2 IF(多条件选择)
-
格式:
<select id="" resultType="blog"> SELECT * from * WHERE * = * <if test="要判断的条件"> sql 语句; <!--如果满足条件,则在 SQL 语句中增加此语句--> </if> </select>
-
BlogMapper.xml
:<select id="queryBlogIF" 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>
-
当不输入查询条件时候,将输出所有的查询结果:
@Test public void queryBlogIf(){ SqlSession session = MyBatisUtils.getSession(); BlogMapper mapper = session.getMapper(BlogMapper.class); HashMap map = new HashMap(); List<Blog> blogs = mapper.queryBlogIF(map); for (Blog blog : blogs) { System.out.println(blog); }
结果如下:
-
只传入 “title”:
@Test public void queryBlogIf(){ SqlSession session = MyBatisUtils.getSession(); BlogMapper mapper = session.getMapper(BlogMapper.class); HashMap map = new HashMap(); map.put("title","math"); List<Blog> blogs = mapper.queryBlogIF(map); for (Blog blog : blogs) { System.out.println(blog); } }
结果:
-
只传入 “author”:
@Test public void queryBlogIf(){ SqlSession session = MyBatisUtils.getSession(); BlogMapper mapper = session.getMapper(BlogMapper.class); HashMap map = new HashMap(); //map.put("title","math"); map.put("author","numb"); List<Blog> blogs = mapper.queryBlogIF(map); for (Blog blog : blogs) { System.out.println(blog); } }
结果:为了便于结果比较,在数据库中将两门课程的作者改为其他人;
-
两者都传入:
@Test public void queryBlogIf(){ SqlSession session = MyBatisUtils.getSession(); BlogMapper mapper = session.getMapper(BlogMapper.class); HashMap map = new HashMap(); map.put("title","math-1"); map.put("author","Rose"); List<Blog> blogs = mapper.queryBlogIF(map); for (Blog blog : blogs) { System.out.println(blog); } }
结果:
2.3 Trim(Where, set)
2.3.1 Where
-
简化 SQL 判断条件
- 使用 where 标签时,它上层的 sql 语句不加 where 条件关键字;
- 如果 where 标签内有返回值,则 where 会自动给 sql 语句加上 where;
- 如果这个标签(返回值)是以 and,or 等查询关键字开头,则自动去除这些关键字;
- 如果 where 标签内没有返回值,则不加 where 条件关键字;
-
实例:
BlogMapper.xml
:<select id="queryBlogWhere" resultType="Blog"> select * from mybatis.blog <!--外层的where关键字不用写;如果where标签内有返回值,则自动加上where关键字,如果没有返回值,则不会加where关键字--> <where> <if test="title !=null"> title = #{title} </if> <if test="author !=null"> AND author = #{author} </if> </where> </select>
测试:
@Test public void quertBlogWhere() { SqlSession session = MyBatisUtils.getSession(); BlogMapper mapper = session.getMapper(BlogMapper.class); HashMap map = new HashMap(); // map.put("title", "JavaSE"); map.put("author", "Bob"); List<Blog> blogs = mapper.queryBlogWhere(map); for (Blog blog : blogs) { System.out.println(blog); } }
2.3.2 Set
-
Set 动态更新
- 更新时,若某个参数为 null,则不需要更新,保持数据库原值;
- 使用 set 标签时,它的上层 sql 语句不加 set 条件关键字;
- 如果 set 标签内有返回值,则 set 会自动给 sql 语句加上 set 关键字;
- set 标签会自动去除(返回值)以 “,” 分隔符结束的 sql 语句;
-
实例:
BlogMapper.xml
:<update id="updateBlog" 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>
测试:
@Test public void updateBlog(){ SqlSession session = MyBatisUtils.getSession(); BlogMapper mapper = session.getMapper(BlogMapper.class); HashMap map = new HashMap(); map.put("title","Love"); map.put("author", "Numb"); map.put("id","3e938d94a312493db85ce29c38114867"); mapper.updateBlog(map); session.close(); }
2.3.3 Trim
-
trim 去除多余关键字,代替 set 和 where
-
trim 标签会自动识别标签内是否有返回值,如果有返回值,则会加上属性的作用;
-
属性:
- prefix 前缀 (在 trim 元素包含的内容上加上前缀)
- where----------where标签
- set----------set标签
- suffix 后缀(在 trim 元素包含的内容上加上后缀)
- prefixOverrides 前缀覆盖:可以覆盖(去除)sql语句开头的 and 或 or 关键字等,例:prefixOverrides=“and | or”
- suffixOverrides 后缀覆盖 :可以覆盖 sql 语句尾部的",“分隔符,例:suffixOverrides=”,"
- prefix 前缀 (在 trim 元素包含的内容上加上前缀)
-
和 where 元素等价的自定义 trim 元素为:
<trim prefix="WHERE" prefixOverrides="AND |OR "> ... </trim>
-
和 set元素等价的自定义 trim 元素为:
<trim prefix="SET" suffixOverrides=","> ... </trim>
-
2.4 choose(when、ptherwise)
-
有些时候,我们不想用到所有的条件语句,而只想从中择其一二。针对这种情况,MyBatis 提供了 choose 元素,它有点像 Java 中的 switch 语句。
-
还是上面的例子,但是这次变为提供了 “title” 就按 “title” 查找,提供了 “author” 就按 “author” 查找,若两者都没有提供,就返回所有符合条件的BLOG(实际情况可能是由管理员按一定策略选出BLOG列表,而不是返回大量无意义的随机结果)。都满足的情况下,按照第一个查询。
-
实例:
BlogMapper.xml
:<select id="quertBlogChoose" resultType="Blog"> select * from mybatis.blog <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>
测试:
@Test public void quertBlogChoose(){ SqlSession session = MyBatisUtils.getSession(); BlogMapper mapper = session.getMapper(BlogMapper.class); HashMap map = new HashMap(); map.put("title", "math-1"); map.put("author", "Rose"); map.put("views",10000); List<Blog> blogs = mapper.quertBlogChoose(map); for (Blog blog : blogs) { System.out.println(blog); } session.close(); }
2.5 foreach
-
foreach 的主要用在构建 in 条件中,它可以在 SQL 语句中进行迭代一个集合;
-
属性:
- item:给集合或数组起个别名;
- index:指定一个名称,表示迭代时每次迭代到的位置;
- collection:必须指定(指定用什么类型接收)
- list 集合;
- array 数组;
- map-key 键值对;
- open:表示该语句以什么开始,它的值是
(
; - separator:分隔符,它的值是
,
; - close:表示该语句以什么结束,它的值是
)
;
-
实例:
BlogMapper.xml
:<!-- sql() 子查询 where in(1,2,3) collection 输入的参数 map集合 item 遍历出来的每一项 通过iteam遍历出来的参数,可以在 foreach 标签中使用 --> <select id="queryBlogByForeach" parameterType="map" resultType="Blog"> select * from mybatis.blog <where> <!-- collection="array" item="ids" 给数组的名称是ids open="(" 以"("开始 close=")" 以")"结束 separator="," 分隔符是"," --> <!-- select * from mybatis.blog where and(id=1 or id=2 or id =3)--> <foreach collection="ids" item="id" open="and (" close=")" separator="or"> id = #{id} </foreach> </where> </select>
测试:
@Test public void testForEach(){ SqlSession session = MyBatisUtils.getSession(true); BlogMapper mapper = session.getMapper(BlogMapper.class); HashMap<String,List> map = new HashMap(); List<String> ids = new ArrayList<String>(); ids.add("0156c8a31b734b30bb89622669e22998"); ids.add("3dbbe603f70f40bbbc94ae6f312c5ca0"); ids.add("3e938d94a312493db85ce29c38114867"); map.put("ids",ids); mapper.queryBlogByForeach(map); session.close(); }