Using MyBatis Dynamic SQL Tags: foreach, if, choose, trim, selectKey and More

The author, a senior architect, introduces MyBatis dynamic SQL tags that enable flexible query construction. Key tags covered are foreach , if , choose , trim , and selectKey , each with their attributes such as item, index, collection, open, separator, and close.

foreach iterates over collections for IN clauses. The collection attribute must be specified and can be a list, array, or a custom Map. Example:

public List<Entity> queryById(List<String> userids);
<select id="queryById" resultMap="BaseResultMap">
  SELECT * FROM entity
  WHERE id IN
  <foreach collection="userids" item="userid" open="(" separator="," close=")">
    #{userid}
  </foreach>
</select>

concat fuzzy query demonstrates conditional SQL building with if to include a LIKE clause only when a parameter is not null.

<select id="queryById" resultMap="BascResultMap" parameterType="entity">
  SELECT * FROM entity
  <where>
    <if test="name!=null">
      name LIKE concat('%', #{name}, '%')
    </if>
  </where>
</select>

choose works like a switch statement, selecting the first when whose test evaluates to true, otherwise executing otherwise. Example:

<select id="getUserList_choose" resultMap="resultMap_user" parameterType="com.yiibai.pojo.User">
  SELECT * FROM User u
  <where>
    <choose>
      <when test="username !=null">
        u.username LIKE CONCAT('%', #{username}, '%')
      </when>
      <when test="sex != null and sex != ''">
        AND u.sex = #{sex}
      </when>
      <otherwise/>
    </choose>
  </where>
</select>

selectKey generates auto‑increment keys for inserts, useful for Oracle sequences or MySQL functions.

<insert id="createStudentAutoKey" parameterType="StudentEntity" keyProperty="studentId">
  <selectKey keyProperty="studentId" resultType="String" order="BEFORE">
    select nextval('student')
  </selectKey>
  INSERT INTO STUDENT_TBL (STUDENT_ID, STUDENT_NAME, ... ) VALUES (#{studentId}, #{studentName}, ...)
</insert>

The article also covers if combined with where to avoid dangling AND/OR, if + set for update statements, and using trim to replace where or set tags, automatically removing redundant keywords.

<trim prefix="WHERE" prefixOverrides="AND|OR">
  <if test="studentName !=null">
    STUDENT_NAME LIKE CONCAT('%', #{studentName}, '%')
  </if>
  ...
</trim>

Further examples illustrate foreach with array and list parameters for IN clauses, and the use of reusable <sql> fragments with <include>.

<!-- sql fragment -->
<sql id="orderAndItem">
  o.order_id, o.cid, o.address, i.product_id, i.count
</sql>
<select id="findOrderAndItemsByOid" parameterType="String" resultMap="BaseResultMap">
  SELECT <include refid="orderAndItem"/>
  FROM ordertable o JOIN orderitem i ON o.orderitem_id = i.orderitem_id
  WHERE o.order_id = #{orderId}
</select>