Dynamic SQL is one of MyBatis’s most powerful features. While basic MyBatis usage simplifies JDBC boilerplate, dynamic SQL empowers developers to construct queries that adapt intelligently to varying runtime conditions—making data access logic both flexible and maintainable.

This guide explores the core constructs of MyBatis dynamic SQL through practical examples, from conditional clauses to collection iteration, enabling you to handle complex query requirements without code duplication.

Handling Conditional Query Logic

Consider a product search interface where users may filter by name, price range, category, or any combination thereof—and optionally sort results. Writing a separate query for every possible input permutation leads to unmaintainable code.

MyBatis solves this with XML-based conditional tags that dynamically assemble SQL at runtime.

Project Setup

We define a Product entity and a corresponding mapper:

// Product.java
@Data
public class Product {
    private Integer id;
    private String name;
    private Integer categoryId;
    private BigDecimal price;
}

// ProductMapper.java
public interface ProductMapper {
    List<Product> searchProducts(
        @Param("name") String name,
        @Param("minPrice") BigDecimal minPrice,
        @Param("maxPrice") BigDecimal maxPrice,
        @Param("orderBy") String orderBy
    );

    int updateProduct(Product product);
    List<Product> getProductsByIds(List<Integer> ids);
}

The <if> Tag

The <if> tag includes SQL fragments only when its test condition evaluates to true:

<select id="searchProducts" resultType="Product">
  SELECT * FROM product
  WHERE 1=1
  <if test="name != null and name != ''">
    AND name LIKE CONCAT('%', #{name}, '%')
  </if>
  <if test="minPrice != null">
    AND price >= #{minPrice}
  </if>
  <if test="maxPrice != null">
    AND price &lt;= #{maxPrice}
  </if>
</select>

However, using WHERE 1=1 is a workaround. A better approach uses the <where> tag.

The <where> Tag

This tag automatically inserts WHERE and strips leading AND/OR:

<select id="searchProducts" resultType="Product">
  SELECT * FROM product
  <where>
    <if test="name != null and name != ''">
      AND name LIKE CONCAT('%', #{name}, '%')
    </if>
    <if test="minPrice != null">
      AND price >= #{minPrice}
    </if>
    <if test="maxPrice != null">
      AND price &lt;= #{maxPrice}
    </if>
  </where>
</select>

Multi-Branch Logic with <choose>

For exclusive conditions (like sorting), use <choose>, analogous to switch:

<choose>
  <when test="orderBy != null and orderBy != ''">
    ORDER BY ${orderBy}
  </when>
  <when test="name != null and name != ''">
    ORDER BY name ASC
  </when>
  <otherwise>
    ORDER BY id DESC
  </otherwise>
</choose>

Security Note: Use ${} for column names only when values are trusted (e.g., from an allowlist). Never use it for user input to avoid SQL injection.

Advanced Dynamic Constructs

The <set> Tag for Updates

Automatically manages commas in UPDATE statements:

<update id="updateProduct">
  UPDATE product
  <set>
    <if test="name != null and name != ''">
      name = #{name},
    </if>
    <if test="categoryId != null">
      category_id = #{categoryId},
    </if>
    <if test="price != null">
      price = #{price},
    </if>
  </set>
  WHERE id = #{id}
</update>

The <foreach> Tag for Collections

Iterates over lists for IN clauses or batch operations:

<select id="getProductsByIds" resultType="Product">
  SELECT * FROM product
  WHERE id IN
  <foreach item="id" collection="list" open="(" separator="," close=")">
    #{id}
  </foreach>
</select>

Key attributes:

• collection: The parameter name (defaults to list for List)
• item: Variable name for each element
• open/close: Wrapping delimiters
• separator: Between elements

Reusing Fragments with <sql> and <include>

Extract commmon column lists or conditions:

<sql id="columns">
  id, name, category_id, price
</sql>

<select id="searchProducts" resultType="Product">
  SELECT <include refid="columns"/>
  FROM product
  <where>...</where>
</select>

This reduces redundancy and centralizes schema changes.

Testing Dynamic Behavior

A test class validates different scenarios:

List<Product> results = mapper.searchProducts("Laptop", null, new BigDecimal("2000"), null);
// Executes: SELECT ... WHERE name LIKE '%Laptop%' AND price <= 2000

mapper.updateProduct(new Product() {{ setId(5); setPrice(new BigDecimal("1499")); }});
// Generates: UPDATE product SET price = 1499 WHERE id = 5

List<Product> batch = mapper.getProductsByIds(Arrays.asList(1, 2, 3));
// Yields: SELECT ... WHERE id IN (1,2,3)

Each invocation produces syntactically correct SQL tailored to the provided parameters.