MyBatis-Plus can significantly improve Java single-table CRUD productivity, but it often introduces hidden errors in scenarios such as paginated joins, logical delete, and automatic field filling. This article distills 10 high-frequency pitfalls and proven fixes to help teams build a more robust data access layer. Keywords: MyBatis-Plus, pagination plugin, logical delete.
Technical Specification Snapshot
| Parameter | Details |
|---|---|
| Core Technology | MyBatis-Plus |
| Language | Java |
| Typical Protocols | JDBC / SQL |
| Article Focus | Pitfall avoidance in an enhanced ORM framework |
| Community Adoption Pattern | Widely used in Java projects |
| Core Dependencies | mybatis-plus-boot-starter, MyBatis, database driver |
MyBatis-Plus delivers high productivity, but its real complexity is easy to overlook
The value of MyBatis-Plus lies in reducing boilerplate SQL, standardizing CRUD patterns, and providing built-in pagination and logical delete capabilities. It is especially well suited for single-table operations in small and medium-sized business systems, and it can quickly support early-stage microservice development.
But the same strength also creates problems: “convention over configuration.” Many features only work with built-in methods. Once you move into custom SQL, complex joins, batch processing, or special field mappings, you must understand its interceptor behavior, parameter recognition rules, and SQL generation boundaries again.
A minimal paginated interface example
public interface UserMapper extends BaseMapper
<User> {
// Page must be the first parameter so the pagination plugin can detect it correctly
Page
<User> selectByAge(Page<User> page, @Param("age") Integer age);
}This code shows the key prerequisite for the pagination plugin to work: the Page parameter must be placed correctly.
Pagination and join queries are the easiest places to get burned
The first category of problems is incorrect total counts. A one-to-many join expands one primary-table row into multiple result rows. When the pagination plugin executes COUNT, it counts the expanded result set, which leads to misleading results such as “5 rows in the list, but 300 rows in total.”
The fix is not to keep stacking joins. Instead, query the primary table first and then fetch child records separately, or use a subquery to aggregate child items so the primary-table row count does not get inflated.
<select id="selectOrderPage" resultMap="OrderWithItemMap">
SELECT o.*,
(SELECT JSON_ARRAYAGG(item_name) FROM order_item WHERE order_id = o.id) AS item_names
FROM orders o
WHERE o.user_id = #{userId}
ORDER BY o.create_time DESC
</select>This SQL uses a subquery to aggregate child-table fields and avoids incorrect total counts in one-to-many pagination.
The second category is pagination plugin failure. The root cause is usually that Page is wrapped by @Param, placed in the wrong argument position, or used with handwritten SQL under the false assumption that pagination will always be appended automatically. The practical rule is simple: in custom paginated methods, always put Page in the first parameter position.
Logical delete and auto-fill do not work unconditionally
@TableLogic only works along MyBatis-Plus convention-based method paths. If you write a DELETE statement manually, the framework will not convert it into a logical delete for you. A physical delete will happen directly.
Likewise, automatic field filling depends on the interaction between MetaObjectHandler and field strategies. If the entity field strategy does not allow updates, or if the filler is not implemented correctly, updateTime will not be updated even if you configured FieldFill.INSERT_UPDATE.
The correct way to implement logical delete
@Delete("update user set deleted = 1 where age > #{age} and deleted = 0")
int logicDeleteByAge(@Param("age") Integer age);This code demonstrates that a custom delete must be explicitly rewritten as an update to the delete marker.
An example auto-fill handler
@Component
public class MyMetaObjectHandler implements MetaObjectHandler {
@Override
public void insertFill(MetaObject metaObject) {
// Fill the creation time during insert
this.strictInsertFill(metaObject, "createTime", LocalDateTime.class, LocalDateTime.now());
// Fill the update time during insert
this.strictInsertFill(metaObject, "updateTime", LocalDateTime.class, LocalDateTime.now());
}
@Override
public void updateFill(MetaObject metaObject) {
// Refresh the update time during update
this.strictUpdateFill(metaObject, "updateTime", LocalDateTime.class, LocalDateTime.now());
}
}This handler automatically maintains timestamp fields during insert and update operations.
Optimistic locking, null conditions, and parenthesis precedence determine data correctness
When optimistic locking fails, the problem usually is not that the plugin is missing. The problem is the update path. You must first query the entity with its version number and then perform the update. Only then can MyBatis-Plus generate the version = oldVersion check and increment the version.
null conditions in QueryWrapper are also frequently misunderstood. eq("name", null) does not mean IS NULL; in most cases, it is ignored. To query null values, you must explicitly use isNull.
if (name == null) {
// Explicitly generate an is null condition
wrapper.isNull("name");
} else {
// Generate an equality match when the value is not null
wrapper.eq("name", name);
}This code avoids missing rows caused by incorrect null-handling behavior.
Another common pitfall is chained or() conditions. If you do not add parentheses manually in complex conditions, SQL operator precedence can drift away from your intent and distort the final filter result.
Batch writes, enum mapping, and JSON field handling require extra design work
Loop-based insert calls are a classic performance trap. Writing 10,000 rows one by one amplifies network round trips and transaction overhead. A safer approach is to generate a multi-value batch insert with `