什么是动态SQL：根据不同的条件生成不同的SQL语句，利用动态 SQL，可以彻底摆脱这种痛苦。
如果你之前用过 JSTL 或任何基于类 XML 语言的文本处理器，你对动态 SQL 元素可能会感觉似曾相识。在 MyBatis 之前的版本中，需要花时间了解大量的元素。借助功能强大的基于 OGNL 的表达式，MyBatis 3 替换了之前的大部分元素，大大精简了元素种类，现在要学习的元素种类比原来的一半还要少。
??????? 1.SQL
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;??????? 2.创建一个基础工程
??????????????? ① 导包
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.10</version>
</dependency>??????????????? ② 编写实体类
@Data
public class Blog {
private String id;
private String title;
private String author;
private Date createTime;//属性名和字段名不一致
private int views;
}??????????????? ③ 编写实体类对应Mapper接口
public interface BlogMapper {
//插入数据
int addBlog(Blog blog);
}??????????????? ④ 编写接口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.sun.dao.BlogMapper">
<insert id="addBlog" parameterType="blog">
insert into blog(id,title,author,create_time,views)
values (#{id},#{title},#{author},#{create_time},#{views})
</insert>
</mapper>??????????????? ⑤ 编写Mapper.xml文件
<!--是否开启驼峰命名自动映射，即从经典数据库列名 A_COLUMN 映射到经典 Java 属性名 aColumn。-->
<setting name="mapUnderscoreToCamelCase" value="true"/>
<mappers>
<mapper class="com.sun.dao.BlogMapper"/>
</mappers>??????????????? ⑥ 创建IDUtils类
public class IDUtils {
public static String getId(){
return UUID.randomUUID().toString().replaceAll("-","");
}
}??????? ??????? ⑦ 测试类
@Test
public void addBlog(){
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("Spring如此简单");
mapper.addBlog(blog);
blog.setId(IDUtils.getId());
blog.setTitle("微服务如此简单");
mapper.addBlog(blog);
sqlSession.close();
}????????1.编写Mapper接口
//查询博客
List<Blog> queryBlogIF(Map map);??????? 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>??????? 3.测试类
@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();
}例如switch case
????????1.编写Mapper接口
List<Blog> queryBlogChoose(Map map);??????? 2.编写接口xml文件
<select id="queryBlogChoose" resultType="blog" parameterType="map">
select * from 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>??????? 3.测试类
@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","狂神说");
List<Blog> blogs = mapper.queryBlogChoose(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
sqlSession.close();
}????????1.编写Mapper接口
//查询博客
List<Blog> queryBlogIF(Map map);
//更新
int updataBlog(Map map);
??????? 2.编写接口xml文件
<select id="queryBlogIF" parameterType="map" resultType="blog">
select * from blog
<where>
<if test="title != null">
and title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</where>
</select>
<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>??????? 3.测试类
@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();
}
@Test
public void updateBlog(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap map = new HashMap();
map.put("title","Java如此简单2");
map.put("author","狂神说");
map.put("id","9ebbfeab76854bb69d3b0869bdfa8dad");
mapper.updateBlog(map);
sqlSession.close();
}????????1.编写Mapper接口
//查询第1、2、3号记录的博客
List<Blog> queryBlogForeach(Map map);??????? 2.编写接口xml文件
<select id="queryBlogForeach" parameterType="map" resultType="blog">
select * from blog
<where>
<foreach collection="ids" item="id" open="and (" close=")" separator="or">
id = #{id}
</foreach>
</where>
</select>??????? 3.测试类
@Test
public void queryBlogForeach(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap map = new HashMap();
ArrayList<Integer> ids = new ArrayList<Integer>();
ids.add(1);
ids.add(2);
map.put("ids",ids);
List<Blog> blogs = mapper.queryBlogForeach(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
sqlSession.close();
}
}有的时候我们可能会将一些公共的部分抽取出来方便复用。
??????? 1.使用SQL标签抽取公共部分
??????? 2.在需要使用的地方使用include标签引用即可
例如：
????????更改前：
<select id="queryBlogIF" parameterType="map" resultType="blog">
select * from blog
<where>
<if test="title != null">
and title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</where>
</select>?????????更改后：
<sql id="if-title-author">
<if test="title != null">
and title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</sql>
<select id="queryBlogIF" parameterType="map" resultType="blog">
select * from blog
<where>
<include refid="if-title-author"></include>
</where>
</select>注意：
?????? 1. 最好基于单表来定义SQL片段！
?????? 2.在SQL片段里不要存在where标签。
??????? 1.所谓的动态SQL，本质还是SQL语句，只是我们可以在SQL层面，去执行一个逻辑代码
??????? 2.动态sql就是在拼接sql语句，我们只要保证给sql的正确性；按照sql的格式，去排列组合就可以了
建议：现在MySQL中写出完整的SQL，再对应的去修改成为我们的动态SQL实通用即可。