10 Common MyBatis-Plus Pitfalls and How to Avoid Them

Pitfall 1: Pagination total count mismatch

When a one‑to‑many join is used, the COUNT query runs on the joined result, inflating the total rows (e.g., 5 records appear but the total shows 300).

Solution: Paginate the main table first, then fetch child data with a sub‑query or a two‑step approach.

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

Page<Order> page = new Page<>(1, 10);
orderMapper.selectOrderPage(page, userId);
// Expected total = 3, actual total = 9 because each order has 3 items

<!-- Correct SQL -->
<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>

Pitfall 2: Pagination interceptor not applied

If the Page object is wrapped inside @Param or placed after other parameters, the PaginationInnerInterceptor cannot detect it, so the query returns all rows.

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

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

Page<User> page = new Page<>(1, 10);
userMapper.selectByAge(page, 18); // returns only 10 rows

Pitfall 3: Logical delete does not work for custom SQL

The @TableLogic annotation only affects built‑in methods of BaseMapper and IService. Custom DELETE statements bypass the logical‑delete filter.

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 fields are not updated

Fields annotated with @TableField(fill = FieldFill.INSERT_UPDATE) are not populated if the entity does not set the field value and the update strategy is NOT_NULL or NOT_EMPTY.

Solution: Implement a MetaObjectHandler and ensure the field strategy allows filling, or set the field to null before update.

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

The version field must be of type Integer, Long, Date or Timestamp. Using an incompatible type (e.g., String) or updating without first selecting the record disables the lock.

Solution: Use a compatible type and follow the "select‑then‑update" pattern, checking the affected row count.

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

Product p = productMapper.selectById(1L);
p.setStock(p.getStock() - 1);
int rows = productMapper.updateById(p);
if (rows == 0) {
    throw new OptimisticLockException("Concurrent update detected");
}

Pitfall 6: null values are ignored in QueryWrapper

By default the field strategy is NOT_NULL, so a condition like eq("name", null) is omitted instead of generating WHERE name IS NULL.

Solution: Use isNull / isNotNull explicitly, 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

Calling save or insert inside a loop issues a separate SQL statement for each record, causing high latency.

Solution: Use a custom <foreach> batch insert or saveBatch with JDBC parameter rewriteBatchedStatements=true.

<!-- MyBatis 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

MyBatis uses EnumTypeHandler which stores the enum name or ordinal. Without configuration, values stored as int or string may not map correctly.

Solution: Annotate the enum field with @EnumValue to indicate the database value and configure the package to scan for enums.

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

public class User {
    private StatusEnum status;
}

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

Pitfall 9: Wrapper conditions are overwritten due to missing parentheses

Chaining or() without explicit grouping can produce unexpected SQL precedence.

Solution: Use nested lambda expressions with and() or or() to enforce parentheses.

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: JSON type handler does not work

When a field is annotated with @TableField(typeHandler = JacksonTypeHandler.class), the database column must be of JSON type and autoResultMap must be enabled; otherwise conversion fails.

Solution: Use a JSON column (MySQL json or PostgreSQL jsonb) and enable autoResultMap, or switch to FastjsonTypeHandler with the proper dependency.

@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; single‑table CRUD requires almost no SQL.

Powerful condition builder for dynamic queries.

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

Active community and comprehensive documentation.

Disadvantages

Complex multi‑table joins are not well supported.

Mixing custom SQL with built‑in methods can easily cause pitfalls.

Batch operations need manual optimization.

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

When to Use MyBatis‑Plus

New projects with simple tables that need rapid development.

Micro‑services where single‑table operations dominate.

Teams that want to reduce repetitive SQL writing.

When Not to Use MyBatis‑Plus

Complex reporting or analytics requiring heavy multi‑table joins.

High‑performance scenarios that demand fine‑grained SQL tuning.

Legacy systems with massive hand‑written SQL where migration cost is high.

Key Takeaways

Use MyBatis‑Plus for simple single‑table CRUD; rely on native MyBatis XML for complex queries.

When paginating, avoid joins that duplicate rows or use sub‑queries.

Handle logical delete and pagination parameters manually in custom SQL.

Use batch SQL for bulk operations.

Configure version fields, enums, and JSON handlers correctly.

Be aware of null handling and parentheses in QueryWrapper.