10 Common Pitfalls in MyBatis-Plus and How to Avoid Them

Preface

MyBatis-Plus has become a de‑facto standard for Java backend development; over 85% of Java projects in an Alibaba Cloud developer survey use it. It follows a "convention over configuration" philosophy, reducing simple CRUD code from about six lines to three. However, the framework is not a silver bullet and hides many pitfalls.

Pitfall 1: Pagination total count does not match actual results

Problem : Using Page for a one‑to‑many join returns a total of 300 records while the list only contains 5.

Cause : The one‑to‑many relationship expands each order into multiple rows, so the COUNT query counts the joined rows.

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

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

<!-- XML query -->
<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>

// 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 does not work

Problem : A manually written join query receives a Page parameter but returns all rows.

Cause : The PaginationInnerInterceptor identifies the Page object by its position. Wrapping it in @Param or placing it after other parameters prevents detection.

Solution : Keep Page as the first method argument and avoid @Param on it.

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

Pitfall 3: Logical delete fails on custom methods

Problem : The @TableLogic annotation works for built‑in methods like deleteById but a custom DELETE SQL physically removes rows.

Cause : The logical‑delete interceptor only applies to methods defined in BaseMapper or IService. Custom SQL does not automatically include the delete flag.

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

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

Pitfall 4: Auto‑fill does not trigger

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

Cause : Auto‑fill works only when the field is not explicitly set and the MetaObjectHandler is invoked. If the field is null and the update strategy is NOT_NULL or NOT_EMPTY, the update statement may omit the field.

Solution : Implement a MetaObjectHandler and ensure the field is not set 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 increment the version column nor perform version checks.

Cause : The version field must be of type Integer, Long, Date or Timestamp, and the update must first fetch the entity with its version.

Solution : Use a compatible type and follow a "read‑then‑write" pattern.

// Entity
@TableName("product")
public class Product {
    @TableId
    private Long id;
    private Integer stock;
    @Version
    private Long version; // Long is acceptable
}

// Update example
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 in QueryWrapper are ignored

Problem : Adding eq("name", null) yields no condition, so records with name IS NULL are not returned.

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

Solution : Use isNull("name") when the intention is to query null, or change the global strategy (not recommended).

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

Pitfall 7: Batch insert performance is poor

Problem : Inserting 10,000 rows with a loop of save or insert takes over 30 seconds.

Cause : Each insert triggers a separate DB round‑trip; saveBatch still generates single‑row statements by default.

Solution : Write a custom batch‑insert SQL using <foreach> or enable JDBC batch rewriting.

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

int insertBatch(@Param("list") List<User> userList);

Pitfall 8: Enum mapping errors

Problem : An enum field stored as an int in the database is inserted as the ordinal or throws a type‑conversion error.

Cause : MyBatis uses EnumTypeHandler, which only stores the enum name or ordinal. Custom codes (e.g., 0/1) are not mapped automatically.

Solution : Mark the field to be persisted with @EnumValue and configure the enum package.

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

Pitfall 9: Wrapper conditions are overwritten

Problem : Chaining multiple or() calls on a QueryWrapper produces unexpected parentheses, e.g., type=1 OR (type=2 OR type=3) instead of the intended (type=1 OR type=2 OR type=3).

Cause : MP does not automatically add parentheses when mixing or and and conditions.

Solution : Use nested lambda expressions with and() or or() to explicitly group conditions.

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 for JSON fields does not work

Problem : Annotating a field with @TableField(typeHandler = JacksonTypeHandler.class) fails to serialize/deserialize JSON objects.

Cause : JacksonTypeHandler requires the column to be of JSON type and the entity to have autoResultMap = true. Without proper registration or a non‑JSON column, mapping fails.

Solution : Ensure the DB column is json (MySQL) or jsonb (PostgreSQL), enable autoResultMap, and register the handler.

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

Advantages and Disadvantages of MyBatis-Plus

Advantages

High development efficiency; zero‑SQL CRUD for single tables.

Powerful condition builder for dynamic queries.

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

Active community and comprehensive documentation.

Disadvantages

Weak support for complex multi‑table joins.

Mixing custom SQL with MP methods can easily cause pitfalls.

Batch insert performance needs manual optimization.

Over‑reliance on the framework may hide essential SQL tuning.

Suitable and Unsuitable Scenarios

Suitable

New projects with simple table structures.

Micro‑services where single‑table operations dominate.

Teams that want to reduce repetitive SQL writing.

Unsuitable

Complex reporting systems with heavy multi‑table joins.

High‑performance scenarios requiring fine‑grained SQL plan optimization.

Legacy systems with massive hand‑written SQL that would be costly to migrate.

Conclusion

MyBatis-Plus is a powerful enhancement tool but not a "set‑and‑forget" framework. In practice, follow these principles:

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

Avoid join‑expanded counts in pagination.

Manually handle logical‑delete flags and pagination parameters in custom SQL.

Employ batch SQL for bulk operations.

Configure version fields, enums, and JSON columns correctly.

Pay attention to null handling and parentheses when using the condition builder.

By being aware of these pitfalls, you can write more robust and efficient code with MyBatis-Plus.