Master MyBatis Dynamic SQL: Eliminate XML Mappers and Supercharge CRUD

When using MyBatis, we often write many SQL statements in mapper.xml. Since MyBatis 1.3.6, the official recommendation is to use Dynamic SQL, which eliminates the need for mapper.xml files and is very convenient.

Dynamic SQL Overview

In Spring we have XML and Java configuration; Spring Boot recommends Java configuration, so using Dynamic SQL is like using Java to operate MyBatis. Dynamic SQL is a framework for generating dynamic SQL statements via a Java API, supporting complex and multi‑table queries.

Dynamic SQL features:

Type safety: ensures parameter types match database column types.

Expressiveness: the construction clearly conveys intent.

Flexibility: any combination of and, or, nested conditions can build where clauses.

Extensibility: can generate SQL for MyBatis3, Spring JDBC, and plain JDBC.

Lightweight: only a small dependency, no transitive dependencies.

Getting Started

First we use a beginner example that includes basic CRUD operations. For those unfamiliar with MBG, see the previous article "Free Your Hands! MyBatis Official Code Generator Is Powerful!"

Integrate Dynamic SQL

Add the following dependency to pom.xml (compared with previous MBG usage, only the MyBatis Dynamic SQL dependency is added):

<dependencies>
    <!--SpringBoot integration MyBatis-->
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>2.1.3</version>
    </dependency>
    <!--MyBatis pagination plugin-->
    <dependency>
        <groupId>com.github.pagehelper</groupId>
        <artifactId>pagehelper-spring-boot-starter</artifactId>
        <version>1.3.0</version>
    </dependency>
    <!--Druid connection pool-->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid-spring-boot-starter</artifactId>
        <version>1.1.10</version>
    </dependency>
    <!--MyBatis Generator-->
    <dependency>
        <groupId>org.mybatis.generator</groupId>
        <artifactId>mybatis-generator-core</artifactId>
        <version>1.4.0</version>
    </dependency>
    <!--MyBatis Dynamic SQL support-->
    <dependency>
        <groupId>org.mybatis.dynamic-sql</groupId>
        <artifactId>mybatis-dynamic-sql</artifactId>
        <version>1.2.1</version>
    </dependency>
    <!--MySQL driver-->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.15</version>
    </dependency>
</dependencies>

Configure the data source and MyBatis mapper XML path in application.yml, only the custom mapper.xml path needs to be set:

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/mall?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
    username: root
    password: root

