1. <if>Element    

What dynamic SQL usually does is conditionally include a part of the where clause. So in MyBatis, the <if>element is the most common element, which is similar to the if statement in Java.

2. <select>, <when>, <otherwise> elements    

  Sometimes, you don't want to use all the conditional statements, you just want to choose one or two of them. In this case, MyBatis provides the choose element, which is a bit like the switch statement in Java. (Executed currently, not later)

<!-- Use choose,when,otherwise Element to dynamically query user information based on conditions -->
	<select id="selectUserByChoose"  resultType="com.po.MyUser" parameterType="com.po.MyUser">
		select * from user where 1=1
		<choose>
		<when test="uname !=null and uname!=''">
			and uname like concat('%',#{uname},'%')
		</when>
		<when test="usex !=null and usex!=''">
			and usex = #{usex}
		</when>
		<otherwise>
			and uid > 10
		</otherwise>
		</choose>
	</select>

3. <trim>elements

The main function of <trim> elements is to prefix or suffix the contents they contain, and the corresponding attributes are prefix and suffix. You can override the first part of the content, that is, ignore it, or override the tail part. The corresponding attributes are prefixOverrides and suffixOverrides. Because the <trim>element has this capability, it is also very simple to use <trim>instead of the <where>element.

<!-- Use trim Element to dynamically query user information based on conditions -->
	<select id="selectUserByTrim"  resultType="com.po.MyUser" parameterType="com.po.MyUser">
		select * from user 
		<trim prefix="where" prefixOverrides="and |or">  
	        <if test="uname !=null and uname!=''">  
	            and uname like concat('%',#{uname},'%')
	        </if>  
	        <if test="usex !=null and usex!=''">  
	            and usex = #{usex} 
	        </if>    
    		</trim>  
	</select>

4. <where>element

The <where>element is used to output a where statement where the <where>element is written. Another benefit is that MyBatis will handle it intelligently without considering what the conditional output inside the <where>element looks like. If all the conditions are not met, MyBatis will find all the records. If the output starts with and, MyBatis will ignore the first and, of course, if it starts with or, MyBatis will also ignore it.

<!-- Use where Element to dynamically query user information based on conditions -->
	<select id="selectUserByWhere"  resultType="com.po.MyUser" parameterType="com.po.MyUser">
		select * from user 
		<where>
			<if test="uname !=null and uname!=''">
				and uname like concat('%',#{uname},'%')
			</if>
			<if test="usex !=null and usex!=''">
				and usex = #{usex}
			</if>
		</where>
	</select>

5. <set>element    

In a dynamic update statement, columns can be dynamically updated using the <set>element.

<!-- Use set Element, dynamically modifying a user -->
	<update id="updateUserBySet" parameterType="com.po.MyUser">
		update user 
		<set>
			<if test="uname != null">uname=#{uname},</if>
			<if test="usex != null">usex=#{usex}</if>
		</set>
		where uid = #{uid}
	</update>

6. <foreach>element    

The <foreach>element is primarily used to construct in conditions, which can iterate over a set in a SQL statement. The main attributes of the foreach element are item, index, collection, open, separator, close.

item represents the alias of each element in the collection for iteration.

index specifies a name that represents the location to which each iteration occurs during the iteration.

open indicates what the statement begins with.

The separator represents what symbol is used as the separator between each iteration.

close denotes what ends.

The most critical and error-prone property when using <foreach> is the collection attribute, which is optional, but has different values in different situations, mainly in the following three cases:    

1. If a single parameter is passed in and the parameter type is a List, the collection property value is list.    
2. If a single parameter is passed in and the parameter type is an array array array, the collection property value is array.    
3. If the parameters passed in are multiple, they need to be encapsulated as a Map, or a single parameter can be encapsulated as a Map. The key of the Map is the parameter name, and the collection property value is the key of the passed List or array object in its encapsulated Map.

<!-- Use foreach Element, query user information -->
	<select id="selectUserByForeach" resultType="com.po.MyUser"  parameterType="List">
		select * from user where uid in
		<foreach item="item" index="index" collection="list" open="(" separator="," close=")">
			#{item}
		</foreach>
	</select>

7. <bind>element

The bind element tag creates a variable well from the OGNL expression to bind it to the context, and the use of bind is also involved in MyBatis's use of mysql's fuzzy query string stitching (like). Once you have created a variable for the bind element label, you can use it directly below. Using bind stitching strings not only prevents SQL from being modified by changing the database, but also prevents SQL injection. (Different databases use different symbols)

    <!-- Use bind Elements for Fuzzy Query -->
	<select id="selectUserByBind" resultType="com.po.MyUser"  parameterType="com.po.MyUser">
		<!-- bind in uname yes com.po.MyUser Property name of -->
<bind name="paran_uname" value="'%' + uname + '%'"/>
		select * from user where uname like #{paran_uname}
	</select>

8. Summary

if	Judgment Statement (Single Conditional Branch Judgment)
choose(when,otherwise)	Equivalent to switch and case statements in Java (multi-conditional branching judgment)
trim	Auxiliary elements for handling specific SQL assembly problems
where	Auxiliary elements for handling specific SQL assembly problems
set	Auxiliary elements for handling specific SQL assembly problems
foreach	Loop statement, often used in in conditional judgment
bind	Create a variable from an OGML expression and bind it to context, commonly used with fuzzy queries