catalogue
Dynamic SQL refers to generating different SQL statements according to different conditions under different circumstances
Build environment
database
CREATE TABLE `blog`( `id` VARCHAR(50) NOT NULL COMMENT 'Blog id', `title` VARCHAR(100) NOT NULL COMMENT 'Blog title', `author` VARCHAR(30) NOT NULL COMMENT 'Blogger', `create_time` DATETIME NOT NULL COMMENT 'Creation time', `views` INT(30) NOT NULL COMMENT 'Views' )ENGINE=INNODB DEFAULT CHARSET=utf8
Entity class
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Blog {
private String id;
private String title;
private String author;
private Date createTime;
private int views;
}Entity classes correspond to mapper and xml files
public interface BlogMapper {
}<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.zhouyue.dao.BlogMapper">
</mapper>UUID tool class
public class IDUtils {
public static String getId(){
return UUID.randomUUID().toString().replaceAll("-","");
}
}Insert data test
@Test
public void test(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
mapper.addBlog(new Blog(IDUtils.getId(), "MyBatis study", "Su Fuai", new Date(), 154));
mapper.addBlog(new Blog(IDUtils.getId(), "Spring study", "Su Fuai", new Date(), 632));
mapper.addBlog(new Blog(IDUtils.getId(), "SpringMVC study", "Su Fuai", new Date(), 148));
mapper.addBlog(new Blog(IDUtils.getId(), "SpringBoot study", "Su Fuai", new Date(), 248));
sqlSession.close();
}
if
Realize the following functions:
If the title is passed in, the specified blog will be queried. If the author is passed in, all blogs of the author will be queried. If nothing is posted, all blogs will be queried
We can use if in MyBatis to realize this function
Tabular data
Interface: receive parameters through map
//Query blog
List<Blog> queryBlogIF(Map map);sql statement: Here we use if to realize the splicing of different sql statements under different circumstances
<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>test
@Test
public void test1(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
Map map = new HashMap();
// map. Put ("title", "spring MVC learning");
// map.put("author", "Su Fu AI");
List<Blog> blogs = mapper.queryBlogIF(map);
for (Blog blog :
blogs) {
System.out.println(blog);
}
sqlSession.close();
}No parameters
Pass in title
Incoming author
By passing in title and author, we can find that the sql statement splices both
choose,when,otherwise
choose, when and otherwise statements can be understood as switch, case and default statements
Use this statement to realize that if you pass in title, you will query the specified blog; if you pass in author, you will query all blogs of the author; if you pass nothing, you will query the blog with 632 views
sql statement
<select id="queryBlogIF" parameterType="map" resultType="blog">
select * from blog where 1=1
<choose>
<when test="title != null">
and title = #{title}
</when>
<when test="author != null">
and author = #{author}
</when>
<otherwise>
and views = 632
</otherwise>
</choose>
</select>The test statement is the same as before
No parameters
Pass in title
Incoming author
By passing in title and author, we can find that the sql statement only splices the title, that is, the content in the front when tag
Add a blog with the same name. You can find that both blogs have been found
trim,where,set
where
In the previous case, in order to meet the splicing of where, we used the form of 1 = 1, but this is obviously not good, so we can use the where tag for splicing
The WHERE element inserts the "WHERE" clause only if the child element returns anything. Moreover, if clause starts with the "AND" OR ", WHERE element will also remove them.
Use where to modify the above case
sql statement
<select id="queryBlogIF" parameterType="map" resultType="blog">
select * from blog
<where>
<choose>
<when test="title != null">
title = #{title}
</when>
<when test="author != null">
and author = #{author}
</when>
<otherwise>
and views = 632
</otherwise>
</choose>
</where>
</select>
set
The function of SET statement is similar to that of where. A similar solution for dynamically updating statements is called set. The set element can be used to dynamically include columns that need to be updated and ignore other columns that do not need to be updated. The set element will dynamically insert the set keyword at the beginning of the row and delete additional commas (these commas are introduced when assigning values to columns using conditional statements).
Examples
Interface
//Update blog
int updateBlog(Map map);sql statement
<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>Testing
@Test
public void test2(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
Map map = new HashMap();
map.put("title", "SpringMVC");
map.put("author", "Su Fugo");
map.put("id", "a37f63f48d384f069f062f5c688114e5");
int i = mapper.updateBlog(map);
System.out.println(i);
sqlSession.close();
}
trim
We can use trim to realize the functions of where and set
Implement where
<select id="queryBlogIF" parameterType="map" resultType="blog">
select * from blog
<trim prefix="WHERE" prefixOverrides="AND |OR">
<if test="title != null">
title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</trim>
</select>
Implement set
<update id="updateBlog" parameterType="map">
update blog
<trim prefix="SET" suffixOverrides=",">
<if test="title != null">
title = #{title},
</if>
<if test="author != null">
author = #{author}
</if>
</trim>
where id = #{id}
</update>
Generally speaking, dynamic sql is still sql statement in essence, but we can execute a logical code at the sql level
sql fragment
Sometimes, we will extract some common parts of sql to facilitate reuse and reduce code redundancy. For example, in the above case, we wrote the following code
We can consider extracting it and using sql tags
<sql id="someSQL">
<if test="title != null">
title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</sql>Then introduce the fragment and use the include tag
Corresponding functions can also be realized
foreach
We have the following information
You need to query the blog information whose author is Su Fuai and blog id is 2 or 3
Using sql statements
select * from blog where author = 'Su Fuai' and (id = 2 or id = 3)
In MyBatis, we can use foreach to implement it
Collection: indicates the name of the incoming collection
item: the name of each element in the collection
open: what do you start with
close: what does it end with
Separator: separator
<select id="queryBlogForeach" parameterType="map" resultType="blog">
select * from blog
<where>
author = 'Su Fuai'
<!--select * from blog where author = 'Su Fuai' and (id = 2 or id = 3)-->
<foreach collection="ids" item="id" open="and (" close=")" separator="or">
id = #{id}
</foreach>
</where>
</select>Test:
@Test
public void test3(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
List<Integer> ids = new ArrayList<>();
ids.add(2);
ids.add(3);
Map map = new HashMap();
map.put("ids",ids);
List<Blog> blogs = mapper.queryBlogForeach(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
sqlSession.close();
}It can be found that the sql is successfully spliced and the correct results are obtained
In a word, dynamic sql is actually splicing sql statements