brief introduction

MyBatis plus (MP for short) is an enhancement tool for MyBatis. On the basis of 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.

1. Create database

CREATE TABLE USER
(
    id BIGINT(20)NOT NULL COMMENT 'Primary key ID',
    NAME VARCHAR(30)NULL DEFAULT NULL COMMENT 'full name',
    age INT(11)NULL DEFAULT NULL COMMENT 'Age',
    email VARCHAR(50)NULL DEFAULT NULL COMMENT 'mailbox',
    PRIMARY KEY (id)
);

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');

2. Create project

Initialization project: use Spring Initializr to quickly initialize a Spring Boot project
Import dependency:

<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
<!--mybatis-plus-->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.3.1</version>
</dependency>

<!--mysql rely on-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<!--lombok Used to simplify entity classes-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
</dependencies>

3. Write code

3.1 configuration file

In application Add the relevant configuration of MySQL database in the properties configuration file

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/mybatis_plus?serverTimezone=GMT%2B8
spring.datasource.username=root
spring.datasource.password=root

be careful
1. What is the url used here? serverTimezone=GMT%2B8 suffix, because the jdbc driver of version 8.0 needs to add this suffix, otherwise run the test case and report the following error:
java.sql.SQLException: The server time zone value 'Öйú±ê׼ʱ¼ä' is unrecognized or represents more
2. The driver class name here uses com mysql. cj. jdbc. Driver, it is recommended to use this driver in jdbc 8, otherwise there will be WARN information when running the test case

3.2 startup

Add the @ MapperScan annotation in the Spring Boot startup class and scan the Mapper folder

package com.example.demomptest;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@MapperScan("com.example.demomptest.mapper")
public class DemomptestApplication {

    public static void main(String[] args) {

        SpringApplication.run(DemomptestApplication.class, args);
    }
}

3.3 entity class

@Data
public class User {
    private Long id;
    private String name;
    private Integer age;
    private String email;
    }

3.4 adding Mapper

Before writing Mapper, we need to write interfaces and SQL statements. We can use mybatis plus to directly inherit the BaseMapper interface.

package com.example.demomptest.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.demomptest.entity.User;
import org.springframework.stereotype.Repository;

@Repository
public interface Usermapper extends BaseMapper<User> {
}

3.5 writing test classes

package com.example.demomptest;

import com.example.demomptest.entity.User;
import com.example.demomptest.mapper.Usermapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;

@SpringBootTest
class DemomptestApplicationTests {
    @Autowired
    private Usermapper usermapper;

    //Test find all users
    @Test
    public void findall() {
        List<User> user = usermapper.selectList(null);
        System.out.println(user);
    }
}

3.6 operation results

View SQL output log
In application Properties, so that you can see the specific SQL and operations on the console.

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

4. Primary key policy

When we tested the insert operation, we found that we do not define User_ID, the system will help us create a 19 bit id value. Why? For example:

The primary key policy of mybatis plus is used here

The default primary key policy of mybatis plus is ASSIGN_ID, this strategy means that the ID is a random nineteen digit number

 @TableId(type = IdType.ASSIGN_ID)
    private Long id;

Other key configurations:
AUTO: database primary key AUTO increment

#Global setting primary key generation policy
mybatis-plus.global-config.db-config.id-type=auto

INPUT: user defined id
NONE: the primary key type is not set

Common notes
@TableName("table name")
When the table name is inconsistent with the entity class name, you can add @ TableName() Declaration on the entity class
@TableId declares that the attribute is the primary key in the table (if the attribute name is not the default id)
@TableFieId("field") can be used to declare when the entity class attribute is inconsistent with the table field

5. Auto fill and lock

What is auto fill?
Some tables have update times_ Time, creation timecreate_ Time, updater, or creator.
These fields need to be set every time you add, delete or modify data. The traditional approach is to set the fields of the entity before these operations. This approach is not only easy to forget and lead to errors, but also the code will appear particularly redundant.
Although the database time can be used for adding and modifying time, such functions cannot be used by adding and modifying people.
Therefore, mybatis plus provides the function of automatic filling to help you set the values of these fields, improve development efficiency, and the code will be particularly elegant.
There are two ways to realize automatic filling, one at the database level and the other by programming
1. Database level implementation
Add a new field create of type datetime to the User table_ time,update_time

