If you’re writing SQL with the XML <if> and <where> tags, you’ve probably accumulated some frustrations: no IDE autocompletion, and no way to catch mistakes in the XML side when you rename a method. MyBatis offers a mechanism to build dynamic SQL using only Java methods, without touching XML at all. This is the SqlProvider approach, using @SelectProvider / @InsertProvider / @UpdateProvider.
This article skips XML notation entirely and focuses on building dynamic SQL purely from Java code with SqlProvider, concluding with criteria for choosing between the XML and SqlProvider approaches. For how to write XML tags, see the dynamic SQL tags article; for basic Mapper setup, see the Mapper implementation guide.
Basics of @SelectProvider/@InsertProvider/@UpdateProvider
The SqlProvider annotations are a mechanism for binding a Java method that builds SQL to a Mapper method. You specify the Provider class in the type attribute and the name of the SQL-generating method in the method attribute.
public interface UserMapper {
@SelectProvider(type = UserSqlProvider.class, method = "selectActiveUsers")
List<User> selectActiveUsers(@Param("minAge") Integer minAge);
@InsertProvider(type = UserSqlProvider.class, method = "insertUser")
int insertUser(User user);
}On the Provider class side, the method simply receives the same arguments as the Mapper method and returns the assembled SQL string.
public class UserSqlProvider {
public String selectActiveUsers(Integer minAge) {
StringBuilder sql = new StringBuilder(
"SELECT * FROM users WHERE status = 'ACTIVE'");
if (minAge != null) {
sql.append(" AND age >= #{minAge}");
}
return sql.toString();
}
public String insertUser(User user) {
return "INSERT INTO users (name, email) VALUES (#{name}, #{email})";
}
}The return value can be an ordinary SQL string. Placeholders like #{minAge} are bound the same way as in XML, as long as they match the @Param name. @UpdateProvider works exactly the same way.
Omitting the method attribute with ProviderMethodResolver (MyBatis 3.5.1+)
Starting with MyBatis 3.5.1, if a Provider class implements ProviderMethodResolver, you can omit the method attribute. It automatically resolves to the method with the same name as the Mapper method, reducing method resolution failures caused by typos.
public class UserSqlProvider implements ProviderMethodResolver {
public String selectActiveUsers(Integer minAge) {
return "SELECT * FROM users WHERE status = 'ACTIVE'"
+ (minAge != null ? " AND age >= #{minAge}" : "");
}
}public interface UserMapper {
@SelectProvider(type = UserSqlProvider.class)
List<User> selectActiveUsers(@Param("minAge") Integer minAge);
}Resolution fails if method names deviate from the naming convention, such as when method names are overloaded, so it’s safest to keep a one-method-per-purpose structure. This interface doesn’t exist in 3.5.0 and earlier, so the method attribute can’t be omitted there.
Building dynamic SQL with SqlBuilder (the SQL class)
String concatenation is convenient, but it has the weakness that forgetting an AND or mistyping a space is easy to miss. Using the org.apache.ibatis.jdbc.SQL class (commonly known as SqlBuilder), you can get the same automatic leading-AND adjustment behavior as the XML <where> and <set> tags, in Java code.
import org.apache.ibatis.jdbc.SQL;
public class UserSqlProvider {
public String selectActiveUsers(final Integer minAge, final String name) {
return new SQL() {{
SELECT("*");
FROM("users");
WHERE("status = 'ACTIVE'");
if (minAge != null) {
WHERE("age >= #{minAge}");
}
if (name != null) {
WHERE("name LIKE concat('%', #{name}, '%')");
}
}}.toString();
}
}If none of the conditions are met, the WHERE clause itself isn’t output, giving nearly the same peace of mind as the XML <where> tag. For queries with few conditional branches, the StringBuilder-based concatenation shown earlier can sometimes be simpler and more readable, so choose based on complexity.
Passing multiple arguments: @Param and ProviderContext
When a Provider method takes two or more arguments, explicitly name them with @Param on the Mapper method side.
public interface UserMapper {
@UpdateProvider(type = UserSqlProvider.class, method = "updatePartial")
int updatePartial(@Param("id") Long id, @Param("user") User user);
}The Provider method’s argument names, types, and order must match the Mapper method. A mismatch here causes a method resolution error at runtime, so when using the IDE’s rename feature, change both sides at the same time.
If you need the Mapper’s class name or method name, you can receive it by adding a ProviderContext argument. It’s resolved by type, so the order doesn’t matter.
public String updatePartial(ProviderContext context, Long id, User user) {
// You can reference method information via context.getMapperMethod(), etc.
return "UPDATE users SET name = #{user.name} WHERE id = #{id}";
}Building a guarded UPDATE statement for partial updates
Partial updates that exclude null fields from the update target are a scenario where SqlProvider particularly shines.
public class UserSqlProvider {
public String updatePartial(final Long id, final User user) {
if (user.getName() == null && user.getEmail() == null) {
throw new IllegalArgumentException("No fields to update");
}
return new SQL() {{
UPDATE("users");
if (user.getName() != null) {
SET("name = #{user.name}");
}
if (user.getEmail() != null) {
SET("email = #{user.email}");
}
WHERE("id = #{id}");
}}.toString();
}
}If SET() is never called, the SQL class doesn’t throw an exception — it simply returns a string with the SET clause omitted entirely. As a result, a syntactically invalid SQL statement like UPDATE users WHERE id = ? gets passed straight to JDBC as-is, and you only notice when the database throws a syntax error — a roundabout way to fail. That’s exactly why it’s safer to guard at the top of the Provider method, rejecting the case before building the SQL.
Pitfalls to watch for during implementation
With multi-argument Provider methods, it’s easy to miss forgetting @Param, and when you do, the argument names become arg0 or param1, which don’t match the #{name} on the Provider side, causing a runtime error. Make it a habit to always add @Param whenever there are multiple arguments.
- Provider method signature mismatches occur when the number of arguments, types, or
@Paramnames on the Mapper method don’t match the Provider method, leading toBindingExceptionor method resolution errors. - Incomplete SqlBuilder assembly, as mentioned above, shows up directly as syntactically invalid SQL when you forget to call
SET/VALUES/WHERE, so it’s reassuring to actually issue the SQL in a unit test and verify it.
Criteria for choosing between XML dynamic SQL and SqlProvider
| Aspect | XML Dynamic SQL | SqlProvider |
|---|---|---|
| IDE autocompletion | Weak without a SQL extension | Java autocompletion works as-is |
| Type safety | String-based; not caught until runtime | Can be detected to some extent at compile time |
| Refactoring resilience | Doesn’t follow method renames | Follows along with IDE rename |
| SQL readability | Can be written in a form close to SQL | Embedded in Java logic, prone to getting complex |
| Good fit for | Teams that prioritize SQL review | Queries with complex conditional branching, Java-centric teams |
A common hybrid pattern is to use XML for simple CRUD and carve out only the searches with heavy conditional branching, or partial updates, into SqlProvider. There’s no need to standardize on one or the other — choosing based on each query’s complexity is sufficient.
Summary
With @SelectProvider/@InsertProvider/@UpdateProvider, you can build dynamic SQL using only Java methods, without writing XML. A practical approach is to omit the method attribute with ProviderMethodResolver, use SqlBuilder when conditional branching gets complex, and use plain string concatenation for simpler cases.
If you’d like to revisit how to write XML dynamic SQL, check out the dynamic SQL tags article, and if you want to get back to Mapper basics, the Mapper implementation guide is also available.
