Get started with Mybatis-Plus!

Official website documents: https://baomidou.com/

1 Introduction

MyBatis-Plus (opens new window) (referred to as MP) is a MyBatis (opens new window) The enhancement tool of MyBatis only enhances and does not change on the basis of MyBatis, and is born to simplify development and improve efficiency.

1.1. Features

Non-invasive: only make enhancements without changing, introducing it will not affect existing projects, smooth as silk
Low loss: Basic CURD is automatically injected at startup, performance is basically lossless, and direct object-oriented operation
Powerful CRUD operations: Built-in general Mapper and general Service, most CRUD operations on a single table can be implemented with only a small amount of configuration, and more powerful conditional constructors to meet various usage needs
Support Lambda form invocation: Through Lambda expressions, you can easily write various query conditions, and you don't need to worry about writing wrong fields.
Supports automatic primary key generation: supports up to 4 primary key strategies (including a distributed unique ID generator - Sequence), which can be freely configured to perfectly solve the primary key problem
Support ActiveRecord mode: Support ActiveRecord form call, entity class can perform powerful CRUD operations only by inheriting Model class
Support custom global general operations: support global general method injection ( Write once, use anywhere )
Built-in code generator: Use code or Maven plugin to quickly generate Mapper , Model , Service , Controller layer code, support template engine, and more custom configurations waiting for you to use
Built-in paging plug-in: Based on MyBatis physical paging, developers do not need to care about specific operations. After configuring the plug-in, writing paging is equivalent to ordinary List query
The paging plugin supports multiple databases: MySQL, MariaDB, Oracle, DB2, H2, HSQL, SQLite, Postgre, SQLServer, etc.
Built-in performance analysis plug-in: It can output SQL statements and their execution time. It is recommended to enable this function during development and testing, which can quickly identify slow queries
Built-in global interception plug-in: provides intelligent analysis and blocking of delete and update operations on the entire table, and can also customize interception rules to prevent misoperation

2. Quick Start

Create database mybatis_plus

create table user

DROP TABLE IF EXISTS user;

CREATE TABLE user
(
    id BIGINT(20) NOT NULL COMMENT 'primary key ID',
    name VARCHAR(30) NULL DEFAULT NULL COMMENT 'Name',
    age INT(11) NULL DEFAULT NULL COMMENT 'age',
    email VARCHAR(50) NULL DEFAULT NULL COMMENT 'Mail',
    PRIMARY KEY (id)
);

insert data

INSERT INTO user (id, name, age, email) VALUES
(1, 'Jone', 18, 'test1@baomidou.com'),
(2, 'Jack', 20, 'test2@baomidou.com'),
(3, 'Tom', 28, 'test3@baomidou.com'),
(4, 'Sandy', 21, 'test4@baomidou.com'),
(5, 'Billie', 24, 'test5@baomidou.com');

Create project and initialize! !

import dependencies

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.5.2</version>
</dependency>

Note: Try not to import mybatis and mybatis-plus dependencies at the same time, it may cause conflicts

Configure the data source

spring:
  datasource:
    username: root
    password: 18227022334a
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/mybatis_plus?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC

Inherit BaseMapper on the corresponding UserMaaper interface
```
@Repository
public interface UserMapper extends BaseMapper<User> {
}
```
Note:
- There is no need to configure the xml file, all the basic query statements are in the BaseMapper interface! ! , the basic CRUD has been implemented, but if there are special sql statements, you still need to write them yourself! !
- Need to pass the entity class generic of pojo in the inherited BaseMaaper

test

@SpringBootTest
class MybatisPlusStudyApplicationTests {

    @Autowired
    private UserMapper userMapper;

    @Test
    void contextLoads() {
        List<User> userList = userMapper.selectList(null);
        for (User user : userList) {
            System.out.println(user);
        }
    }
}

Note: The parameter of the selectList() method in UserMapper is MP's built-in conditional wrapper Wrapper, so if you don't fill it in, there will be no conditions.

3. Configure the log

mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

4.CRUD extension

4.1. Insert

When inserting data, if we do not set the primary key id ourselves, a globally unique id will be automatically generated, and it will be automatically backfilled into our object, that is, it will automatically assign the id of the object for which we did not set the id. ! !
Primary key generation strategy:
- Reference blog:
  - https://blog.csdn.net/seventeenliu/article/details/109225884
  - http://www.manongjc.com/detail/29-rhnyavvmcmcsjdi.html
- UUID
- auto increment ID
- Snowflake algorithm (default): use 41bit as the number of milliseconds, 10bit as the machine ID (5 bits for the data center, 5 bits for the machine ID), 12bit as the serial number in milliseconds (meaning that each node can produce 4096 IDs), and finally have a symbol bit, always 0, that generates a unique value each time
- Redis
- zookeeper

Configure the primary key generation strategy:

