MyBatis Plus detailed tutorial

MyBatis plus is an enhancement tool of MyBatis. Based on MyBatis, it only makes enhancements without changes, and is born to simplify development and improve efficiency.

characteristic:

No invasion: only enhance without change, and the introduction of it will not affect the existing project, as smooth as silk
Low loss: the basic CURD will be automatically injected upon startup, with basically no loss of performance, and direct object-oriented operation
Powerful crud operation: built in general Mapper and general Service, most CRUD operations of a single table can be realized through only a few configurations, and there is a more powerful condition builder to meet various use needs
Support Lambda form calls: through Lambda expressions, you can easily write various query conditions without worrying about wrong fields
Support automatic generation of primary key: support up to four primary key strategies (including distributed unique ID generator Sequence), which can be freely configured to perfectly solve the primary key problem
Support ActiveRecord mode: support ActiveRecord formal calls. Entity classes only need to inherit Model classes to perform powerful CRUD operations
Support custom global general operations: support global general method injection (Write once, use anywhere)
Built in code generator: code or Maven plug-in can be used to quickly generate Mapper, Model, Service, Controller layer code, support template engine, and have more customized configurations 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 plug-in supports a variety of databases: MySQL, MariaDB, Oracle, DB2, H2, HSQL, SQLite, Postgre, SQLServer and other databases
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 to quickly find out slow queries
Built in global interception plug-in: it provides intelligent analysis and blocking of full table delete and update operations, and can also customize interception rules to prevent misoperation

2, Get started

2.1. Create database mybatis_plus

2.2. Create user table

DROP TABLE IF EXISTS user;
?
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)
);

2.3 insert data

DELETE FROM user;
?
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.4. Initialization items

Quickly initialize an empty spring boot project

2.5. Add dependency

Reference spring boot starter parent project

<parent>
 ? ?<groupId>org.springframework.boot</groupId>
 ? ?<artifactId>spring-boot-starter-parent</artifactId>
 ? ?<version>2.6.5</version>
 ? ?<relativePath/>
</parent>

Introduce spring boot starter, spring boot starter test, mybatis plus boot starter, h2 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>
 ? ?</dependency>
 ? ?<dependency>
 ? ? ? ?<groupId>com.baomidou</groupId>
 ? ? ? ?<artifactId>mybatis-plus-boot-starter</artifactId>
 ? ? ? ?<version>3.5.1</version>
 ? ?</dependency>
 ? ?<dependency>
 ? ? ? ?<groupId>mysql</groupId>
 ? ? ? ?<artifactId>mysql-connector-java</artifactId>
 ? ?</dependency>
</dependencies>

Note: try not to import mybatis and mybatis at the same time_ Plus, version difference

2.6. Configuration (connect to the database)

Add the relevant configuration of MySQL database in the application.yml configuration file:

# DataSource Config
spring:
  datasource:
 ?  username: root
 ?  password: 123456
 ?  url: jdbc:mysql:///mybatis_plus?userUnicode=true&characterEncoding=utf-8
 ?  driver-class-name: com.mysql.cj.jdbc.Driver

Add the @MapperScan annotation in the spring boot startup class to scan the Mapper folder:

@SpringBootApplication
@MapperScan("com.wen.mybatis_plus.mapper") ?//Scan mapper
public class MybatisPlusApplication {
?
 ? ?public static void main(String[] args) {
 ? ? ? ?SpringApplication.run(MybatisPlusApplication.class, args);
 ?  }
?
}

2.7 coding

Write the entity class User.java (Lombok is used here to simplify the code)

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

Write UserMapper interface under Mapper package

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.wen.mybatis_plus.pojo.User;
import org.apache.ibatis.annotations.Mapper;
?
//Then implement the basic interface BaseMapper on the corresponding mapper
@Mapper
public interface UserMapper extends BaseMapper<User> {
 ? ?//All CRUD has been completed
 ? ?//There is no need to configure a lot of files as before: POJO Dao (connect to mybatis and configure the mapper.xml file) = = >service controller
}

be careful:

To scan all interfaces under the mapper package on the main startup class: @MapperScan("com.wen.mybatis_plus.mapper")

2.8. Start using

Add a test class for function test:

@SpringBootTest
class MybatisPlusApplicationTests {
?
 ? ?//Inheriting all methods of BaseMapper, you can write your own extension methods
 ? ?@Autowired
 ? ?private UserMapper userMapper;
?
 ? ?@Test
 ? ?public void testSelect(){
 ? ? ? ?System.out.println("--------selectAll method test-------");
 ? ? ? ?//Query all users. The parameter is a Wrapper and a condition constructor. If not used first, it is null
 ? ? ? ?List<User> userList = userMapper.selectList(null);
 ? ? ? ?userList.forEach(System.out::println);
 ?  }

Tips:

The parameter of the selectList() method in UserMapper is the built-in conditional Wrapper wrapper of MP, so there are no conditions if it is not filled in.

Console output:

User(id=1, name=Jone, age=18, email=test1@baomidou.com) User(id=2, name=Jack, age=20, email=test2@baomidou.com) User(id=3, name=Tom, age=28, email=test3@baomidou.com) User(id=4, name=Sandy, age=21, email=test4@baomidou.com) User(id=5, name=Billie, age=24, email=test5@baomidou.com)

2.9 summary

The above steps have realized the CRUD function of the User table, and even the XML file does not need to be written. The above steps can be seen that the integration of mybatis plus is very simple. You only need to introduce the starter project and configure the mapper scanning path. The methods are all written by mybatis plus and can be quoted directly.

3, Configuration log

All SQL is invisible, so if you want to see how SQL is executed in the background, you must configure logs.

Configure logs in the.yml configuration file:

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

4, CRUD

4.1 insertion test

//Test insertion
@Test
public void testInsert(){
 ? ?User user = new User();
 ? ?user.setName("Essay");
 ? ?user.setAge(21);
 ? ?user.setEmail("2312103645@qq.com");
?
 ? ?int insert = userMapper.insert(user);//If the id is not set, the id will be automatically generated
 ? ?System.out.println(insert);//Number of affected rows
 ? ?System.out.println(user);//id will be backfilled automatically
}

The default value of database insert ID is globally unique ID

4.2. Custom ID generator

In complex distributed systems, a large amount of data and messages are often required for unique identification. For example, each account of Alipay needs to have a unique ID to identify after the database is divided into tables. At this time, a system that can generate globally unique IDs is very necessary.

Therefore, the generated ID needs to have the following characteristics:

Global uniqueness: duplicate ID numbers cannot appear. Since they are unique, this is the most basic requirement.
Increasing trend: clustered indexes are used in MySQL InnoDB engine. Since most RDBMS use B-tree data structure to store index data, we should try to use ordered primary keys to ensure writing performance in the selection of primary keys.
Monotonic increment: ensure that the next ID is greater than the previous ID, such as transaction version number, IM increment message, sorting and other special requirements.
Information security: if the ID is continuous, it is very easy for malicious users to steal. Just download the specified URL in order; If it's the order number, it's even more dangerous. The competitor can directly know our daily order quantity. Therefore, in some application scenarios, ID is irregular and irregular.

The above 123 corresponds to three different scenarios. The requirements of 3 and 4 are mutually exclusive and cannot be met with the same scheme.

Here we will only talk about two schemes of automatic ID generation, UUID and SnowFlake

You can check the methods and source code:

Add the @TableId annotation on the user ID, and the value in it is the method of using the primary key to automatically generate

Since 3.3.0, snowflake algorithm +uuid (excluding dash) is used by default

@Data
public class User {
 ? ?//The primary key in the corresponding database (UUID, auto increment id, snowflake algorithm, redis, zookeeper)
 ? ?@TableId(type = IdType.ASSIGN_ID)
 ? ?private Long id;
 ? ?private String name;
 ? ?private Integer age;
 ? ?private String email;
}

Click IdType to view the source code and see what automatic generation methods are available

/**
 * Generate ID type enumeration class
 *
 * @author hubin
 * @since 2015-11-10
 */
@Getter
public enum IdType {
 ? ?/**
 ? ? * Database ID self increment
 ? ? * <p>For this type, please ensure that the database is set with ID auto increment, otherwise it is invalid</p>
 ? ? */
 ? ?AUTO(0),
 ? ?/**
 ? ? * This type is not set with primary key (it is equal to follow global in the annotation, and global Rio is equal to INPUT)
 ? ? */
 ? ?NONE(1),
 ? ?/**
 ? ? * User input ID
 ? ? * <p>This type can be filled by registering the auto fill plug-in</p>
 ? ? */
 ? ?INPUT(2),
?
 ? ?/* The following three types are automatically filled only when the inserted object ID is empty. */
 ? ?/**
 ? ? * Assign ID (the primary key type is number or string),
 ? ? * The default implementation class {@link com.baidu.mybatisplus.core.increment.defaultidentifiergenerator} (snowflake algorithm)
 ? ? *
 ? ? * @since 3.3.0
 ? ? */
 ? ?ASSIGN_ID(3),
 ? ?/**
 ? ? * Assign UUID (the primary key type is string)
 ? ? * The default implementation class {@link com.baidu.mybatisplus.core.increment.defaultidentifiergenerator} (uuid.replace ("-", ""))
 ? ? */
 ? ?ASSIGN_UUID(4);
?
 ? ?private final int key;
?
 ? ?IdType(int key) {
 ? ? ? ?this.key = key;
 ?  }
}

4.2.1,UUID

The standard type of UUID (universal unique identifier) includes 32 hexadecimal digits, which are divided into five segments with hyphens and 36 characters in the form of 8-4-4-4-12. Example: 550e8400-e29b-41d4-a716-446655440000. So far, there are five ways to generate UUIDs in the industry. See the UUID specification issued by IETF for details A Universally Unique IDentifier (UUID) URN Namespace.

advantage:

Very high performance: local generation, no network consumption.

Disadvantages:

Without sorting, the trend cannot be guaranteed to increase.
UUID s are often stored in strings, and the efficiency of query is relatively low.
Not easy to store: UUID is too long, 16 bytes and 128 bits. It is usually represented by a 36 length string, which is not applicable in many scenarios.
Information insecurity: the algorithm of generating UUID based on MAC address may cause MAC address leakage. This vulnerability has been used to find the location of the creator of Melissa virus.
When ID is used as the primary key, there will be some problems in a specific environment. For example, in the scenario of DB primary key, UUID is very inapplicable:
- MySQL officially has a clear recommendation that the primary key should be as short as possible [4], and the 36 character UUID does not meet the requirements.
- Bad for MySQL index: if it is used as the database primary key, under the InnoDB engine, the disorder of UUID may cause frequent changes in data location, seriously affecting performance.

4.2.2 SnowFlake (SnowFlake algorithm)

Generally speaking, this scheme is an algorithm to generate IDS by dividing namespaces (UUID s are also considered, which are common, so they are analyzed separately). This scheme divides 64 bit into multiple segments to mark machines, times, etc. for example, the 64 bit differential representation in snowflake is shown in the following figure (pictures from the network):

41 bit time can be expressed as (1L < < 41) / (1000L_3600_24*365)=69 years, and 10 bit machines can represent 1024 machines respectively. If we have requirements for IDC division, we can also divide 10 bit into 5-bit for IDC and 5-bit for working machines. In this way, 32 IDCs can be represented, and there can be 32 machines under each IDC, which can be defined according to their own needs. 12 self incrementing serial numbers can represent 2^12 IDs. Theoretically, the QPS of the snowflake scheme is about 409.6w/s. This allocation method can ensure that the IDs generated by any machine in any IDC are different in any milliseconds.

Core idea:

41bit is used as the number of milliseconds, 10bit is used as the ID of the machine (5 bits are the data center and 5 bits are the machine ID), 12bit is the serial number within milliseconds (meaning that each node can generate 4096 IDS per millisecond), and finally there is a symbol bit, which is always 0,.

advantage:

The number of milliseconds is in the high order, and the self increasing sequence is in the low order. The whole ID is increasing in trend.
It does not rely on third-party systems such as databases. It is deployed in the form of services, which has higher stability and high performance of ID generation.
bit bits can be allocated according to their own business characteristics, which is very flexible.

Disadvantages:

It strongly depends on the machine clock. If the clock on the machine is dialed back, it will lead to duplicate signal issuance or the service will be unavailable.

4.3. Update operation

All SQL is automatically configured

//Test update
@Test
public void testUpdate(){
 ? ?User user = new User();
 ? ?//Dynamic SQL can be spliced automatically by conditions
 ? ?user.setId(5L);
 ? ?user.setName("id:5,After modification");
 ? ?//updateById parameter is an object!
 ? ?int i = userMapper.updateById(user);
 ? ?System.out.println(i);
}

Note: the updateById parameter is an object! Not ID

5, Auto fill

5.1. What is automatic filling

In common businesses, some attributes need to be configured with some default values. Mybatis plus provides a plug-in to realize this function, that is, the automatic filling function. For example, the creation time and modification time are generally completed automatically, and there is no need to update them manually.

5.2. Automatic filling mode

Method 1: database level (not recommended)

1. Add a new field create in the table_ time,update_ time

Note: in create_ In time, except that update according to the current timestamp is not checked, other steps are the same!

2. To test the insertion method here, you need to synchronize the entity class!!

private Data createTime;
private Data updateTime;

3. Update here to view

//Test update
@Test
public void testUpdate(){
 ? ?User user = new User();
 ? ?//Dynamic SQL can be spliced automatically by conditions
 ? ?user.setId(5l);
 ? ?user.setName("id:5,After modification");
 ? ?user.setAge(25);
 ? ?//updateById parameter is an object!
 ? ?int i = userMapper.updateById(user);
 ? ?System.out.println(i);
}

4. Check whether the timestamp is updated

Method 2: code level

1. Delete the default value of the database and update it

2. The annotation filling field @TableField(.. fill = FieldFill.INSERT) generator policy part can also be configured!

//The annotation filling field @TableField(.. fill = FieldFill.INSERT) generator policy part can also be configured!
@TableField(fill = FieldFill.INSERT)
private Data createTime;
@TableField(fill = FieldFill.INSERT_UPDATE)
private Data updateTime;

3. The custom implementation class MyMetaObjectHandler handles this annotation

@Slf4j
@Component
public class MyMetaObjectHandler implements MetaObjectHandler {
 ? ?//Population policy at insert time
 ? ?@Override
 ? ?public void insertFill(MetaObject metaObject) {
 ? ? ? ?log.info("start intsert fill ....");
 ? ? ? ?//strictInsertFill(MetaObject metaObject, String fieldName, Class<T> fieldType, E fieldVal)
 ? ? ? ?this.strictInsertFill(metaObject,"createTime", LocalDateTime.class,LocalDateTime.now());// Starting version 3.3.0 (recommended)
 ?  }
?
 ? ?//Population policy at update
 ? ?@Override
 ? ?public void updateFill(MetaObject metaObject) {
 ? ? ? ?log.info("start update fill ....");
 ? ? ? ?this.strictUpdateFill(metaObject, "updateTime", LocalDateTime.class, LocalDateTime.now()); // Starting version 3.3.0 (recommended)
 ?  }
}

6, Optimistic lock and pessimistic lock

6.1. What is optimistic lock

Optimistic lock: I'm very optimistic that there will be no problem. No matter what I do, I won't lock it. If there is a problem, I'll update the test value again

Optimistic locking is to maintain an optimistic attitude towards data conflicts. When operating data, it will not lock the operated data (which enables multiple tasks to operate on the data in parallel). Only when the data is submitted can a mechanism be used to verify whether there is a conflict in the data (the general implementation method is to add a version number and then compare the version numbers);

Features: optimistic lock is a kind of concurrent lock. It does not lock data, but realizes the function of locking through business. Not locking data means that multiple requests are allowed to access data at the same time, and the process of locking and unlocking data is also omitted. This method can improve the performance of operations to a certain extent because it saves the operation of pessimistic lock locking. However, in the case of very high concurrency, It will lead to a large number of request conflicts, which will lead to the failure of most operations and waste of resources. Therefore, in the scenario of high concurrency, the performance of optimistic locks is not as good as pessimistic locks.

6.2. What is pessimistic lock

Pessimistic lock: very pessimistic. I think there are always problems. No matter what I do, I will lock it and operate it again

Pessimistic lock is based on a pessimistic attitude class to prevent all data conflicts. It locks the data before modifying the data in a preventive attitude, and then reads and writes the data. Before it releases the lock, no one can operate on its data. After the previous person releases the lock, the next person can lock the data, and then operate on the data, Generally, the locking mechanism of the database itself is based on the pessimistic locking mechanism;

Features: it can completely ensure the exclusivity and correctness of data, because each request will first lock the data, then operate the data, and finally unlock it. The process of locking and releasing the lock will cause consumption, so the performance is not high;

6.3. Configure optimistic lock

This article mainly explains the optimistic locking mechanism

Optimistic lock implementation method:

When fetching records, get the current version
Bring this version when updating
When updating, set version = newVersion where version = oldVersion
If the version is wrong, the update fails

When you want to update a record, you want it not to be updated

-- Optimistic lock: 1. Query first to obtain the version number version=1
-- A thread 
update user name = "tian" ,version = version +1
where id = 2 and version = 1
-- If B The thread finishes first. At this time version=2，Will cause A Thread modification failed
-- B thread 
update user name = "tian" ,version = version +1
where id = 2 and version = 1

Test MP's optimistic lock plug-in

6.3.1 add version field to the database

Check whether the change is completed after adding

6.3.2. Synchronous entity class

Remember to add the @Version annotation on the entity class

@Version ? ?//Optimistic lock version annotation
private Integer version;

6.3.3 configure plug-ins

spring xml method:

<bean class="com.baomidou.mybatisplus.extension.plugins.inner.OptimisticLockerInnerInterceptor" id="optimisticLockerInnerInterceptor"/>
?
<bean id="mybatisPlusInterceptor" class="com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor">
 ? ?<property name="interceptors">
 ? ? ? ?<list>
 ? ? ? ? ? ?<ref bean="optimisticLockerInnerInterceptor"/>
 ? ? ? ?</list>
 ? ?</property>
</bean>

The annotation method of springboot is explained here

Annotation method of spring boot

@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
 ? ?MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
 ? ?interceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());
 ? ?return interceptor;
}

