Use mybatis plus to add, delete, modify and query the database (including logical deletion, primary key generation, optimistic lock and paging query)

Posted by bluto374 on Fri, 07 Jan 2022 07:10:11 +0100



insert should mainly focus on the processing of primary key fields by mybatis plus

Primary key generation strategy

Extension: common strategies for generating primary keys

  1. UUID
  2. Self increment ID
  3. Snowflake algorithm
  4. Generated using redis

Snowflake algorithm:

The id generated by SnowFlake algorithm is a 64bit integer, which is suitable for distributed systems. Its structure is shown in the following figure:

Image source:


Use @ TableId to specify the primary key field corresponding to the entity class

@TableName(value = "user")
public class User {
    private Long id;
    private String name;
    private Integer age;
    private String email;

@TableId can configure the database field name (value) and primary key generation type (type) corresponding to the primary key

attributetypeMust specifyDefault valuedescribe
valueStringno""Primary key field name
typeEnumnoIdType.NONESpecifies the primary key type

IdType enumeration

AUTODatabase ID self increment
NONEStateless. This type has no primary key set (in the annotation, it is equal to follow the global, and the global value is equal to INPUT)
INPUTset the primary key value before insert ing
ASSIGN_IDAssign ID (the primary key type is Number(Long and Integer) or String)(since 3.3.0), and use the method NextID of the interface identifier generator (the default implementation class is defaultidentifier generator)
ASSIGN_UUIDAllocate UUID. The primary key type is String(since 3.3.0). Use the method nextuuid (default method) of the interface IdentifierGenerator
ID_WORKERDistributed globally unique ID long integer type (please use ASSIGN_ID)
UUID32-bit UUID string (please use ASSIGN_UUID)
ID_WORKER_STRDistributed globally unique ID string type (please use ASSIGN_ID)


Dynamic SQL

When using methods such as updateById, MySQL plus will automatically generate dynamic sql statements for execution.

Optimistic lock plug-in


When you want to update a record, you want it not to be updated by others
Optimistic lock implementation method:

When fetching records, get the current version

When updating, bring this version

When updating, set version = newVersion where version = oldVersion

If the version is incorrect, the update fails

to configure:

  • Configure plug-ins
public MybatisPlusInterceptor mybatisPlusInterceptor() {
    MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
    interceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());
    return interceptor;
  • Add the @ Version annotation to the field of the entity class
private Integer version;


  • The only supported data types are: int,Integer,long,Long,Date,Timestamp,LocalDateTime
  • newVersion = oldVersion + 1 under integer type
  • newVersion will be written back to entity
  • Only updateById(id) and update(entity, wrapper) methods are supported
  • Under the update(entity, wrapper) method, the wrapper cannot be reused!!!


  • selectList() queries all
  • selectBatchIds() query batch objects
  • selectById() uses id to query an object

Condition query


Map<String, Object> map=new HashMap<>();
List<User> users = userMapper.selectByMap(map);

Paging query

Expansion: implementation of paging query

  1. Using the limit statement

  2. Using pageHelper third party libraries

  3. MyBatisPlus has built-in paging query function


  • Add a paging Interceptor
public MybatisPlusInterceptor mybatisPlusInterceptor(){
    MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
    interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
    return interceptor;
  • Using mapper
public void testPageSelect(){
    //Page 3, 4 records per page
    final Page<User> userPage = new Page<>(3,4);
    userMapper.selectPage(userPage, null);
    System.out.println("userPage.getTotal() = " + userPage.getTotal());
    System.out.println("userPage.getPages() = " + userPage.getPages());
    System.out.println("userPage.getCurrent() = " + userPage.getCurrent());
    System.out.println("userPage.getSize() = " + userPage.getSize());


Basic deletion

The usage of basic deletion is similar to that of select, such as id deletion, batch deletion, conditional deletion, etc., which will not be repeated here.

Logical deletion

Physical and logical deletion

Physical delete: delete data directly from the database

Logical deletion: data is not deleted directly from the database, but is deleted using a field. For example, if the value of the deleted field is 1, it means deletion.

For logical deletion, you need to add a field in the database table, such as deleted

Step 1: Configure

      logic-delete-field: flag # Global logically deleted entity field name (since 3.3.0, it can be ignored after configuration, and step 2 is not configured)
      logic-delete-value: 1 # Logical deleted value (default is 1)
      logic-not-delete-value: 0 # Logical undeleted value (0 by default)

Step 2: add annotation @ TableLogic on the entity class field

private Integer deleted;

After completing the above two steps, you can directly use the mapper class for logical deletion, and the logically deleted records will be filtered out during query.

Topics: Java Database Mybatis Back-end