public class User {
    @TableId(type = IdType.ASSIGN_ID)//default
    private Long id;
    private String name;
    private Integer age;
    private String email;
}

IdType:

value	describe
AUTO	Database ID auto increment
NONE	Stateless, the type is not set the primary key type (annotation is equal to follow the global, global Rio is equal to INPUT)
INPUT	set the primary key value by yourself before insert
ASSIGN_ID	Assign ID (the primary key type is Number(Long and Integer) or String)(since 3.3.0), use the method nextId of the interface IdentifierGenerator (the default implementation class is DefaultIdentifierGenerator snowflake algorithm)
ASSIGN_UUID	Assign UUID, the primary key type is String(since 3.3.0), and use the method nextUUID of the interface IdentifierGenerator (default default method)
ID_WORKER	Distributed globally unique ID long type (please use ASSIGN_ID) (deprecated)
UUID	32-bit UUID string (please use ASSIGN_UUID) (deprecated)
ID_WORKER_STR	Distributed globally unique ID string type (please use ASSIGN_ID) (deprecated)

Note: If you use auto-increment, it may be related to the fact that the database does not check the auto-increment option! !

4.2. Update

When updating, although the name is ById, an adapted generic class object needs to be passed in. At the same time, it can be found from the log that mybatis-plus will automatically help us splicing sql statements according to the conditions, that is, adding a field with an empty value , then the field will not be set when it is set! !

autofill

Alibaba development manual shows that all database tables should contain two fields: gmt_create, gmt_modified, that is, creation time and modification time, and we should use automation to complete this operation! !
- Database level operations (actual work is not allowed): way of using default values

Code level:

Annotate with @TableField(fill=...)

//@TableField(fill = FieldFill.DEFAULT) default
//Has the following values: DEFAULT, INSERT,UPDATE,INSERT_UPDATE;

@TableField(fill = FieldFill.INSERT)
private Data gmtCreate;//mybatis-plus automatically turns on hump naming
@TableField(fill = FieldFill.INSERT_UPDATE)
private Data gmtModified;

Write a custom implementation class MyMetaObjectHandler to handle this annotation

@Slf4j
@Component
public class MyMetaObjectHandler implements MetaObjectHandler {
   
  //Autofill policy on insert
    @Override
    public void insertFill(MetaObject metaObject) {
        log.info("start insert fill......");
        this.setFieldValByName("gmtCreate",LocalDateTime.now(),metaObject);
        this.setFieldValByName("gmtModified",LocalDateTime.now(),metaObject);
    }

    //Autofill policy on update
    @Override
    public void updateFill(MetaObject metaObject) {
        log.info("start update fill......");
        this.setFieldValByName("gmtModified", LocalDateTime.now(),metaObject);
    }
}

test! !
Note: When the data type is long, you should add l after the data to indicate that the data is a long integer! !

optimistic locking pessimistic locking
- Optimistic lock: very optimistic, always think that there will be no problem, no matter what you do, it will not be locked. If there is a problem, perform the update value test again!
- Pessimistic lock: very pessimistic, always think that there will be problems with everything, no matter what you do, it will be locked, and you are going to operate!
- When a record is to be updated, it is hoped that the record has not been updated by others. The implementation of optimistic locking is as follows:
  1. When fetching a record, get the current version
  2. When updating, bring this version
  3. When performing an update, set version = newVersion where version = oldVersion
  4. If the version is incorrect, the update fails
  5. illustrate:
    - The only supported data types are: int,Integer,long,Long,Date,Timestamp,LocalDateTime
    - In integer type newVersion = oldVersion + 1
    - newVersion will be written back to entity
    - Only support updateById(id) and update(entity, wrapper) methods
    - Under the update(entity, wrapper) method, wrapper cannot be reused!!!
- Implement optimistic locking:
  1. Database add field version
  2. Add property to entity class
```
@Version
private Integer version;
```
  3. Create a new configuration class configuration plugin
```
@Configuration
@MapperScan("com.xiaoye.mapper")//You can put it not on the startup class, you can also put it here
@EnableTransactionManagement//Manage transactions automatically
public class MyBatisPlusConfig {
    //Register optimistic locking plugin
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor mybatisPlusInterceptor = new MybatisPlusInterceptor();
        mybatisPlusInterceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());
        return mybatisPlusInterceptor;
    }
}
```
  4. test! !

4.3. Query

Common queries:
- selectById(): query by id
- selectList(): query all
- selectBatchIds(): batch query, the parameter is a collection
- selectByMap(): conditional query, the parameter is map, the key of the map is the field name, and the value is the field value

Paging query:

Add plugins under the configuration class

/**
     * The new paging plug-in, one and two, follow the rules of mybatis. You need to set MybatisConfiguration#useDeprecatedExecutor = false to avoid cache problems (this property will be removed after the old plug-in is removed)
     */
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor2() {
    MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
    interceptor.addInnerInterceptor(new PaginationInnerInterceptor());
    return interceptor;
}

