Mybatis plus implements database curd operation

Mybatis plus implements database curd operation

  1. What is mp

    • MyBatis plus (MP for short) is an enhancement tool for MyBatis. Based on MyBatis, it only makes enhancements and does not change. It is born to simplify development and improve efficiency.
    • Moisten things silently,
    • Only enhancement without change, and its introduction will not have an impact on the existing project, which is as smooth as silk.
      Efficiency first,
    • CRUD operations can be performed quickly with simple configuration, saving a lot of time.
    • Rich functions
    • Hot loading, code generation, paging, performance analysis and other functions are available·
  2. mp implements adding, modifying and deleting queries

  3. mp auto fill optimistic lock

  4. mp logical deletion

  5. mp paging query

2.1 introduction

  `id` bigint NOT NULL COMMENT 'Primary key ID',
  `name` varchar(30) DEFAULT NULL COMMENT 'full name',
  `age` int DEFAULT NULL COMMENT 'Age',
  `email` varchar(50) DEFAULT NULL COMMENT 'mailbox',
  PRIMARY KEY (`id`)

INSERT INTO `user`(`id`,`name`,`age`,`email`) VALUES

Springboot version 2.2.1

Introduce related dependencies


Download the lombok plug-in

Configure database information

Create entity class User

Create corresponding Mapper

Inherits BaseMapper, which encapsulates common addition, deletion, query and modification methods

Add a path to scan the mapper on the startup class. Because it is generated dynamically by the interface, it cannot be scanned by default

@Autowired annotates class member variables, methods and constructors to complete automatic assembly.

Use @ Autowired to eliminate the set and get methods

@Repository, @ Service, @ Controller, and @ Component identify classes as beans

@Repository("name"): dao layer
@Service("name"): service layer
@Controller("name"): web tier

#mybatis log on

MP primary key policy

  1. ASSIGN_ The ID generates 19 bits by default (generated according to symbol bits, timestamp,,, etc., Baidu)
  2. auto: auto growth

Assign is on by default_ ID

#Global setting primary key generation policy



void findAll() {
    List<User> users = userMapper.selectList(null);


void addUser(){
    User user = new User(12L, "dawn", 23, "");
    int insert = userMapper.insert(user); //Number of affected rows


public void upData(){
    User user = new User();
    int i = userMapper.updateById(user);
    System.out.println("Number of affected rows"+i);

2.3. Automatic filling

set is required for field assignment

When two fields are added, the corresponding entity class is also added

@TableField(fill = FieldFill.INSERT)
private Data createTime;

@TableField(fill = FieldFill.INSERT_UPDATE)
private Data updateTime;

When creating a new class, don't forget to annotate it and leave it to Spring management

public class MyMetaObjectHandler implements MetaObjectHandler {
    //Implement two methods to automatically fill, add and modify

3. Optimistic lock

3.1 scenario

Main applicable scenarios: when a record is to be updated, it is hoped that the record has not been updated by others, that is, thread safe data update is realized

Solve the problem of missing updates. (repeatable reading, which solves the problem of non repeatable reading, the third level of isolation, Mysql default)

Optimistic lock implementation method:

When fetching the record and getting the current version one update, bring this version v

When updating, set version = newexsion where version = oldVersion

If the version is incorrect, the update fails

After submitting, modify the version number. When submitting another one, check whether the version number is the same as when obtaining it. Otherwise, the submission fails and the submission succeeds.

MP implements optimistic locking.

1. Obviously, the field version needs to be added to the table, and so does the entity class

Add annotation @ Version

3. Configure the optimistic lock plug-in and create a configuration class for Spring Boot

Add configuration annotation, add scanning, and create an optimistic lock plug-in

public OptimisticLockerInterceptor optimisticLockerInterceptor() {
    return new OptimisticLockerInterceptor();

4. MP query

4.1 batch query of multiple ID S

selectBatchIds(): Batch

public void select1() {
    List<User> users = userMapper.selectBatchIds(Arrays.asList(1, 2, 3));
    for (User user : users) {

4.2. Simple condition query (Map)

public void select2(){
    Map<String, Object> columnMap = new HashMap<>();
    columnMap.put("name", "Jack");
    columnMap.put("age", "20");
    List<User> users = userMapper.selectByMap(columnMap);

The attribute of value does not correspond to that in the database, but can also be queried

4.3 paging query


public void selectPage() {
    Page<User> page = new Page<>(1,3);
    Page<User> userPage = userMapper.selectPage(page,null);
    long pages = userPage.getPages();//PageCount 
    long current = userPage.getCurrent();//Current page
    List<User> records = userPage.getRecords();//Data set
    long total = userPage.getTotal();
    boolean hasNext = userPage.hasNext();//Is there another page
    boolean previous = userPage.hasPrevious();//previous page


5. MP delete

  • Simple delete, delete according to id. simple

  • Batch delete

  • Same as batch query

  • Simple conditional deletion, as above, uses map

5.1 physical deletion and logical deletion

Physical deletion: real deletion, delete the corresponding data from the database, and then query the deleted data (easy to understand)

**Logical deletion: * * false deletion, change the status of the field representing whether to be deleted in the corresponding data to "deleted status", and then you can still see this data record in the database;

Logical deletion

Logic implemented by logical deletion:

Add a field in the table as a logical deletion flag, and modify the flag bit each time you delete

0 was not deleted
1 delete

1. Add fields to the table and attributes to the entity class. This is a flag

2. Add the annotation @ TableLogic: table logic

Deleting will set the value of this field to 1

When the value of this field is 1, MP cannot find the data, but the data still exists in the database

6. Conditional constructors and common interfaces

Use of Wapper

public void testWrapper1() {
     *  ge : Greater than or equal to
     *  gt: greater than
     *  le :  <=
     *  lt :  <
    QueryWrapper<User> wrapper = new QueryWrapper<>();
    QueryWrapper<User> queryWrapper ="age", 18);
    List<User> users = userMapper.selectList(queryWrapper);

SELECT id,name,age,email,create_time,update_time,version,deleted FROM user WHERE deleted=0 AND (age >= ?)

ge: greater than or equal to

gt: greater than

le : <=

lt : <

eq : =

ne : !=

between: closed interval

notBetween: closed interval

Multi use: fuzzy query

like contains% sheets%

notLike does not contain

likeLeft sheets%

likeRight sheets%


orderBy, orderByDesc, orderByAsc. (parameter)

Tags: Mybatis

Posted on Fri, 24 Sep 2021 09:52:07 -0400 by chadowch