10 Common MyBatis‑Plus Pitfalls and How to Avoid Them

Pagination total count mismatch

Problem : Using Page for a one‑to‑many join returns the correct rows but inflates the total count because each parent row is duplicated for each child row.

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

<!-- XML mapper (incorrect) -->
<select id="selectOrderPage" resultType="com.example.Order">
    SELECT o.*, oi.item_name
    FROM orders o
    LEFT JOIN order_item oi ON o.id = oi.order_id
    WHERE o.user_id = #{userId}
</select>

Page<Order> page = new Page<>(1, 10);
orderMapper.selectOrderPage(page, userId);
// Expected total 3, actual total 9 due to Cartesian product

Cause : The pagination interceptor counts rows after the join, so the duplicate child rows increase the total.

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

<!-- Correct: paginate main table first -->
<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 version
Page<Long> idPage = new Page<>(1, 10);
baseMapper.selectPageIds(idPage, userId);
List<Order> orders = orderService.listByIds(idPage.getRecords());

Pagination plugin not applied

Problem : A manually written join query receives a Page parameter but returns all rows because the interceptor cannot locate the Page object.

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

Cause : PaginationInnerInterceptor identifies the Page argument by its position and name. Wrapping it with @Param or placing it after other parameters prevents detection.

Solution : Declare Page as the first method argument and do not annotate it with @Param.

@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 10 rows

Logical delete not applied to custom methods

Problem : @TableLogic updates the delete flag for built‑in methods, but a custom @Delete SQL physically removes rows.

@TableName("user")
public class User {
    @TableId
    private Long id;
    private String name;
    @TableLogic
    private Integer deleted; // 0 = not deleted, 1 = deleted
}

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

Cause : The logical‑delete interceptor only applies to methods defined in BaseMapper (e.g., deleteById, deleteBatchIds, updateById) and to IService.remove implementations.

Solution : Add the delete‑flag condition manually in custom SQL or use the logical‑delete methods.

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

@Select("select * from user where age > #{age} and deleted = 0")
List<User> selectActiveByAge(@Param("age") Integer age);

Auto‑fill not triggered

Problem : An entity field annotated with @TableField(fill = FieldFill.INSERT_UPDATE) does not get updated when updateById is called.

@Entity
public class Order {
    @TableField(fill = FieldFill.INSERT_UPDATE)
    private LocalDateTime updateTime;
}

Order order = new Order();
order.setId(1L);
order.setStatus("PAID");
orderMapper.updateById(order); // updateTime unchanged

Cause : Auto‑fill works only when the field is null and the update strategy permits filling (e.g., update = "NOW()").

Solution :

Implement MetaObjectHandler to set createTime and updateTime on insert and update.

Ensure the field’s update strategy allows filling.

Do not set the 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());
    }
}

Optimistic lock fails

Problem : Using @Version on a Long field does not increment the version nor perform version comparison during update.

@TableName("product")
public class Product {
    @TableId
    private Long id;
    private Integer stock;
    @Version
    private Long version; // supported types: Integer, Long, Date, Timestamp
}

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

Cause : The optimistic‑lock interceptor requires a compatible version field type and a “read‑modify‑write” flow (select first, then update).

Solution : Use a supported type (e.g., Long) and follow the select‑then‑update pattern, handling the case where updateById returns 0 rows.

Null values ignored in QueryWrapper

Problem : Adding eq("name", null) does not generate WHERE name IS NULL; the condition is omitted.

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

Cause : The default field strategy is NOT_NULL, which skips conditions with null values.

Solution : Use isNull explicitly or handle the null case in code.

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

Batch insert performance

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

for (User user : userList) {
    userMapper.insert(user); // One DB round‑trip per row
}

Cause : Each insert triggers a separate JDBC round‑trip and transaction overhead.

Solution : Use a custom batch insert with <foreach> to generate a single multi‑value INSERT statement, or use saveBatch with the JDBC URL parameter rewriteBatchedStatements=true (MySQL).

<!-- 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);

Enum mapping errors

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

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

@TableName("user")
public class User {
    private StatusEnum status; // No mapping configuration
}

Cause : MyBatis uses EnumTypeHandler, which only stores the enum name or ordinal.

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

public enum StatusEnum {
    @EnumValue
    private final int code;
    private final String desc;
    // constructor, getters
}

# application.yml
mybatis-plus:
  type-enums-package: com.example.enums

Wrapper conditions overwritten by AND/OR precedence

Problem : Chaining multiple or() calls produces 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);
// Generates wrong precedence

Cause : The condition builder does not automatically add parentheses for mixed and / or chains.

Solution : Use nested lambda expressions with and (or or) 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)

Type handler not effective for JSON fields

Problem : A field annotated with @TableField(typeHandler = JacksonTypeHandler.class) fails to serialize/deserialize a complex object.

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

Cause : JacksonTypeHandler requires the database column to be of JSON type and the generic type to be known.

Solution :

Ensure the column type is JSON (MySQL 5.7+) or jsonb (PostgreSQL).

Enable autoResultMap = true on the entity.

Register the handler (MyBatis‑Plus 3.5+ includes it automatically).

MyBatis‑Plus advantages, disadvantages, and suitable scenarios

Advantages

High development speed; single‑table CRUD without writing SQL.

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 may hide underlying SQL performance issues.

Suitable scenarios

New projects with simple table structures needing rapid development.

Micro‑services where single‑table operations dominate.

Teams that want to reduce repetitive SQL writing.

Unsuitable scenarios

Complex reporting systems with heavy multi‑table joins.

High‑performance workloads that require fine‑tuned SQL execution plans.

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

Conclusion

MyBatis‑Plus is a powerful enhancement tool, but it is not a mindless framework. Follow these principles in real projects:

Use MP for simple single‑table CRUD; rely on raw MyBatis XML for complex queries.

Avoid join‑expanded totals in pagination.

Manually handle logical delete 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 building conditions.