A series of questions raised by Mybatis – dynamic SQL

Dynamic SQL is one of the powerful features of MyBatis. If you have used JDBC or other similar frameworks, you should understand how painful it is to concatenate SQL statements according to different conditions. For example, when concatenating, make sure not to forget to add necessary spaces, and also pay attention to remove the comma in the last column name of the list. With dynamic SQL, you can get rid of this pain completely.

if
choose (when, otherwise)
trim (where, set)
foreach

1 if

The most common scenario for using dynamic SQL is to include part of a where clause based on conditions. for example:

<select id="findActiveBlogWithTitleLike" resultType="Blog">
  SELECT * FROM BLOG
  WHERE state = 'ACTIVE'
  <if test="title != null">
    AND title like #{<!-- -->title}
  </if>
</select>

This statement provides optional find text functionality. If no “title” is passed in, then all BLOGs in the “ACTIVE” state will be returned; if the “title” parameter is passed in, then a fuzzy search will be performed on the “title” column and the corresponding BLOG results will be returned (careful readers You may find that the parameter value for “title” needs to contain lookup mask or wildcard characters).

What if you want optional searches via the “title” and “author” parameters? First, I want to change the name of the statement to something more realistic; next, I just need to add another condition.

<select id="findActiveBlogLike" resultType="Blog">
  SELECT * FROM BLOG WHERE state = 'ACTIVE'
  <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>

2 choose, when, otherwise

Sometimes, we don’t want to use all the conditions, but just want to choose one from multiple conditions. For this situation, MyBatis provides the choose element, It is a bit like the switch statement in Java.

The above example is still the same, but the strategy becomes: search by “title” if “title” is passed in, and search by “author” if “author” is passed in. If neither is passed in, return the BLOG marked as featured (this may be because the administrator thinks that instead of returning a large number of meaningless random Blogs, it is better to return some blogs selected by the administrator).

<select id="findActiveBlogLike" resultType="Blog">
  SELECT * FROM BLOG WHERE state = 'ACTIVE'
  <choose>
    <when test="title != null">
      AND title like #{<!-- -->title}
    </when>
    <when test="author != null and author.name != null">
      AND author_name like #{<!-- -->author.name}
    </when>
    <otherwise>
      AND featured = 1
    </otherwise>
  </choose>
</select>

3 trim, where, set

The previous few examples have conveniently solved a notorious dynamic SQL problem. Now go back to the previous “if” example, this time let’s make “state = ‘ACTIVE'” a dynamic condition and see what happens.

<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>

What happens if there are no matching conditions? In the end, this SQL will become like this:

SELECT * FROM BLOG
WHERE

This causes the query to fail. What if only the second condition is matched? The SQL would be like this:

SELECT * FROM BLOG
WHERE
AND title like 'someTitle'

This query also fails. This problem cannot be solved simply with conditional elements. The problem is so intractable that no one who has solved it will want to run into it again.

MyBatis has a simple and suitable solution for most scenarios. While in other scenarios, it can be customized to suit the needs. And this requires only one simple change:

<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>

The where element will only insert a “WHERE” clause if the child element returns nothing. Also, if the clause starts with “AND” or “OR”, the where element will strip them out as well.

If the where element is not what you expect, you can also customize the function of the where element by customizing the trim element. For example, the custom trim element equivalent to the where element is:

 <update id="updateBatch" parameterType="java.util.List">
    <[email protected]>
    update bs_factory_calendar
    <trim prefix="set" suffixOverrides=",">
      <trim prefix="SET_TIME = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          when FACTORY_CALENDAR_ID = #{<!-- -->item.factoryCalendarId,jdbcType=VARCHAR} then #{<!-- -->item.setTime,jdbcType=VARCHAR}
        </foreach>
      </trim>
      <trim prefix="WEEK = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          when FACTORY_CALENDAR_ID = #{<!-- -->item.factoryCalendarId,jdbcType=VARCHAR} then #{<!-- -->item.week,jdbcType=VARCHAR}
        </foreach>
      </trim>
    </trim>
    where FACTORY_CALENDAR_ID in
    <foreach close=")" collection="list" item="item" open="(" separator=", ">
      #{<!-- -->item.factoryCalendarId,jdbcType=VARCHAR}
    </foreach>
  </update>

 <insert id="insertOrUpdateSelective" parameterType="com.inspur.spring.pojo.BsFactoryCalendar">
    <[email protected]>
    insert into bs_factory_calendar
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="factoryCalendarId != null">
        FACTORY_CALENDAR_ID,
      </if>
      <if test="setTime != null">
        SET_TIME,
      </if>
    </trim>
    values
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="factoryCalendarId != null">
        #{<!-- -->factoryCalendarId,jdbcType=VARCHAR},
      </if>
      <if test="setTime != null">
        #{<!-- -->setTime,jdbcType=VARCHAR},
      </if>
    </trim>
    on duplicate key update
    <trim suffixOverrides=",">
      <if test="factoryCalendarId != null">
        FACTORY_CALENDAR_ID = #{<!-- -->factoryCalendarId,jdbcType=VARCHAR},
      </if>
      <if test="setTime != null">
        SET_TIME = #{<!-- -->setTime,jdbcType=VARCHAR},
      </if>
    </trim>
  </insert>

 <update id="updateByPrimaryKeySelective" parameterType="com.inspur.spring.pojo.BsFactoryCalendar">
    <[email protected]>
    update bs_factory_calendar
    <set>
      <if test="updateTime != null">
        UPDATE_TIME = #{<!-- -->updateTime,jdbcType=TIMESTAMP},
      </if>
      <if test="comId != null">
        COM_ID = #{<!-- -->comId,jdbcType=VARCHAR},
      </if>
    </set>
    where FACTORY_CALENDAR_ID = #{<!-- -->factoryCalendarId,jdbcType=VARCHAR}
  </update>

4 foreach

<select id="selectPostIn" resultType="domain.blog.Post">
  SELECT *
  FROM POST P
  <where>
    <foreach item="item" index="index" collection="list"
        open="ID in (" separator="," close=")" nullable="true">
          #{<!-- -->item}
    </foreach>
  </where>
</select>