First, create the configuration class file config. Under this file, create the configuration class MyBatisPlusConfig, which needs to add three annotations:

@Configuration ?//Configuration class
@MapperScan("com.wen.mybatis_plus.mapper") ?//Scan mapper
@EnableTransactionManagement    //Automatically manage transactions. It is on by default

@MapperScan() is to change the original scanning in MybatisPlusApplication here, so @MapperScan() is not required in MybatisPlusApplication, and @MapperScan() can be added to this configuration class

After creating the MyBatisPlusConfig class and adding annotations, you can fill in the annotation method of the above components

@Configuration ?//Configuration class
@MapperScan("com.wen.mybatis_plus.mapper") ?//Scan mapper
@EnableTransactionManagement
public class MyBatisPlusConfig {
 ? ?//Register optimistic lock plug-in
 ? ?@Bean
 ? ?public MybatisPlusInterceptor mybatisPlusInterceptor() {
 ? ? ? ?MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
 ? ? ? ?interceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());
 ? ? ? ?return interceptor;
 ?  }
}

At this point, the optimistic lock has been configured!

6.3.4 test optimistic lock

Test success and failure respectively in the test class

Test successful:

//Test successful optimistic lock
@Test
void testOptimisticLocker_success() {
 ? ?//1. Query user information
 ? ?User user = userMapper.selectById(1l);
 ? ?//2. Modify user information
 ? ?user.setName("tian");
 ? ?user.setAge(21);
 ? ?//3. Execute update operation
 ? ?userMapper.updateById(user);
}

