Understanding MyBatis Dynamic SQL and XML Mapping Tags

1. What is MyBatis Dynamic SQL

Dynamic SQL is one of MyBatis's most powerful features. In JDBC or similar frameworks, developers often have to concatenate SQL strings manually, which is error‑prone and tedious. Dynamic SQL builds queries at runtime based on user input or external conditions, automatically handling spaces, commas, and optional clauses.

2. Execution Principle

MyBatis uses OGNL to evaluate expressions on the parameter object; the results determine which fragments are included, allowing the SQL to be assembled dynamically.

3. Core MyBatis XML Tags

3.1 if Tag (Conditional Judgment)

The <if test="condition">SQL fragment</if> tag works like a Java if statement, enabling or disabling a piece of SQL based on the evaluated condition.

<select id="selectUser" resultType="User" parameterType="User">
    SELECT * FROM user
    WHERE 1=1
    <if test="username != null">AND username = #{username}</if>
    <if test="sex != null">AND sex = #{sex}</if>
</select>

3.2 where Tag

Combines multiple <if> conditions and automatically removes the leading AND or OR keyword.

3.3 set Tag

Used in UPDATE statements to concatenate column assignments while discarding the trailing comma.

3.4 choose / when / otherwise Tag

Provides a switch‑like mechanism; only the first <when> whose test evaluates to true is applied, otherwise <otherwise> is used.

3.5 trim Tag

Formats SQL fragments by adding a prefix or suffix and removing unwanted leading/trailing keywords (e.g., AND , commas).

3.6 foreach Tag

Iterates over collections, arrays, or maps to generate IN clauses or batch statements.

<select id="findAll" resultType="Student" parameterType="Integer">
    SELECT * FROM student WHERE id IN
    <foreach collection="array" item="id" open="(" separator="," close=")">
        #{id}
    </foreach>
</select>

3.7 sql Tag

Defines reusable SQL fragments that can be referenced elsewhere with <include> .

3.8 include Tag

Inserts the content of a previously defined <sql> fragment by specifying its refid .

3.9 resultMap, association, collection Tags

Map query results to Java objects, supporting one‑to‑many, many‑to‑one, and many‑to‑many relationships.

<resultMap id="myStudent" type="Student">
    <id property="sid" column="sid"/>
    <result property="sname" column="sname"/>
    <collection property="teachers" ofType="Teacher">
        <id property="tid" column="tid"/>
        <result property="tname" column="tname"/>
    </collection>
</resultMap>

4. Integrated Example

The article provides a complete MyBatis mapper XML that demonstrates the use of all the above tags for SELECT, INSERT, UPDATE, and DELETE operations, including batch processing with foreach and relationship mapping with resultMap .

5. Test Class

A JUnit test class shows how to obtain a SqlSession , retrieve the mapper, and execute the CRUD methods defined in the XML.

package com.yzx.test;

import com.yzx.entity.Student;
import com.yzx.mapper.StuMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.io.InputStream;
import java.util.List;

public class StuTest {
    SqlSession sqlSession;
    InputStream is;

    @Before
    public void before() throws Exception {
        is = Resources.getResourceAsStream("sqlMapperConfig.xml");
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        SqlSessionFactory factory = builder.build(is);
        sqlSession = factory.openSession();
    }

    @After
    public void after() throws Exception {
        sqlSession.commit();
        sqlSession.close();
        if (is != null) is.close();
    }

    @Test
    public void findAll() {
        StuMapper mapper = sqlSession.getMapper(StuMapper.class);
        List
list = mapper.find();
        list.forEach(System.out::println);
    }
    // other test methods omitted for brevity
}

Overall, the article serves as a comprehensive guide for developers to master MyBatis dynamic SQL and XML mapping techniques.