1. Introduction to MyBatisPlus

1.1. Overview

Official website: https://baomidou.com/
MyBatis-Plus (referred to as MP) is a MyBatis 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.2. 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.
• Support multiple databases: support MySQL, MariaDB, Oracle, DB2, H2, HSQL, SQLite, Postgre, SQLServer2005, SQLServer and other databases
• 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
• Supports XML hot loading: The XML corresponding to Mapper supports hot loading. For simple CRUD operations, it can even be started without XML
• 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 )
• Support keyword automatic escaping: Support database keywords (order, key...) automatic escaping, and can also customize keywords
• 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
• 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
• Built-in Sql injection stripper: supports Sql injection stripping, effectively preventing Sql injection attacks

2. Getting started with MyBatisPlus

2.1. Create test database and tables

mybatis_plus

Its table structure is as follows:

id	name	age	email
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

The corresponding table creation statement is as follows:

CREATE TABLE user2
(
    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)
);

The corresponding database Data script is as follows:

INSERT INTO user2 (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.2. Create a project and add dependencies

(1) Use Spring Initializr to initialize the Spring Boot project

Group: com.example

Artifact: mybatis-plus

Version: 2.2.1.RELEASE

(2) The project introduces dependencies

<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>
     </dependency>

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

    <!--mysql-->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
    </dependency>

    <!--lombok Used to simplify entity classes-->
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
    </dependency>
</dependencies>

(3) Install the lombok plugin in idea

2.3, create a configuration file

mysql5

#mysql database connection
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/mybatis_plus
spring.datasource.username=root
spring.datasource.password=123456

mysql8 and above (spring boot 2.1) Note: changes in driver and url

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

Notice:

1. The url here uses the ?serverTimezone=GMT%2B8 suffix, because Spring Boot 2.1 integrates the 8.0 version of the jdbc driver. This version of the jdbc driver needs to add this suffix, otherwise running the test case will report the following error:

java.sql.SQLException: The server time zone value 'Öйú±ê׼ʱ¼ä' is unrecognized or represents more

2, here driver-class-name uses com.mysql.cj.jdbc.Driver, recommended in jdbc 8, previously com.mysql.jdbc.Driver has been discarded or WARN information will be provided when the test case is run

2.4, write code

(1) Create a startup class

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

@SpringBootApplication
@MapperScan("com.atguigu.mybatisplus.mapper")
public class MybatisPlusApplication {
    ......
}

(2) Create an entity class

@Data
public class UserGgkt {
    @TableId()
    private Long id;
    private String name;
    private Integer age;
    private String email;
}

(3) Create Mapper

public interface UserMapper extends BaseMapper<UserGgkt> {
}

(4) Functional test - query all records

public class MybatisPlusApplicationTests {

    @Autowired
    private UserMapper userMapper;

    @Test
    public void testSelectList() {
        System.out.println(("----- selectAll method test ------"));
        //The parameter of the selectList() method in UserMapper is MP's built-in conditional wrapper Wrapper
        //So if you don't fill it out, you don't have any conditions.
        List<User> users = userMapper.selectList(null);
        users.forEach(System.out::println);
    }
}

Notice:

IDEA reports an error at userMapper because the injected object cannot be found, because the class is dynamically created, but the program can be executed correctly.

In order to avoid errors, you can add the @Repository annotation to the interface of the mapper layer
or replace @Autowired with @Resource

lombok exception:
class lombok.javac.apt.LombokProcessor (in unnamed module @0x7a8b9166) cannot access class com.sun.tools.javac.processing.JavacProcessingEnvironment (in module jdk.compiler) because module jdk.compiler does not export com.sun.tools.javac.processing to unnamed module @0x7a8b9166
The current lombok version is too low Change the version to 1.18.20

3. MyBatisPlus implements CRUD operations

3.1, insert operation

@SpringBootTest
public class CrudTest {
    @Resource
    UserMapper userMapper;
    @Test
    public void testCrudInsert(){
        UserGgkt userGgkt = new UserGgkt();
        userGgkt.setAge(18);
        userGgkt.setName("Allen");
        userGgkt.setEmail("test@qq.com");
//        Returns the number of rows affected
        int insert = userMapper.insert(userGgkt);
        System.out.println(insert);
//        id is automatically backfilled
        System.out.println(userGgkt);

    }
}

**Note: **Database insert id value defaults to: globally unique id

View sql output log

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

3.2, primary key strategy

(1)ID_WORKER

The default primary key strategy of MyBatis-Plus is: ID_WORKER global unique ID

(2) Self-increment strategy

• To auto-increment the primary key, you need to configure the following primary key strategy

• • You need to set the primary key auto-increment when creating the data table
  • Configure @TableId(type = IdType.AUTO) in the entity field

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

Other primary key strategies: analyze the IdType source code to know

public enum IdType {
     /**
     * Database ID auto increment
     */
    AUTO(0),
    
    /**
     * The type is not set the primary key type
     */
    NONE(1),
    
    /**
     * User input ID
     * This type can be filled by registering an autofill plugin by itself
     */    
    INPUT(2),
    
    /**
     * Globally Unique ID
     */    
    ASSIGN_ID(3),
    
    /**
     * Universally Unique ID (UUID)
     */
    ASSIGN_UUID(4),
    
    /** @deprecated */
    @Deprecated
    ID_WORKER(3),
    /** @deprecated */
    @Deprecated
    ID_WORKER_STR(3),
    /** @deprecated */
    @Deprecated
    UUID(4);
    private final int key;
    private IdType(int key) {
        this.key = key;
    }
    public int getKey() {
        return this.key;
    }
}

3.3. Update operation according to Id

**Note: **The sql generated during update is automatically dynamic sql: UPDATE user SET age=? WHERE id=?

    @Test
    public void testUpdateById(){
        //1 Query records based on id
        User user = userMapper.selectById(1L);
        //2 Set the modified value
        user.setAge(50);
        //3 Call method modification
        int result = userMapper.updateById(user);
        System.out.println(result);
    }

3.4. Paging query

MyBatis Plus comes with a paging plug-in, and the paging function can be realized with a simple configuration

(1) Create a configuration class

/**
 * Pagination plugin
 */
@Bean
public PaginationInterceptor paginationInterceptor() {
    return new PaginationInterceptor();
}

(2) Test selectPage paging

**Test:**Finally get the relevant data through the page object

@Test
public void testSelectPage() {
    Page<User> page = new Page<>(1,5);
    userMapper.selectPage(page, null);
    page.getRecords().forEach(System.out::println);
    System.out.println(page.getCurrent());
    System.out.println(page.getPages());
    System.out.println(page.getSize());
    System.out.println(page.getTotal());
    System.out.println(page.hasNext());
    System.out.println(page.hasPrevious());
}

Console sql statement prints: SELECT id,name,age,email,create_time,update_time FROM user LIMIT 0,5

3.5. Delete records according to id

@Test
public void testDeleteById(){
    int result = userMapper.deleteById(1);
    System.out.println(result);
}

3.6. Batch delete

    @Test
    public void testDeleteBatchIds() {
        int result = userMapper.deleteBatchIds(Arrays.asList(8, 9, 10));
        System.out.println(result);
    }

3.7. Logical deletion

• Physical deletion: real deletion, delete the corresponding data from the database, and then the deleted data cannot be queried
• Logical deletion: Fake deletion, change the status of the field in the corresponding data representing whether it has been deleted to "deleted status", and then you can still see this data record in the database

(1) Add the deleted field to the database

ALTER TABLE `user` ADD COLUMN `deleted` boolean

(2) Add deleted field to entity class

And add @TableLogic annotation

@TableLogic
private Integer deleted;

(3) application.properties join the configuration

This is the default value, if your default value is the same as the mp default, this configuration can be omitted

mybatis-plus.global-config.db-config.logic-delete-value=1
mybatis-plus.global-config.db-config.logic-not-delete-value=0

(5) Test logical deletion

• After the test, it was found that the data was not deleted, and the value of the deleted field changed from 0 to 1
• After the test, analyze the printed sql statement, which is an update
• **Note:** The value of the deleted field of the deleted data must be 0 in order to be selected for tombstone deletion

/**
 * test tombstone
 */
@Test
public void testLogicDelete() {
    int result = userMapper.deleteById(1L);
    System.out.println(result);
}

(7) Test the query after logical deletion

The query operation in MyBatis Plus will also automatically add the judgment of the tombstone field

/**
 * Test the query after tombstone:
 * Does not include tombstoned records
 */
@Test
public void testLogicDeleteSelect() {
    User user = new User();
    List<User> users = userMapper.selectList(null);
    users.forEach(System.out::println);
}

Analyze the printed sql statement after the test, including WHERE deleted=0

SELECT id,name,age,email,create_time,update_time,deleted FROM user WHERE deleted=0

4. MyBatisPlus Conditional Constructor

Wrapper: Conditional construction abstract class, the top parent class

AbstractWrapper: used to encapsulate query conditions and generate where conditions of sql

​ QueryWrapper: The Entity object encapsulates the operation class, not using lambda syntax

​ UpdateWrapper : Update condition encapsulation for Entity object update operation

AbstractLambdaWrapper: Lambda syntax uses Wrapper to uniformly process parsing lambda to get column.

​ LambdaQueryWrapper: You can see from the name that it is the query Wrapper used for Lambda syntax

​ LambdaUpdateWrapper: Lambda update package Wrapper

Note: The column in the method input parameters of the following conditional constructors all represent database fields

4.1, QueryWrapper use

(1)ge,gt,le,lt
gt is greater than >
ge greater than or equal to >=
lt is less than <
le is less than or equal to <=

@Test
public void testSelect() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.ge("age", 28);
    List<User> users = userMapper.selectList(queryWrapper);
    System.out.println(users);
}

