MyBatis Dynamic SQL is a type-safe Java DSL (Domain-Specific Language) for building SQL queries programmatically instead of writing SQL strings or XML-based dynamic queries. It generates SQL at runtime using fluent Java builders while still executing through standard MyBatis mappers. This makes query construction safer, easier to refactor, and less error-prone compared to manual string concatenation or complex XML logic.
Because the queries are written in Java, column names and table references are validated at compile time through strongly typed metadata classes, providing better IDE support and reducing runtime SQL errors. This article explains MyBatis Dynamic SQL and shows how it can be used in a Java application.
1. What Can You Do with MyBatis Dynamic SQL?
MyBatis Dynamic SQL supports most common SQL operations, including SELECT, INSERT, UPDATE, and DELETE, as well as joins, subqueries, pagination, sorting, conditional filters, and batch operations. It allows us to build queries incrementally, adding conditions only when certain parameters are present, which is ideal for search screens and filtering APIs.
It integrates directly with MyBatis mapper interfaces, meaning we still benefit from result mapping, transaction handling, and connection management. Since it produces standard SQL, it works with any database supported by MyBatis without vendor lock-in.
1.1 How MyBatis Dynamic SQL Works
Dynamic SQL is based on two components: table metadata classes and DSL builders. The metadata classes describe tables and columns in Java. The DSL builders use these classes to assemble SQL statements in a fluent, type-safe way.
The DSL does not execute SQL directly. Instead, it produces statement provider objects such as SelectStatementProvider or InsertStatementProvider. These are passed to mapper methods annotated with @SelectProvider, @InsertProvider, and similar annotations, which MyBatis then executes using its normal execution engine.
2. Project Setup and Dependencies
Maven Dependencies
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.15</version>
</dependency>
<dependency>
<groupId>org.mybatis.dynamic-sql</groupId>
<artifactId>mybatis-dynamic-sql</artifactId>
<version>1.5.2</version>
</dependency>
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<version>2.4.240</version>
<scope>runtime</scope>
</dependency>
These dependencies include MyBatis itself, the Dynamic SQL DSL, and an embedded database for testing.
The database driver can be replaced with MySQL, PostgreSQL, or any other supported database. MyBatis Dynamic SQL does not depend on the database type, only on standard SQL generation.
Database Schema
schema.sql
CREATE TABLE users (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100),
age INT
);
INSERT INTO users(username, email, age) VALUES
('thomas', 'thomas@jcg.com', 30),
('benjamin', 'benjamin@jcg.com', 22),
('charles', 'charles@jcg.com', 17);
This script creates a simple table and inserts test data. The in-memory H2 database will execute this at startup, so queries can be tested without external dependencies.
Domain Model
public class User {
private Long id;
private String username;
private String email;
private Integer age;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
}
This POJO represents a database row. MyBatis automatically maps columns to fields using matching names, so no additional result mapping is required for this example.
3. Table Metadata for Dynamic SQL
The class below defines the database table and its columns in a type-safe manner, allowing them to be referenced by the Dynamic SQL DSL when building queries. It acts as the bridge between Java code and the actual database structure, enabling compile-time validation of column names and types.
public final class UserDynamicSqlSupport {
public static final User user = new User();
public static final SqlColumn<Long> id = user.id;
public static final SqlColumn<String> username = user.username;
public static final SqlColumn<String> email = user.email;
public static final SqlColumn<Integer> age = user.age;
public static final class User extends SqlTable {
public final SqlColumn<Long> id = column("id", JDBCType.BIGINT);
public final SqlColumn<String> username = column("username", JDBCType.VARCHAR);
public final SqlColumn<String> email = column("email", JDBCType.VARCHAR);
public final SqlColumn<Integer> age = column("age", JDBCType.INTEGER);
public User() {
super("users");
}
}
}
This class defines type-safe metadata for the users table so MyBatis Dynamic SQL can build queries without using raw SQL strings. Instead of referring to columns by name, the DSL uses these Java objects, which improves safety and IDE support.
The inner User class extends SqlTable, which marks it as a database table that can be used in clauses like from(user) and joins. The constructor calls super("users") to tell MyBatis the exact table name to render in SQL statements, such as FROM users.
Each column is defined using the column() method from SqlTable, which registers the column name and its JDBC type. This produces strongly typed SqlColumn<T> objects, ensuring that comparisons and conditions use the correct Java types at compile time.
The outer class exposes static references to both the table and its columns for easy static imports, allowing queries to read naturally, for example: select(id, username).from(user), while remaining fully type-safe and refactor-friendly.
Mapper Interface
@Mapper
public interface UserMapper {
@SelectProvider(type = SqlProviderAdapter.class, method = "select")
List<User> selectMany(SelectStatementProvider selectStatement);
}
The @Mapper annotation tells MyBatis that this interface should be registered as a mapper and proxied at runtime. MyBatis generates an implementation automatically, so no concrete class is required.
The selectMany method accepts a SelectStatementProvider, which encapsulates the fully rendered SQL statement and its parameters. MyBatis executes that statement and maps each result row to a User object, returning them as a List<User>.
The @SelectProvider annotation specifies that the SQL will be supplied dynamically by SqlProviderAdapter, which is part of MyBatis Dynamic SQL. Instead of writing SQL in annotations or XML, the actual SQL is produced at runtime from the SelectStatementProvider built using the DSL.
4. Building Dynamic Queries
Here, we construct SQL statements using the fluent Dynamic SQL DSL instead of writing raw SQL strings.
public static void main(String[] args) throws Exception {
MyBatisUtil.runSchema();
try (SqlSession session = MyBatisUtil.getSession()) {
UserMapper mapper = session.getMapper(UserMapper.class);
SelectStatementProvider select
= select(id, username, email, age)
.from(user)
.where(age, isGreaterThan(18))
.and(username, isLike("%tho%"))
.orderBy(username)
.build()
.render(RenderingStrategies.MYBATIS3);
List<User> users = mapper.selectMany(select);
users.forEach(u
-> System.out.println(u.getUsername() + " - " + u.getAge()));
}
}
This code constructs a SELECT query dynamically using the fluent Dynamic SQL DSL and renders it into a MyBatis-compatible statement provider. By adding conditions programmatically, it enables the creation of complex filters in a type-safe and maintainable way. In this example, the query selects users whose age is greater than 18 and whose username contains “tho”, then orders the results alphabetically by username.
MyBatis Utility Class
public class MyBatisUtil {
private static SqlSessionFactory factory;
static {
try {
Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
factory = new SqlSessionFactoryBuilder().build(reader);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
public static SqlSession getSession() {
return factory.openSession(true);
}
public static void runSchema() throws IOException, SQLException {
try (SqlSession session = getSession()) {
Connection conn = session.getConnection();
Statement stmt = conn.createStatement();
try (InputStream is = Resources.getResourceAsStream("schema.sql")) {
String sql = new String(is.readAllBytes(), StandardCharsets.UTF_8);
stmt.execute(sql);
}
}
}
}
This utility class loads MyBatis configuration, builds the SqlSessionFactory, and provides access to database sessions. It also initialises the database schema manually by executing the SQL script (schema.sql).
5. Insert, Update, and Delete with Dynamic SQL
Dynamic SQL in MyBatis allows us to construct INSERT, UPDATE, and DELETE statements programmatically using the fluent DSL. Here, we demonstrate how to perform these common data manipulation operations.
// INSERT
User newUser = new User();
newUser.setUsername("andrew");
newUser.setEmail("andrew@jcg.com");
newUser.setAge(28);
InsertStatementProvider<User> insert
= insert(newUser)
.into(user)
.map(username).toProperty("username")
.map(email).toProperty("email")
.map(age).toProperty("age")
.build()
.render(RenderingStrategies.MYBATIS3);
int inserted = mapper.insert(insert);
System.out.println("Rows inserted: " + inserted);
// UPDATE
UpdateStatementProvider update
= update(user)
.set(age).equalTo(35)
.where(username, isEqualTo("thomas"))
.build()
.render(RenderingStrategies.MYBATIS3);
int updated = mapper.update(update);
System.out.println("Rows updated: " + updated);
// DELETE
DeleteStatementProvider delete
= deleteFrom(user)
.where(age, isLessThan(18))
.build()
.render(RenderingStrategies.MYBATIS3);
int deleted = mapper.delete(delete);
System.out.println("Rows deleted: " + deleted);
The same DSL style is used for write operations. Statements are built fluently, rendered, and then executed by mapper provider methods.
- INSERT: A new
Userobject is created and populated with values. Using the Dynamic SQL DSL, we map its fields to table columns and generate anInsertStatementProvider. The mapper executes the insert, returning the number of affected rows. - UPDATE: The DSL builds an update statement that sets
age = 35for the user whose username is “thomas”. This ensures only the intended row is modified, and the mapper executes the update. - DELETE: The delete statement removes all users with
age < 18. Using a condition in the DSL guarantees type safety and avoids string concatenation.
Updated Mapper Interface
To support these operations, the mapper interface must include methods for INSERT, UPDATE, and DELETE using MyBatis Dynamic SQL providers.
// INSERT
@InsertProvider(type = SqlProviderAdapter.class, method = "insert")
int insert(InsertStatementProvider<User> insertStatement);
// UPDATE
@UpdateProvider(type = SqlProviderAdapter.class, method = "update")
int update(UpdateStatementProvider updateStatement);
// DELETE
@DeleteProvider(type = SqlProviderAdapter.class, method = "delete")
int delete(DeleteStatementProvider deleteStatement);
Each method in the mapper handles a specific DML operation, insert, update, or delete, and accepts an InsertStatementProvider, UpdateStatementProvider, or DeleteStatementProvider that encapsulates the generated SQL and its parameters. This approach allows all write operations to be expressed programmatically in Java without manually composing SQL strings, while still leveraging MyBatis to execute the statements and map results efficiently.
6. Conclusion
In this article, we explored how to use MyBatis Dynamic SQL in a Java application to create type-safe, maintainable, and programmatic SQL queries. By separating SQL construction from execution, MyBatis Dynamic SQL simplifies the handling of complex query logic, reduces the risk of errors, and improves code readability. This approach is ideal for applications where queries need to be dynamic or frequently modified.
7. Download the Source Code
This article discussed MyBatis Dynamic SQL and how to use it in Java.
You can download the full source code of this example here: java mybatis dynamic sql
