MyBatis Basics: MyBatis Dynamic SQL (3)

1. Overview

Dynamic SQL includes elements in MyBatis:

Elements	function	Notes
If	Judgment statement	Single conditional branch judgment
Choose (when, otherwise)	Equivalent to a case-when statement in Java	Multi-Conditional branch judgment
Trim (where, set)	Auxiliary elements	For dealing with SQL stitching issues
Foreach	Looping statements	For enumeration conditions such as in statements

2. If element

The IF element is the most commonly used judgment statement and is often used in conjunction with the Test property.

2.1 If

<ResultmapID= "Baseresultmap"type= "Com.libing.helloworld.model.Role">    <ID Property= "id"column= "id" />    <result Property= "RoleName"column= "Role_name" /></Resultmap>

<SelectID= "Findbysearchtext"Resultmap= "Baseresultmap">SELECT ID, role_name from role WHERE 1 = 1<ifTest= "SearchText ! = null and SearchText! =" ">and role_name like CONCAT ('% ', #{searchtext,jdbctype=varchar}, '% ')</if>ORDER by ID ASC</Select>

2.2 If + where

<SelectID= "Findbysearchtext"Resultmap= "Baseresultmap">SELECT ID, role_name from role<where>        <ifTest= "ID > 0">ID >= #{id}</if>        <ifTest= "SearchText ! = null and SearchText! =" ">and Role_name like CONCAT (CONCAT ('% ', #{searchtext,jdbctype=varchar}), '% ')</if>    </where>ORDER by ID ASC</Select>

The WHERE tag in the MyBatis will determine if there is a return value in the included tag, insert a ' where '. In addition, if the label returns content that begins with and or or, the beginning of and or or is automatically deleted.

2.3 If + set

<UpdateID= "Update"ParameterType= "Com.libing.helloworld.model.Role">UPDATE Role<Set>        <ifTest= "RoleName ! = null and RoleName! =" ">role_name = #{rolename},</if>        <ifTest= "Remark ! = null and remark! =" ">remark like CONCAT ('% ', #{remark, jdbctype=varchar}, '% ')</if>    </Set>WHERE id = #{id}</Update>

The above form, when ramark=null, the dynamic SQL statement will be due to a "," and error.

3. Choose (when,otherwise) Element

<SelectID= "Findbycondition"Resultmap= "Baseresultmap">SELECT ID, role_name from role<where>        <Choose>            < whenTest= "ID > 0">ID >= #{id}</ when>            <otherwise>and role_name like CONCAT ('% ', #{rolename, jdbctype=varchar}, '% ')</otherwise>        </Choose>    </where>ORDER by ID ASC</Select>

4.trim Element 4.1 Trim:if + where

<SelectID= "Findbycondition"Resultmap= "Baseresultmap">SELECT ID, role_name from role<Trimprefix= "where"Prefixoverrides= "and | OR ">        <ifTest= "ID > 0">ID >= #{id}</if>        <ifTest= "RoleName ! = null and RoleName! =" ">and role_name = like CONCAT ('% ', #{rolename, jdbctype=varchar}, '% ')</if>    </Trim>ORDER by ID ASC</Select>

4.2 Trim:if + Set

<UpdateID= "Update"ParameterType= "Com.libing.helloworld.model.Role">UPDATE Role<Trimprefix= "Set"Suffixoverrides=",">        <ifTest= "RoleName ! = null and RoleName! =" ">role_name = #{rolename},</if>        <ifTest= "Remark ! = null and remark! =" ">remark like CONCAT ('% ', #{remark, jdbctype=varchar}, '% ')</if>    </Trim>WHERE id = #{id}</Update>

5. foreach element

MyBatis Basics: MyBatis Dynamic SQL (3)