12 MyBatis‑Plus Tricks That Instantly Boost Development Efficiency

Preface

Using MyBatis can feel like cooking a bland soup, but MyBatis‑Plus acts like a master chef, handling the tedious preparation so that the resulting code is smooth, efficient, and flavorful.

Avoid Using isNull Checks

// ❌ Not recommended
LambdaQueryWrapper<User> wrapper1 = new LambdaQueryWrapper<>();
wrapper1.isNull(User::getStatus);

// ✅ Recommended: use explicit default value
LambdaQueryWrapper<User> wrapper2 = new LambdaQueryWrapper<>();
wrapper2.eq(User::getStatus, UserStatusEnum.INACTIVE.getCode());

Improves readability and maintainability.

NULL values invalidate indexes, preventing MySQL optimization.

Comparing NULL adds CPU overhead.

NULL consumes extra storage, affecting compression.

Specify Select Fields Explicitly

// ❌ Not recommended – selects all columns
List<User> users1 = userMapper.selectList(null);

// ✅ Recommended – select only needed columns
LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
wrapper.select(User::getId, User::getName, User::getAge);
List<User> users2 = userMapper.selectList(wrapper);

Avoids unnecessary network transfer.

Enables index‑only scans, avoiding table look‑ups.

Reduces parsing and serialization load.

Lowers memory usage for large result sets.

Batch Operations Instead of Loops

// ❌ Not recommended
for (User user : userList) {
    userMapper.insert(user);
}

// ✅ Recommended
userService.saveBatch(userList, 100); // process 100 records per batch

// ✅ Better: custom batch size constant
userService.saveBatch(userList, BatchConstants.BATCH_SIZE);

Reduces connection creation/destruction overhead.

Executes within a single transaction, improving consistency.

Database can optimise batch execution plans.

Significantly cuts network round‑trips, raising throughput.

EXISTS Subquery

// ❌ Not recommended
wrapper.inSql("user_id", "select user_id from order where amount > 1000");

// ✅ Recommended
wrapper.exists("select 1 from order where order.user_id = user.id and amount > 1000");

// ✅ Better: LambdaQueryWrapper
wrapper.exists(orderService.lambdaQuery()
    .gt(Order::getAmount, 1000)
    .apply("order.user_id = user.id"));

EXISTS uses indexes and stops after the first match.

IN subquery loads all data into memory before comparison.

Performance advantage grows with larger outer tables.

Use orderBy Instead of last

// ❌ Not recommended: SQL injection risk
wrapper.last("ORDER BY " + sortField + " " + sortOrder);

// ❌ Not recommended: direct string concat
wrapper.last("ORDER BY FIELD(status, 'active', 'pending', 'inactive')");

// ✅ Recommended: safe Lambda ordering
wrapper.orderBy(true, true, User::getStatus);

// ✅ Multi‑field example
wrapper.orderByAsc(User::getStatus)
       .orderByDesc(User::getCreateTime);

String concatenation can lead to SQL injection.

Dynamic SQL may break statement semantics.

Reduces maintainability and readability.

Bypasses MyBatis‑Plus safety checks.

LambdaQuery for Type Safety

// ❌ Not recommended: possible field omission after changes
QueryWrapper<User> wrapper1 = new QueryWrapper<>();
wrapper1.eq("name", "张三").gt("age", 18);

// ✅ Recommended
LambdaQueryWrapper<User> wrapper2 = new LambdaQueryWrapper<>();
wrapper2.eq(User::getName, "张三")
        .gt(User::getAge, 18);

// ✅ Better: chain call
userService.lambdaQuery()
    .eq(User::getName, "张三")
    .gt(User::getAge, 18)
    .list();

Compile‑time checks prevent misspelled column names.

IDE offers better code completion.

Refactoring updates field references automatically.

Improves readability and maintainability.

Use between Instead of ge/le

// ❌ Not recommended
wrapper.ge(User::getAge, 18).le(User::getAge, 30);

// ✅ Recommended
wrapper.between(User::getAge, 18, 30);

// ✅ Better: conditional dynamic check
wrapper.between(ageStart != null && ageEnd != null,
    User::getAge, ageStart, ageEnd);

Generates simpler SQL, reducing parsing cost.

Database optimizer handles range queries more efficiently.

Code becomes clearer and more readable.

Reduces duplicate field name writing.

Index‑Aware Sorting Fields

// ❌ Not recommended – assume lastLoginTime has no index
wrapper.orderByDesc(User::getLastLoginTime);

// ✅ Recommended – sort by primary key
wrapper.orderByDesc(User::getId);

// ✅ Better: composite index sorting
wrapper.orderByDesc(User::getStatus) // status indexed
       .orderByDesc(User::getId);   // primary key

Indexes provide inherent ordering, avoiding extra sort steps.

Sorting without index forces file‑based sort, hurting performance.

Large data sets may cause memory overflow.

Index ordering enables streaming reads.

Pagination Parameter Settings

// ❌ Not recommended – fetches too many rows
wrapper.last("limit 1000");

// ✅ Recommended
Page<User> page = new Page<>(1, 10);
userService.page(page, wrapper);

// ✅ Better: conditional pagination
Page<User> result = userService.lambdaQuery()
    .eq(User::getStatus, "active")
    .page(new Page<>(1, 10));

