When building dynamic conditional queries in MyBatis, manual WHERE clause construction can introduce syntax errors from leading AND/OR operators or a dagnling WHERE keyword when no filters apply. For example, this flawed query snippet demonstrates both issues:

<select id="fetchBlogEntry" resultType="BlogEntity">
  SELECT blog_id, title, content, publish_status FROM blog_catalog
  WHERE
  <if test="publishStatus != null">
    publish_status = #{publishStatus}
  </if>
  <if test="searchKeyword != null">
    AND title LIKE CONCAT('%', #{searchKeyword}, '%')
  </if>
</select>

If publishStatus is null, the generated SQL becomes SELECT ... WHERE AND title LIKE ...; if both conditions fail, it leaves SELECT ... WHERE. Both variants trigger database syntax exceptions.

Using MyBatis' <where> tag resolves this automatically. The tag inserts a WHERE clause only if at least one nested condition yields a valid SQL fragment. It also trims any leading AND or OR from the resulting filter block:

<select id="fetchBlogEntry" resultType="BlogEntity">
  SELECT blog_id, title, content, publish_status FROM blog_catalog
  <where>
    <if test="publishStatus != null">
      publish_status = #{publishStatus}
    </if>
    <if test="searchKeyword != null">
      AND title LIKE CONCAT('%', #{searchKeyword}, '%')
    </if>
  </where>
</select>

For more granular control over prefixes, suffixes, and trimming behavior, use the flexible <trim> tag, which can replicate <where> or <set> functionality and extend it. For a WHERE-like trim configuration:

<select id="loadUserProfiles" resultType="UserProfile">
  SELECT user_id, full_name, user_role FROM platform_users
  <trim prefix="WHERE" prefixOverrides="AND |OR ">
    <if test="fullName != null and fullName.trim().length() > 0">
      AND full_name = #{fullName}
    </if>
    <if test="userRole != null and userRole.trim().length() > 0">
      AND user_role = #{userRole}
    </if>
  </trim>
</select>

Here, prefix="WHERE" adds the WHERE keyword only when nested conditions exist, and prefixOverrides="AND |OR " removes any leading AND/OR followed by a space from the filtered content.

The <trim> tag also works for UPDATE statements to handle trailing commas in SET clauses, plus add custom suffixes:

<update id="modifyUserProfile">
  UPDATE platform_users
  <trim prefix="SET" suffixOverrides="," suffix=" WHERE user_id = #{userId}">
    <if test="fullName != null and fullName.trim().length() > 0">
      full_name = #{fullName},
    </if>
    <if test="userRole != null and userRole.trim().length() > 0">
      user_role = #{userRole},
    </if>
  </trim>
</update>

In this UPDATE case, suffixOverrides="," strips the final comma from the SET assignmenst, while suffix=" WHERE user_id = #{userId}" appends the mandatory WHERE clause for the update operation.