(grain project VI) mybatis plus basic application CRUD, primary key policy, automatic filling, optimistic lock, etc

brief introduction

Official website: http://mp.baomidou.com/

Reference tutorial: http://mp.baomidou.com/guide/

MyBatis-Plus (MP) is a MyBatis On the basis of MyBatis, the enhancement tool is only enhanced without change. It is born to simplify development and improve efficiency.

quick get start

Quick start reference: http://mp.baomidou.com/guide/quick-start.html

CRUD interface

1, insert

**1. Insert operation

@RunWith(SpringRunner.class)
@SpringBootTest
public class CRUDTests {

    @Autowired
    private UserMapper userMapper;

    @Test
    public void testInsert(){

        User user = new User();
        user.setName("Helen");
        user.setAge(18);
        user.setEmail("55317332@qq.com");

        int result = userMapper.insert(user);
        System.out.println(result); //Number of rows affected
        System.out.println(user); //id auto backfill
    }
}

**Note: * * the database insert id value defaults to globally unique id

[external chain picture transfer failed. The source station may have an anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-xstqnk7n-16337707664667)( file:///E:/weizhiBiJi/temp/3b31cde1 -8ec5-4e1a-8284-9544bf92d819/128/index_files/93ff417f-c9f7-4225-b395-2afe2776183d.jpg)]

2. Primary key policy

(1)ID_WORKER

The default primary key policy of mybatis plus is ID_WORKER globally unique ID

Reference: distributed system unique ID generation scheme summary: https://www.cnblogs.com/haoxinyue/p/5208136.html

(2) Self increasing strategy

To automatically increase the primary key, you need to configure the following primary key policies
- You need to set primary key auto increment when creating a data table
- Configure @ TableId(type = IdType.AUTO) in the entity field

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

To affect the configuration of all entities, you can set the global primary key configuration

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

Other primary key strategies: analyze the IdType source code

@Getter
public enum IdType {
    /**
     * Database ID self increment
     */
    AUTO(0),
    /**
     * The type is not set with primary key
     */
    NONE(1),
    /**
     * User input ID
     * This type can be populated by registering its own auto fill plug-in
     */
    INPUT(2),

    /* The following three types are automatically filled only when the inserted object ID is empty. */
    /**
     * Globally unique ID (idWorker)
     */
    ID_WORKER(3),
    /**
     * Globally unique ID (UUID)
     */
    UUID(4),
    /**
     * String globally unique ID (string representation of idworker)
     */
    ID_WORKER_STR(5);

    private int key;

    IdType(int key) {
        this.key = key;
    }
}

2, update

1. Update operation by Id

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

    @Test
    public void testUpdateById(){

        User user = new User();
        user.setId(1L);
        user.setAge(28);

        int result = userMapper.updateById(user);
        System.out.println(result);

    }

2. Auto fill

Some data are often encountered in the project, which are filled in the same way every time, such as the creation time and update time of the record.

We can use the auto fill function of MyBatis Plus to complete the assignment of these fields:

Add autofill fields to database tables

Add a new field create of type datetime to the User table_ time,update_time

**Automatic filling scheme I. automatic filling of data level, set the default values respectively:

`create_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

Automatic filling scheme 2. Business layer processing:

**(1) Add fields and comments on entities

@Data
public class User {
    ......
        
    @TableField(fill = FieldFill.INSERT)
    private Date createTime;

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

(2) Implement meta object processor interface

**Note: don't forget to add @ Component annotation

package com.atguigu.mybatisplus.handler;

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

import java.util.Date;

/**
 * @author helen
 * @since 2019/2/22
 */
@Slf4j
@Component
public class MyMetaObjectHandler implements MetaObjectHandler {

    @Override
    public void insertFill(MetaObject metaObject) {
        log.info("start insert fill ....");
        this.setFieldValByName("createTime", new Date(), metaObject);
        this.setFieldValByName("updateTime", new Date(), metaObject);
    }

    @Override
    public void updateFill(MetaObject metaObject) {
        log.info("start update fill ....");
        this.setFieldValByName("updateTime", new Date(), metaObject);
    }
}

(3) Testing

3. Optimistic lock

**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

Optimistic lock implementation method:

When fetching records, get the current version: assuming the fetched version=1

SELECT id,name,age,email,create_time,update_time,version FROM user WHERE id=1

When updating, bring this version

UPDATE USER SET `name`='helen yao', `version`=`version` + 1 WHERE id=1 AND `version`=1

When updating, set version = newVersion where version = oldVersion
If the version is incorrect, the update fails

**(1) Add version field to database

ALTER TABLE `user` ADD COLUMN `version` INT DEFAULT 1

(2) Add version field to entity class

And add @ Version annotation

@Version
private Integer version;

Special note:

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

(3) Create profile

Create the package config and create the file MybatisPlusConfig.java

At this point, you can delete the @ MapperScan scan annotation in the main class

package com.atguigu.mybatisplus.config;

@EnableTransactionManagement
@Configuration
@MapperScan("com.atguigu.mybatisplus.mapper")
public class MybatisPlusConfig {
    
}

**(4) Register beans in MybatisPlusConfig

/**
     * Optimistic lock plug-in
     */
@Bean
public OptimisticLockerInterceptor optimisticLockerInterceptor() {
    return new OptimisticLockerInterceptor();
}

(5) The test lock can be modified successfully

After the test, analyze the printed sql statement and add 1 to the value of version

/**
 * Test optimistic lock plug-in
 */
@Test
public void testOptimisticLocker() {

    //query
    User user = userMapper.selectById(1L);
    //Modify data
    user.setName("Helen Yao");
    user.setEmail("helen@qq.com");
    //Execute update
    userMapper.updateById(user);
}

**(5) Test lock modification failed

/**
     * Failed to test optimistic lock plug-in
     */
@Test
public void testOptimisticLockerFail() {

    //query
    User user = userMapper.selectById(1L);
    //Modify data
    user.setName("Helen Yao1");
    user.setEmail("helen1@qq.com");

    //Simulate that the data is updated in the middle of another thread
    //query
    User user2 = userMapper.selectById(1L);
    //Modify data
    user2.setName("Helen Yao2");
    user2.setEmail("helen2@qq.com");
    userMapper.updateById(user2);


    //Execute update
    userMapper.updateById(user);
}

3, select

**1. Query records by id

@Test
public void testSelectById(){

    User user = userMapper.selectById(1L);
    System.out.println(user);
}

2. Batch query through multiple IDS

The foreach function of dynamic sql is completed

@Test
public void testSelectBatchIds(){

    List<User> users = userMapper.selectBatchIds(Arrays.asList(1, 2, 3));
    users.forEach(System.out::println);
}

3. Simple conditional query

Encapsulate query criteria through map

@Test
public void testSelectByMap(){

    HashMap<String, Object> map = new HashMap<>();
    map.put("name", "Helen");
    map.put("age", 18);
    List<User> users = userMapper.selectByMap(map);

    users.forEach(System.out::println);
}

**Note: * * the key in the map corresponds to the column name in the database. For example: database user_id, and the entity class is userId. In this case, the key of map needs to be filled in user_id

4. Pagination

MyBatis Plus comes with a paging plug-in, which can realize paging function with simple configuration

**(1) Add paging plug-in to configuration class

/**
 * Paging plug-in
 */
@Bean
public PaginationInterceptor paginationInterceptor() {
    return new PaginationInterceptor();
}

(2) Test selectPage paging

**Test: * * finally obtain 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 printing: SELECT id,name,age,email,create_time,update_time FROM user LIMIT 0,5

**(3) Test selectMapsPage paging: the result set is Map

@Test
public void testSelectMapsPage() {

    Page<User> page = new Page<>(1, 5);

    IPage<Map<String, Object>> mapIPage = userMapper.selectMapsPage(page, null);

    //Note: this line must use mapIPage to obtain the record list, otherwise there will be data type conversion errors
    mapIPage.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());
}

4, delete

**1. Delete record by id

@Test
public void testDeleteById(){

    int result = userMapper.deleteById(8L);
    System.out.println(result);
}

**2. Batch delete

@Test
public void testDeleteBatchIds() {

    int result = userMapper.deleteBatchIds(Arrays.asList(8, 9, 10));
    System.out.println(result);
}

**3. Simple conditional query deletion

@Test
public void testDeleteByMap() {

    HashMap<String, Object> map = new HashMap<>();
    map.put("name", "Helen");
    map.put("age", 18);

    int result = userMapper.deleteByMap(map);
    System.out.println(result);
}

4. Logical deletion

Physical delete: real delete, delete the corresponding data from the database, and then query the deleted data
Logical deletion: false deletion. Change the status of the field representing whether to be deleted in the corresponding data to deleted status. After that, you can still see this data record in the database

**(1) Add deleted field to database

ALTER TABLE `user` ADD COLUMN `deleted` boolean DEFAULT 0

(2) Add deleted field to entity class

Add @ TableLogic annotation and @ TableField(fill = FieldFill.INSERT) annotation

@TableLogic
private Integer deleted;

(3) application.properties add configuration

This is the default value. If your default value is the same as mp default, this configuration can be null

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

**(4) Register beans in MybatisPlusConfig

@Bean
public ISqlInjector sqlInjector() {
    return new LogicSqlInjector();
}

(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 to be selected for logical deletion

/**
 * Test logical deletion
 */
@Test
public void testLogicDelete() {

    int result = userMapper.deleteById(1L);
    System.out.println(result);
}

(7) Query after test logic deletion

The query operation in MyBatis Plus will also automatically add the judgment of logically deleting fields

/**
 * Query after test logic deletion:
 * Records deleted logically are not included
 */
@Test
public void testLogicDeleteSelect() {
    User user = new User();
    List<User> users = userMapper.selectList(null);
    users.forEach(System.out::println);
}

After the test, analyze the printed sql statements, including WHERE deleted=0

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

5, Performance analysis

Performance analysis interceptor, which is used to output each SQL statement and its execution time

SQL performance analysis is performed, the development environment is used, and it stops running after the specified time. Help identify problems

1. Configure plug-ins

(1) Parameter description

Parameter: maxTime: the maximum SQL execution time, which exceeds the automatic stop, helps to find problems.

Parameter: Format: whether to format SQL. The default is false.

**(2) Configure in MybatisPlusConfig

/**
 * SQL Execute performance analysis plug-in
 * It is not recommended to use the development environment online. maxTime refers to the maximum sql execution time
 */
@Bean
@Profile({"dev","test"})// Set dev test environment on
public PerformanceInterceptor performanceInterceptor() {
    PerformanceInterceptor performanceInterceptor = new PerformanceInterceptor();
    performanceInterceptor.setMaxTime(100);//ms. if the ms set here is exceeded, sql will not be executed
    performanceInterceptor.setFormat(true);
    return performanceInterceptor;
}

**(3) Set dev environment in Spring Boot

#Environment settings: dev, test, prod
spring.profiles.active=dev

You can create different configuration files application-dev.properties, application-test.properties and application-prod.properties for each environment

You can also customize the environment name, such as test1 and test2

2. Testing

**(1) Routine test

/**
 * Test performance analysis plug-in
 */
@Test
public void testPerformance() {
    User user = new User();
    user.setName("I am Helen");
    user.setEmail("helen@sina.com");
    user.setAge(18);
    userMapper.insert(user);
}

Output:

[external chain picture transfer failed. The source station may have anti-theft chain mechanism. It is recommended to save the picture and upload it directly (IMG ouuwmh1-1633770766471)( file:///E:/weizhiBiJi/temp/3b31cde1 -8ec5-4e1a-8284-9544bf92d819/128/index_files/bb355a17-3cdc-4f0a-82f2-232defbd235b.png)]

(2) Test again after reducing maxTime

performanceInterceptor.setMaxTime(5);//ms, if it exceeds the ms set here, it will not be executed

If the execution time is too long, an exception will be thrown: The SQL execution time is too large,