Dynamic SQL for MyBatis

Several tags are supported in xml:? If? Choose, when, otherwise? Where? Set ? Trim? Foreach

OGNL-expression

1. El or E2
2. El and E2
3. el = = E2 or el eq E2
4. el! = E2 or El neq E2
5. El lt E2: less than
6. El LTE E2: Less than equals, others expressed as GT (greater than), GTE (greater than or equal)
7. El + E2, EL-E2, E1 * E2, E 1/e2, el% E2
8.!e or not E: Non, negate
9. E.method (args): Calling Object methods
JO. E.property: Object property values
EL[E2]: Values by index (List, array, and map)
@[email protected] (args): Invoking a static method of a class
@[email protected]: calling static field values for a class
map[' UserName ') or map.username to get the value of key userName in the map.

< if test= "@[email protected" (UserName) ">
and user_name like concat ('% ', #{username}, '% ')
</if>
Where the Stringutil class is as follows:
public class Stringutil {
public static Boolean isEmpty (String str) {
return str = = NULL | | Str.length () = = 0;
}

public static Boolean isnotempty (String str) {
return!isempty (str);
}
}

The IF tag has a required property, test, and test's property value is a judgment expression that conforms to the OGNL requirement, and the result of the expression can be true or false, except that all non-0 values are true and only 0 is false.

Conditional Query
1, judge the condition Property! = NULL, or properties = = NULL, for any type of field, to determine whether the value of the attribute is empty.
2, judge the condition Property! = ' or property = = ', applies only to a type of string, and is used to determine whether it is an empty string.
3, judging the condition list! = null and List.size () >0, determines whether a collection is empty.
4, when there are multiple judging criteria, use and or or to connect, nested judgments can be grouped with parentheses, and equivalent to (&&) in Java, or equivalent to Java In the OR (| | | ) .
Conditional Query
<select id= ""
Select * from sys_user where 1 = 1
<if test= "UserName! = null and userName! = "",
and user_name like concat ('% ', #{username}, '% ')
</if>
<if test = "UserEmail ! = null and UserEmail! = "";
and user_email = #{useremail}
</if>
</select>
1 = 1 has two functions, function 1 : Prevent error; Function 2: Full table query.

Conditional Update
Now you want to implement a requirement to update only the fields that have changed. Updates cannot be updated to null or null for fields that originally had values but not changed. This dynamic column update can be implemented with the IF tag.
<update id= "";
Update sys_user set
<if test= "UserName! = null and UserName! =" ";
User_name= # {userName},
</if>
<if test= "UserPassword! = null and UserPassword! =" ";
user_password= #{ UserPassword},
</if>
<if test= "UserEmail! = null and UserEmail! =" ";
User_email = #{useremail},< BR></IF>
id = #{id}
WHERE id = #{id}
</update>
Note: The WHERE keyword precedes the id = #{id} to prevent an error.
1, if all conditions are empty or null,sql is the update sys_user set where id = #{id}.
2, the query condition only one is not null or empty (assuming userName), SQL is---update sys_user set User_name= #{username}, where id= #{id}

Conditional insertion
When inserting data into a database table, if the parameter value of a column is not empty, the passed-in value is used, and if the incoming parameter is empty, the default value in the database is used instead of the passed-in null value. This ability to dynamically insert columns can also be implemented using IF.
<insert id= "" usegeneratedkeys= "" keyproperty= "" >
INSERT INTO Sys_user (
User_name, User_password,
<if test = "UserEmail! = null and UserEmail! =" ">
User_email,
</if>
User_info, head_img, Create_time)
Values
#{username}, #{userpassword},
<if test= "UserEmail! = null and UserEmail! =" ">
#{useremail},
</if>
#{userinfo}, #{headimg,jdbctype=blob},
#{createtime, Jdbctype = TIMESTAMP})
</insert>

The IF tag provides basic conditional judgment, but it does not implement If...else If...else ... Logic, to implement such logic, you need to use the Choose when otherwise tag. The Choose element contains when and otherwise two tags, and at least one when in a choose, there are 0 or l otherwise.

Choose when-when is the relationship of the If...else If...else if, only one of the paths will go, if the first when return true, then even if the second third is true, there will be no second and third in the condition.
Choose when compared to where in the following, the function of and or or before the first condition is not removed.
<select id= "" resulttype= "Test.mybatis.simple.model.SysUser" >
Select ID, user_name, User_password, User_email, User_info, head_img, Create_time
From Sys_user
where 1 = 1
<choose>
<when test = "id! = NULL" >
and Id= #{id}
</when>
<when test= "UserName! = null and UserName! =" ">
and user_name = #{username}
</when>
<otherwise>
and 1 = 2
</otherwise>
</choose>
</select>

The role of the WHERE tag:

1. When the IF condition is not satisfied, there is no content in the where element, where is not present in SQL;
2. Immediately before the first query condition that follows the where element, if preceded by an and or or, then culling;
3, where label, or where if label combination, does not give the condition before the addition and or or function, so and or or must be added manually.

The function of the set tag:

1, if the tag contains elements of the content, insert a set, if there is no content in the set element, do not insert the set label, there is a SQL error, so in order to avoid errors, such as id=#{id} such an inevitable assignment is still necessary to retain. From this point of view, the set label does not solve all the problems, still need to be aware of when using.
2. If the last condition ends with a comma, the comma is removed, and the comma of the other conditions does not increase or decrease.

Trim label

The functions of where and set tags can be implemented with trim tags, and are implemented at the bottom through Trimsqlnode.
The where label corresponds to trim is implemented as follows:
<trim prefix= "WHERE" prefixoverrides= "and |or" >
...
</trim>
The spaces behind and and or are not omitted here, in order to avoid matching words such as Andes, orders, and so on. The actual prefixeoverrides contains "and", "or", "and\n", "or\n", "and\r", "or\r", "and\t", "or\t", not just the two prefixes with spaces mentioned above.
The trim implementation for the set label corresponds to the following.
<trim prefix= "SET" suffixoverrides= "," >
...
</trim>
The trim tag has the following properties.
Prefix: When content is contained within a trim element, the content is added prefix the specified prefix.
Suffix: When content is contained within a trim element, the content is added suffix the specified suffix.
Prefixoverrides: When content is contained within a trim element, the matching prefix string in the content is removed.
Suffixoverrides: When content is contained within a trim element, the matching suffix string in the content is removed.

Foreach

Foreach can traverse an array, map, or an object that implements the Iterable interface (such as list, Set). Arrays are converted to list objects when they are processed, so the objects that foreach iterates can be divided into two main classes: the iterable type and the map type.
Collection: Required, value is the name of the property to iterate through. There are many cases of this attribute value.
Item: The variable name, which is the value of each value taken out of the iteration object, .
Index: The property name of the index, in the case of the collection array, the value is the current index value, .
Open: The string at the beginning of the entire looping content.
Close: The string that ends the entire loop of content.
Separator: The delimiter for Each loop.

collection property setting Method :
The following code is a method in Defaultsqlsession and is the processing logic by default:
Private Object Wrapcollection (final Object object) {
if (object instanceof Collection) {
STRICTMAP<OBJECT&G T Map = new strictmap<object> ();
Map.put ("collection", object);　　　　　　　　
if (object instanceof list) {
Map.put ("list", object);
return map;
}
} else if (Object! = null && object.getclass (). IsArray ()) {
Strictmap<object> map = New Strictmap<object> ();
Map.put ("array", object);
return map;
}
return object;
}
When the parameter type is a collection, the default is converted to the map type, and a key is "collection", if the parameter type is a list collection, then continue to add a key to "list", so that when collection= "list", You can get this set and loop through it.
When the parameter type is an array, it is also converted to the map type, the default key is "array", and when you use an array parameter, you need to set the collection property value in the foreach tag to array.

List--List or collection
Set--Collection
Array--and array
Map--_parameter
It is recommended to use @param to specify the name of the parameter, when collection is set to the name specified by the @param annotation.

The In keyword is sometimes used in SQL statements, such as IdIn. You can use ${ids} to get the value directly, but this notation does not prevent SQL injection, and you want to avoid the need for SQL injection to use #{}, then use the foreach tag to meet the requirements.
foreach implementation in a collection or array
List<sysuser> selectbyidlist (list<long> idlist);
<select id= "selectbyidlist" resulttype= "Test.mybatis.simple.model.SysUser" >
Select Id,user_name,user_password,user_email,user_info,head_img,create_time
From Sys_user
where ID in
<foreach collection= "List" open= "(" close= ")" separator= "," item= "id" index = "index" >
#{id}
</foreach>
</select>

foreach implementation BULK INSERT
If the database supports bulk inserts, it can be implemented through foreach. Bulk INSERT is a new feature of SQL-92, currently supported databases are DB2, SQL Server 2008 and later, PostgreSQL8.2 and above, MySQL, SQLite 3.7.11 and above, H2. The syntax for BULK INSERT is as follows.
INSERT into TableName (column-a, [Column-b, ...])
VALUES (' Value-la ', [' value-lb ', ...]),
(' value-2a ', [' value-2b ', ...]),
...
int insertlist (list<sysuser> userlist);
<insert id= "Insertlist" >
INSERT INTO Sys_user (
User_name, User_password, User_email, User_info, head_img, Create_time)
Values
<foreach collection = "List" item= "user" separator= "," >
　　 (
#{user.username}, #{user.userpassword}, #{user.useremail},
#{user.userlnfo}, #{user.headlmg, Jdbctype=blob},
#{user.createtime, Jdbctype=timestamp})
　　 )
</foreach>
</insert>
MyBatis supports bulk new writeback of primary key values(This feature is submitted by the author of this book), this feature first requires that the database primary key value is a self-increment type, but also that the JDBC driver provided by the database can support the return of BULK INSERT primary key value (JDBC provides the interface, but not all the database is the perfect implementation of the interface), so so far, The only MySQL database that can perfectly support this feature. Because SQL Server database
The officially provided JDBC can only return the primary key value of the last inserted data, so this feature cannot be supported. If you want to implement bulk insert in MySQL to return the self-increment primary key value, just make the following modifications based on the original code. Note that the MyBatis version is 3.3.1 or above.
<insert id= "insertlist" Usegeneratedkeys = "true" keyproperty= "id" >

foreach Implements dynamic update
int Updatebymap (map<string, object> Map);
<update id= "" >
Update Sys_user
Set
<foreach collection= "_parameter" item= "val" index= "Key" separator= "," >
${key} = #{val}
</foreach>
WHERE id = #{id}
</update>

Dynamic SQL for MyBatis