The results are as follows:

Test failed:

Perform queue jumping by simulating multithreading

@Test
void testOptimisticLocker_failure() {
 ? ?//Simulate multithreading to achieve queue jumping effect
 ? ?//Thread 1
 ? ?User user = userMapper.selectById(1l);
 ? ?user.setName("tian");
 ? ?user.setAge(21);
 ? ?//Thread 2
 ? ?User user2 = userMapper.selectById(1l);
 ? ?user2.setName("xiaotian");
 ? ?user2.setAge(19);
 ? ?userMapper.updateById(user2); ? //Cut in line here
?
 ? ?userMapper.updateById(user); ? ?//If there is no optimistic lock, the value of queue jumping thread will be overwritten
}

View console output:

See the results from the database:

7, Add, delete, modify and check

7.1. Query operation

1. Query users by Id

//Test query
@Test
public void testSelectById(){
 ? ?User user = userMapper.selectById(1L);
 ? ?System.out.println(user);
}

result:

2. Batch query

//Batch query
@Test
public void selectBatchIds(){
 ? ?List<User> users = userMapper.selectBatchIds(Arrays.asList(1, 2, 3));
 ? ?users.forEach(System.out::println);
}

Batch query is performed through the selectBatchIds method. The set is placed in the method, which can be viewed through the source code

