MyBatis <where>, <set>, <trim>, <sql>, <foreach> tag usage

MyBatis use of where tags
The where followed by the query condition simplifies the writing of the criteria in the SQL statement
Cases:
<select id= "user" parametertype= "user" resulttype= "user" >
SELECT * FROM user
<where>
<if test= "Id!=null and id!=" >
Id=#{id}
</if>
<if test= "Name!=null and name!=" >
and Name=#{name}
</if>
<if test= "Gender!=null and gender!=" >
and Gender=#{gender}
</if>
</where>
</select>

MyBatis will intelligently ignore the first and OR or
If the ID is null, the SQL statement executed is:
SELECT * from user where name= ' xxx ' and gender= ' xxx '

MyBatis Use of Set tags
Cases:
<update id= "userupdate" parametertype= "User" >
Update user
<set>
<if test= "Id!=null and id!=" >
Id=#{id},
</if>
<if test= "Name!=null and name!=" >
Name=#{name},
</if>
<if test= "Gender!=null and gender!=" >
Gender=#{gender},
</if>
</set>
</update>

If the above fragment is not NULL, execute SQL as: Update user set id= "xx", name= ' xxx ', gender= ' xxx ';
The last comma in the above SQL is automatically ignored by the set tag

Use of MyBatis trim labels
Trim Property
Prefix: Prefix
Suffix: suffix
Prefixoverrides: Ignores the first specified delimiter
Suffixoverrides: The last delimiter will be slightly
Cases:
1)

<select id= "user" parametertype= "user" resulttype= "user" >
SELECT * FROM user
<trim prefix= "WHERE" prefixoverride= "and | or ">
<if test= "Id!=null and id!=" >
Id=#{id}
</if>
<if test= "Name!=null and name!=" >
and Name=#{name}
</if>
<if test= "Gender!=null and gender!=" >
and Gender=#{gender}
</if>
</trim>
</select>

If the ID is null, the SQL statement executed is:
SELECT * from user where name= "xxx" and gender= "xxx"

2)

<update>
Update user
<trim prefix= "Set" Suffixoverride= "," >
<if test= "Id!=null and id!=" >
Id=#{id},
</if>
<if test= "Name!=null and name!=" >
Name=#{name},
</if>
<if test= "Gender!=null and gender!=" >
Gender=#{gender}
</if>
</trim>
</update>

If the SQL statement executed by gender is null:
Update user set id= "xx", name= "XX"

MyBatis Use of foreach tags

A loop of SQL conditions can be implemented using <foreach> tags, which can be done in a similar batch of SQL

The parameters accepted by MyBatis are divided into:
(1) Basic type (2) object (3) List (4) Array (5) Map

Parameters:
Collection: The collection to loop
Index: A name that is used to indicate where each iteration takes place during the iteration
Item: Alias for each element in the collection when iterating
Open: Start with what
Close: What ends with
Separator: What separates the looping content
Cases:
1) Batch Add
<insert id= "Add" parametertype= "List" >
Insert into User (Id,name,gender) values
<foreach collection= "list" item= "User" open= "(" close= ")" separator= "," >
#{user.id},#{user.name},#{user.gender}
</foreach>
</insert>

If the list has two data, the SQL statement executed is:
Insert into User (Id,name,gender) VALUES ("1", "Zhang San", "Male"), ("2", "John Doe", "female")

Mapper Interface:
void Add (list<user> users);

2) Bulk Delete
<delete id= "DeleteUser" parametertype= "Array" >
Delete from the user where ID in
<foreach collection= "Array" item= "id" open= "(" close= ")" separator= "," >
#{id}
</foreach>
</delete>

If there are two data to delete the executed SQL statement is:
Delete from user where ID in (UP)

MyBatis Use of SQL tags

Code reuse with SQL fragments
Cases:
<sql id= "Sqlcont" >
Select COUNT (*)
</sql>

<sql id= "Sqlselect" >
SELECT *
</sql>
<sql id= "Sqlcontent" >
From user
</sql>
　　　　

<select id= "Finduser" parametertype= "user" resulttype= "user" >
<include refid= "Sqlcont"/>
<include refid= "Sqlcontent"/>
</select>

MyBatis <where>, <set>, <trim>, <sql>, <foreach> tag usage