use

void contextLoads() {
    //The first parameter: the current page
    //The second parameter: the number of pages
    Page<User> userPage = new Page<>(2,3);
    userMapper.selectPage(userPage, (Wrapper<User>) null);
    for (User record : userPage.getRecords()) {
        System.out.println(record);
    }
}
///userPage.getTotal() gets the total number of data

4.4, delete

Common deletes:

//
userMapper.delete();
//batch deletion
userMapper.deleteBatchIds();
//delete by id
userMapper.deleteById();
//delete by condition
userMapper.deleteByMap();

Logical deletion: It is not deleted directly in the database, but through a logical variable, such as is_delete=0|1, instead of real deletion

Additional instructions:
- Only works for automatically injected sql:
- Insert: no restrictions
- Find: Append a where condition to filter out deleted data, and the where condition generated using wrapper.entity will ignore this field
- Update: Append where condition to prevent updates to deleted data, and where condition generated with wrapper.entity ignores this field
- delete: convert to update
1. Add an is_delete field to the database
2. Add attributes to the entity class (after 3.3, no annotations can be added)
```
@TableLogic
private Integer isDelete;
```
3. Configure in yaml
```
mybatis-plus:
  global-config:
    db-config:
      logic-delete-field: isDelete # Global tombstone entity field name
      logic-delete-value: 1 # logical removed value (default 1)
      logic-not-delete-value: 0 # Logical undeleted value (default 0)
```
4. test! !
5. common problem:
  1. How to insert:
    - Database defined defaults (recommended)
    - set yourself
    - Use autofill
  2. The automatic filling function of the delete interface function fails:
    - Use deleteById method (recommended)
    - Use the update method and: UpdateWrapper.set(column, value) (recommended)
    - Use the update method and: UpdateWrapper.setSql("column=value")

5. Performance analysis plugin

This function relies on the p6spy component, which perfectly outputs and prints SQL and execution time

Import dependencies:

<dependency>
    <groupId>p6spy</groupId>
    <artifactId>p6spy</artifactId>
    <version>3.2.0</version>
</dependency>

application.yaml changes the configuration of all aspects of the database

username: root
password: 18227022334a
driver-class-name: com.p6spy.engine.spy.P6SpyDriver
url: jdbc:p6spy:mysql://localhost:3306/mybatis_plus?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC

Configure spy.properties

#3.2.1 Use above
modulelist=com.baomidou.mybatisplus.extension.p6spy.MybatisPlusLogFactory,com.p6spy.engine.outage.P6OutageFactory
# Custom log printing
logMessageFormat=com.xiaoye.log.P6SpyLogger
#log output to console
appender=com.baomidou.mybatisplus.extension.p6spy.StdoutLogger
# log sql using the logging system
#appender=com.p6spy.engine.spy.appender.Slf4JLogger
# Set p6spy driver proxy
deregisterdrivers=true
# Unprefix JDBC URL
useprefix=true
# With the exception of the configuration record Log, the result sets that can be removed are error, info, batch, debug, statement, commit, rollback, result, and resultset.
excludecategories=info,debug,result,commit,resultset
# date format
dateformat=yyyy-MM-dd HH:mm:ss
# The actual drive can be multiple
#driverlist=org.h2.Driver
# Whether to enable slow SQL logging
outagedetection=true
# Slow SQL logging standard 2 seconds
outagedetectioninterval=2

Write a custom log class

@Slf4j
public class P6SpyLogger implements MessageFormattingStrategy {

    private final SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    @Override
    public String formatMessage(int connectionId, String now, long elapsed, String category, String prepared, String sql) {
        String date = dateFormat.format(new Date());
        if (log.isInfoEnabled()) {
            log.info("execution time: {}", date);
            log.info("sql:  {}", sql);
            log.info("time consuming:{} millisecond", elapsed);
        }
        return "";
    }
}

Notice:
- driver-class-name is the driver class provided by p6spy
- The url prefix is jdbc:p6spy followed by a colon for the corresponding database connection address
- Print out sql as null, add commit in excludecategories
- Batch operations do not print sql, remove batch in excludecategories
- Please use MybatisPlusLogFactory (new in 3.2.1) for the problem of repeated printing in batch operations
- This plugin has performance loss and is not recommended for production environments.

6. Conditional constructor Wrapper

QueryWrapper: select, delete use
UpdateWrapper: update use
common:

Chain programming can be used:

public void test1(){
    QueryWrapper<User> wrapper = new QueryWrapper<>();
    wrapper.gt("age",21).like("name","plum");
    List<User> userList = userMapper.selectList(wrapper);
    userList.forEach(System.out::println);
}

7. Code Generator