10 Common MyBatis-Plus Pitfalls and How to Avoid Them

Introduction

MyBatis-Plus has become a de‑facto standard for Java backend development, offering a "convention over configuration" approach that reduces a typical single‑table CRUD from six lines of code to about three. However, many developers treat the framework as a silver bullet and overlook hidden traps that can cause subtle bugs or severe performance degradation.

Pitfall 1: Pagination Total Count Mismatch

Problem : Using Page for a one‑to‑many join returns a total count far larger than the actual number of rows (e.g., 5 records but total shows 300).

public interface OrderMapper extends BaseMapper<Order> {
    // Directly join OrderItem
    Page<Order> selectOrderPage(Page<Order> page, @Param("userId") Long userId);
}

SELECT o.*, oi.item_name
FROM orders o
LEFT JOIN order_item oi ON o.id = oi.order_id
WHERE o.user_id = #{userId}

Cause : The COUNT query counts the Cartesian product generated by the one‑to‑many join, inflating the total.

Solution : First paginate the main table, then fetch child data via a sub‑query or a two‑step approach.

Page<Long> idPage = new Page<>(1, 10);
baseMapper.selectPageIds(idPage, userId);
List<Order> orders = orderService.listByIds(idPage.getRecords());

Pitfall 2: Pagination Plugin Not Working

Problem : Passing a Page object wrapped in @Param or placed after other parameters prevents the pagination interceptor from intercepting the query, returning all rows.

@Select("select * from user where age > #{age}")
Page<User> selectByAge(@Param("age") Integer age, @Param("page") Page<User> page);

Cause : PaginationInnerInterceptor only recognizes Page when it is the first method argument and not wrapped by @Param.

Solution : Declare Page as the first parameter without @Param.

@Select("select * from user where age > #{age}")
Page<User> selectByAge(Page<User> page, @Param("age") Integer age);

Pitfall 3: Logical Delete Fails

Problem : The @TableLogic annotation works for built‑in methods like deleteById, but custom delete statements physically remove rows.

@TableLogic
private Integer deleted; // 0 = not deleted, 1 = deleted

@Delete("delete from user where age > #{age}")
int deleteByAge(@Param("age") Integer age);

Cause : Logical‑delete interceptors only apply to methods defined in BaseMapper or IService. Custom SQL does not automatically include the deleted = 0 condition.

Solution : Manually add the logical‑delete condition in custom statements.

@Delete("update user set deleted = 1 where age > #{age} and deleted = 0")
int logicDeleteByAge(@Param("age") Integer age);

Pitfall 4: Auto‑Fill Not Working

Problem : Fields annotated with @TableField(fill = FieldFill.INSERT_UPDATE) are not automatically updated when calling updateById.

@TableField(fill = FieldFill.INSERT_UPDATE)
private LocalDateTime updateTime;

Cause : Auto‑fill only triggers when the field is null and the update strategy is NOT_NULL or NOT_EMPTY. If the field is explicitly set (even to null), the interceptor skips it.

Solution :

Implement a MetaObjectHandler to define fill logic.

Ensure the field’s update strategy allows filling (e.g., remove update = "NOW()" if not needed).

Do not set the auto‑fill field manually before calling updateById.

@Component
public class MyMetaObjectHandler implements MetaObjectHandler {
    @Override
    public void insertFill(MetaObject metaObject) {
        this.strictInsertFill(metaObject, "createTime", LocalDateTime.class, LocalDateTime.now());
        this.strictInsertFill(metaObject, "updateTime", LocalDateTime.class, LocalDateTime.now());
    }
    @Override
    public void updateFill(MetaObject metaObject) {
        this.strictUpdateFill(metaObject, "updateTime", LocalDateTime.class, LocalDateTime.now());
    }
}

Pitfall 5: Optimistic Lock Ineffective

Problem : Using @Version does not automatically increment the version column nor perform version comparison during updates.

@Version
private Long version; // must be Integer, Long, Date, or Timestamp

Cause : The optimistic‑lock plugin only works with compatible version field types and requires the entity to be fetched first so that the original version is known.

Solution : Use a compatible type (Integer/Long/Date/Timestamp) and follow the "read‑modify‑write" pattern.

Product p = productMapper.selectById(1L);
 p.setStock(p.getStock() - 1);
 int rows = productMapper.updateById(p);
 if (rows == 0) {
     throw new OptimisticLockException("Operation conflict, please retry");
 }

Pitfall 6: Null Values Ignored in QueryWrapper

Problem : Adding eq("name", null) generates no condition, causing records with name IS NULL to be missed.

String name = null;
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.eq("name", name);
List<User> users = userMapper.selectList(wrapper);

