[crazy God said] Mybatis latest complete tutorial IDEA version reference link: https://www.bilibili.com/video/BV1NE411Q7Nx
[crazy God said] complete route of Java learning https://www.bilibili.com/read/cv5702420
catalogue
Create a new database table: blog
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.
Build environment
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
IDutil utility class
public class IDUtil {
public static String genId(){
return UUID.randomUUID().toString().replaceAll("-","");
}
}Entity class preparation [pay attention to the function of set method]
import java.util.Date;
@Data
public class Blog {
private String id;
private String title;
private String author;
private Date createTime;
private int views;
}Write Mapper interface and xml file
public interface BlogMapper {
}
<?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.kuang.dao.BlogMapper">
</mapper>mybatis core configuration file, underline Hump Automatic Conversion
<settings> <setting name="mapUnderscoreToCamelCase" value="true"/> <setting name="logImpl" value="STDOUT_LOGGING"/> </settings> <!--register Mapper.xml--> <mappers> <mapper class="com.kuang.dao.BlogMapper"/> </mappers>
Insert initial data
Write interface
//Add a new blog int addBlog(Blog blog);
sql configuration file
<insert id="addBlog" parameterType="blog">
insert into blog (id, title, author, create_time, views)
values (#{id},#{title},#{author},#{createTime},#{views});
</insert>Initialize blog method
@Test
public void addInitBlog(){
SqlSession session = MybatisUtils.getSession();
BlogMapper mapper = session.getMapper(BlogMapper.class);
Blog blog = new Blog();
blog.setId(IDUtil.genId());
blog.setTitle("Mybatis So simple");
blog.setAuthor("Madness theory");
blog.setCreateTime(new Date());
blog.setViews(9999);
mapper.addBlog(blog);
blog.setId(IDUtil.genId());
blog.setTitle("Java So simple");
mapper.addBlog(blog);
blog.setId(IDUtil.genId());
blog.setTitle("Spring So simple");
mapper.addBlog(blog);
blog.setId(IDUtil.genId());
blog.setTitle("Microservices are so simple");
mapper.addBlog(blog);
session.close();
}if statement
Demand: query the blog according to the author's name and blog name! If the author's name is empty, query only by the blog name; otherwise, query by the author's name
1. Writing interface classes
//Demand 1 List<Blog> queryBlogIf(Map map);
2. Writing SQL statements
<!--Requirement 1:
According to the author's name and blog name to query the blog!
If the author's name is empty, query only by the blog name; otherwise, query by the author's name
select * from blog where title = #{title} and author = #{author}
-->
<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>
</select>3. Testing
@Test
public void testQueryBlogIf(){
SqlSession session = MybatisUtils.getSession();
BlogMapper mapper = session.getMapper(BlogMapper.class);
HashMap<String, String> map = new HashMap<String, String>();
map.put("title","Mybatis So simple");
map.put("author","Madness theory");
List<Blog> blogs = mapper.queryBlogIf(map);
System.out.println(blogs);
session.close();
}In this way, we can see that if the author is equal to null, the query statement is select * from user where title=#{title}, but what if the title is empty? The query statement is select * from user where and author=#{author}. This is a wrong SQL statement. How to solve it? Look at the following where statement!
where
Modify the above SQL statement;
<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>The "where" tag will know that if it contains a return value in the tag, it will insert a "where". In addition, if the content returned by the tag starts with AND OR, it will be eliminated.
set
Similarly, the above query SQL statement contains the where keyword. If the update operation contains the set keyword, how can we deal with it?
1. Writing interface methods
int updateBlog(Map map);
2. sql configuration file
<!--be careful set Are separated by commas-->
<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. Testing
@Test
public void testUpdateBlog(){
SqlSession session = MybatisUtils.getSession();
BlogMapper mapper = session.getMapper(BlogMapper.class);
HashMap<String, String> map = new HashMap<String, String>();
map.put("title","dynamic SQL");
map.put("author","Qin Jiang");
map.put("id","9d6a763f5e1347cebda43e2a32687a77");
mapper.updateBlog(map);
session.close();
}choose
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, we can use the choose tag to solve such problems, which is similar to the switch statement in Java
1. Write interface method
List<Blog> queryBlogChoose(Map map);
2. sql configuration file
<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>3. Test class
@Test
public void testQueryBlogChoose(){
SqlSession session = MybatisUtils.getSession();
BlogMapper mapper = session.getMapper(BlogMapper.class);
HashMap<String, Object> map = new HashMap<String, Object>();
map.put("title","Java So simple");
map.put("author","Madness theory");
map.put("views",9999);
List<Blog> blogs = mapper.queryBlogChoose(map);
System.out.println(blogs);
session.close();
}SQL fragment
Sometimes we may use a certain sql statement too much. In order to increase the reusability of the code and simplify the code, we need to extract these codes and call them directly when using them.
Extract SQL fragment:
<sql id="if-title-author">
<if test="title != null">
title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</sql>Reference SQL fragment:
<select id="queryBlogIf" parameterType="map" resultType="blog">
select * from blog
<where>
<!-- quote sql Clip, if refid If the specified is not in this document, it needs to be preceded by namespace -->
<include refid="if-title-author"></include>
<!-- You can also refer to other sql fragment -->
</where>
</select>be careful:
① . 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
Foreach
Modify the IDs of the first three data in the database to 1,2,3;
Requirement: we need to query the blog information with id 1, 2 and 3 in the blog table
1. Write interface
List<Blog> queryBlogForeach(Map map);
2. Writing SQL statements
<select id="queryBlogForeach" parameterType="map" resultType="blog">
select * from blog
<where>
<!--
collection:Specifies the collection properties in the input object
item:Generated objects per traversal
open:Splice string at the beginning of traversal
close:String spliced at end
separator:Traverse the strings that need to be spliced between objects
select * from blog where 1=1 and (id=1 or id=2 or id=3)
-->
<foreach collection="ids" item="id" open="and (" close=")" separator="or">
id=#{id}
</foreach>
</where>
</select>3. Testing
@Test
public void testQueryBlogForeach(){
SqlSession session = MybatisUtils.getSession();
BlogMapper mapper = session.getMapper(BlogMapper.class);
HashMap map = new HashMap();
List<Integer> ids = new ArrayList<Integer>();
ids.add(1);
ids.add(2);
ids.add(3);
map.put("ids",ids);
List<Blog> blogs = mapper.queryBlogForeach(map);
System.out.println(blogs);
session.close();
}Summary: in fact, the compilation 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.
Dynamic SQL is widely used in development. You must master it skillfully!