12. Dynamic SQL
What is dynamic SQL?
Dynamic SQL is to generate different SQL statements according to different conditions
Dynamic SQL is one of the powerful features of MyBatis. If you have used JDBC or other similar frameworks, you should be able to understand how painful it is to splice SQL statements according to different conditions. For example, when splicing, make sure you can't forget to add the necessary spaces and remove the comma of the last column name in the list. Using dynamic SQL, you can completely get rid of this pain.
If you've used it before JSTL Or any class based XML Language text processor, you are on the dynamic SQL Elements may feel deja vu. stay MyBatis In previous releases, it took time to understand a large number of elements. With powerful OGNL Expression for, MyBatis 3 Most of the previous elements have been replaced, and the element types have been greatly simplified. Now there are fewer element types to learn than half of the original ones. if choose (when, otherwise) trim (where, set) foreach
Build environment
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;
Create a basic project
1. Guide Package
pom. Introducing lombok into XML
2. Prepare configuration file
db.properties
mybatis-config.xml
Basically the same as previous projects
Just mybatis config XML added a setting: enable hump naming
<!--Whether to enable automatic hump naming mapping--> <setting name="mapUnderscoreToCamelCase" value="true"/>
3. Write entity class
package com.gongyi.pojo;
import lombok.Data;
import java.util.Date;
@Data
public class Blog {
private String id;
private String title;
private String author;
private Date createTime;//The property name and field name are inconsistent
private int views;
}
Tools:
package com.gongyi.utils;
import org.junit.Test;
import java.util.UUID;
@SuppressWarnings("all")//Suppress warning
public class IDutils {
public static String getId() {
return UUID.randomUUID().toString().replaceAll("-","");
}
@Test
public void test() {
System.out.println(IDutils.getId());
System.out.println(IDutils.getId());
System.out.println(IDutils.getId());
}
}
4. Write Mapper interface and Mapper.xml corresponding to entity class XML file
Interface class:
package com.gongyi.dao;
import com.gongyi.pojo.Blog;
import java.util.List;
import java.util.Map;
public interface BlogMapper {
//insert data
int addBlog(Blog blog);
//Query blog
List<Blog> queryBlogIF(Map map);
}
xml file:
<?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.gongyi.dao.BlogMapper">
<insert id="addBlog" parameterType="blog">
insert into mybatis.blog(id, title, author, create_time, views) values
(#{id},#{title},#{author},#{createTime},#{views});
</insert>
<select id="queryBlogIF" resultType="blog" parameterType="map">
select * from mybatis.blog where 1=1
<if test="title != null">
and title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</select>
</mapper>
Test class:
import com.gongyi.dao.BlogMapper;
import com.gongyi.pojo.Blog;
import com.gongyi.utils.IDutils;
import com.gongyi.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
public class MyTest {
@Test
public void addInitBlog() {
SqlSession session = MybatisUtils.getSqlSession();
BlogMapper mapper = session.getMapper(BlogMapper.class);
Blog blog = new Blog();
blog.setId(IDutils.getId());
blog.setTitle("MyBatis So simple");
blog.setAuthor("Gongyi theory");
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");
mapper.addBlog(blog);
blog.setId(IDutils.getId());
blog.setTitle("Microservices are so simple");
mapper.addBlog(blog);
session.close();
}
@Test
public void queryBlogIf() {
SqlSession session = MybatisUtils.getSqlSession();
BlogMapper mapper = session.getMapper(BlogMapper.class);
HashMap map = new HashMap();
// map.put("title","MyBatis is so simple");
map.put("author", "Gongyi theory");
List<Blog> blogs = mapper.queryBlogIF(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
session.close();
}
}
Code structure diagram:
IF
<select id="queryBlogIF" resultType="blog" parameterType="map">
select * from mybatis.blog where 1=1
<if test="title != null">
and title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</select>
choose(when,otherwise)
<select id="queryBlogChoose" resultType="blog" parameterType="map">
select * from mybatis.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>
trim(where,set)
<trim prefix="WHERE" prefixOverrides="AND |OR "> ... </trim>
The so-called dynamic SQL is still an SQL statement in essence, but we can execute a logical code at the SQL level
if
where,set,choose,when
SQL fragment
Sometimes, we may extract some common parts for reuse
1. Use SQL tags to extract common parts
<sql id="if-title-author">
<if test="title != null">
and title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</sql>
2. Use the include tag reference where necessary
<select id="queryBlogIFUsingSqlFragment" resultType="blog" parameterType="map">
select * from mybatis.blog where 1=1
<include refid="if-title-author"></include>
</select>
3. Test
@Test
public void queryBlogIFUsingSqlFragment() {
SqlSession session = MybatisUtils.getSqlSession();
BlogMapper mapper = session.getMapper(BlogMapper.class);
HashMap map = new HashMap();
//map.put("title","MyBatis is so simple");
map.put("author", "Gongyi theory");
List<Blog> blogs = mapper.queryBlogIFUsingSqlFragment(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
session.close();
}
matters needing attention:
- It is best to define SQL fragments based on a single table
- Do not have a where tag
Foreach
select * from user where 1=1 and (id=1 or id=2 or id=3)
select * from user where 1=1 and
<foreach item="id" collection="ids" open="(" separator="or" close=")">
#{id}
</foreach>
Modify the id of the blog to a number:
Core xml:
<!--
select * from user where 1=1 and (id=1 or id=2 or id=3)
We are now passing on a universal message map，this map A collection can exist in
-->
<select id="queryBlogForeach" parameterType="map" resultType="blog">
select * from mybatis.blog
<where>
<foreach collection="ids" item="id" open="and (" close=")" separator="or">
id = #{id}
</foreach>
</where>
</select>
Test:
@Test
public void queryBlogForeach() {
SqlSession session = MybatisUtils.getSqlSession();
BlogMapper mapper = session.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);
}
session.close();
}
Summary:
Dynamic sql is splicing sql statements. We just need to ensure the correctness of sql and arrange and combine them according to the sql format
Recommendations:
First write a complete sql in mysql, and then modify it accordingly to become our dynamic sql to achieve universal
Easter egg
1.mybatis dynamic sql official website
2. Wave line prompt solution in idea
1) Code change specification
2)@SuppressWarnings("all")
Problem summary
1.Caused by: org.apache.ibatis.builder.BuilderException: The setting mapUnderscoreToCamelCase is not known. Make sure you spelled it correctly (case sensitive).
Solution: add a space to mapUnderscoreToCamelCase in the configuration and remove it