MyBatis Dynamic SQL

MyBatis tags used by dynamic sql:

1:if judgment

2:where solving the problem of SQL statement stitching condition

Cases:

<select id= "Finduserbyid" resulttype= "User" >

SELECT * from user where

<where>

<IF test= "id! = NULL" >

Id=#{id}

</if>

and deleteflag=0;

</where>

</select>

If id = NULL then the SQL statement will be: select * from user where and and deleteflag=0, it is obvious that this is the wrong SQL statement. Add where note can be resolved, such as top <where></where> Remove above red section where

3:trim processing Splicing

2 problems can also be handled as such

<select id= "Finduserbyid" resulttype= "User" >

<trim prefix= "where" prefixoverrodes= "and | or" >

? SELECT * from user where ?

<IF test= "id! = NULL" >

Id=#{id}

</if>

and deleteflag=0;

</trim>

</select>

4:set

<update id= "UpdateUser" parametertype= "Com.domin.User" >

Update User Set

<set>

<if test= "Name! = NULL" >

name = #{name},

</if>

</set>

and Deleteflag = 0;

</where>

</update>

If name = NULL, the SQL statement is Update user set and Deleteflag = 0, which is an obvious error. Use <set></set> replace <set>

It can also be trim processing

5:foreach looping through a collection or array

<select id= "Selectuser" resulttype= "Domain.domin.User" >

SELECT *

From user

where ID in

<foreach item= "Item" index= "index" collection= "list"

Open= "(" separator= "," close= ")" >

#{item}

</foreach>

</select>

The corresponding SQL is as follows:

SELECT * from user where ID in (item1,item2 ...)

Item1,item2 ... Represents the value of #{item} each time

6:choose equivalent to Swich in Java

The format is as follows:

<choose>

<when>

Statement

</when>

<otherwise>

Statement

</otherwise>

</choose>

Problems encountered:

<! [Cdata[and shelve_at <= #{currenttimemillis,jdbctype=bigint} and #{currenttimemillis,jdbctype=bigint} < unshelve_at]]>//can't be written <!. [Cdata[and shelve_at <= #{currenttimemillis,jdbctype=bigint} < unshelve_at]]>

MyBatis Dynamic SQL