Source code of selectBatchIds method:

/**
 * Query (batch query according to ID)
 *
 * @param idList List of primary key ID S (cannot be null and empty)
 */
List<T> selectBatchIds(@Param(Constants.COLLECTION) Collection<? extends Serializable> idList);

You can see that the parameter is Collection, that is, Collection. Here, the Arrays Collection is used

result:

3. Condition query

Query by user-defined criteria

//Condition query
@Test
public void selectByMap(){
 ? ?HashMap<String,Object> map = new HashMap<>();
 ? ?//Custom query
 ? ?map.put("name","Essay");
 ? ?map.put("age",20);
 ? ?List<User> users = userMapper.selectByMap(map);
 ? ?users.forEach(System.out::println);
}

It can be seen that the parameters (field names, parameters) of the map class will be automatically combined into query conditions by MySQLPlus

7.2 paging query

Paging with the original limit
pageHelper third party plug-ins
MyBatisPlus built-in paging plug-in

Support database:

mysql，oracle，db2，h2，hsql，sqlite，postgresql，sqlserver，Phoenix，Gauss ，clickhouse，Sybase，OceanBase，Firebird，cubrid，goldilocks，csiidb
Dameng database, virtual Valley database, NPC Jincang database, Nanjing University General (Huaku) database, Nanjing University general database, Shentong database, Hangao database

Attribute introduction

Attribute name

type

Default value

describe

overflow

boolean

false

Whether to process after overflowing the total number of pages (not by default)

maxLimit

Long

Limit on the number of pages per page (unlimited by default)

dbType

DbType

Database type (get the paging dialect that should be used according to the type)

dialect

IDialect

Dialect implementation class

It is recommended to set dbType for single database type

How to use MyBatisPlus inner page plug-in?

1. Configure interceptor components

/**
 * Register plug-ins
 */
