Dynamic SQL

If: determine whether to concatenate internal SQL according to the condition WHERE: Determine where to concatenate according to whether there is SQL behind where

<select id="findUser" resultType="com.shang.pojo.User">
   select * from user 
   <where>
     <if test="id! =null and id! = "">
	    id = #{id}
	 </if>
     <if test="name! =null and name! = "">
	    and name = #{name}
	 </if>
   </where>
</select>
Copy the code

Choose: Similar to switch in Java. Enter only one condition that satisfies when; if all when conditions are not met, otherwise is entered

<select id="findUser" resultType="com.shang.pojo.User">
   select * from user
   <choose>
      <when test="id! =null and id! ='' and name! =null and name! = "">
	      id = #{id} and name = #{name}
	  </when>
	  <when test="id! =null and id! = "">
	      id = #{id}
	  </when>
	  <otherwise>
	      name = #{name}
	  </otherwise>
   </choose>
</select>
Copy the code

Set is similar to WHERE, after which the set is concatenated if a condition exists. Applicable to the update

<update id="updateUser">
   update user 
   <set>
	   <if test="name! =null and name! = "">
		  name = #{name},
	   </if>
	   <if test="age! =null and age! = "">
		  age = #{age}
	   </if>
   </set>
   where id = #{id}
</update>
Copy the code

Foreach: You can traverse List, Map, and Array

  • Collection: Specifies the elements to be traversed
  • Item: Each item after the traversal
  • Separator: Defines the separator for the statement inside the foreach
  • Index: indicates the key in map and the index in array
<select id="findAll" resultType="com.shang.pojo.User">
    select * from user where id in
    <foreach collection="ids" item="id" open="(" close=")" separator=",">
       #{id}
    </foreach>
</select>

<insert id="batchInsertUser">
    insert into user(id,name,age)
    values
    <foreach collection="users" item="user" separator=",">
       (#{user.id},#{user.name},#{user.age})
    </foreach>
</insert>

<update id="updateUser">
    update user
	<set>
		<foreach collection="map" item="val" index="key" separator=",">
       		${key}=#{val}
    	</foreach>
	</set>
	where id = #{id}
</update>
Copy the code

Bind: used to define variables

<select id="findUserById" resultType="com.shang.pojo.User">
    <bind name="userName" value="name+'%'"/>
    select * from user where name = #{userName}
</select>
Copy the code