Dynamic SQL
Official documents:
https://mybatis.net.cn/dynamic-sql.html
What is dynamic SQL: dynamic SQL refers to generating different SQL statements according to different query conditions.
The SQL statements we wrote before are relatively simple. If there are complex businesses, we need to write complex SQL statements, which often need to be spliced. If we don't pay attention to splicing SQL, errors may be caused due to the lack of quotation marks, spaces and so on.
So how to solve this problem? This requires the use of mybatis dynamic SQL. Through tags such as if, choose, when, otherwise, trim, where, set and foreach, it can be combined into very flexible SQL statements, which not only improves the accuracy of SQL statements, but also greatly improves the efficiency of developers.
1. Database design
Create a new database table: blog
Fields: id, title, author, create_time，views
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
2. Create Mybatis foundation project
Create entity class
Blog.java
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Blog {
private String id;
private String title;
private String author;
private Date createTime; // If it is inconsistent with the database field, hump naming conversion can be started
private int views;
}
Start hump naming conversion in the core configuration file and scan by means of guided package
<settings>
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
<mappers>
<!--<mapper class="com.xxc.dao.TeacherMapper"/>
<mapper class="com.xxc.dao.StudentMapper"/>-->
<package name="com.xxc.dao"/>
</mappers>
Create tool class
IDUtils.java
public class IDUtils {
public static String getId(){
return UUID.randomUUID().toString().replaceAll("-","");
}
}
uuid generates a non unique string, which is used as the primary key.
Write and insert data interface
BlogMapper.java
public interface BlogMapper {
int addBlog(Blog blog);
List<Blog> queryBlogIf(Map map);
}
Writing xml files
<?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.xxc.dao.BlogMapper">
<insert id="addBlog" parameterType="blog">
insert into blog (id,title,author,create_time,views)
values (#{id} ,#{title} ,#{author} ,#{createTime},#{views});
</insert>
</mapper>
Write test method
MyTest.java
public class MyTest {
static Logger logger = Logger.getLogger(MyTest.class);
@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 So simple");
blog.setAuthor("Jiangnan rain");
blog.setCreateTime(new Date());
blog.setViews(9999);
mapper.addBlog(blog);
blog.setId(IDUtils.getId());
blog.setTitle("Java So simple");
mapper.addBlog(blog);
blog.setId(IDUtils.getId());
blog.setTitle("Spring So simple");
blog.setViews(1000);
mapper.addBlog(blog);
blog.setId(IDUtils.getId());
blog.setTitle("Microservices are so simple");
blog.setViews(2000);
mapper.addBlog(blog);
sqlSession.close();
}
}
3. Dynamic SQL
Requirements: query data from the blog table. If no parameters are passed, query all parameters. Pass in parameters and query according to parameters.
Transform our blogmapper xml
<?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.xxc.dao.BlogMapper">
<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>
</mapper>
Write test method
MyTest.java
public class MyTest {
static Logger logger = Logger.getLogger(MyTest.class);
@Test
public void queryBlogIf(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap map = new HashMap();
List<Blog> blogs = mapper.queryBlogIf(map);
for (Blog blog : blogs) {
logger.debug(blog);
}
sqlSession.close();
}
}
Discovery: when the map is empty, all data is queried.
Discovery: after the parameter is passed in, the data meeting the requirements is queried.
4. Dynamic SQL common tags
4.1 where
In the actual development, this writing method is not standardized, and the situation of 1 = 1 cannot be written.
<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>
We can do the following transformation
<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>
Note: this "where" tag will know that if it contains a return value in the tag, it will insert a "where". In addition, if the label is returned
If the returned content starts with AND OR, it will be eliminated. [this is the most used case]
4.2 set
Similarly, the above query SQL statement contains the where keyword. If the update operation contains the set keyword,
BlogMapper.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>
Note: note that set is separated by commas
test
public class MyTest {
static Logger logger = Logger.getLogger(MyTest.class);
@Test
public void updateBlog(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap<String, String> map = new HashMap<String, String>();
map.put("title","dynamic SQL");
map.put("author","Jiangnan");
map.put("id","7d83bc1ae6a34171b5a1db74c708ea0f");
mapper.updateBlog(map);
sqlSession.close();
}
}
Note: the SET element will dynamically insert the SET keyword at the beginning of the line and delete the extra comma
4.3 choose(when,otherwise)
Sometimes, we don't want to use all the query conditions. We just want to select one of them. If one of the query conditions is satisfied, use choose
Tags can solve such problems, similar to Java's switch statement
BlogMapper.xml
<select id="queryBlogChoose" 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 = #{views}
</otherwise>
</choose>
</where>
</select>
test
public class MyTest {
static Logger logger = Logger.getLogger(MyTest.class);
@Test
public void queryBlogChoose(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap<String, String> map = new HashMap<String, String>();
map.put("title","dynamic SQL");
map.put("author","Jiangnan");
mapper.queryBlogChoose(map);
sqlSession.close();
}
}
Note: Although several conditions are written, due to the use of choose, he will only select one of the conditions that first meet the sql statement for query.
4.4 Foreach
Another common use scenario for dynamic SQL is traversal of collections (especially when building IN conditional statements).
The foreach element is very powerful. It allows you to specify a collection and declare the collection items and index variables that can be used in the element body. It also allows you to specify the separator between the beginning and end of the string and the iteration of the collection item. This element will not add extra delimiters by mistake. See how smart it is!
In order to demonstrate the effect, first modify the IDs of the first three data in the database to 1,2,3,4;
Requirement: we need to query the blog information with id 1, 2 and 3 in the blog table.
Mode 1:
Thinking: the requirement is equivalent to writing this sql, select * from blog where id in(1,2,3); Then we can splice this sql in xml.
BlogMapper.xml
<?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.xxc.dao.BlogMapper">
<select id="queryBlogForeach" parameterType="map" resultType="blog">
select * from blog
<where>
id in
<foreach collection="ids" item="id" open="(" close=")" separator=",">
#{id}
</foreach>
</where>
</select>
</mapper>
Description: Collection: receive collection. item: the parameter obtained from each traversal. open: start with what. close: with what. Separator: separator.
test
public class MyTest {
static Logger logger = Logger.getLogger(MyTest.class);
@Test
public void queryBlogForeach(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap<String, Object> map = new HashMap<String, Object>();
ArrayList list = new ArrayList();
list.add(1);
list.add(2);
list.add(3);
map.put("ids",list);
List<Blog> blogs = mapper.queryBlogForeach(map);
for (Blog blog : blogs) {
logger.debug(blog);
}
sqlSession.close();
}
}
Note: what is put in the map here is a collection, using list.
Mode 2:
Thinking: the requirement is equivalent to writing this sql, select * from blog where (id = 1 or id = 2 or id = 3); Then we can splice this sql in xml.
BlogMapper.xml
<select id="queryBlogForeach" parameterType="map" resultType="blog">
select * from blog
<where>
<foreach collection="ids" item="id" open="(" close=")" separator="or">
id = #{id}
</foreach>
</where>
</select>
test
public class MyTest {
static Logger logger = Logger.getLogger(MyTest.class);
@Test
public void queryBlogForeach(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap<String, Object> map = new HashMap<String, Object>();
ArrayList list = new ArrayList();
list.add(1);
list.add(2);
list.add(3);
map.put("ids",list);
List<Blog> blogs = mapper.queryBlogForeach(map);
for (Blog blog : blogs) {
logger.debug(blog);
}
sqlSession.close();
}
}
5. SQL fragment
Extract the repeated parts in multiple sql. Enhance the reusability of sql statements.
<mapper namespace="com.xxc.dao.BlogMapper">
<sql id="if-title-author">
<if test="title != null">
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>
</mapper>
sql tag and include tag.
Note: ① it is better to define sql fragments based on a single table to improve the reusability of fragments
② . do not include where in the sql fragment
6. Summary
In fact, the writing of dynamic sql statements is often a problem of splicing. In order to ensure the accuracy of splicing, we'd better first write the original sql statements, and then change them through the dynamic sql of mybatis to prevent errors. It is the skill to master it by using it in practice.