@Bean
public MybatisPlusInterceptor paginationInterceptor() {
?
 ? ?MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
 ? ?// Add paging plug-in
 ? ?PaginationInnerInterceptor pageInterceptor = new PaginationInnerInterceptor();
 ? ?// Set the operation after the requested page is larger than the maximum page, true calls back to the home page, and false continues the request. Default false
 ? ?pageInterceptor.setOverflow(false);
 ? ?// The number of pages per page is limited, which is unlimited by default
 ? ?pageInterceptor.setMaxLimit(500L);
 ? ?// Set database type
 ? ?pageInterceptor.setDbType(DbType.MYSQL);
?
 ? ?interceptor.addInnerInterceptor(pageInterceptor);
 ? ?return interceptor;
}

2. Paging component test

//Test the MybatisPlus paging plug-in
@Test
public void testMybatisPlus_Page(){
 ? ?// Two parameters: the default value of current is 1, starting from 1, not 0. size is the number of entries per page.
 ? ?Page<User> page = new Page<>(1, 4);
 ? ?userMapper.selectPage(page,null);
 ? ?page.getRecords().forEach(System.out::println);
}

Result analysis:

Try querying the second page

In addition, there are many methods of Page, such as:

//Other methods of page
System.out.println("Current page:" + page.getCurrent());
System.out.println("Total pages:" + page.getPages());
System.out.println("Number of records:" + page.getTotal());
System.out.println("Is there a previous page:" + page.hasPrevious());
System.out.println("Is there a next page:" + page.hasNext());

result:

7.3. Deletion

It's similar to the query operation. I won't explain it in detail, but I'll go directly to the code

//Test delete
@Test
public void testDeleteById(){
 ? ?userMapper.deleteById(4L);
}
?
//Batch delete
@Test
public void testDeleteBatchId(){
 ? ?userMapper.deleteBatchIds(Arrays.asList(1L,2L));
}
?
//Delete through map
@Test
public void testdeleteByMap(){
 ? ?Map<String, Object> map = new HashMap<>();
 ? ?map.put("name","xiaotian");
 ? ?userMapper.deleteByMap(map);
}

7.4. Logical deletion

Physical delete: delete directly from the database

Logical deletion: it is not deleted in the database, but invalidated by a variable. deleted=0 ==>deleted=1

Administrators can view deleted records to prevent data loss, which is equivalent to a recycle bin.

Test:

1. Add a deleted field to the data table

2. Synchronize entity classes and add @TableLogic annotation to entity classes

@TableLogic //Logical deletion
private Integer deleted;

3. Configure the application.yml file

#Configuration log
mybatis-plus:
  global-config:
 ?  db-config:
 ? ?  logic-delete-field: flag # Entity field name of global logical deletion (since 3.3.0, it can be ignored after configuration, and step 2 is not configured)
 ? ?  logic-delete-value: 1 # Logical deleted value (default is 1)
 ? ?  logic-not-delete-value: 0 # Logical undeleted value (default is 0)

4. Testing

Use the previous delete test directly here

//Test delete
@Test
public void testDeleteById(){
 ? ?userMapper.deleteById(4L);
}

Looking at the log output, you can see that the delete statement has changed

In essence, it is an update (modify) statement, which changes the deleted field from 1 to 0

5. Query the user with Id 4

//Test query
@Test
public void testSelectById(){
 ? ?User user = userMapper.selectById(4L);
 ? ?System.out.println(user);
}

Looking at the log output, you can see that the statement of seletc has changed

Added the judgment statement of deleted to judge whether the deleted is 1. If it is 1, you can search, and if it is 0, you cannot

6. Summary

Only valid for automatically injected SQL:

Insert: no restrictions
Find: append the where condition to filter out the deleted data, and use wrapper The where condition generated by entity ignores this field
Update: append a where condition to prevent updating to deleted data, and use wrapper The where condition generated by entity ignores this field
Delete: convert to update

For example:

The deletion statement is converted to: update user set deleted=1 where id = 1 and deleted=0

The lookup statement is converted to: select id,name,deleted from user where deleted=0

matters needing attention:

Logical deletion is a scheme to facilitate data recovery and protect the value of data itself, but it is actually deletion.
If you need to check it frequently, you should not use logical deletion, but express it in a state.

8, Execute SQL analysis print

It can output SQL statements and their execution time. It is recommended to enable this function when developing and testing, so as to quickly find out slow queries

Note: PerformanceInterceptor was removed in 3.2.0. If you want to perform performance analysis, the third one, the official wrote, "the plug-in version above 3.2.0 is removed. It is recommended that the third extension perform SQL analysis printing function". That is p6spy.

Use steps:

8.1 p6spy dependency introduction

Maven:

<dependency>
 ?<groupId>p6spy</groupId>
 ?<artifactId>p6spy</artifactId>
 ?<version>Latest version</version> <!--Here we use>3.9.1 edition-->
</dependency>

8.2. application.yml configuration

