Use of dynamic SQL in MyBatis
This is the 15th day of my participation in the Last Text Challenge of 2021. What is dynamic SQL
Dynamic SQL is one of the powerful features of MyBatis. If you’ve ever used JDBC or a similar framework, you can understand how painful it can be to concatenate SQL statements based on different conditions, such as making sure you don’t forget to add the necessary whitespace and removing the comma from the last column name of the list. With dynamic SQL, you can get rid of this pain completely.
Using dynamic SQL is not an easy task, but with the powerful dynamic SQL language that can be used in any SQL mapping statement, MyBatis significantly improves the ease of use of this feature.
If you’ve ever worked with JSTL or any xmL-like language based text processor before, dynamic SQL elements may feel familiar. In previous versions of MyBatis, it took time to understand a large number of elements. With the help of powerful OGNL based expressions, MyBatis 3 replaces most of the previous elements, greatly reducing the number of elements to be learned by less than half. There are mainly
- if
- choose (when, otherwise)
- trim (where, set)
- foreach
Database preparation, create a blok table
create table blog(
id int primary key comment 'blog id',
title varchar(100) not null comment 'Blog title',
author varchar(30) not null comment 'Blogger',
create_time varchar(50) not null comment 'Creation time',
views int(30) not null comment 'Views'
)
insert into blog values(1.'javaWeb tutorial'.'Dark Horse Programmer',now(),1000)
insert into blog values(2.'Android Software Development'.'Zhou Shikai, Chen Xiaolong',now(),1000)
insert into blog values(3.'Data structure'.Tsinghua University Press,now(),10000)
insert into blog values(4.'Humanities Foundation and Application'.'MAO Can moon',now(),560)
insert into blog values(5.'the Java tutorial'.'money',now(),123456)
insert into blog values(6.'C'.'Tam Ho Keung',now(),10000)
insert into blog values(7.'C'.'xiaomao',now(),10000)
Copy the code
Writing entity classes
@Data
public class Blog {
private int id;
private String title;
private String author;
private String create_Time;
private int views;
}
Copy the code1. If statement
Write the interface
// Query blogs with if
List<Blog> queryBlogIF(Map map);
Copy the codeWrite SQL statements for mapper.xml
<select id="queryBlogIF" parameterType="map" resultType="pojo.Blog">
select * from blog
<where>
<if test="title! =null">
and title=#{title}
</if>
<if test="author! =null">
and author=#{author}
</if>
</where>
</select>
Copy the codeIf “title” is not passed, all blogs will return; If the “title” argument is passed in, the “title” column is searched and the corresponding Blog result is returned
The WHERE tag is described below
The test class
@Test
public void queryBlogIF(a){
SqlSession sqlSession = Mybatisutil.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap map = new HashMap();
map.put("title"."C");
map.put("author"."Tan Ho-keung");
List<Blog> blogs = mapper.queryBlogIF(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
sqlSession.close();
}
Copy the codeThe results of
Choose, when, otherwise
Sometimes, we don’t want to use all of the conditions, but just choose one of several conditions to use. For this, MyBatis provides the Choose element, which is a bit like the Switch statement in Java.
Write the interface
// Use choose to query blogs
List<Blog> queryBlogChoose(Map map);
Copy the codeWrite SQL statements for mapper.xml
<select id="queryBlogChoose" parameterType="map" resultType="pojo.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>
Copy the codeThe test class
// Use choose to query information
@Test
public void queryBlogChoose(a){
SqlSession sqlSession = Mybatisutil.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap map = new HashMap();
map.put("title"."C");
map.put("author"."Tan Ho-keung");
List<Blog> blogs = mapper.queryBlogChoose(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
sqlSession.close();
}
Copy the codeThe results of
When the title meets the requirements, that is, ‘break’ exit choose, the following SQL statement will not be spliced, just like switch, so there are two records detected here. When I pass only views parameter, the statement of the otherwise label will be spliced, and the result is as follows
3, trim, where, set
When concatenating statements, there may be punctuation marks, prefix and suffix marks, etc., which can be solved with trim.
SQL > select * from blog WHERE SQL > select * from blog where SQL > select * from blog where Then the WHERE element inserts the “where” clause only if the child element returns anything. Also, if the clause begins with “AND” OR “OR,” the WHERE element removes those as well.
<select id="queryBlogIF" parameterType="map" resultType="pojo.Blog">
select * from blog
where
<if test="title! =null">
and title=#{title}
</if>
<if test="author! =null">
and author=#{author}
</if>
</select>
Copy the codeYou can also customize the functionality of the WHERE element by customizing the trim element if the where element is different from what you expected. For example, the custom trim element equivalent to the WHERE element is:
<trim prefix="WHERE" prefixOverrides="AND |OR ">.</trim>
Copy the codeThe prefixOverrides property ignores sequences of text separated by pipe characters (note that Spaces are required in this example). The above example removes all the content specified in the prefixOverrides attribute and inserts the content specified in the prefix attribute.
A similar solution for dynamically updated statements is called SET. The set element can be used to dynamically contain columns that need to be updated, ignoring other columns that are not updated. Such as:
<update id="updateAuthorIfNecessary">
update Author
<set>
<if test="username ! = null">username=#{username},</if>
<if test="password ! = null">password=#{password},</if>
<if test="email ! = null">email=#{email},</if>
<if test="bio ! = null">bio=#{bio}</if>
</set>
where id=#{id}
</update>
Copy the codeIn this example, the set element inserts the set keyword dynamically at the beginning of the line and removes additional commas (which are introduced when a conditional statement is used to assign values to a column).
Take a look at the custom trim element equivalent to the set element:
<trim prefix="SET" suffixOverrides=",">.</trim>
Copy the code3.1. Set Modifies data
Write the interface
// Use set to modify data
int updateBlog(Map map);
Copy the codeWrite SQL statements for mapper.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>
Copy the codesetThe element inserts the SET keyword dynamically at the beginning of the line and removes additional commas that are introduced when a conditional statement is used to assign a value to a column
The test class
@Test
public void updateBlog(a){
SqlSession sqlSession = Mybatisutil.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap map = new HashMap();
/ / a map. The put (" title ", "C");
map.put("author".Little Hao Qiang);
map.put("id".6);
mapper.updateBlog(map);
sqlSession.close();
}
Copy the codeThe results of
4, Foreach
Another common use of dynamic SQL is traversal of collections
Write the interface
// use foreach to query 3, 4, 5 blogs
List<Blog> queryBlogForeach(Map map);
Copy the codeWrite SQL statements for mapper.xml
<select id="queryBlogForeach" parameterType="map" resultType="pojo.Blog">
select * from blog
<where>
<foreach collection="ids" item="id" open="and (" close=")" separator="or">
id=#{id}
</foreach>
</where>
</select>
Copy the codeThe test class
@Test
public void queryBlogForeach(a){
SqlSession sqlSession = Mybatisutil.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap map = new HashMap();
ArrayList<Integer> idlist = new ArrayList<>();
idlist.add(3);
idlist.add(4);
idlist.add(5);
map.put("idlist",idlist);
List<Blog> blogs = mapper.queryBlogForeach(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
sqlSession.close();
}
Copy the codeResults:
The foreach element is very powerful. It allows you to specify a collection and declare the collection item and index variables that can be used inside the element. It also allows you to specify beginning and ending strings and separators between iterations of collection items. This element also doesn’t add extra delimiters by mistake, look how smart it is!
Hint that you can pass any iterable (List, Set, etc.), Map, or array object to Foreach as a collection parameter. When using an iterable or array, index is the sequence number of the current iteration and the value of item is the element fetched in the current iteration. When using a Map object (or a collection of Map.Entry objects), index is the key and item is the value.
5. SQL fragments
We’ll extract the common parts for reuse
Use:
- Extract the common parts using SQL tags
<! -- Extract the common parts of the SQL that need to be reused, using the SQL tag -->
<sql id="if-titlt-author">
<if test="title! =null">
title=#{title},
</if>
<if test="author! =null">
and author=#{author}
</if>
</sql>
Copy the code- Use include tags where needed
<! -- Use include tags where needed -->
<select id="queryBlogIF" parameterType="map" resultType="pojo.Blog">
select * from blog
<where>
<include refid="if-titlt-author"></include>
</where>
</select>
Copy the codeMatters needing attention:
- It is best to define SQL fragments based on a single table
- Do not have a WHERE tag
Conclusion:
-
Dynamic SQl is the concatenation of SQl statements, as long as we ensure the correctness of SQl, according to the FORMAT of SQl, to arrange the line
-
Test your SQL statements before writing them in an SQL query environment to avoid errors and find bugs when writing mappre.xml.