Dynamic SQL in Spring Boot + MyBatis: How to Write where, set, trim, choose, bind, and foreach, and How to Fix "Leading AND Left Behind", "Trailing Comma", and "Empty IN Clause Crashes"

2026-09-13

Written by: Zuko

About this article

This article deepens your understanding of Spring Boot. A walkthrough of Spring Boot + MyBatis dynamic SQL tags (where/set/trim/choose/bind/foreach), showing the XML you write side by side with the SQL actually issued. Covers a search Mapper that doesn't break even when every condition is empty, partial updates that never leave a trailing comma, and defensive patterns so foreach doesn't fail when given an empty list or null, organized as fixes by error type.

About the author View all Spring Boot articles

Here is the English translation of the article body.

If you toggle search conditions with <if>, you end up with a bare WHERE and a syntax error when every condition is empty. In a partial update, a trailing comma is left at the end of SET. When you pass a list of IDs to <foreach>, an empty list produces a syntax error. Once you start writing dynamic SQL in MyBatis, you are almost guaranteed to run into these three.

All of them can be solved with the guards provided by <where>, <set>, and <foreach>. In this article, for each tag we place “the XML you wrote” side by side with “the actual SQL that appeared in the log,” and confirm what gets stripped automatically and what gets added.

Note that this article covers the dynamic SQL tags used in XML and annotations. The mybatis-dynamic-sql library and MyBatis-Plus are out of scope. For adding the dependency and the basics of Mappers, see the MyBatis Mapper implementation guide.

Environment and Log Settings to See the Issued SQL

The verification environment is Spring Boot 3.x, mybatis-spring-boot-starter 3.x, and H2 as the database. The fastest way to write dynamic SQL is to look at “what was actually issued” as you go, so let’s set up logging first.

logging:
  level:
    com.example.shop.mapper: debug   # Mapperインターフェースのパッケージ

