To streamline single-table CRUD operations, MyBatis Plus offers a foundational BaseMapper interface that comes with pre-implemented CRUD methods. By having your custom Mapper interfaces extend BaseMapper, you eliminate the need to write repetitive single-table CRUD logic yourself.

package com.example.mp.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.mp.domain.po.User;

public interface UserMapper extends BaseMapper<User> {
}

Integration test example:

@SpringBootTest
class UserMapperIntegrationTest {

    @Autowired
    private UserMapper userMapper;

    @Test
    void testAddNewUser() {
        User user = new User();
        user.setId(6L);
        user.setUsername("Luna");
        user.setPassword("456");
        user.setCreateTime(LocalDateTime.now());
        user.setUpdateTime(LocalDateTime.now());
        userMapper.insert(user);
    }

    @Test
    void testRetrieveUserById() {
        User user = userMapper.selectById(6L);
        System.out.println("Fetched user: " + user);
    }
}

Common Annotations

When your Mapper extends BaseMapper, the generic type specifies the PO (Plain Old Object) class mapped to your database table. MyBatis Plus uses reflection to access the entity's properties and automatically infers table metadata to generate SQL. By default:

• The PO class name (camelCase) is converted to snake_case as the database table name
• All PO fields (camelCase) are converted to snake_case as column names, with data types inferred from field types
• Fields named id are treated as the primary key

To customize these mappings when defaults don't fit, MyBatis Plus provides key annotations:

• @TableName: Explicitly defines the corresponding database table name
• @TableId: Marks a field as the primary key, supporting custom key generation strategies
• @TableField: Used for regular fields to resolve issues such as:
  • Fields starting with "is" (MP automatically strips the "is" prefix by default)
  • Field names conflicting with SQL keywords
  • Fields that don't map to any database column

Core Features

Condition Constructors

For operations like update, delete, and complex queries, you need flexible WHERE conditions. BaseMapper supports dynamic conditions via MyBatis Plus's Wrapper classes, which let you build SQL conditions programmatically.

(1) QueryWrapper

Use QueryWrapper to build SELECT and UPDATE conditions. For example, retrieve users with usernames containing "a" and balance ≥ 2000:

@Autowired
private UserMapper userMapper;

@Test
void testDynamicQuery() {
    // Build condition: WHERE username LIKE '%a%' AND balance >= 2000
    QueryWrapper<User> queryWrapper = new QueryWrapper<User>()
            .select("id", "username", "balance", "create_time")
            .like("username", "a")
            .ge("balance", 2000);
    // Execute query
    List<User> users = userMapper.selectList(queryWrapper);
    users.forEach(System.out::println);
}

Update example: Set balance to 3000 for user "Mike":

@Test
void testUpdateWithCondition() {
    // Data to update
    User user = new User();
    user.setBalance(3000);
    // Build condition: WHERE username = 'Mike'
    QueryWrapper<User> queryWrapper = new QueryWrapper<User>().eq("username", "Mike");
    // Execute update (non-null fields in user become SET clauses)
    userMapper.update(user, queryWrapper);
}

(2) UpdateWrapper

When you need to update fields based on their current values (e.g., balance = balance - 300), use UpdateWrapper's setSql method. Example: Deduct 300 from users with IDs 2,3,5:

@Test
void testUpdateWithCalculation() {
    List<Long> targetIds = List.of(2L, 3L, 5L);
    // Build update logic: SET balance = balance - 300 WHERE id IN (2,3,5)
    UpdateWrapper<User> updateWrapper = new UpdateWrapper<User>()
            .setSql("balance = balance - 300")
            .in("id", targetIds);
    // Execute update (pass null as first param since setSql defines the update)
    userMapper.update(null, updateWrapper);
}

(3) LambdaQueryWrapper

To avoid hardcoding column names and reduce typos, use LambdaQueryWrapper, which leverages method references to reference entity fields:

@Test
void testTypeSafeQuery() {
    // Build type-safe condition: WHERE username LIKE '%a%' AND balance >= 2000
    LambdaQueryWrapper<User> lambdaWrapper = new LambdaQueryWrapper<User>()
            .select(User::getId, User::getUsername, User::getBalance)
            .like(User::getUsername, "a")
            .ge(User::getBalance, 2000);
    // Execute query
    List<User> users = userMapper.selectList(lambdaWrapper);
    users.forEach(System.out::println);
}

(4) Custom SQL with Wrapper

You can combine custom SQL with Wrapper-generated conditions. Example: Deduct a specific amount from users matching a condition:

Test code:

@Test
void testCustomSqlWithWrapper() {
    List<Long> targetIds = List.of(2L, 3L, 5L);
    QueryWrapper<User> queryWrapper = new QueryWrapper<User>().in("id", targetIds);
    // Call custom mapper method
    userMapper.reduceBalanceForUsers(300, queryWrapper);
}

Mapper interface:

package com.example.mp.mapper;

public interface UserMapper extends BaseMapper<User> {
    @Update("UPDATE user SET balance = balance - #{amount} ${ew.customSqlSegment}")
    void reduceBalanceForUsers(@Param("amount") int amount, @Param("ew") QueryWrapper<User> wrapper);
}

Service Layer Support