Controls per‑query data volume, preventing OOM.

Improves first‑screen load speed and user experience.

Reduces network transmission pressure.

Makes database resource usage more reasonable.

Handle Null Values in Condition Construction

// ❌ Not recommended – many if‑else checks
if (StringUtils.isNotBlank(name)) {
    wrapper.eq("name", name);
}
if (age != null) {
    wrapper.eq("age", age);
}

// ✅ Recommended
wrapper.eq(StringUtils.isNotBlank(name), User::getName, name)
       .eq(Objects.nonNull(age), User::getAge, age);

// ✅ Better: combine business logic
wrapper.eq(StringUtils.isNotBlank(name), User::getName, name)
       .eq(Objects.nonNull(age), User::getAge, age)
       .eq(User::getDeleted, false) // default to non‑deleted
       .orderByDesc(User::getCreateTime); // default order

Gracefully handles empty values, avoiding useless conditions.

Reduces boilerplate if‑else statements.

Improves code readability.

Prevents generation of redundant SQL clauses.

Query Performance Tracking

// ❌ Not recommended – manual timing, verbose
public List<User> listUsers(QueryWrapper<User> wrapper) {
    long startTime = System.currentTimeMillis();
    List<User> users = userMapper.selectList(wrapper);
    long endTime = System.currentTimeMillis();
    log.info("Query time: {}ms", (endTime - startTime));
    return users;
}

// ✅ Recommended – try‑with‑resources
public List<User> listUsersWithPerfTrack(QueryWrapper<User> wrapper) {
    try (PerfTracker.TimerContext ignored = PerfTracker.start()) {
        return userMapper.selectList(wrapper);
    }
}

// Performance tracker utility
@Slf4j
public class PerfTracker {
    private final long startTime;
    private final String methodName;
    private PerfTracker(String methodName) {
        this.startTime = System.currentTimeMillis();
        this.methodName = methodName;
    }
    public static TimerContext start() {
        return new TimerContext(Thread.currentThread().getStackTrace()[2].getMethodName());
    }
    public static class TimerContext implements AutoCloseable {
        private final PerfTracker tracker;
        private TimerContext(String methodName) {
            this.tracker = new PerfTracker(methodName);
        }
        @Override
        public void close() {
            long executeTime = System.currentTimeMillis() - tracker.startTime;
            if (executeTime > 500) {
                log.warn("Slow query warning: method {} took {}ms", tracker.methodName, executeTime);
            }
        }
    }
}

Separates business logic from performance monitoring.

try‑with‑resources guarantees timing even on exceptions.

No manual start/stop management needed.

Provides cleaner code.

Enum Type Mapping

// Enum definition
public enum UserStatusEnum {
    NORMAL(1, "正常"),
    DISABLED(0, "禁用");
    @EnumValue // MyBatis‑Plus annotation
    private final Integer code;
    private final String desc;
}

// ✅ Recommended – automatic mapping
public class User {
    private UserStatusEnum status;
}

// Query example
userMapper.selectList(new LambdaQueryWrapper<User>()
    .eq(User::getStatus, UserStatusEnum.NORMAL));

Ensures type safety.

Automatically converts between DB values and enums.

Avoids magic numbers.

Improves code readability.

Automatic Logical Deletion Handling

@TableLogic // logical delete annotation
private Integer deleted;

// ✅ Recommended – auto‑filter deleted records
public List<User> getActiveUsers() {
    return userMapper.selectList(null); // automatically excludes deleted=1
}

// Manual delete (actually updates the flag)
userService.removeById(1L);

Data is not physically removed.

Queries automatically filter out deleted rows.

Supports data recovery.

Reduces boilerplate delete logic.

Optimistic Lock Update Protection

public class Product {
    @Version // optimistic lock version field
    private Integer version;
}

// ✅ Recommended – version handled automatically
public boolean reduceStock(Long productId, Integer count) {
    LambdaUpdateWrapper<Product> wrapper = new LambdaUpdateWrapper<>();
    wrapper.eq(Product::getId, productId)
           .ge(Product::getStock, count);
    Product product = new Product();
    product.setStock(product.getStock() - count);
    return productService.update(product, wrapper);
}

Prevents concurrent update conflicts.

Version field is managed automatically.

Simplifies concurrent update logic.

Improves data consistency.

Increment and Decrement: setIncrBy and setDecrBy

// ❌ Not recommended – raw SQL string
userService.lambdaUpdate()
    .setSql("integral = integral + 10")
    .update();

// ✅ Recommended – use setIncrBy
userService.lambdaUpdate()
    .eq(User::getId, 1L)
    .setIncrBy(User::getIntegral, 10)
    .update();

// ✅ Recommended – use setDecrBy
userService.lambdaUpdate()
    .eq(User::getId, 1L)
    .setDecrBy(User::getStock, 5)
    .update();

Provides type safety.

Avoids manual SQL concatenation, preventing injection.

Results in clearer, more maintainable code.

Conclusion

Just as a masterfully prepared soup requires careful ingredient selection and timing, writing code with MyBatis‑Plus benefits from these twelve refined techniques. Applying them turns ordinary data‑access code into elegant, high‑performance, and maintainable Java applications.