(2)eq,ne
eq equal
ne range
**Note: **seletOne returns an entity record, an error will be reported when there are multiple records

@Test
public void testSelectOne() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.eq("name", "Tom");
    User user = userMapper.selectOne(queryWrapper);
    System.out.println(user);
}

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

(3)like,likeLeft,likeRight

selectMaps returns a list of Map collections

@Test
public void testSelectMaps() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper
        .like("name", "e")
        .likeRight("email", "t");
    List<Map<String, Object>> maps = userMapper.selectMaps(queryWrapper);//The return value is a list of Map s
    maps.forEach(System.out::println);
}

SELECT id,name,age,email,create_time,update_time,deleted,version

FROM user WHERE deleted=0 AND name LIKE ? AND email LIKE ?

(4)orderByDesc,orderByAsc
orderByDesc descending order
orderByAsc ascending order

@Test
public void testSelectListOrderBy() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.orderByDesc("id");
    List<User> users = userMapper.selectList(queryWrapper);
    users.forEach(System.out::println);
}

SELECT id,name,age,email,create_time,update_time,deleted,version

FROM user WHERE deleted=0 ORDER BY id DESC

4.2. Using LambdaQueryWrapper

@Test
public void testLambdaQuery() {
    LambdaQueryWrapper<User> queryWrapper = new LambdaQueryWrapper<>();
    queryWrapper.eq(User::getAge,30);
    queryWrapper.like(User::getName,"open");
    List<User> list = userMapper.selectList(queryWrapper);
    System.out.println(list);
}

SELECT id,name,age,email,create_time,update_time,deleted,version

FROM user WHERE deleted=0 AND age = ? AND name LIKE ?

5. MyBatisPlus encapsulates the Service layer

5.1. Create service

public interface UserService extends IService<User> {
    
}

5.2, create a service implementation class

@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {
}

The bottom layer encapsulates the process of injecting Mapper

5.3, method call test

@SpringBootTest
class TestApplicationTests {

    //inject service
    @Autowired
    private UserService userService;
    
    //Query all data in the table
    @Test
    public void findAll() {
        List<User> userList = userService.list();
        for (User user:userList) {
            System.out.println(user);
        }
    }
}