Dynamic SQL is one of the powerful features of MyBatis, which can complete different SQL splicing under different conditions.
Refer to the official document: https://mybatis.org/mybatis-3/zh/dynamic-sql.html
tag
Looking at this scene, there are required fields and non-required fields. When the field is not sure whether it is passed in, how does the programmer implement it?
-
insert into user(name,age,brithday) values(?,?,?);
-
insert into user(name,age) values (?,?);
For example, in the above two sql statements, when we select different content to insert, we need to use different sql statements, but we can’t write them in every case, right? At this time, we can implement it through the
if tag syntax
<if test="birthday != null">//if after
birthday
</if>
These tags are provided to us by MyBatis and implemented in the xml file, such as the following code:
tag
If all the above fields are not required, then if you only use the above
Attributes of the
? prefix (prefix): the first character, usually (
? suffix (suffix): the last character, usually when inserted)
? suffixOverrides (prefix coverage): Indicates the prefix to be removed from the entire statement block
? prefixOverrides (suffix coverage): Indicates the suffix to be removed from the entire statement block. When inserting, it is usually ‘,’ as shown in the picture below. In this case, it will automatically remove the suffix after the photo. If the photo is null, it will automatically remove the suffix after the password. of,
When the above sql is dynamically parsed, the first
-
Based on the ?prefix? configuration, add ?(?
-
Based on ?suffix? configuration, end with ?)?
-
The statements of multiple
organizations end with ?,?, and the concatenated string will end with ?,? at the end, based on ?suffixO
-
Overrides? Configuration removes the last ?,?
-
Note that createTime in ?
? is a property of the incoming object
tag
-
For the incoming user object, do a where condition query based on the attributes, and the attributes in the user object that are not null are all query conditions.
-
If there is no content about the judgment passed in the condition, then no where restriction will be added automatically, which is a dynamic generation.
-
It will decide whether to generate where according to the parameters you pass
feature:
-
Usually used together with
-
The
tag can only remove the leading and keyword.
-
tag If there is no content inside, then no where content will be generated
-
The
tag itself can be seen as equivalent to
tag
feature:
-
Usually used together with
-
The
tag can only remove the last ‘ , ‘
-
tag If there is no content inside, an error will be reported
-
The
tag itself can be seen as equivalent to
tag
Use this tag when traversing the collection
-
collection: the collection in the bound method parameter, such as List, Set, Map or array object
-
item: each object when traversing
-
open: the string at the beginning of the statement block
-
close: the string at the end of the statement block
-
separator: the string of intervals between each traversal
Usage scenario:
1. The environmental case used
delete from user where id in (1,2,3,4,5);
--When we need to delete data in batches, how should we implement it? You can use the <foreach> tag
2. Use <foreach> to implement
--Suppose there is a List List<Integer> list = new ArrayList<>(); there are 1,2,3,4,5 in it
--The following is the code constructed using <foreach>
-- arr is an array and item represents the contents of the array, similar to the foreach loop
-- open is equivalent to the prefix in trim and close is equivalent to the suffix separator
<delete id="deleteByIds">
delete from article
where id in
<foreach collection="arr" item="item" open="(" close=")" separator=",">
#{item}
</foreach>
</delete>
The knowledge points of the article match the official knowledge files, and you can further learn related knowledge. Java skill tree setSet interface 109281 people are studying systematically