Mybatis — Trim and Choose

1. Trim label

The trim tag of Mybatis is used to remove unnecessary AND keywords, commas, or add suffixes such as “WHERE”, “set”, and “values” before SQL statements. It can be used for selective insert, update, delete, or conditional query operations.

Here are the attributes involved in the trim tag:

1. Use the trim label

Trim has four attributes prefix, with suffix indicating that the suffix is added before or after the trim tag (note: If there is no prefixOverrides, suffixOverrides, suffixOverrides Overrides the content in the Overrides. If only prefixOverrides, suffixOverrides indicates deletion. Such as:

<update id="testTrim" parameterType="com.mybatis.pojo.User">
    update user
    <trim prefix="set" suffixOverrides=",">
        <if test="cash! =null and cash! = "">cash= #{cash},</if>
        <if test="address! =null and address! = "">address= #{address},</if>
    </trim>
    <where>id = #{id}</where>
</update>
Copy the code

Only prefix= “set”, which indicates that the set is added before the trim wrap section. SuffixOverrides = “, “, which deletes the last comma.

The above example can also be written as

<update id="testTrim" parameterType="com.mybatis.pojo.User">
    update user
    set
    <trim suffixOverrides="," suffix="where id = #{id}">
        <if test="cash! =null and cash! = "">cash= #{cash},</if>
        <if test="address! =null and address! = "">address= #{address},</if>
    </trim>
</update>
Copy the code

Since set is written outside, the prefix attribute is no longer needed in trim, so removed. The where tag is removed from the outside into trim, which is thought to replace the last comma with where id = #{id}. So suffix and suffixOverrides are used together.

2. Use the trim tag to remove redundant and keywords

Here’s an example:

<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG 
  WHERE 
  <if test="state ! = null">
    state = #{state}
  </if> 
  <if test="title ! = null">
    AND title like #{title}
  </if>
  <if test="author ! = null and author.name ! = null">
    AND author_name like #{author.name}
  </if>
</select>
Copy the code

What happens if none of these conditions match? The resulting SQL will look like this:

SELECT * FROM BLOG
WHERE
Copy the code

This will cause the query to fail. What if only the second condition matches? The SQL will end up like this:

SELECT * FROM BLOG
WHERE 
AND title like'someTitle'Copy the code

You can solve this problem by using the WHERE tag. The WHERE element inserts the “WHERE” clause only if the SQL clause is returned for the condition of at least one child element. Also, if a statement begins with “AND” OR “OR,” the WHERE element removes those as well.

<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG 
  <where> 
    <if test="state ! = null">
         state = #{state}
    </if> 
    <if test="title ! = null">
        AND title like #{title}
    </if>
    <if test="author ! = null and author.name ! = null">
        AND author_name like #{author.name}
    </if>
  </where>
</select>
Copy the code

The trim tag also performs the same function as follows:

<trim prefix="WHERE" prefixOverrides="AND">
	<if test="state ! = null">
	  state = #{state}
	</if> 
	<if test="title ! = null">
	  AND title like #{title}
	</if>
	<if test="author ! = null and author.name ! = null">
	  AND author_name like #{author.name}
	</if>
</trim>
Copy the code

3. Use the trim tag to remove extra commas

The most important property is

suffixOverrides=","
Copy the code

Choose (when, otherwise) tag

Sometimes we don’t want to apply all of the criteria, but we just want to choose one of several options. The “Choose” tag determines whether the test condition in the “When” tag is true, and if one of them is true, the “choose” tag ends. When all when conditions in Choose are not satisfied, SQL in Otherwise is executed. Similar to the Java switch statement, choose is switch, when is case, and otherwise is default.

For example, in the following example, all the conditions that can be restricted are also written in. Choose selects an SQL execution from the top down where test of the WHEN tag is true.

<!Select the first non-null attribute of the entity class User as: where condition -->
<select id="selectControlTableColumnByTableColumnIdAndIsUpdateOrIsDelete"  parameterType="com.uama.mdm.model.mdata.MdControlTableColumn" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_list"></include>
        FROM md_control_table_column u
        <where>
            <choose>
                <when test="isUpdate ! =null ">
                    AND u.is_update = #{isUpdate, jdbcType=INTEGER}
                </when>
                <when test="isDelete ! = null">
                    AND u.is_delete = #{isDelete, jdbcType=INTEGER}
                </when>
                <otherwise>
                </otherwise>
            </choose>
            <if test="tableColumnId ! = null">
               AND table_column_id = #{tableColumnId}
            </if>
        </where>
    </select>
Copy the code

< SQL ID = “base” ID,name,age> to call the field,test to determine