MyBatis dynamic SQL allows you to build SQL statements conditionally at runtime, avoiding string concatenation and SQL injection risks.

Core Dynamic Tags

Tag Purpose
<if> Include fragment if condition is true
<choose> / <when> / <otherwise> Switch/case logic
<where> Adds WHERE with smart AND/OR trimming
<set> Adds SET for UPDATE with comma trimming
<foreach> Iterate over collections
<trim> Generic prefix/suffix trimming

Conditional WHERE 

  <select id="searchUsers" resultType="User">
    SELECT id, email, name FROM users
    <where>
        <if test="email != null and email != ''">
            AND email = #{email}
        </if>
        <if test="name != null and name != ''">
            AND name LIKE CONCAT('%', #{name}, '%')
        </if>
        <if test="active != null">
            AND active = #{active}
        </if>
    </where>
    ORDER BY id
</select>

<where> automatically removes leading AND/OR.

Dynamic UPDATE 

  <update id="updateUser">
    UPDATE users
    <set>
        <if test="email != null">email = #{email},</if>
        <if test="name != null">name = #{name},</if>
        <if test="active != null">active = #{active},</if>
    </set>
    WHERE id = #{id}
</update>

Foreach (IN clause) 

  <select id="findByIds" resultType="User">
    SELECT id, email, name FROM users
    WHERE id IN
    <foreach collection="ids" item="id" open="(" separator="," close=")">
        #{id}
    </foreach>
</select>

<!-- Batch insert -->
<insert id="batchInsert">
    INSERT INTO users (email, name) VALUES
    <foreach collection="users" item="user" separator=",">
        (#{user.email}, #{user.name})
    </foreach>
</insert>

Choose / When / Otherwise 

  <select id="findUsers" resultType="User">
    SELECT * FROM users
    <where>
        <choose>
            <when test="email != null">
                AND email = #{email}
            </when>
            <when test="name != null">
                AND name LIKE CONCAT('%', #{name}, '%')
            </when>
            <otherwise>
                AND active = true
            </otherwise>
        </choose>
    </where>
</select>

SQL Fragments (Reusable) 

  <sql id="userColumns">
    id, email, name, active, created_at
</sql>

<sql id="userSearchConditions">
    <if test="email != null">AND email = #{email}</if>
    <if test="name != null">AND name LIKE CONCAT('%', #{name}, '%')</if>
</sql>

<select id="searchUsers" resultType="User">
    SELECT <include refid="userColumns"/> FROM users
    <where>
        <include refid="userSearchConditions"/>
    </where>
</select>

Dynamic SQL with Java Provider

For complex logic, use @SelectProvider:

  public class UserSqlProvider {
    public String searchUsers(Map<String, Object> params) {
        return new SQL() {{
            SELECT("id, email, name");
            FROM("users");
            if (params.get("email") != null) {
                WHERE("email = #{email}");
            }
            if (params.get("name") != null) {
                WHERE("name LIKE CONCAT('%', #{name}, '%')");
            }
            ORDER_BY("id");
        }}.toString();
    }
}

@Mapper
public interface UserMapper {
    @SelectProvider(type = UserSqlProvider.class, method = "searchUsers")
    List<User> searchUsers(@Param("email") String email, @Param("name") String name);
}

Pagination with PageHelper 

  <dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper-spring-boot-starter</artifactId>
    <version>2.1.0</version>
</dependency>
  
  PageHelper.startPage(pageNum, pageSize);
List<User> users = userMapper.searchUsers(criteria);
PageInfo<User> pageInfo = new PageInfo<>(users);

Best Practices

  • Always use #{} for parameters (prepared statements) — never ${} with user input
  • Use <where> and <set> instead of manual AND/comma trimming
  • Extract reusable fragments with <sql> and <include>
  • Use @SelectProvider for very complex dynamic logic
  • Combine with PageHelper for easy pagination