spring:
  datasource:
 ?  driver-class-name: com.p6spy.engine.spy.P6SpyDriver
 ?  url: jdbc:p6spy:h2:mem:test
 ?  ...

Note: the driver class url prefix provided by driver class name for p6spy is jdbc:p6spy followed by a colon as the corresponding database connection address

The actual configuration is:

spring:
  datasource:
 ?  username: root
 ?  password: 123456
 ?  driver-class-name: com.p6spy.engine.spy.P6SpyDriver
 ?  url: jdbc:p6spy:mysql:///mybatis_plus?userUnicode=true&characterEncoding=utf-8

8.3. spy.properties configuration

#3.2.1 above use
modulelist=com.baomidou.mybatisplus.extension.p6spy.MybatisPlusLogFactory,com.p6spy.engine.outage.P6OutageFactory
#3.2.1 the following are used or not configured
#modulelist=com.p6spy.engine.logging.P6LogFactory,com.p6spy.engine.outage.P6OutageFactory
# Custom log printing
logMessageFormat=com.baomidou.mybatisplus.extension.p6spy.P6SpyLogger
#Log output to console
appender=com.baomidou.mybatisplus.extension.p6spy.StdoutLogger
# Use the logging system to record sql
#appender=com.p6spy.engine.spy.appender.Slf4JLogger
# Set up p6spy driver agent
deregisterdrivers=true
# Remove JDBC URL prefix
useprefix=true
# The Log exceptions of the configuration record include error,info,batch,debug,statement,commit,rollback,result,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 record
outagedetection=true
# Slow SQL record standard 2 seconds
outagedetectioninterval=2

8.4 testing

Here we use the previous paging query to test it

//Test the MybatisPlus paging plug-in
@Test
public void testMybatisPlus_Page(){
 ? ?// Two parameters: the default value of current is 1, starting from 1, not 0. size is the number of entries per page.
 ? ?Page<User> page = new Page<>(2, 4);
 ? ?userMapper.selectPage(page,null);
 ? ?page.getRecords().forEach(System.out::println);
 ? ?//Other methods of page
 ? ?System.out.println("Current page:" + page.getCurrent());
 ? ?System.out.println("Total pages:" + page.getPages());
 ? ?System.out.println("Number of records:" + page.getTotal());
 ? ?System.out.println("Is there a previous page:" + page.hasPrevious());
 ? ?System.out.println("Is there a next page:" + page.hasNext());
}

View log output

Because the slow SQL check is set to 2s in the configuration file, the query here can pass

But as long as the time is exceeded, an exception will be thrown

9, Conditional constructor

Wrapper can be used to construct complex SQL

be careful:

1. High coupling

2. Transmitting wrapper s is equivalent to conroller receiving values with map, and later maintenance is extremely difficult

So here is just a small amount of code for demonstration

9.1 code demonstration

1. Query users whose name and mailbox are not empty and whose age is greater than or equal to 20

@Test
void WrapperTest(){
 ? ?//Query users whose name and mailbox are not empty and whose age is greater than or equal to 20
 ? ?QueryWrapper<User> wrapper = new QueryWrapper<>();
 ? ?wrapper
 ? ? ? ? ?  .isNotNull("name")
 ? ? ? ? ?  .isNotNull("email")
 ? ? ? ? ?  .ge("age",12);
 ? ?userMapper.selectList(wrapper).forEach(System.out::println);
}

2. Query the user whose name is Xiaowen

@Test
void WrapperTest2(){
 ? ?//Query the user whose name is Xiaowen
 ? ?QueryWrapper<User> wrapper = new QueryWrapper<>();
 ? ?wrapper.eq("name","Essay"); ? ?//equals
 ? ?User user = userMapper.selectOne(wrapper); 
 ? ?System.out.println(user);
}

be careful:

Use List or map when there are multiple results in querying one data

3. Query users aged 19-23

@Test
void WrapperTest3(){
 ? ?//Query users aged 19-23
 ? ?QueryWrapper<User> wrapper = new QueryWrapper<>();
 ? ?wrapper.between("age", 19, 23);
 ? ?Long count = userMapper.selectCount(wrapper);//Number of query results
 ? ?System.out.println(count);
}

4. Query users aged 19-23

@Test
void WrapperTest3(){
 ? ?//Query users aged 19-23
 ? ?QueryWrapper<User> wrapper = new QueryWrapper<>();
 ? ?wrapper.between("age", 19, 23);
 ? ?Long count = userMapper.selectCount(wrapper);//Number of query results
 ? ?System.out.println(count);
}

The value range of the method here is left open and right closed!

5. Fuzzy query