mybatis:
  mapper-locations: classpath:mapper/*.xml
  configuration:
    map-underscore-to-camel-case: true
    # log-impl: org.apache.ibatis.logging.stdout.StdOutImpl  # SLF4Jを通さず標準出力に出したい場合

Setting the Mapper package to debug prints the SQL under Preparing: and the bound values under Parameters:. Leaving this DEBUG level on in production makes log volume explode, so switch it per profile.

The sample uses a single table, products. Its columns are id, name, category, price, status, and updated_at, and every example below uses this table. Search conditions are collected in a plain class called ProductSearchCondition with six fields: name, category, minPrice, maxPrice, ids, and sortKey. The ProductUpdateRequest used for partial updates holds only id and the fields you want to update.

Understanding How if test Is Evaluated

Let’s start with the foundation, <if test>. The test attribute is evaluated as an OGNL expression, and null and empty string are different things. If you want to “add the condition only when there is input,” check both, as in name != null and name != ''. For a list, you can check for emptiness with a method call: ids != null and !ids.isEmpty().

The three most common pitfalls are these.

Writing only name != '' evaluates to true even for null, so a condition like name = null gets attached
Adding price != '' to a numeric type can cause 0 to be treated as an empty string and the condition to disappear. For numbers, only check for null
< and && cannot be written as-is inside XML. Use and / or, or escape as <, or wrap in CDATA

If you get There is no getter for property 'name', the name in the XML does not match the argument. For Mapper methods with multiple arguments, add @Param("cond") and reference it as cond.name.

where - Strips the Leading AND and Omits WHERE Entirely When There Are No Conditions

First, the broken version. If you hard-code WHERE and line up <if> tags, only WHERE remains when every condition is empty.

<!-- ビフォー: 全条件が空だと "... FROM products WHERE" で構文エラー -->
SELECT * FROM products
WHERE
<if test="name != null and name != ''">name = #{name}</if>
<if test="category != null and category != ''">AND category = #{category}</if>

You often see the workaround of putting WHERE 1=1, but MyBatis has <where>, so use that instead.

<select id="search" resultType="com.example.shop.entity.Product">
  SELECT * FROM products
  <where>
    <if test="name != null and name != ''">
      AND name = #{name}
    </if>
    <if test="category != null and category != ''">
      AND category = #{category}
    </if>
    <if test="minPrice != null">
      AND price &gt;= #{minPrice}
    </if>
    <if test="maxPrice != null">
      AND price &lt;= #{maxPrice}
    </if>
  </where>
</select>

<where> removes a leading AND or OR from its content, and if the content is empty, it does not output the WHERE clause at all. That is why you can freely write AND in the first <if> too. Here resultType points directly at the entity, but if you want to receive JOINed results as nested objects, see the resultMap article.

On the calling side, you just set cond.setCategory("coffee") and cond.setMinPrice(500) and call productMapper.search(cond). Looking at the log, here is how it differs between having conditions and having none.

==>  Preparing: SELECT * FROM products WHERE category = ? AND price >= ?
==> Parameters: coffee(String), 500(Integer)

==>  Preparing: SELECT * FROM products
==> Parameters:

One caveat: wrap conditions containing OR in parentheses. If you don’t write it as AND (status = 'a' OR status = 'b'), it mixes with the other ANDs and the precedence breaks.

set - Strips the Trailing Comma in Partial Updates

An UPDATE that only updates non-null fields leaves a comma after the last item if you build it with <if> alone. <set> removes the trailing comma, so you can line up each item with a comma at its end (in the implementation, a leading comma is removed as well). The only differences from <where> are whether it strips AND/OR or ,, and whether it prepends WHERE or SET.

<update id="updatePartial">
  UPDATE products
  <set>
    <if test="name != null">name = #{name},</if>
    <if test="category != null">category = #{category},</if>
    <if test="price != null">price = #{price},</if>
    updated_at = CURRENT_TIMESTAMP
  </set>
  WHERE id = #{id}
</update>

The key point is placing updated_at = CURRENT_TIMESTAMP unconditionally at the end. Without it, when every field is null the statement becomes UPDATE products SET WHERE id = ?, which is issued with an empty SET and results in a syntax error. Always updating the modification timestamp is natural, so this form keeps things safe. Another approach is to reject the call in the service layer when there is nothing to update.

If you call it with only price set, the log shows Preparing: UPDATE products SET price = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?. You can see that the comma after price is kept properly and there is none at the end.

trim - Shape Freely with prefix and overrides

In fact, <where> and <set> are really <trim> underneath. It has four attributes, which break down like this.

Attribute	Meaning
prefix	String to prepend if the content is not empty
suffix	String to append if the content is not empty
prefixOverrides	Strings to strip from the start of the content (multiple, separated by `\|`)
suffixOverrides	Strings to strip from the end of the content

<where> is equivalent to <trim prefix="WHERE" prefixOverrides="AND |OR ">, and <set> is equivalent to <trim prefix="SET" prefixOverrides="," suffixOverrides=",">. The trailing space in AND |OR is intentional. It is specified with the space so that the beginning of a column name like ANDROID is not mistakenly removed.

<trim> really shines in situations that fit neither <where> nor <set>. For example, when dynamically building an INSERT’s column list and VALUES, you use the parentheses as prefix/suffix and drop the trailing comma.

<insert id="insertSelective">
  INSERT INTO products
  <trim prefix="(" suffix=")" suffixOverrides=",">
    name, category,
    <if test="price != null">price,</if>
    status, updated_at,
  </trim>
  <trim prefix="VALUES (" suffix=")" suffixOverrides=",">
    #{name}, #{category},
    <if test="price != null">#{price},</if>
    #{status}, CURRENT_TIMESTAMP,
  </trim>
</insert>

If you call it without price, the output is Preparing: INSERT INTO products ( name, category, status, updated_at ) VALUES ( ?, ?, ?, CURRENT_TIMESTAMP ), with price missing from both the columns and the VALUES. Just like <where>, neither prefix nor suffix is output if the content is empty.

choose / when / otherwise - Mutually Exclusive Branching

If you line up <if> tags and several are true at the same time, all of them are output. When you want “exactly one of these,” use <choose>. Only the first <when> that evaluates to true is output, and if none are true, <otherwise> is used. It feels just like a switch statement.

A typical use case is switching the sort order. ORDER BY column names cannot be bound with #{}, and using ${} opens the door to SQL injection. Whitelisting with <choose> lets you avoid ${} entirely. Here it is written as a separate statement, adding ORDER BY to the earlier search.

<select id="searchSorted" resultType="com.example.shop.entity.Product">
  SELECT * FROM products
  <where>
    <if test="category != null and category != ''">AND category = #{category}</if>
  </where>
  <choose>
    <when test="sortKey == 'price'">ORDER BY price</when>
    <when test="sortKey == 'name'">ORDER BY name</when>
    <otherwise>ORDER BY id</otherwise>
  </choose>
</select>

Even if sortKey receives a value like "price; DROP TABLE", it matches none of the <when> branches and simply falls through to ORDER BY id. Calling it with sortKey set to price logs Preparing: SELECT * FROM products WHERE category = ? ORDER BY price, SQL in which exactly one <when> was applied.

There is one trap: in OGNL, a single character in single quotes such as 'A' is treated as a char, not a string. status == 'A' will not match a String, so to compare a single character, use status == "A" or 'A'.toString().

Prioritized conditions such as “if an ID is specified, search by ID only; otherwise search by name and category” can be written in the same form. If you need to paginate a sorted list, continue with the pagination article.

bind - Safely Concatenating LIKE Wildcards

If you write LIKE '%' || #{name} || '%' for a partial-match search, it works on H2 and PostgreSQL, but on MySQL || is treated as logical OR by default (it becomes concatenation if sql_mode includes PIPES_AS_CONCAT). CONCAT('%', #{name}, '%') has the opposite problem: the number of arguments and behavior differ by database.

If you don’t want to depend on the database dialect, use <bind>. It puts a value concatenated in OGNL into a new variable, which you then bind with #{}. The Mapper side is List<Product> searchByName(@Param("name") String name);.

<select id="searchByName" resultType="com.example.shop.entity.Product">
  SELECT * FROM products
  <where>
    <if test="name != null and name != ''">
      <bind name="namePattern" value="'%' + name + '%'" />
      name LIKE #{namePattern}
    </if>
  </where>
</select>

<!-- NG: name LIKE '%${name}%' は文字列連結なのでインジェクションが成立する -->

There is a reason the <bind> is placed inside the <if>. If you put it at the top without a guard, then when name is null, OGNL turns '%' + null + '%' into the string %null%, and the results silently go wrong without any error.

If you pass a value like %' OR '1' = '1 to the ${} version, it is embedded directly into the SQL and every row is returned. With <bind>, the value is only passed to a placeholder, so it is safe.

==>  Preparing: SELECT * FROM products WHERE name LIKE ?
==> Parameters: %ブレンド%(String)

However, the meaning of % and _ in user input does not change. If you want to treat them as literals, escape them on the Java side before passing them in.

foreach - IN Clauses, Batch INSERT, and Empty List Handling

<foreach> has six attributes. collection is what to iterate over, item is the variable name for each element, index is the index, open/close are the strings placed before and after, and separator is the delimiter between elements. You can also pass a Map, in which case index receives the key and item receives the value. The form is to pass @Param("prices") Map<Long, Integer> prices and write <foreach collection="prices" index="id" item="price" separator=",">(#{id}, #{price})</foreach>.

Be careful with the name you specify in collection, since it is easy to get wrong. If you added @Param("ids"), use that name. If you passed only a List without it, use list or collection, and for an array use array. Spring Boot’s Maven/Gradle plugins compile with -parameters, so the actual argument name ids does in fact work, but since that depends on the compile configuration, being explicit with @Param avoids accidents. Getting the name wrong fails with Parameter 'ids' not found.

Now to the main issue, empty lists. When you pass an empty list, <foreach> outputs nothing at all, including open/close. That means SQL cut off at WHERE id IN reaches the database as-is and causes a syntax error (you get IN () when the parentheses are hard-coded outside the <foreach>, and it fails just the same). If you pass null, it fails even earlier: MyBatis throws a BuilderException at the stage of building the SQL. From MyBatis 3.5.9 onward, the nullable="true" attribute or mybatis.configuration.nullable-on-for-each: true lets you treat null as empty, but the empty-list problem remains, so you still need a guard. There are two approaches.

<!-- 対策1: IN句ごと省く（条件なし = 全件になる点に注意） -->
<select id="findByIds" resultType="com.example.shop.entity.Product">
  SELECT * FROM products
  <where>
    <if test="ids != null and !ids.isEmpty()">
      id IN
      <foreach collection="ids" item="id" open="(" separator="," close=")">#{id}</foreach>
    </if>
  </where>
</select>

<!-- 対策2: 空なら意図的に0件を返す -->
<where>
  <choose>
    <when test="ids != null and !ids.isEmpty()">
      id IN
      <foreach collection="ids" item="id" open="(" separator="," close=")">#{id}</foreach>
    </when>
    <otherwise>1 = 0</otherwise>
  </choose>
</where>

The Mapper side is List<Product> findByIds(@Param("ids") List<Long> ids);. Here is how the log and exceptions change before and after the fix.

# 対策なしで空リスト: open/close ごと消えて途切れる
==>  Preparing: SELECT * FROM products WHERE id IN
org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "SELECT * FROM products WHERE id IN[*]"; expected "("

# 対策なしでnull: SQLを発行する前に落ちる
org.apache.ibatis.builder.BuilderException: The expression 'ids' evaluated to a null value.

# 対策1/2で要素あり
==>  Preparing: SELECT * FROM products WHERE id IN ( ? , ? , ? )
==> Parameters: 1(Long), 2(Long), 3(Long)

# 対策2で空リスト
==>  Preparing: SELECT * FROM products WHERE 1 = 0

Whether “an empty ID filter means all rows” is acceptable or it should be “empty means zero rows” depends on the business. If you use approach 1 in something like a bulk delete, every row becomes a target, so when in doubt, approach 2 causes fewer accidents.

Batch INSERT can also generate multiple VALUES rows with <foreach>.

<insert id="insertAll">
  INSERT INTO products (name, category, price, status, updated_at)
  VALUES
  <foreach collection="list" item="p" separator=",">
    (#{p.name}, #{p.category}, #{p.price}, #{p.status}, CURRENT_TIMESTAMP)
  </foreach>
</insert>

Once the count exceeds several thousand, the SQL becomes huge, and some databases hit their placeholder limit. Split into chunks of a few hundred on the Java side and call it repeatedly.

Writing It with Annotations

If your project doesn’t use XML, you have two options. One is to wrap the contents of @Select in <script>, which lets you use the same tags as in XML. However, since you are writing XML inside a string, you still need escapes like >, and readability drops as conditions grow.

The other is @SelectProvider, which builds the statement in Java using the org.apache.ibatis.jdbc.SQL builder.

public class ProductSqlProvider {
    public String search(ProductSearchCondition cond) {
        return new SQL() {{
            SELECT("*");
            FROM("products");
            if (cond.getName() != null && !cond.getName().isEmpty()) {
                WHERE("name = #{name}");
            }
            if (cond.getMinPrice() != null) {
                WHERE("price >= #{minPrice}");
            }
        }}.toString();
    }
}

public interface ProductMapper {
    @SelectProvider(type = ProductSqlProvider.class, method = "search")
    List<Product> search(ProductSearchCondition cond);

    // <script> 版: XMLと同じタグが使える
    @Select("<script>"
        + "SELECT * FROM products"
        + "<where>"
        + "  <if test='name != null'>AND name = #{name}</if>"
        + "  <if test='minPrice != null'>AND price &gt;= #{minPrice}</if>"
        + "</where>"
        + "</script>")
    List<Product> searchScript(ProductSearchCondition cond);
}

If WHERE() is never called, no WHERE clause is output at all, and if it is called multiple times, the conditions are joined with AND, giving the same effect as <where>. Write the values as #{name} placeholders directly in the string and leave binding to MyBatis. If you concatenate like "name = '" + cond.getName() + "'" here, the builder you went to the trouble of using becomes a breeding ground for injection.

Quick Reference of Errors and Causes

Symptom	Cause	Fix
`There is no getter for property`	Parameter name mismatch, missing `@Param`	See the if test section
`syntax error` near `WHERE` or `AND`	Hard-coded `WHERE` with all conditions empty	`<where>`
Trailing comma in UPDATE, or `SET WHERE`	`<set>` not used, all fields null	`<set>` plus an unconditional update column
Syntax error with SQL cut off at `WHERE ... IN`	Empty list passed to foreach	`<if>` guard or `1 = 0`
`BuilderException: evaluated to a null value`	null passed to foreach	`<if>` guard, or `nullable="true"` / nullableOnForEach
`Parameter 'xxx' not found`	Wrong collection name	`@Param` or `list`/`collection`/`array` (actual argument name also works with `-parameters`)
LIKE doesn’t work on MySQL	`\|\|` is logical OR by default	`<bind>`
Condition has no effect, `0` is ignored	Only `!= ''` so null slips through, empty-string check on a number	See the if test section

Summary

Let’s recap each tag’s role in one line. <where> strips the leading AND, <set> strips the trailing comma, <trim> is the general-purpose version of both, <choose> is exclusive branching, <bind> is safe value transformation, and <foreach> is iteration where an empty guard is mandatory.

For every tag, watching “what SQL the XML you wrote turns into” in the DEBUG log as you write is ultimately the fastest way to debug.

If you want to do the same thing on the JPA side, see the Specification article, and if you are deciding between the two, the MyBatis vs JPA comparison may also help.

References

Official documentation and references for the topics covered in this article.