Remember the dynamic sql commonly used by MyBatis once

1. Dynamic Stitching Label of SQL

if tag
where tag
choose when otherwise tag
set tag
trim tag
bind tag
sql and include tags foreach Tags

2. if tag:

Write the judgement parameter in test directly paramN or alias feature: as long as it is true, stitch it together in Sql statement, all stitch together Note: where clause with 1=1 to avoid the risk of and

<select id="uid" resultType="UserEntity">
select * from UserEntity where 1=1
<if test="param1!=null and param1!=''">
and outno=#{param1}
</if>
<if test="param2!=null and param2!=''">
and inno=#{param2}
</if>
</select>

Conditional Fuzzy Lookup and Query Period Data

<select id="uid" resultType="UserEntity">
	select * from UserEntity where 1=1
	<if test="param1!=null and param1!=''">
		and outno=#{param1}
	</if>
	<if test="param2!=null and param2!=''">
		and inno LIKE CONCAT('%',#{param2 },'%' )
	</if>
	<if test="effectiveTime !=null and effectiveTime !=''">   
		and begin_time <= #{effectiveTime}    
		//EffectiveTime is the query field of the envelope's query condition class and end_time >= #{effectiveTime}     
		//begin_time, end_time corresponds to the database field.
	</if>
</select>

3. where label:

Feature: The where keyword is automatically added to the Sql statement and the first and is removed.

<select id="uid" resultType="UserEntity">
    select * from UserEntity
    <where>
    	<if test="param1!=null and param1!=''">
   			and outno=#{param1}
    	</if>
    	<if test="param2!=null and param2!=''">
    		and inno=#{param2}
    	</if>
    </where>
</select>

4. choose when otherwise tag

Features: As long as one condition is valid, the others will not be judged. If no condition is valid, the contents in otherwise will be executed by default.

<select id="uid" resultType="UserEntity">
	select * from UserEntity
	<where>
		<choose>
			<when test="param1!=null and param1!=''">
				and outno=#{param1}
			</when>
			<when test="param2!=null and param2!=''">
				and inno=#{param2}
			</when>
			<otherwise>
				and 1=1
			</otherwise>
		</choose>
	</where>
</select>

5. set tag:

Produces a set keyword that automatically removes the last comma.
Be careful:
There is always a condition in the final judgment. Avoid sql errors.

<update id="uid">
	update accountTable 
	<set>
		<if test="aname!=null and aname!=''">
			aname=#{aname},
		</if>
		<if test="money !=null  and money !=''">
			money=#{money},
		</if>
		<if test="ano !=null  and ano !=''">
			ano=#{ano},
		</if>
	</set>
	where  ano=#{ano}
</update>

6. trim tags:

prefix: Add the specified content before the trim content
prefixOverrides removes the specified content before the trim content
suffix: Add the specified content after the trim content
suffixOverrides: Remove specified contents after trim contents
Note:
Remove then add
Adding content adds a space by default.

<update id="upT" parameterType="account">    
	update account     
	<trim prefix="$" prefixOverrides="" suffix="" suffixOverrides="">    
		<if test="ano !=null  and ano !=''">    
			ano=#{ano},    
		</if>    
		<if test="aname!=null and aname!=''">    
			aname=#{aname},    
		</if>    
		<if test="money !=null  and money !=''">   
			money=#{money},    
		</if>     
	</trim>    
	where ano=#{ano} 
</update>

7. Bid tags:

Name:parameter name
value: expression, note that string splicing is done as a variable.
For example:

<bind name="money" value="'$'+money"/>

Re-assign parameters

<update id="upB" parameterType="account">
    <bind name="money" value="money+100"/>
    update account 
    <trim prefix="set" suffixOverrides=",">
    	<if test="ano !=null  and ano !=''">
    		ano=#{ano},
    	</if>
    	<if test="aname!=null and aname!=''">
    		aname=#{aname},
    	</if>
    	<if test="money !=null  and money !=''">
    		money=#{money},
    	</if>
    </trim>
    where ano=#{ano}
</update>

8. sql and include tags:

SQL tag: declare public SQL statements externally
id ​
include tag: introduces declared public SQL statements
refid: ​
Advantages: Easy overall modification of SQL
Disadvantages: Hard to read

<select id="selA" resultType="account">
    select <include refid="mysql"></include> from account
</select>
<sql id="mysql">
	ano,aname,apwd,money
</sql>

9. foreach tags:

The set needs to be traversed in order to construct the IN conditional statement, so the foreach element can be used. The foreach removes the redundant ",".
If the collection is empty, the operation in the foreach element will not be performed, but the "in" keyword will be added and an error will be reported.
item: elements in a collection
Index: The subscript of the set in which the element resides, index is the key when iterating over a map
Collection: the type of collection, optional value list,array, in addition to @Param("name"), key in Map, member variable open of class,
close: a character stitched at the beginning and end
Separator: separator between each element
Note: foreach tags support traversal of List, Set, Map, Array, etc.
When iterating over an array or a Set of List s or Sets, index is the number of iterations, and item is the element obtained in this iteration.
When iterating over a Map (or a collection of Map.Entry objects), index is the key and item is the value
Introduction to collection properties
collection="list" when a single parameter is passed in and it is a List
collection="array" when a single parameter is passed in and it is Array
When passing in a single parameter and is a Set, use the @Param annotation, as follows
When a single parameter is passed in and @Param("name") is used, collection="name", that is, the value property value of the @Param annotation is the same, and list, array is invalid at this time
When multiple parameters are passed in and encapsulated into a Map, such as map.put("ids", Arrays.asList (1,2)), collection="ids"
When multiple parameters are passed in and encapsulated into a class, such as a member variable List roleIds in the User class, collection="roleIds";
If there is a member variable Role in the User class and a member variable prilIds in the Role class, collection="role.prilIds"

<select id="selF" parameterType="list" resultType="account">
    select * from account where ano in
    <foreach collection="list" item="item" open="(" separator="," close=")">
    #{item}
    </foreach>
 </select>
   <insert id="inF">
    insert into log values 
    <foreach collection="list"  item="log" separator=",">
    (#{log.outno},#{log.inno},#{log.money})
    </foreach>  
   </insert>

Continue to increase in the future...

Tags: Database MySQL Mybatis PostgreSQL SQL

Posted on Thu, 09 Sep 2021 12:04:33 -0400 by TimTimTimma