Cover: Luo Xiaoxi
Author: Pan Pan
In 2021, look to the sky and keep your feet on the ground.
This is the first Mybatis article after the Spring Festival ~
After crossing a year, I feel like I’ve written for half a century…
By the Goddess festival ヾ(◍°∇°◍) Blue ゙
I wish men gods and goddesses in advance more beautiful more rich more hi Sen!
The above picture can be saved as a circle of friends cover picture ~
preface
In this section, we introduce one of the powerful features of Mybatis: dynamic SQL. From the birth background and basic concepts of dynamic SQL, to the tag members and basic usage of dynamic SQL, we slowly explained, combined with the framework source code, analyze the underlying principle of dynamic SQL (tag), and finally ridicule at the end of the article: Before we had dynamic SQL features (tags), we used to fall into some nasty traps
Suggest paying attention to us! Mybatis full solution series has been updated oh
Full solution of Mybaits series
- Mybatis series full solution (a) : handwriting a set of persistent layer frame
- Mybatis series full solution (2) : Mybatis introduction and environment construction
- Mybatis series full solution (3) : Mybatis simple CRUD use introduction
- Mybatis series full solution (four) : the most complete network! Mybatis configuration file XML overview
- Mybatis series full solution (5) : the most complete network! Mybatis Mapper mapping file
- Mybatis series full solution (6) : Mybatis most core API you know how many?
- Mybatis: Tradition and proxy of two implementations of the Dao layer
- Mybatis series full solution (8) : Mybatis dynamic SQL
- Mybatis series full solution (9) : Complex mapping of Mybatis
- Mybatis series full solution (10) : Mybatis annotation development
- Mybatis series full solution (11) : Mybatis cache full solution
- Mybatis plug-in development
- Mybatis series full solution (13) : Mybatis code generator
- Spring integrates Mybatis
- Mybatis series full solution (15) : SpringBoot integrated Mybatis
- Mybatis series full solution (16) : Mybatis source code analysis
This article directory
1. What is dynamic SQL
2. The birth of dynamic SQL
3. Nine tags for dynamic SQL tags
4. The underlying principles of dynamic SQL
1. What is dynamic SQL?
Dynamic SQL allows us to understand “dynamic SQL”, where “dynamic” is an adjective and “SQL” is a noun. Obviously we need to understand the noun first, after all, adjectives only represent some form or some state of it.
SQL stands for:
Structured Query Language
SQL itself is easy to say, we all learned it in elementary school, it’s just CRUD, and we also know that it’s a language, and language is the ability to communicate between objects, For example, we communicate with the Chinese in Chinese, with the British in English, with martians in Martian, with cats in meow, with computers in machine language, with database management systems (DBMSS) in SQL.
It is immediately obvious that in order to communicate with an object, one must have the language ability to communicate with that object! So whether it is a technical person, or an application system, or a high-level language environment, to access/manipulate the database, must have SQL this ability; So you see high-level environments like Java, Python, Go, etc., all of which embed SQL capabilities to interact with databases.
Obviously, able to learn Mybatis such a sophisticated (ru-Men) persistent layer framework programming crowd, for SQL writing ability must have mastered SS, usually all kinds of SQL writing that is handy, but for dynamic SQL is exactly what, Some friends seem to understand! But never mind, let’s Google it.
Dynamic SQL: Generally, SQL statement blocks are dynamically combined based on user input or external conditions.
Very easy to understand, with the external conditions of the dynamic composition of the SQL statement block! Let’s first analyze the word dynamic SQL, everything in the world, dynamic that corresponds to static, so where is their boundary? How do you tell the difference?
In Java, for example, you can write SQL statements directly in code or configuration files. If a SQL statement has its body structure determined at compile time, it is called static SQL. If a SQL statement cannot determine the body structure at compile time and needs to wait until the program is “run”, it is called dynamic SQL. For example:
<! SQL > select * from 'SQL';
<mapper namespace="dao">
<select id="selectAll" resultType="user">
select * from t_user
</select>
</mapper>
Copy the code// 2
sqlSession.select("dao.selectAll");
Copy the codeThis is static SQL because we already know at compile time that the main structure of the table t_user will be queried without having to wait until the program runs. Let’s look at the following statement:
<! SQL > select * from 'SQL';
<mapper namespace="dao">
<select id="selectAll" parameterType="user">
select * from t_user
<if test="id ! = null">
where id = #{id}
</if>
</select>
</mapper>
Copy the code// 2
User user1 = new User();
user1.setId(1);
sqlSession.select("dao.selectAll",user1); / / id
User user2 = new User();
sqlSession.select("dao.selectAll",user2); / / no id
Copy the codeNote that the SQL statement above has an additional if tag as a condition, so the application cannot determine the final body structure of the SQL statement at compile time. Only at run time, the application can dynamically concatenate the final SQL statement based on whether the application passed the id condition. So it’s dynamic SQL.
In addition, there is a common situation, let’s look at the following SQL statement is a dynamic SQL statement?
<! SQL > select * from 'SQL';
<mapper namespace="dao">
<select id="selectAll" parameterType="user">
select * from t_user where id = #{id}
</select>
</mapper>
Copy the code// 2
User user1 = new User();
user1.setId(1);
sqlSession.select("dao.selectAll",user1); / / id
Copy the codeAccording to the definition of dynamic SQL, can you determine whether the above statement block is dynamic SQL?
Answer: Not dynamic SQL!
The reason for this is simple: this SQL statement has a specified body structure at compile time. Although the external body is dynamically passed an ID, which may be 1, 2, or 100, because its body structure is determined, this statement is to query the user record with the specified ID. The resulting SQL statement does not change dynamically, so it is at best a static SQL that supports dynamic parameter passing.
So far, we have a basic understanding of the difference between dynamic SQL and static SQL, but some curious friends will consider another question: is dynamic SQL unique to Mybatis?
2. The birth of dynamic SQL
As we all know, SQL is a great database language standard, in the era of database management system disputes, its emergence unified standardized database operation language, and at this time, the market of database management software bloom, I used the EARLIEST SQL Server database, The database Management tool I used at that time was SQL Server Management Studio, then I contacted Oracle database and used PL/SQL Developer, and then I almost used MySQL database until today (this is related to the rise of various cloud vendors). So the basic use of Navicat as a database management tool, of course, there are many database management tools on the market now, as long as it can be convenient and efficient management of our database, that is a good tool, DUCK does not have to struggle to choose which one!
So what do all these great tools do? This input interface supports almost all SQL syntax. For example, we write a statement to query the user data record whose ID is equal to 15:
select * from user where id = 15 ;
Copy the codeLet’s look at the query result:
Obviously, any SQL statement entered in this input interface is dynamic SQL for database management tools! Because the tool itself cannot know in advance what SQL statement the user will enter, only when the user executes, the tool will receive the actual SQL statement entered by the user, can finally determine the body structure of the SQL statement, of course! Even if you don’t use visual database management tools, you can use command-line tools that come with the database itself to execute SQL statements. But no matter what kind of tool the user uses, the input statement is considered dynamic SQL by the tool!
Dynamic SQL is not unique to Mybatis. In addition to the database management tools introduced above, in the pure JDBC era, we often use strings to dynamically concatenate SQL statements, which is also used in high-level language environments (such as Java language programming environment) in the early dynamic SQL build way!
// External condition id
Integer id = Integer.valueOf(15);
// Dynamic concatenation SQL
StringBuilder sql = new StringBuilder();
sql.append(" select * ");
sql.append(" from user ");
// Dynamically concatenate SQL based on external condition ID
if ( null! = id ){ sql.append(" where id = " + id);
}
// Execute the statement
connection.prepareStatement(sql);
Copy the codeHowever, this way of building dynamic SQL has great security problems and abnormal risks (we will introduce them in detail in Point 5), so it is not recommended to use it. Later, after Mybatis entered the wto, it paid special attention to dynamic SQL, and silently vowed, Be sure to provide a set of awesome solutions (tags) for users using the Mybatis framework to flexibly build dynamic SQL!
As a result, Mybatis with the help of OGNL expression great design, can calculate in dynamic SQL construction to provide all kinds of powerful auxiliary tags, we simply list: If, choose, when, otherwise, trim, where, set, foreach, bind, etc., I casually looked through the study notes saved in my computer. Let’s review the old and learn the new together in section 3 and talk about them in detail
In addition, need to correct a point, is that we usually in Mybatis framework often said dynamic SQL, in fact, specifically refers to the Mybatis framework is a set of dynamic SQL tags, or this feature, but not in the context of dynamic SQL itself.
3. Nine tags for dynamic SQL tags
According to the previous matting, in fact, we can find that most of the time static SQL statements can not meet the needs of our complex business scenarios, so we need to have a set of appropriate flexible way or ability to convenient and efficient construction of dynamic SQL statements, To match our dynamic business needs. For example, dynamic SQL statements are particularly important in multi-conditional scenarios like the following (start with the if tag).
Of course, many of you will say that you can’t use SQL to look up these requirements, you have to use a search engine, and that’s true. But, in our actual business needs, there are still many no search engine system are introduced, and some don’t need to introduce application or function of search engine, they also involve multiple options condition or fruitful business requirements, that at this time is really need to use dynamic SQL TAB to construct flexible execution statement.
So, Mybatis currently provides what kind of awesome dynamic SQL tags? We’ll start with a class called XMLScriptBuilder, which is a builder that parses our dynamic SQL tags. We’ll cover this in more detail in point 4.
// XML script tag builder
public class XMLScriptBuilder{
// Label node processor pool
private final Map<String, NodeHandler> nodeHandlerMap = new HashMap<>();
/ / the constructor
public XMLScriptBuilder(a) {
initNodeHandlerMap();
/ /... Other initializations are unnecessary and unimportant
}
/ / initialization
private void initNodeHandlerMap(a) {
nodeHandlerMap.put("trim".new TrimHandler());
nodeHandlerMap.put("where".new WhereHandler());
nodeHandlerMap.put("set".new SetHandler());
nodeHandlerMap.put("foreach".new ForEachHandler());
nodeHandlerMap.put("if".new IfHandler());
nodeHandlerMap.put("choose".new ChooseHandler());
nodeHandlerMap.put("when".new IfHandler());
nodeHandlerMap.put("otherwise".new OtherwiseHandler());
nodeHandlerMap.put("bind".newBindHandler()); }}Copy the codeIn fact, the source code is very clear, a total of 9 dynamic SQL tags! Mybatis instantiates the tag node constructor when it initializes the config file. It will instantiate all the processors of the dynamic SQL tag object that Mybatis supports in advance and put them into a Map pool. Is an anonymous inner class of this kind of XMLScriptBuilder, and the function of the anonymous inner class is also very simple, is to parse and process the corresponding type of tag node, when the subsequent application uses dynamic tags, Mybatis will match the corresponding tag node processor in the Map pool at any time, and then parse. Gen (ju), wo (de), xi (hao);
Top1, if tag
Commonly used: ★★★★★
Practicality: ★★★★☆
If label, is definitely a great label, any does not support process control (or control) applications, are all play rascal, almost don’t have the practical significance, the practical application of scene and process condition control and flow, there must be and if label branch to judge the condition of single application scenario is played a role in the world, The syntax is simple: if yes, execute; if not, ignore/skip.
- If tag: embedded in select/DELETE/update/INSERT tags, executes code blocks if the conditions of the test attribute are met
- Test attribute: As an attribute of the if tag, used for condition determination, using OGNL expression.
Here’s an example:
<select id="findUser">
select * from User where 1=1
<if test=" age ! = null ">
and age > #{age}
</if>
<if test=" name ! = null ">
and name like concat(#{name},'%')
</if>
</select>
Copy the codeObviously, the if tag element is often used for a conditional concatenation containing a WHERE clause. It is the equivalent of the If statement in Java. It is used with the test attribute to determine whether to use a query condition by determining the parameter value, and can also be used to determine whether to Update a field in an Update statement. Or used in Insert statements to determine whether to Insert the value of a field.
Each if the tag in the single condition judgment, need to determine conditions set properties in the test, it is a common application scenario, we often use the user query system function, on the front end generally provide a number of optional query item, support for selection, name, age range for the screening of fuzzy matching, etc., so after we receive input from the user program, Mybatis dynamic SQL saves us a lot of work, allowing us to code level without parameter logic processing and SQL splicing, but the parameters into SQL for conditional judgment dynamic processing, we only need to focus on XML maintenance, both flexible and convenient maintenance, readability is also strong.
Some of you might be wondering why the where statement adds 1=1. Select * from user where age > 0; select * from user where age > 0; Of course, it does not comply with the syntax of SQL, and the database can not execute successfully, so we added the statement 1=1 opportunely, but we still think it is unnecessary and unnecessary, Mybatis also took into account, so later we will talk about where tag, how it perfectly solves this problem.
Note: The if tag, as a single conditional branch judgment, can only control the either-or process. For example, if age and name are not present, the system will query all the results, but sometimes we want the system to be more flexible and have more process branches. For example, if else or Switch Case Default in Java has more than one conditional branch, so we will introduce the Choose tag, which can meet the application scenario of multi-branch judgment.
Top2 choose tags, When tags, and otherwise tags
★★★★ ★★
Practicality: ★★★★☆
Sometimes, we don’t want condition control to be either/or, but rather to provide multiple conditions and choose one of them. So Mybatis provides a choose tag element, similar to if else or Switch Case Default in Java. The choose tag must be used with the WHEN tag and the otherwise tag, and the validation condition is still the test attribute.
- Choose label: the top level multi-branch label. It is meaningless to use it alone
- When tag: embedded in the choose tag. When a certain WHEN condition is met, the corresponding code block is executed and the exit of the Choose tag is terminated. Choose must have at least one WHEN tag, otherwise it is meaningless
- An OTHERWISE tag: embedded in the Choose tag. If all THE WHEN conditions are not met, an OTHERWISE code block is executed. At most, an Otherwise tag exists in the CHOOSE, which may not exist
- The test attribute: As an attribute of the when and otherwise tags, the OGNL expression is used as a criterion
As shown in the following example, when the application enters an age or a name, the corresponding code block in the WHEN tag is executed. If neither age nor name is met in the WHEN tag, the code block in the Otherwise tag is spliced.
<select id="findUser">
select * from User where 1=1
<choose>
<when test=" age ! = null ">
and age > #{age}
</when>
<when test=" name ! = null ">
and name like concat(#{name},'%')
</when>
<otherwise>And sex = 'male'</otherwise>
</choose>
</select>
Copy the code
Obviously, choose label, as a multi-branch condition judgment, provides more flexible process control, while otherwise also provides a bottom for program process control, sometimes avoiding part of the system risk, filtering part of the conditions, and avoiding the query or update of the whole database resources when the program does not match the conditions.
Why is the Choose tag so good, but still one less star than the if tag? The reason is simple, because many of the scenarios for using the choose tag can be replaced directly with the IF tag. In addition, according to my statistics, if tag in the actual business application, also more than choose tag, you can also check their own application in the dynamic SQL tag ratio, statistical analysis.
Top3. Foreach tags
★★★ ★★★
Practicality: ★★★★☆
In some scenarios, you may need to query user records whose ids range from 1 to 100
In some scenarios, you may need to batch insert 100 user records
In some scenarios, you might need to update 500 user names
In some scenarios, you may need to delete 10 user records
May I ask you:
Many add, delete, change and check scenarios operate on collections/lists
If you were designing a collection/list traversal scenario that supports Mybatis, what capabilities would you provide to help build your SQL statements to satisfy this business scenario?
The forehead (even though it’s o…
What if you must use the Mybatis framework?
Mybatis provides a foreach tag to handle scenarios that require traversal of collections. Foreach is a loop statement that supports arrays, maps, or implements Iterable interfaces (List, Set, etc.). Especially when building in conditional statements, we normally use id in (1,2,3,4,5… ${ids}; ${ids}; ${ids}; ${ids}; ${ids}; To meet our actual business needs. Select * from user where id = 1 and id = 100;
<select id="findAll">
select * from user where ids in
<foreach collection="list"
item="item" index="index"
open="(" separator="," close=")">
#{item}
</foreach>
</select>
Copy the codeThe final concatenated statement becomes:
select * from user where ids in (1.2.3. .100);
Copy the codeOf course, you could write it like this:
<select id="findAll">
select * from user where
<foreach collection="list"
item="item" index="index"
open="" separator=" or " close="">
id = #{item}
</foreach>
</select>
Copy the codeThe final concatenated statement becomes:
select * from user where id =1 or id =2 or id =3.or id = 100;
Copy the codeIn the case of large data volume, this performance can be awkward, here is only an example of its use. Foreach: foreach: foreach: foreach: foreach: foreach
- Foreach tag: top-level traversal tag, meaningless alone
- Collection property: Mandatory, Map or array or list property name (different types of value fetching are explained below)
- Item attribute: the variable name. The value is the value of each iterated value (object or base type). If it is an object, the value is the same as the OGNL expression value, such as #{item.id}, #{user.name}, etc
- Index: The name of the index’s property, which is the current index value when iterating over a list or array, and the Map’s key when iterating over an object of the Map type.
- Open property: a string concatenated at the beginning of the loop content, which can be an empty string
- Close property: the concatenated string at the end of the loop content, which can be an empty string
- Separator property: The separator for each loop
First, when a List object is passed in, the system adds a value with key ‘List’ by default, and places the contents of the List in the set with key ‘List’. It doesn’t matter if you pass it KKK or AAA. By default, the system adds a key to the list, and item specifies the value of the object to traverse. Index Specifies the traversal index value.
/ / Java code
List kkk = new ArrayList();
kkk.add(1);
kkk.add(2); . kkk.add(100);
sqlSession.selectList("findAll",kkk);
Copy the code<! -- XML configuration -->
<select id="findAll">
select * from user where ids in
<foreach collection="list"
item="item" index="index"
open="(" separator="," close=")">
#{item}
</foreach>
</select>
Copy the codeSecond, when an array is passed in, the system adds a value with a key of ‘array’ by default, and places the contents of the list in the collection with a key of ‘array’. In the foreach tag, you can retrieve the array object directly from collection=”array”. It doesn’t matter if you pass in ids or AAA. By default, the system will add a key to array, and item specifies the object value to traverse, and index specifies the index value to traverse.
/ / Java code
String [] ids = new String[3];
ids[0] = "1";
ids[1] = "2";
ids[2] = "3";
sqlSession.selectList("findAll",ids);
Copy the code<! -- XML configuration -->
<select id="findAll">
select * from user where ids in
<foreach collection="array"
item="item" index="index"
open="(" separator="," close=")">
#{item}
</foreach>
</select>
Copy the codeThird, when a Map object is passed in, the system does not add a key value by default. You need to pass it in manually. For example, if you pass in a collection object with a key value of map2, you can directly obtain the Map object through collection=”map2″ in the foreach tag. Item represents the value of each iteration, and index represents the key of each iteration. The value nouns of item and index can be arbitrarily defined. For example, item =” value111″ and index =”key111″.
/ / Java code
Map map2 = new HashMap<>();
map2.put("k1".1);
map2.put("k2".2);
map2.put("k3".3);
Map map1 = new HashMap<>();
map1.put("map2",map2);
sqlSession.selectList("findAll",map1);
Copy the codeMap1 has map2, so it can be found in the collection property of foreach.
<! -- XML configuration -->
<select id="findAll">
select * from user where
<foreach collection="map2"
item="value111" index="key111"
open="" separator=" or " close="">
id = #{value111}
</foreach>
</select>
Copy the code
You might think that maps are being treated unfairly, but why don’t they have a ‘Map’ key by default, like List or Array? However, it is not unfair, but in Mybatis framework, all the parameters passed in will be used by the context, so the parameters will be unified into a built-in parameter pool, and the data structure of the built-in parameter pool is a map set. The map set can be obtained by using “_parameter”. All keys are stored in the _parameter set, so:
- When you pass in a list parameter, the pool of parameters needs to have a key value for the context to retrieve the list object, so by default a ‘list’ string is set as the key value, which is obtained by using _parameter.list. Generally, use list.
- Similarly, when you pass in an array as a parameter, the pool of parameters also defaults to a string ‘array’ as a key, which is used to retrieve the object value of the array from the context, using _parameter.array. Generally, array is used.
- But! When you pass in a map set type, there is no need to add a default key to the pool because the map set type itself has many keys. For example, if you want to get a map set key, You can use _parameter.name or _parameter.age instead of _parameter.map.name or _parameter.map.age. So that’s why map parameter types don’t need to build a ‘map’ string as key, and the same is true for object types, such as when you pass in a User object.
So, if it’s a Map collection, you can use it like this:
/ / Java code
Map map2 = new HashMap<>();
map2.put("k1".1);
map2.put("k2".2);
map2.put("k3".3);
sqlSession.selectList("findAll",map2);
Copy the codeUse collection=”_parameter” directly, and you’ll find magic keys and values traversed through indexes and items via _parameter.
<! -- XML configuration -->
<select id="findAll">
select * from user where
<foreach collection="_parameter"
item="value111" index="key111"
open="" separator=" or " close="">
id = #{value111}
</foreach>
</select>
Copy the code
Extension: When multiple objects are passed in as arguments, such as User and Room, then you can use _parameter.get(0).username or _parameter.get(1).roomname to get objects with built-in arguments. If you pass in an argument of a simple data type, such as int =1 or String = ‘hello’, then you can use _parameter instead of getting the value. This is why many people use # {_parameter} directly in dynamic SQL to get values of simple data types.
If you want to insert 100 user records in a batch, you can insert 100 user records in a batch. If you want to insert 100 user records in a batch, you can insert 100 user records.
<insert id="insertUser" parameterType="java.util.List">
insert into user(id,username) values
<foreach collection="list"
item="user" index="index"
separator="," close=";" >
(#{user.id},#{user.username})
</foreach>
</insert>
Copy the codeIf you want to update the names of 500 users:
<update id="updateUser" parameterType="java.util.List">Update user set username = 'id' where id in<foreach collection="list"
item="user" index="index"
separator="," open="(" close=")" >
#{user.id}
</foreach>
</update>
Copy the codeIf you want to delete 10 user records:
<delete id="deleteUser" parameterType="java.util.List">
delete from user
where id in
<foreach collection="list"
item="user" index="index"
separator="," open="(" close=")" >
#{user.id}
</foreach>
</delete>
Copy the codeMore gameplay, look forward to your own digging!
Note: When using the foreach tag, you need to check whether the collection parameters (List, Map, Set, etc.) passed in are null. Otherwise, syntax exceptions may occur in dynamic SQL. Select * from user where ids in (); select * from user where ids in (); select * from user where ids in (); Or you can perform logical processing directly in Java code. For example, if SQL is null, do not execute SQL.
Top4, where tag, set tag
★★ ★★ ★
Practicality: ★★★★☆
We put the WHERE tag and the SET tag together to explain. First, the two tags are equally commonly used in practical application development. Second, the two tags come from the same family and inherit the trim tag. (The underlying principles will be explained in detail in Part 4)
When we introduced the if tag, as I’m sure you’ve all seen, we concatenated the condition 1=1 at the end of the WHERE clause, just to make sure that conditions are concatenated correctly, which is what we used to do when we used strings to concatenate SQL conditions, but it wasn’t very nice, It also makes us look inferior.
<select id="findUser">
select * from User where 1=1
<if test=" age ! = null ">
and age > #{age}
</if>
<if test=" name ! = null ">
and name like concat(#{name},'%')
</if>
</select>
Copy the codeThis is how we use the 1=1 notation, so how do we manipulate subsequent conditional statements after the where tag is created?
<select id="findUser">
select * from User
<where>
<if test=" age ! = null ">
and age > #{age}
</if>
<if test=" name ! = null ">
and name like concat(#{name},'%')
</if>
</where>
</select>
Copy the codeWe just changed the where keyword and 1 = 1 to < where > tag, there is a special processing capacity, is where the label can get rid of the (ignore) intelligent first meet the prefix of conditional statements, such as the above conditions if age and name are met, The age prefix and is intelligently removed, whether you use the AND operator or the OR operator, Mybatis framework will do it for you intelligently.
The usage is particularly simple, we use the official technique to summarize:
- Where tag: A top-level traversal tag that needs to be used in conjunction with the if tag, is meaningless alone, and inserts a WHERE clause only if the child element (such as the if tag) returns anything. In addition, if the clause begins with “AND” OR “OR”, the WHERE tag will also replace AND remove it.
Now that we know the basics, let’s look at our example:
<select id="findUser">
select * from User
<where>
<if test=" age ! = null ">
and age > #{age}
</if>
<if test=" name ! = null ">
and name like concat(#{name},'%')
</if>
</where>
</select>
Copy the codeIf age is passed a valid value of 10, age! After the = null condition, the WHERE tag is returned and the first clause operator and is removed. The final SQL statement becomes:
select * from User where age > 10;
-- And cleverly disappeared
Copy the codeIt is important to note that the WHERE tag will only intelligently remove (ignore) the prefix of the first statement that meets the condition, so it is recommended that we use the WHERE tag to prefix each statement with an and or or prefix, otherwise we will have a serious problem:
<select id="findUser">
select * from User
<where>
<if test=" age ! = null ">
age > #{age}
<! -- age prefix has no operator -->
</if>
<if test=" name ! = null ">
name like concat(#{name},'%')
<! -- name prefix also does not have operator -->
</if>
</where>
</select>
Copy the codeWhen age is passed 10 and name is passed ‘pan pan’, the final SQL statement is:
select * from User
where
age > 10
name like concat('pan %')
All conditions do not have and or OR operators
This makes age and name awkward
Copy the codeThe name prefix does not contain an AND or or concatenator, and the WHERE tag does not intelligently remove (ignore) prefixes that are not the first to satisfy the condition. In principle, it is recommended to add the operator AND or or at the beginning of each conditional clause. The first conditional statement can be added with or without the operator.
It is also worth noting that when we configure SQL using XML, if we add comments after the WHERE tag, then when a child element meets the condition, except for <! — –> comments are ignored by where, other comments such as // OR /**/ OR — are treated as the first clause element by where, resulting in a syntax error as the first AND OR clause element fails to be prefixed.
Based on the WHERE tag element, it helps to quickly understand the set tag element, since the two are so similar. Let’s recall our previous update SQL statement:
<update id="updateUser">
update user
set age = #{age},
username = #{username},
password = #{password}
where id =#{id}
</update>
Copy the codeThe above statements are used to update the age, USERNAME, and password fields of the specified ID object. However, most of the time, we may only want to update certain fields of the object, rather than updating all fields of the object each time, which makes the construction of statement structure very poor. So we have the set tag element.
The usage is similar to the WHERE tag element:
- Set tag: A top-level traversal tag that needs to be used in conjunction with the if tag, which alone is meaningless and inserts a set clause only if the child element (such as the if tag) returns anything. In addition, if there is a comma “at the beginning or end of a clause,” the set tag replaces and removes it.
Based on this usage we can change the above example to:
<update id="updateUser">
update user
<set>
<if test="age ! =null">
age = #{age},
</if>
<if test="username ! =null">
username = #{username},
</if>
<if test="password ! =null">
password = #{password},
</if>
</set>
where id =#{id}
</update>
Copy the codeAge =10; username = ‘pan’; username = ‘pan’; And “username = ‘pan pan’,”. The last comma is removed from the last clause. The final SQL statement is as follows:
update user set age = 10,username = 'pan pan'
Copy the codeNote that at least one condition must be satisfied under the set tag, otherwise the syntax error will still occur. For example, if no clause condition is satisfied, the final SQL statement will look like this:
update user ; ( oh~ no!)
Copy the codeThere is no set tag added and no clause to update the field, so the syntax is wrong, so situations like this usually require logical processing in the application to determine whether at least one parameter exists, otherwise the UPDATE SQL is not executed. Therefore, in principle, at least one condition is required under the set tag, and it is recommended to add a comma at the end of each condition clause, and the last condition statement can be added or not. Or add a comma at the beginning of each conditional clause, and the first conditional statement can be added with or without a comma, for example:
<update id="updateUser">
update user
<set>
<if test="age ! =null">
,age = #{age}
</if>
<if test="username ! =null">
,username = #{username}
</if>
<if test="password ! =null">
,password = #{password}
</if>
</set>
where id =#{id}
</update>
Copy the codeAs with the WHERE tag, when we configure SQL using XML, if we add a comment to the end of the set tag clause, then when any of the child elements satisfy the condition, except for <! — –> comments are ignored by set, and other comments, such as // or /**/ or –, are treated as trailing clause elements by the set tag. As a result, the comma of the trailing clause element fails to be replaced with the suffix, resulting in syntax errors!
This completes our introduction of the WHERE tag element and the set tag. They are indeed very similar, except that:
- The WHERE tag inserts the prefix WHERE
- The set tag inserts the prefix set
- The WHERE tag only intelligently replaces the prefix AND OR OR
- The set tag can only replace a prefix comma, or a suffix comma,
Both of these strategies are derived from the trim tag design, so let’s see how flexible the trim tag is.
Top5. Trim labels
* * * * * * *
Practicality: ★☆☆☆
We introduced above the where TAB AND set the label, which both have in common is front-facing keywords where OR set of inserts, AND former suffix symbols (smart AND | OR |, for example). Since both the WHERE tag and the set tag inherit the trim tag, we can guess the rough implementation of the trim tag.
Both the WHERE tag and the set tag are implementations of trim tags. Trim tags are implemented using the TrimSqlNode class, which has several key attributes:
- Prefix: the prefix that is inserted into the trim element if the content exists
- Suffix: suffix, which is inserted when content exists in the trim element
- PrefixesToOverride: Removes multiple prefixes. If the trim element contains content, the prefix string matching the content is removed.
- SuffixesToOverride: suffix, support multiple, when trim elements within content, the content of the matched suffix strings removed.
So the where tag, if implemented with the trim tag, could be written as 🙁
<! If you use the trim tag to implement the WHERE tag, you must add Spaces after AND AND OR to avoid matching android, order, etc.
<trim prefix="WHERE" prefixOverrides="AND | OR" >.</trim>
Copy the codeThe set tag, if implemented with the trim tag, could be written like this:
<trim prefix="SET" prefixOverrides="," >.</trim>or<trim prefix="SET" suffixesToOverride="," >.</trim>
Copy the codeTrim is flexible enough, but since the WHERE tag and the set tag are both variations of trim tag, there are not many situations where I use trim tag directly (actually, I don’t use it much myself).
Note that the set tag removes the prefix and suffix commas because the trim tag is removed, whereas the WHERE tag removes the prefix only when the TRIM tag is constructed.
When the set tag element is constructed:
/ / Set the label
public class SetSqlNode extends TrimSqlNode {
private static final List<String> COMMA = Collections.singletonList(",");
// Can be used as prefix or suffix removal
public SetSqlNode(Configuration configuration,SqlNode contents) {
super(configuration, contents, "SET", COMMA, null, COMMA); }}Copy the codeWhere tag elements are constructed:
/ / the Where TAB
public class WhereSqlNode extends TrimSqlNode {
// There are many different scenarios
private static List<String> prefixList = Arrays.asList("AND "."OR "."AND\n"."OR\n"."AND\r"."OR\r"."AND\t"."OR\t");
// Obviously only prefix removal is used, notice that prefixes are passed in prefixList and suffixes are passed in NULL
public WhereSqlNode(Configuration configuration, SqlNode contents) {
super(configuration, contents, "WHERE", prefixList, null.null); }}Copy the codeTop6. Bind tag
Frequently used: * * * * * *
Practicality: ★☆☆☆
In simple terms, this tag is used to create a variable and bind it to the context, that is, to be used by the context.
<select id="selecUser">
<bind name="myName" value="'%' + _parameter.getName() + '%'" />
SELECT * FROM user
WHERE name LIKE #{myName}
</select>
Copy the codeYou can get a sense of what the above example does, which is to aid in building a fuzzy query by concatenating statements, so you might wonder, why don’t you concatenate statements directly, why do you need to create a variable and go around?
Let me start with a question: how do you concatenate fuzzy queries like statements in mysql?
select * from user where name like concat(The '%',#{name},The '%')
Copy the codeThat’s true, but what if one day your manager tells you that the database has been changed to Oracle? Does the above statement still work? Oracle concat supports concatenation of two strings. For example, the most you can do is write:
select * from user where name like concat(The '%',#{name})
Copy the codeBut it’s missing the pound sign on the right, so it’s not as good as you’d like, so you change it to something like this:
select * from user where name like The '%'||#{name}||The '%'
Copy the codeYes, but a few days later, the leader told you that the database changed back to mysql? Er… Sorry, you’ll have to change the relevant use to fuzzy query back.
select * from user where name like concat(The '%',#{name},The '%')
Copy the codeIf you bind a database, you can use the Like syntax for a query. If you bind a database, you can use the Like syntax for a query. If you bind a database, you can use the Like syntax.
<select id="selecUser">
<bind name="myName" value="'%' + _parameter.getName() + '%'" />
SELECT * FROM user
WHERE name LIKE #{myName}
</select>
Copy the codeThe use of bind solves some of the problems caused by database re-selection. Of course, it doesn’t happen very often in practice, so I personally don’t use bind very much. There may be some other application scenarios, I hope you can find them and share with us. All in all, I grudgingly gave one star (not much practical use, but to save face anyway).
Extension: SQL tags + include tags
★★★ ★★★
Practicality: ★★★☆
SQL tag and include tag combination, used for the reuse of SQL statements, daily high-frequency or common use of the statement block can be extracted for reuse, in fact, we should not be unfamiliar, early when we learn JSP, there is a include tag can introduce some common reusable page files, Such as header or tail page code elements, this kind of reuse design is common.
Strictly speaking, SQL and include are not part of the dynamic SQL tag membership, because they are such a treasure trove, so I will briefly say that SQL tags are used to define a reusable section of SQL statements that can be used in other statements. The include tag, on the other hand, uses the attribute refid to refer to the SQL tag statement fragment matching the id.
A simple block of reusable code could be:
<! -- Reusable block of field statements -->
<sql id="userColumns">
id,username,password
</sql>
Copy the codeSimple reuse when querying or inserting:
<! -- Simple reuse for query -->
<select id="selectUsers" resultType="map">
select
<include refid="userColumns"></include>
from user
</select>
<! -- Simple reuse when inserting -->
<insert id="insertUser" resultType="map">
insert into user(
<include refid="userColumns"></include>
)values(
#{id},#{username},#{password}
)
</insert>
Copy the codeOf course, reuse statements also support attribute passing, such as:
<! -- Reusable block of field statements -->
<sql id="userColumns">
${pojo}.id,${pojo}.username
</sql>
Copy the codeThis SQL fragment can be used in other statements:
<! -- Query reuse -->
<select id="selectUsers" resultType="map">
select
<include refid="userColumns">
<property name="pojo" value="u1"/>
</include>.<include refid="userColumns">
<property name="pojo" value="u2"/>
</include>
from user u1 cross join user u2
</select>
Copy the codeAttribute values can also be used in the reFID attribute of an include element or in a multi-layer internal statement. Attributes can be passed through, for example:
<! -- Simple block -->
<sql id="sql1">
${prefix}_user
</sql>
<! -- Nested statement block -->
<sql id="sql2">
from
<include refid="${include_target}"/>
</sql>
<! SQL > select * from nested blocks
<select id="select" resultType="map">
select
id, username
<include refid="sql2">
<property name="prefix" value="t"/>
<property name="include_target" value="sql1"/>
</include>
</select>
Copy the codeSo far, we have introduced the basic usage of 9 big dynamic SQL tags. In addition, we have some questions: Mybatis is how to parse these dynamic SQL tags? How do you finally build a fully executable SQL statement? With these questions in mind, we analyze them in detail in Section 4.
4. The underlying principles of dynamic SQL
How does Mybatis parse and build dynamic SQL? The first recommendation, of course, is to read the source code, and read the source code, is a technical study of the problem, in order to learn from, in order to work reserves, in order to solve the problem, in order to let their own programming on the road to run to understand some… And hope to read the source code, to understand the underlying implementation principle, remember not to separate from the whole to read part, otherwise you understand must be limited and one-sided, thus neglecting the design of the eucore. Just as we read history or view the universe, the best way is to enlarge from the whole to the part, and extend forward and backward, which will be very comfortable and transparent. So I am ready to gradually enlarge the analysis from the core main line of Mybatis framework.
Mybatis core API (Mybatis core API) ), but we know the core part of Mybatis framework is the component of the build process, so as to support the use of an external application, from the application to create the configuration and call the API, to frame the load configuration and initialize the members, to create a session and receives the request, and then process the request, eventually return to process the results, etc.
Our dynamic SQL parsing takes place when the SQL statement object MappedStatement is built. If we zoom in a little bit, we can see that whether the SQL statement is configured using XML or annotated, the framework eventually stores the parsed SQL statement object into the MappedStatement collection pool.
The part highlighted above is the dynamic SQL tag parsing process involved in XML configuration mode parsing and annotation configuration mode parsing. We explain the process respectively:
- First, how does the framework parse SQL statements configured in XML?
Above for XML configuration mode of SQL statement parsing process, whether use Mybatis framework alone or integrated with Mybatis Spring framework, to boot entry will begin from the SqlSessionFactoryBuilder is. The build () to start building, The global Configuration object is built with XMLConfigBuilder, each Mapper mapperBuilder is built with XMLMapperBuilder, and each SQL in the Mapper is built with XMLStatementBuilder A statement object (the select/insert/update/delete). As each SQL statement object is parsed and built, a key method, parseStatementNode (), highlighted in orange above, contains a core node that handles dynamic SQL.
// XML configuration statement builder
public class XMLStatementBuilder {
// Actually parses each SQL statement
/ / such as the select | insert | update | delete
public void parseStatementNode(a) {
// [ignore] parameter build...
// [ignores] cache build..
// [ignores] result set construction etc..
// this is the core of dynamic SQL.
String lang = context.getStringAttribute("lang"); LanguageDriver langDriver = getLanguageDriver(lang); SqlSource sqlSource = langDriver.createSqlSource(..) ;// [ignores] Finally add the parsed statement object to the statement collection poolBuilderAssistant. AddMappedStatement (statement object)}}Copy the codeWe first focus on this code, which [key] part of the LanguageDriver and SqlSource will be our next to explain the dynamic SQL statement parsing of the core class, we are not in a hurry to analyze, we first annotate the way process is also comb comparison.
- Second, annotate the configuration of SQL statements, how does the framework parse?
You’ll notice that annotated configuration SQL parsing is very similar to XML parsing, except that annotated SQL parsing uses the MapperAnnotationBuilder, Each statement object (@select, @INSERT, @update, @delete, etc.) is parsed through a key parsing method called parseStatement (), which is highlighted in orange in the image above. There is also a core node inside this method that handles dynamic SQL.
// Annotate the configuration statement builder
public class MapperAnnotationBuilder {
// Actually parses each SQL statement
/ / @ the Select, for example, @ Insert, @ Update, @ Delete
void parseStatement(Method method) {
// [ignore] parameter build...
// [ignores] cache build..
// [ignores] result set construction etc..
// this is the core of dynamic SQL.
final LanguageDriver languageDriver = getLanguageDriver(method);
finalSqlSource sqlSource = buildSqlSource( languageDriver,... ) ;// [ignores] Finally add the parsed statement object to the statement collection poolBuilderAssistant. AddMappedStatement (statement object)}}Copy the codeThe process is the same whether it is through XML configuration statements or annotations, and the LanguageDriver LanguageDriver and SqlSource are still present. What are these two classes/interfaces? How can SQL statement parsers be so tied up?
It all starts with the SQL you write…
We know that eventually all of your SQL statement objects, whether XML or annotations, will be parsed neatly and placed in a pool of SQL statement objects that Executor can use to perform CRUD. We know the properties of each SQL statement object, which are extremely complex, such as timeout Settings, caches, statement types, result set mappings, and so on.
// SQL statement object
public final class MappedStatement {
private String resource;
private Configuration configuration;
private String id;
private Integer fetchSize;
private Integer timeout;
private StatementType statementType;
private ResultSetType resultSetType;
/ / SQL source
private SqlSource sqlSource;
private Cache cache;
private ParameterMap parameterMap;
private List<ResultMap> resultMaps;
private boolean flushCacheRequired;
private boolean useCache;
private boolean resultOrdered;
private SqlCommandType sqlCommandType;
private KeyGenerator keyGenerator;
private String[] keyProperties;
private String[] keyColumns;
private boolean hasNestedResultMaps;
private String databaseId;
private Log statementLog;
private LanguageDriver lang;
private String[] resultSets;
}
Copy the codeOne particular property is our statement source, SqlSource, which functions as pure as its name. ParameterObject is an interface that combines parameterObject, a user-passed parameterObject, with dynamic SQL to generate SQL statements and wrap BoundSql objects. The SqlSource interface has five implementation classes: StaticSqlSource, DynamicSqlSource, RawSqlSource, ProviderSqlSource, VelocitySqlSource (while VelocitySqlSource is currently just a test case, Not yet used as an actual Sql source implementation).
- StaticSqlSource: StaticSqlSource implementation class. All SQL sources will eventually be built into an instance of StaticSqlSource. This implementation class generates the final executable SQL statement for use in a statement or prepareStatement.
- RawSqlSource: Native SQL source implementation class that parses and builds SQL statements with ‘#{}’ placeholders, or native SQL statements, and ultimately builds an instance of StaticSqlSource.
- DynamicSqlSource: a DynamicSqlSource implementation class that parses SQL statements with the ‘${}’ substitution or statements with dynamic SQL (such as If/Where/Foreach, etc.), resulting in a StaticSqlSource instance.
- ProviderSqlSource: Annotated SQL source implementation class that will be distributed to RawSqlSource or DynamicSqlSource depending on the content of the SQL statement, and of course will eventually build the StaticSqlSource instance.
- VelocitySqlSource: Template SQL source implementation class. Currently (V3.5.6) this is officially stated as a test case and is not yet used as a true template SQL source implementation class.
Mybatis selects which data source instance to build based on three dimensions of information.
- The first dimension: client SQL configuration: XML or annotation.
- The second dimension: whether dynamic SQL is used in the SQL statement (if/ WHERE /foreach, etc.).
- The third dimension: whether the SQL statement contains the substitution ‘${}’ or the placeholder ‘#{}’.
The SqlSource interface has only one method, getBoundSql, which creates a BoundSql object.
public interface SqlSource {
BoundSql getBoundSql(Object parameterObject);
}
Copy the codeBoundSql objects can be retrieved from the SQL source to retrieve the SQL string that is ultimately sent to the database (via JDBC) for execution.
The SQL string executed in JDBC is indeed in the BoundSql object. The BoundSql object stores dynamically (or statically) generated SQL statements and the corresponding parameter information, which is built from the actual SqlSource instance when the executor executes the CURD.
public class BoundSql {
SQL statement, which may contain "?" A placeholder
private final String sql;
// Set of parameter attributes in SQL
private final List<ParameterMapping> parameterMappings;
// The actual parameter values passed in when the client executes the SQL
private final Object parameterObject;
// Copy the contents of the DynamicContext.bindings collection
private final Map<String, Object> additionalParameters;
// Build the meta-parameter object with additionalParameters
private final MetaObject metaParameters;
}
Copy the codeWhen an Executor instance (such as BaseExecutor) executes an add, delete, change, or query, the BoundSql instance is built using SqlSource, and the BoundSql instance is used to obtain the SQL statement that is ultimately sent to the database for execution. The system can construct statements or prepare statements from SQL statements and send them to a database for execution, such as the execution of Statement processor StatementHandler.
Mybatis is the most hardcore API you know. These execution processes are explained in detail.
BoundSql: BoundSql: BoundSql: BoundSql: BoundSql: BoundSql: BoundSql: BoundSql: BoundSql
- When the program starts the initial build, the framework builds the corresponding SqlSource source instance (static/dynamic) based on the SQL statement type.
- When the program is actually running, the framework dynamically builds BoundSql objects based on the incoming parameters and sends the final SQL to the database for execution.
BoundSql: BoundSql: BoundSql: BoundSql: BoundSql: BoundSql: BoundSql: BoundSql: BoundSql: BoundSql: BoundSql LanguageDriver is the LanguageDriver we are going to introduce next!
public interface LanguageDriver {
SqlSource createSqlSource(...).;
}
Copy the codeLanguage-driven interface LanguageDriver is also very concise, internal definition of the method to build SQL source, LanguageDriver interface has two implementation classes, respectively: XMLLanguageDriver, RawLanguageDriver. A brief introduction:
- XMLLanguageDriver: Is the framework’s default language driver that creates matching SQL sources (DynamicSqlSource, RawSqlSource, etc.) based on the three dimensions of the SQL source described above. The following code is some of the actions related to the language driver when Mybatis assembs the global configuration, including: built-in two language drivers and set the alias for reference, registered two language drivers to the language registration factory, set the XML language driver as the default language driver.
// Global configuration constructor
public Configuration(a) {
// Build/register a lot of interesting aliases
// ...
// There is a built-in driver for both languages.
typeAliasRegistry.registerAlias("XML", XMLLanguageDriver.class);
typeAliasRegistry.registerAlias("RAW", RawLanguageDriver.class);
// Register XML [language-driven] --> and set it to default!
languageRegistry.setDefaultDriverClass(XMLLanguageDriver.class);
// Register native [language driven]
languageRegistry.register(RawLanguageDriver.class);
}
Copy the code- RawLanguageDriver: Driven by a native language, as the name suggests, it can only create a native SQL source (RawSqlSource), and it also inherits XMLLanguageDriver.
/**
* As of 3.2.4 the default XML language is able to identify static statements
* and create a {@link RawSqlSource}. So there is no need to use RAW unless you
* want to make sure that there is not any dynamic tag for any reason.
*
* @since 3.2.0
* @author Eduardo Macarron
*/
public class RawLanguageDriver extends XMLLanguageDriver {}Copy the codeGeneral meaning of the note: Since Mybatis 3.2.4, the XML language driver supports parsing static statements (as well as dynamic statements) and creating the corresponding SQL source (e.g. static statements are native SQL sources), so unless you are absolutely sure that your SQL statement does not contain any dynamic tags, Otherwise don’t use RawLanguageDriver! Otherwise an error will be reported!! Let’s look at some examples of individual name references:
<select id="findAll" resultType="map" lang="RAW" >
select * from user
</select>
<! Aliases or fully qualified class names are allowed -->
<select id="findAll" resultType="map" lang="org.apache.ibatis.scripting.xmltags.XMLLanguageDriver">
select * from user
</select>
Copy the codeThe framework allows you to manually specify the language driver using the lang attribute. If you do not specify the lang attribute, the default is lang = “XML”, which stands for XMLLanguageDriver. Of course, the lang attribute can be either our built-in alias or our fully qualified language driver name, but it is worth noting that, When the statement contains dynamic SQL tags, the only option is to use lang=”XML”, otherwise the program will report an error when initializing the component.
## Cause: org.apache.ibatis.builder.BuilderException: ## Dynamic content is not allowed when using RAW language ## Dynamic content is not supported by native language drivers!Copy the codeThis error occurs when RawLanguageDriver checks a dynamic SQL source:
public class RawLanguageDriver extends XMLLanguageDriver {
// RAW cannot contain dynamic content
private void checkIsNotDynamic(SqlSource source) {
if(! RawSqlSource.class.equals(source.getClass())) {throw new BuilderException(
"Dynamic content is not allowed when using RAW language"); }}}Copy the codeSo far, the basic logic has been clarified: during program startup, the language-driven SQL source is created, and BoundSql is dynamically parsed out of the SQL source at runtime.
So besides the system’s default two language drivers, what else?
The answer is: yes, for example the Mybatis framework currently uses a language driver called VelocityLanguageDriver. I’m sure you’ve all learned JSP templating engines, and many of you have learned other templating engines, such as Freemark and Velocity, which have their own set of templating language syntax. Mybatis tries to use Velocity template engine as the language driver. Although Mybatis is only used in test cases at present, it tells us that the framework allows custom language drivers, so it is not only the OGNL syntax used in XML and RAW language drivers. It could also be Velocity (syntax), or a set of template languages that you can define yourself (while you define a set of syntax). For example, the following is the Velocity language driver and the corresponding SQL source used in Mybatis framework. They parse BoundSql objects using Velocity syntax/mode.
/** * Just a test case. Not a real Velocity implementation
public class VelocityLanguageDriver implements LanguageDriver {
public SqlSource createSqlSource(a) {...}
}
Copy the codepublic class VelocitySqlSource implements SqlSource {
public BoundSql getBoundSql(a) {...}
}
Copy the codeOk, so the basic idea of being language driven is this. The dynamic SQL source SqlSource is an attribute of the statement object MappedStatement. How does the language driver create it during the initial build phase of the program? Let’s start by looking at how common dynamic SQL source objects are created.
From the initial build phase of the program above, we can see that the final language driver creates the SQL source by calling the XMLScriptBuilder object.
// XML language driven
public class XMLLanguageDriver implements LanguageDriver {
// Create the SQL source by calling the XMLScriptBuilder object
@Override
public SqlSource createSqlSource(a) {
/ / instance
XMLScriptBuilder builder = new XMLScriptBuilder();
/ / parsing
returnbuilder.parseScriptNode(); }}Copy the codeAs we saw earlier, all dynamic tag handlers are initially built when an instance of XMLScriptBuilder is initially constructed:
// XML script tag builder
public class XMLScriptBuilder{
// Label node processor pool
private final Map<String, NodeHandler> nodeHandlerMap = new HashMap<>();
/ / the constructor
public XMLScriptBuilder(a) {
initNodeHandlerMap();
/ /... Other initializations are unnecessary and unimportant
}
// Dynamic tag handler
private void initNodeHandlerMap(a) {
nodeHandlerMap.put("trim".new TrimHandler());
nodeHandlerMap.put("where".new WhereHandler());
nodeHandlerMap.put("set".new SetHandler());
nodeHandlerMap.put("foreach".new ForEachHandler());
nodeHandlerMap.put("if".new IfHandler());
nodeHandlerMap.put("choose".new ChooseHandler());
nodeHandlerMap.put("when".new IfHandler());
nodeHandlerMap.put("otherwise".new OtherwiseHandler());
nodeHandlerMap.put("bind".newBindHandler()); }}Copy the codeFollowing the XMLScriptBuilder initialization process, the parse create SQL source process is divided into two steps:
1. Dynamic label parsing: determine the type of each dynamic label, use the corresponding label processor for attribute parsing and statement processing, and finally place it into MixedSqlNode pool for use when BoundSql is built.
2, new SQL source, according to whether the SQL has dynamic label or wildcard placeholder to identify the static or dynamic SQL source of the generated object.
public SqlSource parseScriptNode(a) {
// 1. Parse the dynamic tags and place them in a mixed SQL node pool
MixedSqlNode rootSqlNode = parseDynamicTags(context);
// 2, according to the statement type, new out the final SQL source
SqlSource sqlSource;
if (isDynamic) {
sqlSource = new DynamicSqlSource(configuration, rootSqlNode);
} else {
sqlSource = new RawSqlSource(configuration, rootSqlNode, parameterType);
}
return sqlSource;
}
Copy the codeParsing dynamic tags is left to the parseDynamicTags() method, and the dynamic SQL tags for each statement object end up in a mixed SQL node pool.
// Mix SQL node pools
public class MixedSqlNode implements SqlNode {
// All dynamic SQL tags: IF, WHERE, SET, etc
private final List<SqlNode> contents;
}
Copy the codeLet’s take a look at the implementation class of the SqlNode interface, which basically covers all the node instances we need to use for dynamic SQL tag processors. The MixedSqlNode MixedSqlNode is only used to facilitate the acquisition of all dynamic label nodes for each statement, so it was born in response to the situation.
When the program runs, the executor calls the SQL source to help build BoundSql objects. The core job of the SQL source is to build BoundSql objects based on the type of each segment of the tag. Match to the corresponding node implementation class to parse and concatenate each small SQL statement.
When the program runs, the dynamic SQL source gets the BoundSql object:
// Dynamic SQL source
public class DynamicSqlSource implements SqlSource {
// The rootSqlNode property here is MixedSqlNode
private final SqlNode rootSqlNode;
@Override
public BoundSql getBoundSql(Object parameterObject) {
// Dynamic SQL core parsing processrootSqlNode.apply(...) ;returnboundSql; }}Copy the codeObviously, loop through each specific label node by calling the Apply () method of MixedSqlNode.
public class MixedSqlNode implements SqlNode {
// All dynamic SQL tags: IF, WHERE, SET, etc
private final List<SqlNode> contents;
@Override
public boolean apply(...). {
// Loop over, assigning the resolution of each node to a specific node implementation
// For example, the parsing of
nodes is handed over to IfSqlNode
For example, the parsing of a plain text node is handed over to StaticTextSqlNodecontents.forEach(node -> node.apply(...) );return true; }}Copy the codeWe choose one or two tag node parsing process to illustrate, other tag node implementation classes are basically the same. First let’s look at the handling of the IF tag node:
// IF tag node
public class IfSqlNode implements SqlNode {
private final ExpressionEvaluator evaluator;
// Implement logic
@Override
public boolean apply(DynamicContext context) {
Evaluator is a parse validation class based on OGNL syntax
if (evaluator.evaluateBoolean(test, context.getBindings())) {
contents.apply(context);
return true;
}
return false; }}Copy the codeThe parsing process of the IF tag node is very simple. The expression in the test attribute of the IF tag is verified by parsing the verification class ExpressionEvaluator. IF the expression meets the requirements, it will be spliced, and IF the expression does not meet the requirements, it will be skipped. Let’s look at the node resolution of the Trim tag. The lower level processing of the set tag and the WHERE tag is based on this:
public class TrimSqlNode implements SqlNode {
// Core processing method
public void applyAll(a) {
// Add and remove prefix intelligentlyapplyPrefix(..) ;// Add and remove prefix intelligently
applySuffix(..);
}
}
Copy the codeLet’s look at the parsing process of a plain text tag node implementation class:
// Plain text label node implementation class
public class StaticTextSqlNode implements SqlNode {
private final String text;
public StaticTextSqlNode(String text) {
this.text = text;
}
// Node processing, just pure statement concatenation
@Override
public boolean apply(DynamicContext context) {
context.appendSql(text);
return true; }}Copy the codeSo far, we have basically explained the underlying parsing process of dynamic SQL, which is a bit long, but the process is roughly complete, there are omissions, we will come back to supplement.
conclusion
Unconsciously, I am such a huge length of explanation and analysis, really not suitable for fragmentation time to read, but in other words, after all, this article belongs to the Mybatis full solution series, as a student or research or suggest to master it, will be helpful for the future study of many framework technology. The syntax of dynamic SQL is written in the same way as that of XML, but you need to add script tags before and after the string to declare the statement as dynamic SQL, for example:
public class UserDao {
/** * Update user */
@Select( "" )
void updateUser( User user);
}
Copy the codeThis kind of dynamic SQL writing is not very readable and can be difficult to maintain, so I personally prefer XML configuration statements, always pursuing decoupling and simplicity. Of course, there are many teams and projects that use annotations for development. There is no absolute rule, and you still have to make trade-offs based on your actual project situation and the team.
After this, we will talk about the whole solution of Mybatis series (9) : Complex mapping of Mybatis.
The article continues to update, wechat search “Pan Pan and his friends” the first time to read, there are surprises at any time. This article will be included on GitHub github.com/JavaWorld, about hot technology, frame, face classics, solutions, fishing skills, tutorials, videos, comics and so on, we will be the first time to the most beautiful posture, welcome Star ~ we will not only articles in the future! Want to enter the reader group of friends welcome to my personal number: Panshenlian, remarks “add group” we chat in the group, BIU ~
