Mybatis learning - dynamic SQL
What is dynamic SQL
Dynamic SQL is to generate different SQL statements according to different conditions
Dynamic SQL is similar to JSTL or any text processor based on XML like languages. In previous versions of MyBatis, it took time to understand a large number of elements. With the powerful expression based on OGNL, MyBatis 3 replaces most of the previous elements and greatly simplifies the types of elements. Now there are fewer types of elements to learn than half of the original.
- if
- choose (when, otherwise)
- trim (where, set)
- f
- oreach
Build environment
Build 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'
);
INSERT INTO `blog` (`id`, `title`, `author`, `create_time`, `views`) VALUES ('dd82ce6a67da49808352e97fa29f7943', 'Mybatis study', 'Happy-change', '2022-02-25 22:27:17', 9999);
INSERT INTO `blog` (`id`, `title`, `author`, `create_time`, `views`) VALUES ('7715080c90544c3b99804c07fd3ced21', 'spring study', 'Happy-change', '2022-02-25 22:27:17', 9999);
INSERT INTO `blog` (`id`, `title`, `author`, `create_time`, `views`) VALUES ('289a485479944c8eb1f471e867fe3622', 'springmvc study', 'Happy-change', '2022-02-25 22:27:17', 9999);
INSERT INTO `blog` (`id`, `title`, `author`, `create_time`, `views`) VALUES ('3c0444b8961343c594a26c60381a2385', 'springboot study', 'Happy-change', '2022-02-25 22:27:17', 9999);
Create a basic maven project
- Guide Package
- Write configuration file
- Writing entity classes
@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;
}
Turn on Automatic Hump naming mapping in the core configuration file
<settings>
<setting name="logImpl" value="STDOUT_LOGGING"/>
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
- to write
- The entity class corresponds to Mapper interface and Mapper XML file
IF
Interface
List<Blog> queryBlogIf(Map map);
Mapper
<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 queryBlogIf(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap map = new HashMap();
map.put("title","mybatis study");
List<Blog> blogs = mapper.queryBlogIf(map);
for (Blog blog: blogs){
System.out.println(blog);
}
sqlSession.close();
}
trim (where, set)
where element
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.
Thus, the above IF case can be optimized as follows:
<select id="queryBlogIf" parameterType="map" resultType="Blog">
select * from blog
<where>
<if test="title != null">
and title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</where>
</select>
trim
Trim element to customize the function of where element. For example, the user-defined trim element equivalent to the where element is:
<trim prefix="WHERE" prefixOverrides="AND |OR "> ... </trim>
The prefixOverrides property ignores text sequences separated by pipe characters (note that spaces in this example are necessary). The above example will remove all the contents specified in the prefixOverrides attribute and insert the contents specified in the prefix attribute.
set element
The SET element dynamically inserts the SET keyword at the beginning of the line and deletes additional commas (which were introduced when using conditional statements to assign values to columns).
<!--Update blog-->
<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>
Custom trim elements equivalent to set elements:
<trim prefix="SET" suffixOverrides=","> ... </trim>
choose (when, otherwise)
Sometimes, we don't want to use all the conditions, but just want to choose one from multiple conditions. In this case, MyBatis provides the choose element, which is a bit like the switch statement in Java.
<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>
Foreach
Traverse the collection (especially when building IN conditional statements)
select * from user where 1=1 and (id=1 or id=2 or id=3)
<select id="selectPostIn" resultType="domain.blog.Post">
SELECT *
FROM POST P
<where>
<foreach item="item" index="index" collection="list"
open="ID in (" separator="," close=")" nullable="true">
#{item}
</foreach>
</where>
</select>
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!
Tip: you can pass any iteratable object (such as List, Set, etc.), Map object or array object to foreach as a Set parameter. When using iteratable objects or arrays, index is the sequence number of the current iteration, and the value of item is the element obtained in this iteration. When using a Map object (or a collection of Map.Entry objects), index is the key and item is the value.
<!--select * from blog where 1=1 and (id=1 or id = 2 or id = 3)
Transitive map A collection can be passed
-->
<select id="queryBlogForearch" parameterType="map" resultType="blog">
select * from blog
<where>
<foreach collection="ids" item="id" open = "and (" close=")" separator="or">
id = #{id}
</foreach>
</where>
</select>
@Test
public void queryBlogForearch(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap map = new HashMap();
ArrayList<Integer> ids = new ArrayList<>();
ids.add(1);
ids.add(2);
ids.add(3);
map.put("ids",ids);
List<Blog> blogs = mapper.queryBlogForearch(map);
for (Blog blog: blogs){
System.out.println(blog);
}
sqlSession.close();
}
SQL fragment
Extract the public part for reuse!
1. Use sql tags to extract the common parts
<sql id="if-title-author">
<if test="title != null">
title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</sql>
2. Use the include tag where needed
<select id="queryBlogIf" parameterType="map" resultType="Blog">
select * from blog
<where>
<include refid="if-title-author"></include>
</where>
</select>
matters needing attention:
- It is best to define SQL fragments based on a single table
- Do not have a where tag
Summary:
- The so-called dynamic SQL is still an SQL statement in essence, but we can execute a logical code at the SQL level
- Dynamic SQL is splicing SQL statements. We just need to ensure the correctness of SQL and arrange and combine them according to the format of SQL
- It is recommended to test whether the sql statement can execute successfully in mysql before writing