Dynamic SQL
If you have any experience with native JDBC, you should know how painful and inefficient it is to use StringBuffer to concatenate SQL statements
StringBuffer sql = new StringBuffer("select * from smbms_provider");
if(! StringUtils.isNullOrEmpty(providerCode)) { sql.append(" where proCode like ?");
list.add("%"+providerCode+"%");
}
if(! StringUtils.isNullOrEmpty(providerName)) { sql.append(" and proName like ?");
list.add("%"+providerName+"%");
}
if(...). {... }Copy the codeIn fact, the dynamic SQL function of MyBatis can simplify this process to a certain extent, and make the original complex splicing logic become simple
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.
- if
- choose (when, otherwise)
- trim (where, set)
- foreach
Let’s go through it one by one
if
The if element is the heart of the whole dynamic SQL and the easiest element to understand. It allows us to make some simple if judgments at the SQL level, and to determine the CONCatenation of SQL based on the incoming criteria.
Java’s if judgment and StringBuffer are used to do this in JDBC native code above, so let’s see how MyBatis simplifies this operation.
The first is the construction of the environment:
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for blog
-- ----------------------------
DROP TABLE IF EXISTS `blog`;
CREATE TABLE `blog` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'blog id',
`title` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Blog title',
`author` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Blogger',
`create_time` datetime(0) NULL DEFAULT NULL COMMENT 'Creation time',
`views` bigint(20) NULL DEFAULT NULL COMMENT 'Views'.PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of blog
-- ----------------------------
INSERT INTO `blog` VALUES (1.MyBatis config file.'Moluu'.'the 2021-06-12 17:50:18'.100);
INSERT INTO `blog` VALUES (2.'MyBatis initialization process details'.'Moluu'.'the 2021-06-12 17:51:16.100);
INSERT INTO `blog` VALUES (3.'MyBatis dynamic SQL'.'Moluu'.'the 2021-06-12 17:51:33'.50);
INSERT INTO `blog` VALUES (4.'MyBatis cache details'.'moluu'.'the 2021-06-12 17:52:05'.0);
SET FOREIGN_KEY_CHECKS = 1;
Copy the codeWith the table created we can write some test demos based on the table
The if element is very simple to use. It is usually embedded in the SQL fragment of the DML statement element of the operating database, and it has a mandatory **”test”** attribute, where our logical judgment is written.
The following is a demonstration:
<select id="selectBlogByIf" resultType="blog" parameterType="blog">
select * from `blog` where 1=1
<if test="title ! = null">
and title = #{title}
</if>
<if test="author ! = null">
and author = #{author}
</if>
<if test="views ! = null">
and views = #{views}
</if>
</select>
Copy the codeWe have three if elements embedded in the SQL statement that must return all blog data when the condition in the test attribute of the if element is true
The SQL statement we wrote inside the if element will be appended to the query SQL. For example, when title has a value, our SQL will look like this:
select * from `blog` where 1=1 and title=#{tiitle}
Copy the codeSQL statement where 1=1 (where 1=1)
Because you’re not sure what the first condition that might be passed in is, it doesn’t make sense to write where inside any if element, but it doesn’t bother if it exists in the first place.
# if I get rid of thiswhere 1=1, performsqlIt's probably going to look something like thisselect * from `blog` and title=#{title} # This is obviously unpassablesqlStatement, this problem cannot be solved unless you restrict which query criteria must be passed in firstCopy the codeWe test with a test class:
@Test
public void queryTestByIf(a){
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
// When we create a blog object that takes no arguments
List<Blog> blogs = mapper.selectBlogByIf(new Blog());
blogs.forEach(System.out::println);
/* Select * from (*); Blog(id=1, title=MyBatis, author=Moluu, createTime=Sat Jun 12 17:50:18 CST 2021, views=100) Blog(id=2, File =Moluu, createTime=Sat Jun 12 17:51:16 CST 2021, views=100) Blog(id=3, title=MyBatis) Author =Moluu, createTime=Sat Jun 12 17:51:33 CST 2021, views=50) Blog(id=4, title=MyBatis) createTime=Sat Jun 12 17:52:05 CST 2021, views=0) */
sqlSession.close();
}
Copy the codeWhen we create an empty Blog object and pass it into the method as a query condition, it becomes clear that none of the conditions we wrote in the test attribute of the if element will be satisfied
Therefore, the SQL executed is:
select * from `blog` where 1=1
Copy the codeWhen a condition is met:
@Test
public void queryTestByIf(a){
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
Blog blog = new Blog();
// Set the property title for our empty blog object
blog.setTitle("MyBatis cache details");
List<Blog> blogs = mapper.selectBlogByIf(blog);
blogs.forEach(System.out::println);
// The result of the query is
// Blog(id=4, title=MyBatis, author=moluu, createTime=Sat Jun 12 17:52:05 CST 2021, views=0)
sqlSession.close();
}
Copy the codeWhen we set some attribute values for the empty Blog object we created, the condition on the test attribute of the if element is satisfied
<if test="title ! = null">
and title = #{title}
</if>
Copy the codeThe SQL statement is concatenated to the original SQL statement, so the complete SQL statement should be executed as follows:
select * from `blog` where 1=1 and title = ?
Copy the codeThis will enable us to query the blog with the specified title
This is called dynamic SQL, but it’s just the tip of the iceberg, and it’s the simplest use; Read on if you’re interested in dynamic SQL
trim
In the Demo of the if element, we wrote an extra where 1=1 to ensure that the concatenated SQL would execute successfully; But it’s not very elegant to write it that way.
We can’t remove the WHERE clause because the query is less free, so we can use the trim element to solve this awkward problem.
where
The WHERE element is a built-in trim element.
The USE of the WHERE element is also quite simple, as it simply wraps around dynamic SQL where errors can occur when concatenating SQL
For example, the dynamic SQL in the sample Demo of the if element
# before<select id="selectBlogByIf" resultType="blog" parameterType="blog">
select * from `blog` where 1=1
<if test="title ! = null">
and title = #{title}
</if>
<if test="author ! = null">
and author = #{author}
</if>
<if test="views ! = null">
and views = #{views}
</if>
</select># after using the WHERE element<select id="selectBlogByIf" resultType="blog" parameterType="blog">
select * from `blog`
<where>
<if test="title ! = null">
and title = #{title}
</if>
<if test="author ! = null">
and author = #{author}
</if>
<if test="views ! = null">
and views = #{views}
</if>
</where>
</select>
Copy the codeWe removed where 1=1 from the default query SQL and wrapped the previous if element with a WHERE element
Why do we do that? Let’s see what the WHERE element does, right
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.
Add logs to the configuration file and run the previous Demo to see the output of the SQL statement
<settings>
<! -- Enable MyBatis built-in default log implementation -->
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
Copy the codeAfter running, the OUTPUT SQL in the console is as follows:
This is the SQL statement after the WHERE element is processed when the SQL is concatenated;
The SQL statement in the if element should have been and title = #{title}, and the default SQL statement was not executed with the WHERE keyword
But after the where element is processed, the where keyword is automatically added, but this is not enough. If only the where keyword is added, the SQL will not execute properly
# is going to look like thisselect * from `blog` where and title = ?
Copy the codeThis is an obvious syntax error. To ensure smooth execution of SQL, the WHERE element must remove the and keyword at the beginning of the clause, which is exactly one of the functions of the WHERE element
Of course, not every and keyword will be removed. The where element will only remove the and at the beginning of the clause, and the second or third AND will remain
// All if element criteria are met
blog.setTitle("MyBatis cache details");
blog.setAuthor("moluu");
blog.setViews(10);
Select * from 'blog' WHERE title =? and author = ? and views = ?
// You can see that other AND's are still preserved
Copy the codeset
The set element is similar to the WHERE element in that it automatically adds the set keyword to the first line of our SQL statement when we perform an UPDATE. And remove the trailing “, “(because we use”, “to separate each element that needs to be modified when writing updateSQL statements)
Let’s go straight to the example Demo to see what this element does:
<update id="updateBlogBySet" parameterType="blog">
update `blog`
<set>
<if test=" title ! = null">
title = #{title},
</if>
<if test=" author ! = null">
author = #{author},
</if>
<if test=" createTime ! = null">
create_time = #{createTime},
</if>
<if test=" views ! = null">
views = #{views},
</if>
</set>
where id = #{id}
</update>
Copy the code@Test
public void queryTestByIf(a){
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
Blog blog = new Blog();
blog.setTitle("Set element test");
blog.setId(1);
mapper.updateBlogBySet(blog);
sqlSession.close();
}
Copy the codeWe set the attribute value title for the empty Blog object to satisfy the judgment condition in the if element. If we did not add the set element, the concatenated SQL would look like this
update `blog` title =? .where id = ?
Copy the codeIf you don’t have a set keyword, even if you add a comma to the set keyword, you will fail to compile the SQL
But if we write the if element inside the set element, all of these problems will be solved
The set element adds the unadded set keyword and removes the “” that causes the compilation error, leaving the concatenated SQL like this
trim
In fact, the trim element will probably be used sparingly, as where and set are already used for most of our purposes
The existence of the trim element allows us to do more than just use WHERE and set; It’s also more complicated to use
It has the following four properties to enable customization
-
prefix
-
prefixOverriders
-
suffix
-
suffixOverriders
prefix
For a quick look at these attributes, prefix means prefix when added to the trim element and given its value
When dynamic concatenation of SQL is required, the contents of prefix will be concatenated first. Here is a simple change from the previous Demo
<update id="updateBlogBySet" parameterType="blog">
update `blog`
<trim prefix="SET">
<if test=" title ! = null">
title = #{title}
</if>
</trim>
where id = #{id}
</update>
Copy the codeAs you can see, we have removed all but one of the redundant if elements and replaced the set element with trim, deleting the “, “after the”} “.
This is to ensure that the SQL will work, because the trim element is handled (that is, prefixed with “SET”), which is fine.
The SQL statement executed is
update `blog` SET title = ? where id = ?
Copy the codesuffix
Suffix is the opposite of prefix, where the string content in the attribute value is concatenated at the end of the dynamic SQL to be concatenated
We can try to write where ID = #{id} in the suffix element attribute value that was previously written outside of trim
<update id="updateBlogBySet" parameterType="blog">
update `blog`
<trim prefix="SET" suffix="where id = #{id}">
<if test=" title ! = null">
title = #{title}
</if>
</trim>
</update>
Copy the codeWhen we start the test class, the suffix element attribute values are concatenated to the end
The SQL statement executed is
update `blog` SET title = ? where id = ?
Copy the codeprefixOverriders
The string content in the prefixOverrides attribute value will be removed if it appears at the beginning of the sentence where the CONCatenated SQL is required
As convoluting as it may sound, the WHERE element is actually a combination of the prefixOverrides attribute and the prefix attribute
If you want to use the trim element to do the same thing as the WHERE element
So trim elements prefixOverriders attribute value should be “and | or”, the prefix attribute values for “where” is the following:
<trim prefix="where" prefixOverrides="and |or ">
</trim>
Copy the codeThis allows you to remove the AND or or keyword if it appears at the beginning of the sentence you want to concatenate, and add the WHERE keyword at the beginning of the sentence
Let’s test this property value:
<update id="updateBlogBySet" parameterType="blog">
update `blog`
<trim prefix="set" prefixOverrides="test">
<if test=" title ! = null">
test title = #{title}
</if>
</trim>
</update>
Copy the codeAs mentioned above, we changed the SQL that we needed to concatenate, and wrote a test at the beginning of the sentence. Normally, you will get an error if you write this way
If we set the prefixOverrides attribute value string to “test”, the test string will be removed
SQL statement ();
update `blog` set title = ?
Copy the codesuffixOverriders
Similar to the prefixOverriders attribute, the suffixOverrides attribute removes the string content in the value of the suffixOverrides attribute if it appears at the end of the required concatenation SQL
That is, the set element is a combination of the prefix and suffixOverriders attributes
The trim element would look something like this:
<trim prefix="SET" suffixOverrides=",">
</trim>
Copy the codeThis removes the extra “” from the set element and adds the set keyword at the beginning of the sentence
We also tested it:
<update id="updateBlogBySet" parameterType="blog">
update `blog`
<trim prefix="set" suffixOverrides=",">
<if test=" title ! = null">
title = #{title},
</if>
</trim>
where id = #{id}
</update>
Copy the codeSuffixOverrides =” suffixOverrides=”,” if suffixOverrides=” suffixOverrides=”,” the SQL will not compile. It would look like this:
update `blog` SET title =? .where id = ?
Copy the codeBut if we add suffixOverrides=”,” we don’t have this problem, and the “,” at the end,” is removed by the suffixOverrides attribute
choose
MyBatis also provides a dynamic SQL element choose similar to the Switch statement. In this element, we can also write secondary elements when and otherwise;
These two elements correspond to the switch case (case with break) and default, so they are also fairly easy to understand
when
Typically we write multiple WHEN elements inside the Choose element, just as we would write case elements inside the switch element
<select id="selectBlogByWhen" parameterType="blog" resultType="blog">
select * from `blog`
<where>
<choose>
<when test="title ! = null">
and title = #{title}
</when>
<when test="author ! = null">
and author = #{author}
</when>
<when test="views ! = null">
and views = #{views}
</when>
</choose>
</where>
</select>
Copy the codeWhen the middle condition is true for the value of the TEST attribute of the WHEN element, the SQL in the WHEN element is processed and concatenated to the default executed SQL statement
However, unlike if, once one of the when elements is true, all the other when elements are broken
@Test
public void queryTestByWhen(a){
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
Blog blog = new Blog();
blog.setTitle("MyBatis initialization process details");
blog.setAuthor("moluu");
mapper.selectBlogByWhen(blog);
sqlSession.close();
}
Copy the codeAlthough we have conditions in both when elements (which are contradictory), only one when element will actually be executed; The one that is satisfied first
/* Preparing: select * from `blog` WHERE title = ? Row: 2, MyBatis initialization process details, Moluu, 2021-06-12 17:51:16, 100 */
Copy the codeotherwise
In addition to writing a lot of when in the Choose element, we can also write an otherwise element to ensure that our query SQL must select a condition to execute once
<where>
<choose>
<when test="title ! = null">
and title = #{title}
</when>
<when test="author ! = null">
and author = #{author}
</when>
<when test="views ! = null">
and views = #{views}
</when>
<otherwise>
views = 100
</otherwise>
</choose>
</where>
Copy the codeAs above, we add an otherwise element after the many WHEN elements, where the SQL is views = 100
After writing these lines of code, our query SQL will still perform a query for all blogs with 100 page views, even if all the incoming query parameters are null
@Test
public void queryTestByWhen(a){
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
Blog blog = new Blog();
/* blog.settitle ("MyBatis initialization process details "); blog.setAuthor("moluu"); * /
// Pass an empty blog object as a query parameter, and any WHEN elements will not be satisfied
mapper.selectBlogByWhen(blog);
sqlSession.close();
}
Select * from 'blog' WHERE views = 100
2, MyBatis initialization process details, Moluu, 2021-06-12 17:51:16100
Copy the codeforeach
In some cases, we write SQL statements in which the parameters we pass in are not fixed, and it becomes a lot of work to kill the SQL
However, it is more elegant and efficient to do this with MyBatis dynamic SQL feature
The foreach element allows you to specify a collection. It iterates through the elements in the collection. You can alias each item and index
Additionally, we can specify the prefix (open) separator and suffix (close) at the collection item references using specific properties.
How do you understand that? It’s difficult to understand this advanced feature through text alone, but we’ll look at examples to see how this element is used
Suppose we have a situation where we need to return all blogs with user-specified IDS (or other attributes). How do you do that at the SQL level
We could wrap all the ids we receive in a collection, finally iterate over them and pass them in as arguments to SQL, but then you would need to write a lot of redundant SQL
select * from `blog` where (id = ? or id = ? or id = ? or id =...).Copy the codeUnless you specify the maximum number of ids that can be passed in at one time, your SQL will at least run to the end, and if you don’t specify who knows how many blogs the user will query at one time
So it was impossible to write dead SQL, and we desperately needed a more elegant way to solve this problem: the foreach element
We can pass the wrapped collection as an argument to the foreach element, which iterates over it and refers to each collection item directly as an argument (prefix, suffix, and separator can be specified if necessary)
<select id="selectBlogByForeach" resultType="blog" parameterType="arraylist">
select * from `blog`
<where>
<foreach collection="ids" item="foreach_id" open="(" separator="or" close=")">
id = #{foreach_id}
</foreach>
</where>
</select>
Copy the codeAbove, we specify a collection IDS for the foreach element, and the alias of the collection item iterated from the IDS is foreach_id.
When a collection item is referenced, Foreach also does some prefix and suffix delimiters at the reference
We do a simple test in the test class:
@Test
public void queryTestByWhen(a){
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap<Object, Object> map = new HashMap<>();
ArrayList<Integer> ids = new ArrayList<>();
/* Emulated the user to specify id */ here
ids.add(1);
ids.add(2);
ids.add(3);
// Encapsulate the collection parameter IDS to be passed into the map
map.put("ids",ids);
mapper.selectBlogByForeach(map);
sqlSession.close();
}
Select * from 'blog' WHERE (id =? or id = ? or id = ? )
/* MyBatis configuration file details, Moluu, 2021-06-12 17:50:18, 1002, MyBatis initialization process details, Moluu, 2021-06-12 17:51:16, 1003, MyBatis Dynamic SQL, Moluu, 2021-06-12 17:51:33, 50 */
Copy the codeAs you can see, no matter how many ids the user passes in (as long as they exist in the database), our SQL can complete the corresponding query
The id passed in by the user is picked up by Foreach and iterated over, nicely referenced as a query parameter. Isn’t that elegant and efficient
Relax your eyes