2. Entity class level implementation

1, Entity class modification

Add fields on entities and add auto fill annotations

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

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

2, Implement meta object processor interface

package com.example.demomptest.handler;

import com.baomidou.mybatisplus.core.handlers.MetaObjectHandler;
import org.apache.ibatis.reflection.MetaObject;
import org.springframework.stereotype.Component;

import java.util.Date;

@Component
public class MyMetaObjectHandler implements MetaObjectHandler {

    // mp performs the add operation. This method performs
    @Override
    public void insertFill(MetaObject metaObject){
        this.setFieldValByName("createTime",new Date(),metaObject);
        this.setFieldValByName("updateTime",new Date(),metaObject);
        this.setFieldValByName("version",1,metaObject);
    }

    // mp performs the modification operation. This method performs
    @Override
    public void updateFill(MetaObject metaObject){
        this.setFieldValByName("updateTime",new Date(),metaObject);
    }
}

Optimistic lock

Scenario: 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
Optimistic lock implementation
1. When fetching records, get the current version
2. When updating, bring this version
3. When updating, set version = newVersion where version = oldVersion
4. If the version is incorrect, the update fails
Special note
The only supported data types are: int, Integer, long, long, Date, Timestamp, LocalDateTime

newVersion = oldVersion + 1 under integer type

Optimistic lock implementation process

1. Modify entity class

	@Version
    private Integer version;

2. Create a profile

Create the package config and create the file mybatisplusconfig java

3. Register beans in MybatisPlusConfig

@Configuration
@MapperScan("com.atguigu.demomptest.mapper")
public class MpConfig {
    /**
     * Optimistic lock plug-in
     */
    @Bean
    public OptimisticLockerInterceptor optimisticLockerInterceptor() {
        return new OptimisticLockerInterceptor();
    }
}

Conditional constructor (Wrapper)

Scenario: Wrapper is required to write some complex SQL statements

1, Test ge, gt, le, lt, isNull, isNotNull

Find the user with age > = 22

   // Wrapper test
    @Test
    public void testWrapper(){
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.ge("age",22);
        List<User> users = usermapper.selectList(queryWrapper);
        System.out.println(users);
    }

2, Test eq, ne

Find the user with name = Lucy

	// Test eq
	@Test
    public void testWrapper(){
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("name","Lucy");
        User user = usermapper.selectOne(queryWrapper);
        System.out.println(user);
    }

3, Test between, not between

Test users aged 18 to 22

// Test between
    @Test
    public void testWrapper(){
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.between("age",18,22);
        List<User> users = usermapper.selectList(queryWrapper);
        System.out.println(users);
    }

4, Test like, notLike, likeLeft, likereight

Test users with Zhang's name

 	// Test like
    @Test
    public void testWrapper(){
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.like("name","Zhang");
        List<User> users = usermapper.selectList(queryWrapper);
        System.out.println(users);
    }

5, Test orderBy, orderByDesc, orderByAsc

Test age in descending order

	// Age in reverse order
	@Test
    public void testSelectListOrderBy() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.orderByDesc("age", "id");
        List<User> users = usermapper.selectList(queryWrapper);
        users.forEach(System.out::println);
    }

Query mode

Query mode	explain
setSqlSelect	Set SELECT query field
where	WHERE statement, splice + WHERE condition
eq	Equals=
ne	Not equal to < >
gt	Greater than >
ge	Greater than or equal to >=
lt	Less than<
le	Less than or equal to<=
like	Fuzzy query LIKE
notLike	Fuzzy query NOT LIKE
in	IN query
notIn	NOT IN query
isNull	NULL value query
isNotNull	IS NOT NULL
groupBy	GROUP BY
having	HAVING keyword
groupBy	GROUP BY
orderBy	Sort ORDER BY
orderByAsc	Ascending sort
orderByDesc	Descending sort
between	BETWEEN conditional statement
notBetween	Not between conditional statement