Through this article, you will see the following knowledge:

• How to handle the xml file <, >, < =, >=
• Use of if, where and foreach tags in dynamic sql
• Use custom name for collection in foreach
• How to deal with repeated sql statements

Use of judgment class symbols in xml

If the < sign is forbidden in the xml file, the judgment class symbol defined in the xml file label will be replaced by the entity symbol

Judgment class symbol	Entity symbol
>	>
<	&lt;
>=	&gt; =
<=	&lt; =

< if > of dynamic SQL

• The function of the if tag is the same as that of the if tag in java, which is used to judge the if tag
• Syntax: < if test = "condition" > part of SQL statement < / if >
• If the value in test is true, the SQL fragment contained in it will be spliced into its SQL statement

<!--Will execute first if label
 1 here=1 To prevent where And and Direct splicing and where No statement followed
 Thus appear sql syntax error -->
<select id="selectStudentIf" 
resultType="com.lanan.domain.Student"> 
    select id,name,email,age from student 
    where 1=1 
    <if test="name != null and name !='' "> 
        and name = #{name} 
    </if> 
    <if test="age > 0 "> 
        and age &gt; #{age} 
    </if> 
</select>

Dynamic SQL < where >

• Solve the problem of adding 1 = 1 (identity) to the if tag. Wrap the if tag in the where tag. If the if judgment is successful, add where, and clear the redundant and and or in the if
• Syntax: other dynamic sql

<select id="selectStudentIf" 
resultType="com.lanan.domain.Student"> 
    select id,name,email,age from student 
    where 1=1 
    <where>
		<if test="name != null and name !='' "> 
        	and name = #{name} 
    	</if> 
    	<if test="age > 0 "> 
        	and age &gt; #{age} 
    	</if> 
	</where>
</select>

< foreach > of dynamic SQL

• The foreach tag is used to traverse arrays and collections
• Syntax: < foreach collection = "collection type" open = "start character" close = "end character" item = "member in collection" separator = "separator between collection members" >
  #{value of item}
  </foreach>
• Pay special attention to the attribute collection. When you don't encapsulate list and array into a Map, mybatis will automatically encapsulate it for you. List corresponds to Map Key is list, and array corresponds to Map The key is array, which has been fixed. Unless you encapsulate it yourself, fill in the key you set in the collection
• If you want to use a self-defined name, in addition to encapsulating the map yourself, you can also pass the parameter @ Param("custom name") in the naming method when passing parameters to the mapper

<select id="selectStudentForList" 
resultType="com.bjpowernode.domain.Student"> 
    select id,name,email,age from student 
    <if test="list !=null and list.size > 0 "> 
        where id in 
        <!--Will produce results( stuid1,stuid2,stuid3....) -->
        <foreach collection="list" open="(" close=")"  
    item="stuid" separator=","> 
            #{stuid} 
        </foreach> 
    </if> 
</select>

Similar to foreach in java

StringBuilder builder = new StringBuilder();
builder.append("(");//open
for (Interger item : list) {
	builder.append(item).append(",");//separator
}
builder.deleteCharAt(builder.length()-1);
builder.append(")");//close

• Traversing the collection is a simple type, which can be obtained directly; If it is a reference type (city), traverse to get the id attribute under the object and use #{city.id}

Handling reused sql statements

The < SQL / > tag is used to define SQL fragments so that other SQL tags can be reused. For other tags that use this SQL fragment, you need to use the < include / > sub tag. The < SQL / > tag can define any part of the SQL statement, so the sub tag can be placed anywhere in the dynamic SQL

<sql id="studentSql"> 
    select id,name,email,age from student 
</sql>
<select id="selectStudentSqlFragment" 
resultType="com.lanan.domain.Student"> 
    <!-- quote sql fragment --> 
    <include refid="studentSql"/> 
    <if test="list !=null and list.size > 0 "> 
        where id in 
        <foreach collection="list" open="(" close=")"  
   item="stuobject" separator=","> 
            #{stuobject.id} 
        </foreach> 
    </if> 
</select>

Other common tag usage for MyBatis dynamic SQL

choose (when, otherwise) label

Similar to while... Case in java, choose is while, when is case, and otherwise is default

<select id="getUserList_choose" resultMap="resultMap_user" parameterType="com.yiibai.pojo.User">  
    SELECT *  FROM User u   
    <where>  
        <choose>  
            <when test="username !=null ">  
                u.username LIKE CONCAT(CONCAT('%', #{username}),'%')  
            </when >  
            <when test="sex != null and sex != '' ">  
                AND u.sex = #{sex}  
            </when >  
            <when test="birthday != null "> 
            </when>  
            <otherwise>  
            <!--when In label test All for false
            implement otherwise Medium sql -->
            </otherwise>  
        </choose>  
    </where>    
</select> 

if + trim instead of where/set tag

trim is a more flexible tag to remove redundant keywords. It can practice the effects of where and set

<select id="getStudentList_if_trim" resultMap="resultMap_studentEntity">  
    SELECT studentName 
      FROM student  
    <trim prefix="WHERE" prefixOverrides="AND|OR">  
        <if test="studentName !=null ">  
           studentName  = #{studentName}  
        </if>  
    </trim>     
</select> 

<update id="updateStudent_if_trim" parameterType="liming.student.manager.data.model.StudentEntity">  
    UPDATE student
    <trim prefix="SET" suffixOverrides=",">  
        <if test="studentName != null and studentName != '' ">  
            studentName = #{studentName},  
        </if>  
        <if test="studentSex != null and studentSex != '' ">  
            studentSex = #{studentSex},  
        </if>  
    </trim>  
    WHERE studentId = #{studentId}  
</update>