Version: Next

动态SQL

什么是动态SQL: 就是指根据不同的条件生成不同的SQL语句

所谓的动态SQL本质还是SQL语句，只是我们可以在SQL层面执行一些逻辑代码

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;

创建一个基础工程mybatis08

导包
编写配置文件
编写实体类
@Data
public class Blog {
private String id;
private String titile;
private String author;
private Date createTime;
private int views;
}
编写实体类对应的Mapper接口和mapper.xml文件
//插入数据
int addBlog(Blog blog);
<mapper namespace="com.bsx.dao.BlogMapper">
<insert id="addBlog" parameterType="com.bsx.pojo.Blog">
insert into blog (id, title, author, create_time, views)
values (#{id}, #{title} , #{author}, #{createTime}, #{views});
</insert>
</mapper>
编写IDUtils工具类
public class IDUtils {
public static String getUUID() {
return UUID.randomUUID().toString().replace("-", "");
}
}
1. 开启驼峰命名映射在核心配置文件中配置settings开启mapUnderscoreToCamelCase
<settings>
<setting name="logImpl" value="LOG4J"/>
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
1. 测试
@Test
public void testInsert() throws IOException {
InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession(true);

try {
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
Blog blog = new Blog();
blog.setId(IDUtils.getUUID());
blog.setAuthor("bsx");
blog.setTitle("Title 1");
blog.setCreateTime(new Date());
blog.setViews(9999);

mapper.addBlog(blog);

blog.setId(IDUtils.getUUID());
blog.setTitle("Title 2");
blog.setCreateTime(new Date());

mapper.addBlog(blog);

blog.setId(IDUtils.getUUID());
blog.setTitle("Title 3");
blog.setCreateTime(new Date());

mapper.addBlog(blog);

} catch (Exception e) {
e.printStackTrace();
} finally {
sqlSession.close();
}
}

2. 动态SQL之IF语句

如果传了作者就按作者查，如果传了标题就按标题查，如果啥也没传就查所有

接口
//查询博客
List<Blog> queryBlogIF(Map map);
mapper.xml
<select id="queryBlogIF" resultType="com.bsx.pojo.Blog" parameterType="map">
select * from blog where 1=1
<if test="title != null">
and title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</select>

测试
@Test
public void testQueryBlogIF() throws IOException {
InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession(true);

try {
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);

//1. 啥也不传
Map map = new HashMap();

//查出所有blog
List<Blog> blogs = mapper.queryBlogIF(map);
for (Blog blog : blogs) {
System.out.println(blog);
}

//2. 传title
map.put("title", "Title 1");

//Blog(id=ac6d574b9f0a4dbe9ecb8a9ce433a461, title=Title 1, author=bsx, createTime=Thu Mar 19 20:38:00 CST 2020, views=9999)
blogs = mapper.queryBlogIF(map);
for (Blog blog : blogs) {
System.out.println(blog);
}

map.put("author", "bsx");

//Blog(id=ac6d574b9f0a4dbe9ecb8a9ce433a461, title=Title 1, author=bsx, createTime=Thu Mar 19 20:38:00 CST 2020, views=9999)
blogs = mapper.queryBlogIF(map);
for (Blog blog : blogs) {
System.out.println(blog);
}

} catch (Exception e) {
e.printStackTrace();
} finally {
sqlSession.close();
}
}

3. 动态SQL之常用标签

3.1 trim(where, set)

where元素只会在至少有一个子元素的条件返回sql子句的情况下才去插入"WHERE"子句。而且，若语句开头为"AND"或者"OR"，where元素也会将它们去除

where标签
<select id="queryBlogIF" resultType="com.bsx.pojo.Blog" parameterType="map">
select * from blog
<where>
<if test="title != null">
and title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</where>
</select>
set 用在Update语句中，set元素会动态前置SET关键字，同时也会删掉无关的逗号，因为用了条件语句之后很可能就会在生成的SQL语句的后面留下这些逗号
- 接口
  //更新博客
  int updateBlog(Map map);
- mapper.xml
  <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>
- 测试
  @Test
  public void testUpdateSet(){
  SqlSession sqlSession = MybatisUtils.getSqlSession();
  
  try {
  BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
  Map map = new HashMap();
  //这里的id从数据库复制
  map.put("id","ac6d574b9f0a4dbe9ecb8a9ce433a461");
  map.put("title","UpdatedTitle");
  map.put("author","updateAuthor");
  
  mapper.updateBlog(map);
  } catch (Exception e) {
  e.printStackTrace();
  } finally {
  sqlSession.close();
  }
  }