mybatis:
  mapper-locations:
    - classpath:dao/*.xml

Add Java configuration to scan mapper interfaces. MBG‑generated mappers go under the mapper package, custom ones under dao:

@Configuration
@MapperScan({"com.macro.mall.tiny.mbg.mapper","com.macro.mall.tiny.dao"})
public class MyBatisConfig {
}

Using Code Generator

Before generating code with MBG, configure the database connection in generator.properties:

jdbc.driverClass=com.mysql.cj.jdbc.Driver
jdbc.connectionURL=jdbc:mysql://localhost:3306/mall?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
jdbc.userId=root
jdbc.password=root

Then configure MBG in generatorConfig.xml. Important changes: set targetRuntime to MyBatis3DynamicSQL and omit the sqlMapGenerator tag.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN" "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">

<generatorConfiguration>
    <properties resource="generator.properties"/>
    <context id="MySqlContext" targetRuntime="MyBatis3DynamicSQL">
        <property name="beginningDelimiter" value="`"/>
        <property name="endingDelimiter" value="`"/>
        <property name="javaFileEncoding" value="UTF-8"/>
        <!-- plugins for serialization and toString -->
        <plugin type="org.mybatis.generator.plugins.SerializablePlugin"/>
        <plugin type="org.mybatis.generator.plugins.ToStringPlugin"/>
        <commentGenerator type="com.macro.mall.tiny.mbg.CommentGenerator">
            <property name="suppressAllComments" value="true"/>
            <property name="suppressDate" value="true"/>
            <property name="addRemarkComments" value="true"/>
        </commentGenerator>
        <jdbcConnection driverClass="${jdbc.driverClass}" connectionURL="${jdbc.connectionURL}" userId="${jdbc.userId}" password="${jdbc.password}">
            <property name="nullCatalogMeansCurrent" value="true"/>
        </jdbcConnection>
        <javaModelGenerator targetPackage="com.macro.mall.tiny.mbg.model" targetProject="mall-tiny-dynamic-sql\src\main\java"/>
        <javaClientGenerator type="XMLMAPPER" targetPackage="com.macro.mall.tiny.mbg.mapper" targetProject="mall-tiny-dynamic-sql\src\main\java"/>
        <table tableName="ums_admin">
            <generatedKey column="id" sqlStatement="MySql" identity="true"/>
        </table>
        <table tableName="ums_role">
            <generatedKey column="id" sqlStatement="MySql" identity="true"/>
        </table>
        <table tableName="ums_admin_role_relation">
            <generatedKey column="id" sqlStatement="MySql" identity="true"/>
        </table>
    </context>
</generatorConfiguration>

When using Dynamic SQL, the previous addFieldComment method is ineffective; you need to override addFieldAnnotation to add Swagger annotations.

public class CommentGenerator extends DefaultCommentGenerator {
    @Override
    public void addFieldAnnotation(Field field, IntrospectedTable introspectedTable, IntrospectedColumn introspectedColumn, Set<FullyQualifiedJavaType> imports) {
        if (!addRemarkComments || CollUtil.isEmpty(imports)) return;
        long count = imports.stream()
                .filter(item -> API_MODEL_PROPERTY_FULL_CLASS_NAME.equals(item.getFullyQualifiedName()))
                .count();
        if (count <= 0L) return;
        String remarks = introspectedColumn.getRemarks();
        if (StringUtility.stringHasValue(remarks)) {
            if (remarks.contains("\"")) {
                remarks = remarks.replace("\"", "'");
            }
            field.addJavaDocLine("@ApiModelProperty(value = \"" + remarks + "\")");
        }
    }
}

After preparation, run the Generator main method. No mapper.xml or Example classes are generated; instead, DynamicSqlSupport classes appear, e.g., UmsAdminDynamicSqlSupport, which map tables and columns to SqlTable and SqlColumn objects.

Implement Basic CRUD

Here we use the mall-tiny project's permission‑management tables. See the article "Still Building from Scratch? A Rapid Development Scaffold!" for details.

Inspect the MBG‑generated mapper interface; it now contains many default methods and implementations, moving XML logic into the interface.

@Mapper
public interface UmsAdminMapper {
    @Generated("org.mybatis.generator.api.MyBatisGenerator")
    BasicColumn[] selectList = BasicColumn.columnList(id, username, password, icon, email, nickName, note, createTime, loginTime, status);

    @Generated("org.mybatis.generator.api.MyBatisGenerator")
    @SelectProvider(type=SqlProviderAdapter.class, method="select")
    long count(SelectStatementProvider selectStatement);

    @Generated("org.mybatis.generator.api.MyBatisGenerator")
    @DeleteProvider(type=SqlProviderAdapter.class, method="delete")
    int delete(DeleteStatementProvider deleteStatement);

    @Generated("org.mybatis.generator.api.MyBatisGenerator")
    @InsertProvider(type=SqlProviderAdapter.class, method="insert")
    @SelectKey(statement="SELECT LAST_INSERT_ID()", keyProperty="record.id", before=false, resultType=Long.class)
    int insert(InsertStatementProvider<UmsAdmin> insertStatement);

    @Generated("org.mybatis.generator.api.MyBatisGenerator")
    @SelectProvider(type=SqlProviderAdapter.class, method="select")
    @ResultMap("UmsAdminResult")
    Optional<UmsAdmin> selectOne(SelectStatementProvider selectStatement);

    @Generated("org.mybatis.generator.api.MyBatisGenerator")
    @SelectProvider(type=SqlProviderAdapter.class, method="select")
    @Results(id="UmsAdminResult", value={
        @Result(column="id", property="id", jdbcType=JdbcType.BIGINT, id=true),
        @Result(column="username", property="username", jdbcType=JdbcType.VARCHAR),
        @Result(column="password", property="password", jdbcType=JdbcType.VARCHAR),
        @Result(column="icon", property="icon", jdbcType=JdbcType.VARCHAR),
        @Result(column="email", property="email", jdbcType=JdbcType.VARCHAR),
        @Result(column="nick_name", property="nickName", jdbcType=JdbcType.VARCHAR),
        @Result(column="note", property="note", jdbcType=JdbcType.VARCHAR),
        @Result(column="create_time", property="createTime", jdbcType=JdbcType.TIMESTAMP),
        @Result(column="login_time", property="loginTime", jdbcType=JdbcType.TIMESTAMP),
        @Result(column="status", property="status", jdbcType=JdbcType.INTEGER)
    })
    List<UmsAdmin> selectMany(SelectStatementProvider selectStatement);

    @Generated("org.mybatis.generator.api.MyBatisGenerator")
    @UpdateProvider(type=SqlProviderAdapter.class, method="update")
    int update(UpdateStatementProvider updateStatement);

    // default methods for count, deleteByPrimaryKey, insert, insertSelective, selectByPrimaryKey, updateByPrimaryKey, updateByPrimaryKeySelective, etc.
}

The generated UmsAdminDynamicSqlSupport class abstracts the table and columns:

public final class UmsAdminDynamicSqlSupport {
    public static final UmsAdmin umsAdmin = new UmsAdmin();
    public static final SqlColumn<Long> id = umsAdmin.id;
    public static final SqlColumn<String> username = umsAdmin.username;
    public static final SqlColumn<String> password = umsAdmin.password;
    public static final SqlColumn<String> icon = umsAdmin.icon;
    public static final SqlColumn<String> email = umsAdmin.email;
    public static final SqlColumn<String> nickName = umsAdmin.nickName;
    public static final SqlColumn<String> note = umsAdmin.note;
    public static final SqlColumn<Date> createTime = umsAdmin.createTime;
    public static final SqlColumn<Date> loginTime = umsAdmin.loginTime;
    public static final SqlColumn<Integer> status = umsAdmin.status;

    public static final class UmsAdmin extends SqlTable {
        public final SqlColumn<Long> id = column("id", JDBCType.BIGINT);
        public final SqlColumn<String> username = column("username", JDBCType.VARCHAR);
        public final SqlColumn<String> password = column("password", JDBCType.VARCHAR);
        public final SqlColumn<String> icon = column("icon", JDBCType.VARCHAR);
        public final SqlColumn<String> email = column("email", JDBCType.VARCHAR);
        public final SqlColumn<String> nickName = column("nick_name", JDBCType.VARCHAR);
        public final SqlColumn<String> note = column("note", JDBCType.VARCHAR);
        public final SqlColumn<Date> createTime = column("create_time", JDBCType.TIMESTAMP);
        public final SqlColumn<Date> loginTime = column("login_time", JDBCType.TIMESTAMP);
        public final SqlColumn<Integer> status = column("status", JDBCType.INTEGER);
        public UmsAdmin() { super("ums_admin"); }
    }
}

Using the generated code, basic CRUD operations become straightforward:

@Service
public class UmsAdminServiceImpl implements UmsAdminService {
    @Autowired
    private UmsAdminMapper adminMapper;

    @Override
    public void create(UmsAdmin entity) {
        adminMapper.insert(entity);
    }

    @Override
    public void update(UmsAdmin entity) {
        adminMapper.updateByPrimaryKeySelective(entity);
    }

    @Override
    public void delete(Long id) {
        adminMapper.deleteByPrimaryKey(id);
    }

    @Override
    public UmsAdmin select(Long id) {
        return adminMapper.selectByPrimaryKey(id).orElse(null);
    }

    @Override
    public List<UmsAdmin> listAll(Integer pageNum, Integer pageSize) {
        PageHelper.startPage(pageNum, pageSize);
        return adminMapper.select(SelectDSLCompleter.allRows());
    }
}

Advanced Usage

Basic operations are not enough; advanced techniques are needed to fully leverage Dynamic SQL.

SqlBuilder

SqlBuilder is a powerful class for flexibly building SQL conditions. Common methods include:

Condition

Example

SQL

Between

where(foo, isBetween(x).and(y))

where foo between ? and ?

Equals

where(foo, isEqualTo(x))

where foo = ?

Greater Than

where(foo, isGreaterThan(x))

where foo > ?

In

where(foo, isIn(x, y))

where foo in (?,?)

Like

where(foo, isLike(x))

where foo like ?

Not Equals

where(foo, isNotEqualTo(x))

where foo <> ?

Null

where(foo, isNull())

where foo is null

Present Equals

where(foo, isEqualToWhenPresent(x))

where foo = ? (rendered only if x is non‑null)

StatementProvider

StatementProvider encapsulates both the SQL and its parameters, similar to a select tag in traditional mapper XML, but built via Java.

Conditional Query

Build a StatementProvider with SqlBuilder and invoke the mapper method.

SQL example: select users by username and status, ordered by creation time descending.

SELECT id, username, PASSWORD, icon, email, nick_name, note, create_time, login_time, STATUS
FROM ums_admin
WHERE ( username = 'macro' AND STATUS IN (0,1) )
ORDER BY create_time DESC;

@Override
public List<UmsAdmin> list(Integer pageNum, Integer pageSize, String username, List<Integer> statusList) {
    PageHelper.startPage(pageNum, pageSize);
    SelectStatementProvider stmt = SqlBuilder.select(UmsAdminMapper.selectList)
        .from(UmsAdminDynamicSqlSupport.umsAdmin)
        .where(UmsAdminDynamicSqlSupport.username, isEqualToWhenPresent(username))
        .and(UmsAdminDynamicSqlSupport.status, isIn(statusList))
        .orderBy(UmsAdminDynamicSqlSupport.createTime.descending())
        .build()
        .render(RenderingStrategies.MYBATIS3);
    return adminMapper.selectMany(stmt);
}

Lambda Conditional Query

Use lambda expressions for simpler single‑table queries.

@Override
public List<UmsAdmin> lambdaList(Integer pageNum, Integer pageSize, String username, List<Integer> statusList) {
    PageHelper.startPage(pageNum, pageSize);
    return adminMapper.select(c -> c.where(UmsAdminDynamicSqlSupport.username, isEqualToWhenPresent(username))
        .and(UmsAdminDynamicSqlSupport.status, isIn(statusList))
        .orderBy(UmsAdminDynamicSqlSupport.createTime.descending()));
}

Subquery

Dynamic SQL allows subqueries directly in Java.

SQL: select admins whose id is in a subquery of role‑admin relations.

SELECT * FROM ums_admin WHERE id IN (SELECT admin_id FROM ums_admin_role_relation WHERE role_id = 1);

@Override
public List<UmsAdmin> subList(Long roleId) {
    SelectStatementProvider stmt = SqlBuilder.select(UmsAdminMapper.selectList)
        .from(UmsAdminDynamicSqlSupport.umsAdmin)
        .where(UmsAdminDynamicSqlSupport.id, isIn(
            SqlBuilder.select(UmsAdminRoleRelationDynamicSqlSupport.adminId)
                .from(UmsAdminRoleRelationDynamicSqlSupport.umsAdminRoleRelation)
                .where(UmsAdminRoleRelationDynamicSqlSupport.roleId, isEqualTo(roleId))
        ))
        .build()
        .render(RenderingStrategies.MYBATIS3);
    return adminMapper.selectMany(stmt);
}

Group and Join Query

Multi‑table queries that previously required manual XML can now be expressed with Dynamic SQL.

SQL: count admins per role using LEFT JOIN.

SELECT ur.id AS roleId, ur.NAME AS roleName, COUNT(ua.id) AS count
FROM ums_role ur
LEFT JOIN ums_admin_role_relation uarr ON ur.id = uarr.role_id
LEFT JOIN ums_admin ua ON uarr.admin_id = ua.id
GROUP BY ur.id;

public List<RoleStatDto> groupList() {
    SelectStatementProvider stmt = SqlBuilder.select(UmsRoleDynamicSqlSupport.id.as("roleId"),
            UmsRoleDynamicSqlSupport.name.as("roleName"),
            count(UmsAdminDynamicSqlSupport.id).as("count"))
        .from(UmsRoleDynamicSqlSupport.umsRole)
        .leftJoin(UmsAdminRoleRelationDynamicSqlSupport.umsAdminRoleRelation)
        .on(UmsRoleDynamicSqlSupport.id, equalTo(UmsAdminRoleRelationDynamicSqlSupport.roleId))
        .leftJoin(UmsAdminDynamicSqlSupport.umsAdmin)
        .on(UmsAdminRoleRelationDynamicSqlSupport.adminId, equalTo(UmsAdminDynamicSqlSupport.id))
        .groupBy(UmsRoleDynamicSqlSupport.id)
        .build()
        .render(RenderingStrategies.MYBATIS3);
    return adminDao.groupList(stmt);
}

Conditional Delete

Delete rows based on conditions without XML.

SQL: delete admin by username.

DELETE FROM ums_admin WHERE username = 'andy';

@Override
public void deleteByUsername(String username) {
    DeleteStatementProvider stmt = SqlBuilder.deleteFrom(UmsAdminDynamicSqlSupport.umsAdmin)
        .where(UmsAdminDynamicSqlSupport.username, isEqualTo(username))
        .build()
        .render(RenderingStrategies.MYBATIS3);
    adminMapper.delete(stmt);
}

Conditional Update

Update rows conditionally using generated update methods.

SQL: set status = 1 for ids 1 and 2.

UPDATE ums_admin SET STATUS = 1 WHERE id IN (1,2);

@Override
public void updateByIds(List<Long> ids, Integer status) {
    UpdateStatementProvider stmt = SqlBuilder.update(UmsAdminDynamicSqlSupport.umsAdmin)
        .set(UmsAdminDynamicSqlSupport.status).equalTo(status)
        .where(UmsAdminDynamicSqlSupport.id, isIn(ids))
        .build()
        .render(RenderingStrategies.MYBATIS3);
    adminMapper.update(stmt);
}

One‑to‑Many Query

Dynamic SQL can handle one‑to‑many queries, though the resultMap must still be defined in XML.

DAO method:

public interface UmsAdminDao {
    @SelectProvider(type=SqlProviderAdapter.class, method="select")
    @ResultMap("AdminRoleResult")
    AdminRoleDto selectWithRoleList(SelectStatementProvider selectStatement);
}

ResultMap defined in mapper.xml (attributes stripped):

<resultMap id="AdminRoleResult" type="com.macro.mall.tiny.domain.AdminRoleDto" extends="com.macro.mall.tiny.mbg.mapper.UmsAdminMapper.UmsAdminResult">
    <collection property="roleList" resultMap="com.macro.mall.tiny.mbg.mapper.UmsRoleMapper.UmsRoleResult" columnPrefix="role_"/>
</resultMap>

@Override
public AdminRoleDto selectWithRoleList(Long id) {
    List<BasicColumn> cols = new ArrayList<>(CollUtil.toList(UmsAdminMapper.selectList));
    cols.add(UmsRoleDynamicSqlSupport.id.as("role_id"));
    cols.add(UmsRoleDynamicSqlSupport.name.as("role_name"));
    cols.add(UmsRoleDynamicSqlSupport.description.as("role_description"));
    cols.add(UmsRoleDynamicSqlSupport.createTime.as("role_create_time"));
    cols.add(UmsRoleDynamicSqlSupport.status.as("role_status"));
    cols.add(UmsRoleDynamicSqlSupport.sort.as("role_sort"));
    SelectStatementProvider stmt = SqlBuilder.select(cols)
        .from(UmsAdminDynamicSqlSupport.umsAdmin)
        .leftJoin(UmsAdminRoleRelationDynamicSqlSupport.umsAdminRoleRelation)
        .on(UmsAdminDynamicSqlSupport.id, equalTo(UmsAdminRoleRelationDynamicSqlSupport.adminId))
        .leftJoin(UmsRoleDynamicSqlSupport.umsRole)
        .on(UmsAdminRoleRelationDynamicSqlSupport.roleId, equalTo(UmsRoleDynamicSqlSupport.id))
        .where(UmsAdminDynamicSqlSupport.id, isEqualTo(id))
        .build()
        .render(RenderingStrategies.MYBATIS3);
    return adminDao.selectWithRoleList(stmt);
}

Summary

When using MyBatis Generator (MBG), the targetRuntime setting determines the usage style. Dynamic SQL favors a Java‑API approach, while the traditional style relies on XML mapper files. MyBatis officially recommends Dynamic SQL, but the choice ultimately depends on personal preference.

References

Official documentation: https://mybatis.org/mybatis-dynamic-sql/docs/introduction.html

Project Source

https://github.com/macrozheng/mall-learning/tree/master/mall-tiny-dynamic-sql

Recommended Reading

Alibaba Completely Dismantles the Middle Platform!

Don’t Miss These Sweet Java Syntax Sugars!

What’s So Great About Lombok? SpringBoot and IDEA Officially Support It!

Free Your Hands! MyBatis Official Code Generator Is Powerful!

IDEA 2020.3 Official Release – New Features Are Awesome!

Cool Themes for IDEA – These Are Amazing!

DockerHub Slow? Build an Enterprise‑Level Image Repository!

Newcomer’s Code Style That Makes Colleagues Say “Addicted”!

40K+ Stars! Mall E‑Commerce Project Open‑Source Recap!

mall‑swarm Microservice E‑Commerce Major Update – Spring Cloud Best Practices!

Welcome to follow and give a like!