Cause : The default field strategy is NOT_NULL, which skips null parameters.

Solution :

Use isNull("name") when you intend to query null values.

Or change the global field strategy to include nulls (not recommended).

if (name == null) {
    wrapper.isNull("name");
} else {
    wrapper.eq("name", name);
}

Pitfall 7: Batch Insert Performance Is Terrible

Problem : Looping save or insert for thousands of rows takes tens of seconds.

for (User user : userList) {
    userMapper.insert(user);
}

Cause : Each insert triggers a separate database round‑trip. saveBatch still issues single‑row inserts by default.

Solution :

Write a custom <foreach> batch‑insert SQL that generates a single multi‑value INSERT statement.

Or use saveBatch with the JDBC parameter rewriteBatchedStatements=true (MySQL) to enable driver‑level batch execution.

<insert id="insertBatch">
INSERT INTO user (name, age) VALUES
<foreach collection="list" item="item" separator=",">
    (#{item.name}, #{item.age})
</foreach>
</insert>

Pitfall 8: Enum Mapping Errors

Problem : Enum fields stored as integers or strings are not correctly mapped, leading to wrong values or conversion exceptions.

public enum StatusEnum {
    NORMAL(0, "正常"),
    DISABLED(1, "禁用");
    @EnumValue
    private final int code;
    private final String desc;
}

private StatusEnum status;

Cause : MyBatis’s default EnumTypeHandler only stores the enum name or ordinal. Without configuration, custom codes (0/1) are not persisted correctly.

Solution :

Annotate the field that should be persisted with @EnumValue.

Configure the enum package in mybatis-plus.type-enums-package so that MP can scan it.

mybatis-plus:
  type-enums-package: com.example.enums

Pitfall 9: Wrapper Conditions Overridden by AND/OR Precedence

Problem : Chaining multiple or() calls may produce unexpected SQL grouping, e.g., (type=1) OR (type=2 OR type=3) instead of (type=1 OR type=2 OR type=3).

wrapper.eq("type",1).or().eq("type",2).or().eq("type",3);

Cause : MP does not automatically add parentheses around chained or conditions, causing precedence issues with subsequent and clauses.

Solution : Use nested lambda syntax to enforce grouping.

wrapper.and(w -> w.eq("type",1).or().eq("type",2).or().eq("type",3));
// Generates (type = 1 OR type = 2 OR type = 3)

Pitfall 10: TypeHandler Not Effective for JSON Fields

Problem : Configuring @TableField(typeHandler = JacksonTypeHandler.class) on a JSON column fails to serialize/deserialize the object.

@TableField(typeHandler = JacksonTypeHandler.class)
private Address address;

Cause : JacksonTypeHandler requires the column to be of a JSON type and the entity to have autoResultMap=true so that MyBatis can map the custom handler.

Solution :

Use a JSON column type (e.g., json in MySQL 5.7+ or jsonb in PostgreSQL).

Enable autoResultMap=true on the entity’s @TableName.

Ensure the handler is registered (MyBatis‑Plus 3.5.0+ registers JacksonTypeHandler automatically).

@TableName(value="user", autoResultMap=true)
public class User {
    @TableField(typeHandler = JacksonTypeHandler.class)
    private Address address;
}

MyBatis-Plus Pros, Cons, and Suitable Scenarios

Advantages :

High development efficiency; single‑table CRUD requires no SQL.

Powerful condition builder for dynamic queries.

Built‑in pagination, optimistic lock, logical delete, etc.

Active community and comprehensive documentation.

Disadvantages :

Limited support for complex multi‑table joins.

Mixing custom SQL with MP methods can easily cause pitfalls.

Batch operations need manual optimization.

Over‑reliance on the framework may hide underlying SQL performance issues.

Suitable Scenarios :

New projects with simple table structures.

Micro‑services where single‑table operations dominate.

Teams that want to reduce repetitive SQL writing.

Unsuitable Scenarios :

Complex reporting systems requiring heavy multi‑table joins.

High‑performance workloads that need fine‑grained SQL tuning.

Legacy systems with extensive hand‑written SQL (migration cost is high).

Conclusion

MyBatis-Plus is a powerful enhancement tool, but it is not a "mindless" framework. Follow these principles:

Use MP for simple single‑table CRUD; resort to native MyBatis XML for complex queries.

Avoid pagination on joined tables without pre‑filtering.

When writing custom SQL, handle logical delete and pagination parameters yourself.

Employ true batch SQL for bulk operations.

Configure version fields, enums, and JSON columns correctly.

Pay attention to null handling and parentheses in QueryWrapper.

By understanding and avoiding these ten pitfalls, you can write more robust, maintainable, and high‑performance code with MyBatis-Plus.