@Test
void WrapperTest4(){
 ? ?QueryWrapper<User> wrapper = new QueryWrapper<>();
 ? ?wrapper
 ? ? ? ? ?  .notLike("name","a") ? ?//Query users whose names do not contain a
 ? ? ? ? ?  .likeRight("email","t"); ? //Left and right are the positions representing%. If both sides need to match, it is%e%. Here is the t% starting with t in email
 ? ?List<Map<String, Object>> maps = userMapper.selectMaps(wrapper);
 ? ?maps.forEach(System.out::println);
}

6. Joint table query

//Joint table query
@Test
void WrapperTest5(){
 ? ?QueryWrapper<User> wrapper = new QueryWrapper<>();
 ? ?wrapper.inSql("id","select id from user where id < 4");
 ? ?List<Object> objects = userMapper.selectObjs(wrapper);
 ? ?objects.forEach(System.out::println);
}

7. Sort by ID

@Test
void WrapperTest6(){
 ? ?//Sort by ID
 ? ?QueryWrapper<User> wrapper = new QueryWrapper<>();
 ? ?wrapper.orderByAsc("id"); ? //Ascending by id
 ? ?List<User> users = userMapper.selectList(wrapper);
 ? ?users.forEach(System.out::println);
}

10, Automatic code generator

MybatisPlus can see the official documents. Here is an Idea plug-in Easy Code code generator

10.1,EasyCode

EasyCode is a code generation plug-in developed based on IntelliJ IDEA Ultimate. It mainly generates all kinds of code you want through custom templates (based on velocity). It is usually used to generate Entity, Dao, Service and Controller. If you have strong hands-on ability, it can also be used to generate HTML, JS, PHP and other codes. Theoretically, any code related to data can be generated.

Supported database types:

MySQL
SQL Server
Oracle
PostgreSQL
Sqlite
Sybase
Derby
DB2
HSQLDB
H2

Of course, the supported database types will also be updated synchronously with the update of the Database Tool plug-in.

10.2 functions

Support simultaneous operation of multiple tables
Multiple templates can be generated at the same time
Support custom templates
Support custom type mapping (support regular)
Support custom additional columns
Support column additional attributes
All configuration items support grouping mode. In different items (or when selecting different databases), you only need to switch the corresponding grouping, and all configurations change uniformly

10.3 operation

1. Install EasyCode

2. Establish database

No more here, just use the previous database

3. Configure connection database in IDEA

I won't say much here, that is, idea connects to its own database

4. Use EasyCode

Right click the corresponding field to see an additional EasyCode, click and select generate

5. Start generating code

Check the code to be generated and click OK

6. Renderings

These are the automatically generated code. When the code is mature, you should generate it yourself

First of all, I would like to introduce myself. I graduated from Jiaotong University in 13 years. I once worked in a small company, went to large factories such as Huawei OPPO, and joined Alibaba in 18 years, until now. I know that most junior and intermediate Java engineers who want to improve their skills often need to explore and grow by themselves or sign up for classes, but there is a lot of pressure on training institutions to pay nearly 10000 yuan in tuition fees. The self-study efficiency of their own fragmentation is very low and long, and it is easy to encounter the ceiling technology to stop. Therefore, I collected a "full set of learning materials for java development" and gave it to you. The original intention is also very simple. I hope to help friends who want to learn by themselves and don't know where to start, and reduce everyone's burden at the same time. Add the business card below to get a full set of learning materials

Tags: Android Interview Back-end Front-end

Posted by defeated on Thu, 04 Aug 2022 22:02:07 +0530

Programs Team

MyBatis Plus detailed tutorial

1, What is MybatisPlus

2, Get started

2.1. Create database mybatis_plus

2.2. Create user table

2.3 insert data

2.4. Initialization items

2.5. Add dependency

2.6. Configuration (connect to the database)

2.7 coding

2.8. Start using

2.9 summary

3, Configuration log

4, CRUD

4.1 insertion test

4.2. Custom ID generator

4.2.1,UUID

4.2.2 SnowFlake (SnowFlake algorithm)

4.3. Update operation

5, Auto fill

5.1. What is automatic filling

5.2. Automatic filling mode

6, Optimistic lock and pessimistic lock

6.1. What is optimistic lock

6.2. What is pessimistic lock

6.3. Configure optimistic lock

6.3.1 add version field to the database

6.3.2. Synchronous entity class

6.3.3 configure plug-ins

6.3.4 test optimistic lock

7, Add, delete, modify and check

7.1. Query operation

7.2 paging query

7.3. Deletion

7.4. Logical deletion

8, Execute SQL analysis print

8.1 p6spy dependency introduction

8.2. application.yml configuration

8.3. spy.properties configuration

8.4 testing

9, Conditional constructor

9.1 code demonstration

10, Automatic code generator

10.1,EasyCode

10.2 functions

10.3 operation

Hot Tags