explain

When we write some sql queries, it is very troublesome and error prone to splice sql according to conditions.
The dynamic sql tag of mybatis can solve this annoying problem.
The dynamic sql tags commonly used by mybatis are as follows:

1.if

The most common scenario of if tag is to determine whether to use a condition by judging the parameter value according to a part of the where clause contained in the condition.

<select id="findUser" resultType="User">
  SELECT * FROM user
  WHERE class = '160801'
  <if test="age != null">
    AND age > #{age}
  </if>
</select>

2.choose,when,otherwise

Sometimes, we don't want to use all the conditions, but just want to choose one from multiple conditions. In this case, MyBatis provides the choose element, which is a bit like the switch statement in Java. During the execution of choose, judge whether the conditions in when are true in sequence. If one of them is true, choose ends. When all the when conditions in choose are not satisfied, execute sql in otherwise.

<select id="getUser" resultMap="User">
    SELECT * from STUDENT WHERE 1=1
    <where>
        <choose>
		    <when test="name!= null">
		      AND name like #{name}
		    </when>
		    <when test="phone != null and address != null">
		      AND age > #{age}
		    </when>
		    <otherwise>
		      AND featured = 1
		    </otherwise>
  		</choose>
    </where>
</select>

3. set,where,trim

3.1 set

When using the if tag in the update statement, if the last if is not executed, it will cause a comma redundant error. Using the set tag, you can dynamically insert the set keyword at the beginning of the line and delete additional commas (which were introduced when assigning values to columns using conditional statements).

#If the last if is not executed, it will cause a comma redundant error.

<update id="updateUser">
  update User SET
      <if test="name != null">
      	name =#{name},
      </if>
      <if test="age != null">
      	age=#{age},
      </if>
      <if test="phone != null">
      	phone =#{phone},
      </if>
      <if test="class != null">
      	class=#{class}
      </if>
  where id=#{id}
</update>

#The set tag removes the comma at the end of the condition
<update id="updateUser">
  update User 
      <if test="name != null">
      	name=#{name},
      </if>
      <if test="age != null">
      	age=#{age},
      </if>
      <if test="phone != null">
      	phone=#{phone},
      </if>
      <if test="class != null">
      	adress=#{adress}
      </if>
  where class=#{class}
</update>

3.2 where

The where tag inserts the "where" clause only if the child element returns anything. Moreover, if clause starts with the "AND" OR ", where element will also remove them. So we don't have to add a 1 = 1 after where

<select id="findUser" resultType="User">
  SELECT * FROM user
	<where>
	    <if test="class != null">
	         AND class = #{class}
	    </if>
	    <if test="phone != null and adress != null">
	        AND age > #{age}
	    </if>
  </where>
</select>

3.3 trim

Both set and where are actually types of trim tags. Both functions can be implemented with trim tags. If you are not satisfied with the functions implemented by where and set, you can use trim to customize the implementation. Among them, trim has four main attributes, namely:

• Prefix: prefix sql statements
• Suffix: add suffix to sql statement
• prefixOverrides: remove the content of the specified prefix, such as prefixOverrides = "AND | OR", AND remove the redundant prefix "AND" OR "of the sql statement.
• suffixOverrides: remove the specified suffix content, such as suffixOverrides = ",", and remove the redundant commas in the sql statement.

Customize the set of implementation 3.1 and remove the extra comma of the suffix of the update statement

<update id="updateUser" resultType="User">
	update user set 
	<trim  suffixOverrides=",">
		<if test="class != null">
	         class = #{class},
	    </if>
	    <if test="phone != null and adress != null">
	        age > #{age},
	    </if>
	</trim> 
	where class=#{class}
</update>

Customize the where of implementation 3.2 AND remove the redundant AND prefix of the where clause

<select id="findUser" resultType="User">
	select * from user
	<trim prefix="WHERE" prefixOverrides="AND | OR">
		<if test="class != null">
	        AND class = #{class}
	    </if>
	    <if test="phone != null and adress != null">
	        AND age > #{age}
	    </if>
	</trim>
</select>

4.foreach

IN batch operations, one of the more frequently used scenarios is to traverse the set, especially when constructing IN conditional statements.

<select id="findUser" resultType="User">
  SELECT * FROM user
	where class in
    <foreach item="item" index="index" collection="classList" open="(" separator="," close=")">
        #{item}
    </foreach>
</select>

Properties in foreach:

• Collection: there are usually three optional values: list, array and map collection, of which list is the most commonly used
• Item: collection item, that is, the value in the collection
• Index: index variable during traversal, that is, subscript
• open: prefix
• close: suffix
• Separator: separator. What separator is used between each value

5.bind

The bind tag means to create a variable outside the expression and bind it to the current context.

<select id="queryUser" resultType="User">
  <bind name="pattern" value="'%' + name + '%'" />
  SELECT * FROM user
  WHERE name LIKE #{name}
</select>

Reference article: mybatis official website document