trim 把,后缀替换为SET
<trim prefix="SET" suffixOverrides=",">
...
</trim>
同理把AND或者OR替换为WHERE

3.2 choose, when, otherwise

类似Java中的switch语句,用where标签替换sql语句中的where

接口
//test Choose when otherwise
List<Blog> queryBlogChoose(Map map);
mapper.xml
<select id="queryBlogChoose" resultType="com.bsx.pojo.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>
测试

@Test
public void testChoose() {
SqlSession sqlSession = MybatisUtils.getSqlSession();

try {
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
Map map = new HashMap();
map.put("views", 9999);

List<Blog> blogs = mapper.queryBlogChoose(map);
for (Blog blog : blogs) {
System.out.println(blog);
}

// map.put("title", "Title 1");
// blogs = mapper.queryBlogChoose(map);
// for (Blog blog : blogs) {
// System.out.println(blog);
// }

map.put("author", "bsx");
blogs = mapper.queryBlogChoose(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
sqlSession.close();
}
}

4. SQL片段

有的时候，我们可能会将一些公共的部分抽取出来，用sql标签包起来，方便复用。

实用include标签引用sql标签的内容

注意事项：

最好基于单表定义SQL片段
不要存在where标签

<select id="queryBlogIF" resultType="com.bsx.pojo.Blog" parameterType="map">
    select * from blog where 1=1
    <include refid="if-title-author"/>
</select>

 <sql id="if-title-author">
    <if test="title != null">
        and title = #{title}
    </if>
    <if test="author != null">
        and author = #{author}
    </if>
</sql>

5. 动态SQL之Foreach

select * from blog where 1=1 and (id=1 or id=2 or id=3)

动态SQL的另一个常用的操作需求是对一个集合进行遍历，通常是在构造IN条件语句的时候

foreach标签允许指定一个集合，声名可以在元素体内实用的集合项item和索引index变量。它也允许你指定开始和结尾的字符串以及迭代结果之间分隔符。

当使用List、Set或者数组时，index是当前迭代的次数，item是本次迭代获取的元素；当使用Map对象或者Map.Entry对象的集合时，index是键，item是值

为了测试方便，现将数据库中的id改为1234的形式

接口
//查询第1-2-3号记录的博客
List<Blog> queryBlogForeach(Map map);
mapper.xml 现在传递一个万能的map，这个map中存在一个集合ids对应foreach中的collection，其中的每一项为id对应item
<select id="queryBlogForeach" resultType="com.bsx.pojo.Blog" parameterType="map">
select * from blog
<where>
<foreach collection="ids" item="id" open="(" separator="or" close=")">
id = #{id}
</foreach>
</where>
</select>

测试
@Test
public void testForeach() {
SqlSession sqlSession = MybatisUtils.getSqlSession();

try {
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
Map map = new HashMap();
List list = Arrays.asList(1, 2, 3);
map.put("ids", list);
List<Blog> blogs = mapper.queryBlogForeach(map);

for (Blog blog : blogs) {
System.out.println(blog);
}

} catch (Exception e) {
e.printStackTrace();
} finally {
sqlSession.close();
}
}
结果
Blog(id=1, title=UpdatedddddTitle, author=updateAuthor, createTime=Thu Mar 19 20:38:00 CST 2020, views=9999)
Blog(id=2, title=Title 2, author=bsx, createTime=Thu Mar 19 20:38:01 CST 2020, views=9999)
Blog(id=3, title=Title 3, author=bsx, createTime=Thu Mar 19 20:38:01 CST 2020, views=9999)
动态情况
select * from blog
select * from blog where (id=1)
select * from blog where (id=1 or id=2 or id=2)

动态SQL就是在拼接SQL语句，我们只要确保SQL的正确性，按照SQL的格式，进行排列组合

建议：

先在Mysql中写出完整的SQL，在对应写改成为动态SQL，实现通用即可

动态SQL#

1. 环境搭建#

2. 动态SQL之IF语句#

3. 动态SQL之常用标签#

3.1 trim(where, set)#

3.2 choose, when, otherwise#

4. SQL片段#

5. 动态SQL之Foreach#