11.动态sql
什么是动态sql？
-
动态 SQL 是 MyBatis 的强大特性之一。
-
在 MyBatis 之前的版本中，需要花时间了解大量的元素。借助功能强大的基于 OGNL 的表达式，MyBatis 3 替换了之前的大部分元素，大大精简了元素种类，现在要学习的元素种类比原来的一半还要少。
-
if
-
choose (when, otherwise)
-
trim (where, set)
-
foreach
-
11.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
?
?
INSERT INTO blog VALUES(‘1‘,‘设计模式之单例模式‘,‘xuan‘,CURRENT_DATE,100);
INSERT INTO blog VALUES(‘2‘,‘设计模式之工厂模式‘,‘xuan‘,CURRENT_DATE,200);
INSERT INTO blog VALUES(‘3‘,‘设计模式之创建者模式‘,‘xuan‘,CURRENT_DATE,300);
INSERT INTO blog VALUES(‘4‘,‘设计模式之抽象工厂模式‘,‘xuan‘,CURRENT_DATE,400);
INSERT INTO blog VALUES(‘5‘,‘设计模式之责任链模式‘,‘xuan‘,CURRENT_DATE,1000);
INSERT INTO blog VALUES(‘6‘,‘设计模式之桥接模式‘,‘xuan‘,CURRENT_DATE,50);
?
第二步：导入依赖、编写实体类，接口以及对应的配置文件
<dependencies>
<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.5</version>
</dependency>
<!--mysql数据库驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.20</version>
</dependency>
<!--junit测试-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.12</version>
</dependency>
</dependencies>
public interface BlogMapper {
}
11.2 IF语句
-
使用动态 SQL 最常见情景是根据条件包含 where 子句的一部分
第一步：编写mapper接口
public interface BlogMapper {
?
//通过if条件查询所需信息
List<Blog> findBlogByIf(Map<String,Object> map)throws Exception;
}
第二步：编写配置 加上if条件
<select id="findBlogByIf" parameterType="map" resultType="blog">
select * from blog where 1=1
<if test="title != null">
and title=#{title}
</if>
<if test="author != null">
and author=#{author}
</if>
</select>
第三步：编写测试map为空返回全部
-
测试结果
Blog(id=1, title=设计模式之单例模式, author=xuan, createTime=Tue Jul 14 08:00:00 GMT+08:00 2020, views=100)
Blog(id=2, title=设计模式之工厂模式, author=xuan, createTime=Tue Jul 14 08:00:00 GMT+08:00 2020, views=200)
Blog(id=3, title=设计模式之创建者模式, author=xuan, createTime=Tue Jul 14 08:00:00 GMT+08:00 2020, views=300)
Blog(id=4, title=设计模式之抽象工厂模式, author=xuan, createTime=Tue Jul 14 08:00:00 GMT+08:00 2020, views=400)
Blog(id=5, title=设计模式之责任链模式, author=xuan, createTime=Tue Jul 14 08:00:00 GMT+08:00 2020, views=1000)
Blog(id=6, title=设计模式之桥接模式, author=xuan, createTime=Tue Jul 14 08:00:00 GMT+08:00 2020, views=50)
第四步：测试加上if判断的语句
-
测试结果
Blog(id=3, title=设计模式之创建者模式, author=xuan, createTime=Tue Jul 14 08:00:00 GMT+08:00 2020, views=300)
11.3 choose、when、otherwise语句
-
有时候，我们不想使用所有的条件，而只是想从多个条件中选择一个使用。
第一步：编写mapper接口
//通过choose条件查询所需信息
List<Blog> findBlogByChoose(Map<String,Object> map)throws Exception;
第二步：编写配置文件（加入choose判断语句）
<select id="findBlogByChoose" parameterType="map" resultType="blog">
select * from blog where author="xuan"
<choose>
<when test="title != null">
and title=#{title}
</when>
<when test="views != null">
and views=#{views}
</when>
<otherwise>
and id=1
</otherwise>
</choose>
</select>
第三步：编写测试
--------------------------------------测试成功------------------------------------
11.4 trim、where、set语句
-
where标签作用：where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且，若子句的开头为 “AND” 或 “OR”，where 元素也会将它们去除。
<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>
-
set标签作用：set 元素会动态地在行首插入 SET 关键字，并会删掉额外的逗号（这些逗号是在使用条件语句给列赋值时引入的）。
<update id="updateAuthorIfNecessary">
update Author
<set>
<if test="username != null">username=#{username},</if>
<if test="password != null">password=#{password},</if>
<if test="email != null">email=#{email},</if>
<if test="bio != null">bio=#{bio}</if>
</set>
where id=#{id}
</update>
-
trim标签：prefixOverrides 属性会忽略通过管道符分隔的文本序列（注意此例中的空格是必要的）。上述例子会移除所有 prefixOverrides 属性中指定的内容，并且插入 prefix 属性中指定的内容。
<trim prefix="WHERE" prefixOverrides="AND |OR ">
...
</trim>
<trim prefix="SET" suffixOverrides=",">
...
</trim>
11.5 foreach语句
-
动态 SQL 的另一个常见使用场景是对集合进行遍历（尤其是在构建 IN 条件语句的时候）
<select id="selectPostIn" resultType="domain.blog.Post">
SELECT *
FROM POST P
WHERE ID in
<foreach item="item" index="index" collection="list"
open="(" separator="," close=")">
#{item}
</foreach>
</select>
-
foreach 元素的功能非常强大，它允许你指定一个集合，声明可以在元素体内使用的集合项（item）和索引（index）变量。
-
它也允许你指定开头与结尾的字符串以及集合项迭代之间的分隔符（ open="(" separator="," close=")">）。这个元素也不会错误地添加多余的分隔符
完整的sql: select * from blog where id in (1 or 2 or 3)
<select id="findByForEach" resultType="blog" parameterType="map">
select * from blog
<where>
<foreach item="id" index="index" collection="ids"
open="(" separator="or" close=")">
id = #{id}
</foreach>
</where>
</select>
11.6 SQL片段
为什么要写SQL片段？
-
主要是可以实现代码复用，有些重复的sql抽取出来重复使用。
-
sql标签： 通过id用来存放可重复利用的片段
-
include标签：refid用来引入sql标签中的语句
<select id="findBlogByIf" parameterType="map" resultType="blog">
select * from blog
<where>
<include refid="sql-if"></include>
</where>
</select>
?
<sql id="sql-if">
<if test="title != null">
and title=#{title}
</if>
<if test="author != null">
and author=#{author}
</if>
</sql>