12. 动态SQL
什么是动态SQL:动态SQL就是根据不同的条件生成不同的SQL语句
动态 SQL 元素和 JSTL或任何基于类 XML 语言的文本处理器相似。在 MyBatis 之前的版本中,需要花时间了解大量的元素。借助功能强大的基于 OGNL 的表达式,MyBatis 3 替换了之前的大部分元素,大大精简了元素种类,现在要学习的元素种类比原来的一半还要少。
- if
- choose (when, otherwise)
- trim (where, set)
- foreach
12.1.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;
12.1.2 导包
12.1.3 编写实体类
@Alias("blog")
@Data
public class Blog {
private int id;
private String title;
private String author;
private Date createTime;
private int views;
}
12.1.4 编写Mapper接口和Mapper.xml配置文件
public interface BlogMapper {
//插入数据
int addBlog(Blog blog);
}
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.hwt.dao.BlogMapper">
<insert id="addBlog">
insert into blog (id, title, author, create_time, views)
VALUE (#{id},#{title},#{author},#{createTime},#{views});
</insert>
</mapper>
12.1.5 插入实验数据
public class ITest {
@Test
public void addBlog(){
SqlSession sqlSession = MybatisUntils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
Blog blog = new Blog();
blog.setId(IdUtils.getId());
blog.setTitle("保证SQL的可读性,尽量保证通俗易懂");
blog.setAuthor("高启强");
blog.setCreateTime(new Date());
blog.setViews(6666);
mapper.addBlog(blog);
blog.setId(IdUtils.getId());
blog.setTitle("注意一对多和多对一中,属性名和字段的问题");
blog.setAuthor("高启盛");
mapper.addBlog(blog);
blog.setId(IdUtils.getId());
blog.setTitle("如果问题不好排查错误,可以使用日志,建议使用log4j");
blog.setAuthor("麻子");
mapper.addBlog(blog);
sqlSession.close();
}
}
12.2 IF
12.2.1 接口
//查询博客
List<Blog> queryBlogIf(Map map);
12.2.2 XML
<select id="queryBlogIf" 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>
12.2.3 测试
@Test
public void queryBlogIf(){
SqlSession sqlSession = MybatisUntils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap map = new HashMap();
map.put("title","保证SQL的可读性,尽量保证通俗易懂");
map.put("author","高启强");
List<Blog> blogs = mapper.queryBlogIf(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
sqlSession.close();
}
12.3 choose,when,otherwise
choose:不想使用所有的条件,而只是想从多个条件中选择一个使用。
<select id="queryBlogChoose" resultType="Blog" parameterType="map">
select * from blog
<where>
<choose>
<when test="title != null">
title = #{title}
</when>
<otherwise>
and views = #{views}
</otherwise>
</choose>
</where>
</select>
12.4 trim,where,set
12.4.1 where
where: 只会在子元素返回任何内容的情况下才插入“where”字句,而且如果字句的开头位AND或者是OR,where也会将他们去除。
<select id="queryBlogIf" parameterType="map" resultType="blog">
select * from blog
<where>
<if test="title != null">
title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</where>
</select>
12.4.2 set
set: 会动态的在行首插入set关键字,并会删掉额外的逗号(这些逗号是在使用条件是在使用条件语句给列赋值时引用)或者可以通过使用trim元素来达到同样的效果。
<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>
12.4.3 trim
prefix/suffix属性:如果trim后内容不为空,则增加某某字符串(作前缀或后缀),如果trim后内容不为空,则删掉前缀或者后缀某某字符串。
<trim prefix="SET" suffixOverrides=",">
...
</trim>
我们覆盖了后缀值设置,并且自定义了前缀值。所谓动态动态SQL,本质还是SQL语句,只是我们可以在SQL层面去执行一个逻辑代码。
12.4.4 SQL片段
就是将SQL中一些功能片段提取出来,方便使用。
- 使用sql标签抽取公共部分
<sql id="if-title-author">
<if test="title != null">
title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</sql>
- 在需要的地方使用include标签引用
<select id="queryBlogIf" parameterType="map" resultType="blog">
select * from blog
<where>
<include refid="if-title-author"></include>
</where>
</select>
注意:
最好基于单表来定义sql片段,不要存在where标签。
12.4.5 foreach
1.接口
//查询第1,2,3号的记录
List<Blog> queryBlogForeach(Map map);
2.mapper.xml
<select id="selectPostIn" resultType="domain.blog.Post">
SELECT *
FROM POST P
<where>
<foreach item="item" index="index" collection="list"
open="ID in (" separator="," close=")" nullable="true">
#{item}
</foreach>
</where>
</select>
foreach 元素的功能非常强大,它允许指定一个集合,声明可以在元素体内使用的集合项(item)和索引(index)变量。也允许指定开头与结尾的字符串以及集合项迭代之间的分隔符。这个元素也不会错误地添加多余的分隔符。
提示
可以将任何可迭代对象(如 List、Set 等)、Map 对象或者数组对象作为集合参数传递给 foreach。当使用可迭代对象或者数组时,index 是当前迭代的序号,item 的值是本次迭代获取到的元素。当使用 Map 对象(或者 Map.Entry 对象的集合)时,index 是键,item 是值。
<select id="queryBlogForeach" resultType="Blog" parameterType="map">
select * from blog
<where>
<foreach collection="ids" item="id"
open="and (" close=")" separator="or">
id =#{id}
</foreach>
</where>
</select>
小结:
动态SQL就是在拼接SQL语句,我们只要保证SQL的正确性,按照SQL的格式,去排列组合就可以了
建议:
先在Mysql中写出完整的SQL再对应去修改成为我们的动态SQL实现通用即可
Mysql重点掌握的知识
- Mysql引擎
- InnoDB底层原理
- 索引
- 索引优化