MyBatis Plus provides a generic IService interface and its default implementation ServiceImpl, which encapsulates common service-layer methods (save, remove, update, get, list, count, page).

To use this, create a custom Service interface extending IService, then implement it with a class extending ServiceImpl:

Custom Service interface:

package com.example.mp.service;

import com.baomidou.mybatisplus.extension.service.IService;
import com.example.mp.domain.po.User;

public interface IUserService extends IService<User> {
    // Add business-specific methods here
    void reduceUserBalance(Long userId, Integer amount);
}

Service implementation:

package com.example.mp.service.impl;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.example.mp.domain.po.User;
import com.example.mp.mapper.UserMapper;
import com.example.mp.service.IUserService;
import org.springframework.stereotype.Service;

@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements IUserService {
    @Override
    public void reduceUserBalance(Long userId, Integer amount) {
        // Retrieve user
        User user = getById(userId);
        // Validate user status
        if (user == null || user.getStatus() == 2) {
            throw new RuntimeException("User status is invalid");
        }
        // Validate sufficient balance
        if (user.getBalance() < amount) {
            throw new RuntimeException("Insufficient user balance");
        }
        // Deduct balance via custom mapper method
        baseMapper.reduceBalanceById(userId, amount);
    }
}

Controller example using RESTful endpoints:

package com.example.mp.controller;

import com.example.mp.domain.dto.UserCreateDTO;
import com.example.mp.domain.vo.UserResponseVO;
import com.example.mp.service.IUserService;
import org.springframework.beans.BeanUtils;
import org.springframework.web.bind.annotation.*;
import java.time.LocalDateTime;
import java.util.List;
import java.util.stream.Collectors;

@RestController
@RequestMapping("/api/users")
public class UserController {
    private final IUserService userService;

    public UserController(IUserService userService) {
        this.userService = userService;
    }

    @PostMapping
    public void createUser(@RequestBody UserCreateDTO dto) {
        User user = new User();
        BeanUtils.copyProperties(dto, user);
        user.setCreateTime(LocalDateTime.now());
        user.setUpdateTime(LocalDateTime.now());
        userService.save(user);
    }

    @DeleteMapping("/{id}")
    public void deleteUserById(@PathVariable Long id) {
        userService.removeById(id);
    }

    @GetMapping("/{id}")
    public UserResponseVO getUserById(@PathVariable Long id) {
        User user = userService.getById(id);
        UserResponseVO vo = new UserResponseVO();
        BeanUtils.copyProperties(user, vo);
        return vo;
    }

    @PutMapping("/{id}/balance/deduct")
    public void deductUserBalance(@PathVariable Long id, @RequestParam Integer amount) {
        userService.reduceUserBalance(id, amount);
    }
}

Complex Dynamic Queries

Use lambdaQuery() to build dynamic conditions that only apply when parameters are non-null. For example, filter users based on optional username, status, and balance range:

@GetMapping("/filter")
public List<UserResponseVO> filterUsers(UserFilter filter) {
    List<User> users = userService.lambdaQuery()
            .like(filter.getUsername() != null, User::getUsername, filter.getUsername())
            .eq(filter.getStatus() != null, User::getStatus, filter.getStatus())
            .ge(filter.getMinBalance() != null, User::getBalance, filter.getMinBalance())
            .le(filter.getMaxBalance() != null, User::getBalance, filter.getMaxBalance())
            .list();
    return users.stream()
            .map(user -> {
                UserResponseVO vo = new UserResponseVO();
                BeanUtils.copyProperties(user, vo);
                return vo;
            })
            .collect(Collectors.toList());
}

Optimistic Lock for Concurrency Control

To handle concurrent updates (e.g., preventing over-deduction of balance), use optimistic locking by adding a version field to your PO and using lambdaUpdate() with a condition on the current balance:

@Override
public void reduceUserBalance(Long userId, Integer amount) {
    User user = getById(userId);
    if (user == null || user.getStatus() == 2) {
        throw new RuntimeException("Invalid user status");
    }
    if (user.getBalance() < amount) {
        throw new RuntimeException("Insufficient balance");
    }
    int newBalance = user.getBalance() - amount;
    lambdaUpdate()
            .set(User::getBalance, newBalance)
            .set(newBalance == 0, User::getStatus, 2) // Mark inactive if balance is zero
            .eq(User::getId, userId)
            .eq(User::getBalance, user.getBalance()) // Optimistic lock: ensure balance hasn't changed
            .update();
}

Bulk Insert Optimization

For bulk inserts, enable MySQL's rewriteBatchedStatements parameter to boost performance (up to 10x faster). Add this to your JDBC URL in application.yml:

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/mp_db?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true
    driver-class-name: com.mysql.cj.jdbc.Driver
    username: root
    password: your_password

Summary

• For simple single-table operations, extend BaseMapper in your custom Mappers to use pre-built CRUD methods without writing extra code.
• For complex business logic, create custom Service interfaces and implementations. Use Wrapper classes to build dynamic conditions, and combine with custom SQL when needed.
• Straightforward operations can be implemented directly in controllers via generic Service methods (e.g., userService.save(user)).
• Business-specific logic should be defined in custom Service methods (e.g., userService.reduceUserBalance(userId, amount)), implemented in ServiceImpl classes, and called from controllers.