springboot-mybatisplus Chapter 1 Section

SpringBoot integrates Mybatis-Plus articles

1. Overview

Since mybatis-plus was not officially developed, no starter was provided. But there are gods among the people who have their own offerings, so first go to the official website to find them:


Then analyze the pom dependency:

You can see that the mybatis-spring package, annotations, and so on are introduced here. And the corresponding jdbc operations and so on. The library connection pool uses Hikari et al.

It means that we can operate the database without having to configure anything.

2. Automatic configuration analysis

So look at autoconfig in the corresponding maven dependency to find the autoconfig in the spring.factories directory

# Auto Configure

So look directly at the last:


Look at the source code:

@Configuration(proxyBeanMethods = false)
// There are two classes for importing mybatis
@ConditionalOnClass({SqlSessionFactory.class, SqlSessionFactoryBean.class})
// If there is only one class for the database connection pool
// Binding class properties in configuration files
@AutoConfigureAfter({DataSourceAutoConfiguration.class, MybatisPlusLanguageDriverAutoConfiguration.class})
public class MybatisPlusAutoConfiguration implements InitializingBean {

Continue to see what is configured in the class:

    public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception {

This code configures the SqlSessionFactory, imports the DataSource from the container, and then proceeds with the crazy set operation.

Configuration of SqlSessionTemplate, also known as SqlSession

public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory sqlSessionFactory) {
    ExecutorType executorType = this.properties.getExecutorType();
    if (executorType != null) {
        return new SqlSessionTemplate(sqlSessionFactory, executorType);
    } else {
        return new SqlSessionTemplate(sqlSessionFactory);

Registration of AutoConfiguredMapperScannerRegistrar, which is the path to the configured mapper package

@Configuration(proxyBeanMethods = false)
@ConditionalOnMissingBean({MapperFactoryBean.class, MapperScannerConfigurer.class})
public static class MapperScannerRegistrarNotFoundConfiguration implements InitializingBean {

    public void afterPropertiesSet() {
            "Not found configuration for registering mapper bean using @MapperScan, MapperFactoryBean and MapperScannerConfigurer.");

Take a look at the configuration file:

 * Configuration properties for MyBatis.
 * @author EddĂș MelĂ©ndez
 * @author Kazuki Shimizu
@Accessors(chain = true)
@ConfigurationProperties(prefix = Constants.MYBATIS_PLUS)
public class MybatisPlusProperties {

    private static final ResourcePatternResolver resourceResolver = new PathMatchingResourcePatternResolver();

     * Location of MyBatis xml config file.  Global Profile
    private String configLocation;

     * Locations of MyBatis mapper files.
     *  mapper Map interface file location, default here is all xml files under / mapper path
     * @since 3.1.2 add default value
    private String[] mapperLocations = new String[]{"classpath*:/mapper/**/*.xml"};

     * Packages to search type aliases. (Package delimiters are ",; \t\n")  Alias operation
    private String typeAliasesPackage;

     * The super class for filtering type alias.
     * If this not specifies, the MyBatis deal as type alias all classes that searched from typeAliasesPackage.
    private Class<?> typeAliasesSuperType;

     * Packages to search for type handlers. (Package delimiters are ",; \t\n")
    private String typeHandlersPackage;

     * Indicates whether perform presence check of the MyBatis xml config file.  Whether a global profile needs to be checked
    private boolean checkConfigLocation = false;

     * Execution mode for {@link org.mybatis.spring.SqlSessionTemplate}.
    private ExecutorType executorType;

     * The default scripting language driver class. (Available when use together with mybatis-spring 2.0.2+)
     * <p>
     * If you set this up, you will lose at least all of the functions provided by MPS
    private Class<? extends LanguageDriver> defaultScriptingLanguageDriver;

     * Externalized properties for MyBatis configuration.
    private Properties configurationProperties;

     * A Configuration object for customize default settings. If {@link #configLocation}
     * is specified, this property is not used.
     * TODO Global configuration using MybatisConfiguration mybatis-plus
    private MybatisConfiguration configuration;

     * TODO Enumerate package scans
    private String typeEnumsPackage;

configuration file

    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/mp?serverTimezone=Asia/Shanghai&useSSL=false
    username: root
    password: 123456

# mybatisplus almost zero configuration

    # Add Console Log
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

3. Configuration logic deletion

First you need to specify it in yaml:

	  # Fields in entity classes to be deleted
      logic-delete-field: deleted
      # Define values for logically deleted data in the database
      logic-delete-value: 1
      # Define values that are not deleted from the database
      logic-not-delete-value: 0

Take a look at a test:

    public void testFindAll(){
        List<User2> users = user2Mapper.selectList(null);

View the corresponding sql:

SELECT id,name,age,email,manager_id,create_time,update_time,version,deleted FROM user2 WHERE deleted=0

You can see that we have automatically added the corresponding logically deleted values here, and for those in the configuration file, only the values that have not been deleted will be queried, not the values that have not been deleted will be queried again.

Looking at the SQL statement above, we can see that there is a field deleted in the field where the selection is made, but this field usually does not need to appear in the field after the selection because it does not.

So if necessary, add a comment on the entity class:

@TableField(select = false)
private Integer deleted;

Then when you execute again, you won't see this value.

Take a look at the corresponding SQL statement:

SELECT id,name,age,email,manager_id,create_time,update_time,version FROM user2 WHERE deleted=0

You can see that there is no corresponding deleted field here.

In the global profile, the values of logically deleted fields are configured. For multiple tables, we set duplicate fields to be the same.

But if you need to configure it separately like this, you need to add the @TableField annotation.

Take another test:

    public void testDeleteById(){
        int i = user2Mapper.deleteById(1);

Then look at the corresponding SQL statement:

UPDATE user2 SET deleted=1 WHERE id=1 AND deleted=0

You can see that what you are doing here is an update statement, not a delete statement. Because here deleted is modified to the value we need to modify.


Configured impact of logical deletion:

Logical Delete select insert update delete
No impact Append where condition to filter out deleted data Append where condition to filter out deleted fields Convert to update statement

Note that the above effects are only valid for SPLs automatically injected by mp. If you add your own custom SQL manually, it will not work. For example:

public interface User2Mapper extends BaseMapper<User2> {
     @Select("select * from user2")
 	 List<User2> selectRaw();

If this selectRaw is called, the logical deletion of mp will not take effect.

Logical deletion can be configured globally in application.yml or locally in entity classes with @TableLogic.

4. Automatic Filling

Tables often have fields such as Add Time, Modify Time, Operator, etc. The original way is to set it manually each time you insert or update it. MPs can be configured to automatically fill in certain fields, as shown below

  1. Auto-populate some fields in the entity class with the @TableField setting;
public class User2 {
 private Long id;
 private String name;
 private Integer age;
 private String email;
 private Long managerId;

 @TableField(fill = FieldFill.INSERT)
 private LocalDateTime createTime;
 @TableField(fill = FieldFill.UPDATE)
 private LocalDateTime updateTime;
 private Integer version;
 @TableField(select = false)
 private Integer deleted;

2. Add another component to the container

public class MyMetaObjectHandler implements MetaObjectHandler {
    // Note that the second parameter fills in the field name in the entity class, not the column name of the table  
    public void insertFill(MetaObject metaObject) {
        strictFillStrategy(metaObject,"createTime", LocalDateTime::now);
    // Note that the second parameter fills in the field name in the entity class, not the column name of the table  
    public void updateFill(MetaObject metaObject) {
        strictFillStrategy(metaObject,"updateTime", LocalDateTime::now);

Then test it:

    public void testInsert() {
        User2 user = new User2();
        user.setName("Monarch Egg");

You can see that the insertion time in the database has been updated

Then test the changes:

    public void testUpdate() {
        User2 user = new User2();
        user.setName("Monarch Egg");

Look at the database and find that the corresponding event was also modified successfully

Note that auto-filling only works when the field is empty, and if the field is not empty, use the existing values directly

5. Optimistic Lock Plugin

When concurrent operations occur, you need to ensure that each user's operations on the data do not conflict, and you need a means of concurrency control. The pessimistic method is to modify a record in a database by locking the data directly (the database's lock mechanism), then proceeding with the operation. Optimistic locks, as their name implies, assume that there is no conflict, and check for conflicts when actually doing data operations. One common implementation of optimistic locks is version number, which also has version number-based concurrent transaction control called MVCC in MySQL.

Optimistic locks are more appropriate in scenarios with more reads and fewer writes, which can reduce performance overhead caused by locking operations and improve system throughput.

Pessimistic locks are used in scenarios where more writes and less reads are used, otherwise retries will fail due to optimistic locks and performance will degrade.

Optimistic locks are implemented as follows:

  1. Get the current version when the record is taken out
  2. When updating, bring this version with you
  3. When performing an update, set version = newVersion where version = oldVersion
  4. Update fails if oldVersion is inconsistent with version in the database

This idea is very similar to CAS (Compare And Swap).

The steps for optimistic locking are as follows:

Configure Optimistic Lock Plugin

public class MybatisPlusConfig {
     * 3.4.0 For future mp versions, the following configuration is recommended
     * @return
/*    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());
        return interceptor;

     * Old MPs can be used in the following ways. Note that in old and new versions, new classes with Inner names, old versions without, don't mismatch
     * @return
    public OptimisticLockerInterceptor opLocker() {
        return new OptimisticLockerInterceptor();

Write a unit test:

    public void testOpLocker() {
        int version = 1; // Suppose this version was obtained from a previous query
        User2 user = new User2();
        int i = user2Mapper.updateById(user);

Take a look at the corresponding SQL statement:

UPDATE user2 SET email='version@baomidou.com', update_time='2021-11-05T00:34:08.190', version=2 WHERE id=8 AND version=1 AND deleted=0

You can see that when you insert, you query the value of the corresponding version first, and if it does, you start.

Update succeeds when UPDATE returns 1, indicating the number of rows affected is 1. Conversely, because versions after WHERE do not match any records in the database, the number of rows affected is 0, indicating that the update failed. When the update is successful, the new version is encapsulated back into the entity object.

Note that the Optimistic Lock Plugin only supports the updateById(id) and update(entity, wrapper) methods

Note: Wapper cannot be reused if it is used! Examples are as follows

    public void testOpLockerTwo() {
        User2 user = new User2();
        // First use
        LambdaQueryWrapper<User2> wrapper = new LambdaQueryWrapper<>();
        wrapper.eq(User2::getName, "Monarch Egg");
        user2Mapper.update(user, wrapper);
        // Second Reuse
        user2Mapper.update(user, wrapper);

Look at the corresponding SQL below as follows:

 Consume Time: 20 ms 2021-11-05 00:38:48
 Execute SQL: UPDATE user2 SET age=2, update_time='2021-11-05T00:38:47.079', version=2 WHERE deleted=0 AND (name = 'Monarch Egg' AND version = 1)

 Consume Time: 19 ms 2021-11-05 00:38:48
 Execute SQL: UPDATE user2 SET age=3, update_time='2021-11-05T00:38:47.079', version=3 WHERE deleted=0 AND (name = 'Monarch Egg' AND version = 1 AND version = 2)

You can see that the second time you reuse wrapper, there are two version s in the latter WHERE statement in the spliced SQL, which is problematic.

6. Performance Analysis Plug-in

The plug-in outputs the execution time of the SQL statement for performance analysis and tuning.

Note: After version 3.2.0, the performance analysis plug-in provided by mp has been officially removed, and third-party performance analysis plug-in is recommended

1. Introducing maven dependencies


2. Modify application.yaml

    driver-class-name: com.p6spy.engine.spy.P6SpyDriver #Replace with p6spy driver
    url: jdbc:p6spy:mysql://Localhost:3306/mp?ServerTimezone=Asia/Shanghai#url modification
    username: root
    password: root

3. Add spy.properties to the src/main/resources resource directory

#Use above 3.2.1
# True JDBC driver, multiple separated by commas, empty by default. Since modulelist is set above, driverlist may not be set here
# Custom Log Printing
#Log output to console
#To log output to a file, comment out the appnder above, or use the appender below and add the logfile configuration
#When appender is not configured, the default is to output to a file
# Set up p6spy driver agent
# Unprefix JDBC URL s
# Configure Log exceptions, the result sets that can be removed are error,info,batch,debug,statement,commit,rollback,result,resultset.
# Date format
dateformat=yyyy-MM-dd HH:mm:ss
# Whether to turn on slow SQL records
# Slow SQL Recording Standard 2 seconds
# Execution time settings, which are only recorded if this execution time exceeds, default 0, in milliseconds

Execute any SQL statement and you can see that the console prints the corresponding log:

 Consume Time: 19 ms 2021-11-05 00:38:48
 Execute SQL: UPDATE user2 SET age=3, update_time='2021-11-05T00:38:47.079', version=3 WHERE deleted=0 AND (name = 'Monarch Egg' AND version = 1 AND version = 2)

8. Code Generator

Just go to the official website or blog in detail and look for one to see. No more details here.

7. Quick start operation

mybatis-plus provides two interfaces, the BaseMapper, or mapper interface. Another IService is the service interface

The biggest difference between them is that service s support a large number of bulk operations.

The most powerful thing about mp is that it provides wapper s, which can be very constructed where conditions. From here you can see that this is where the conditions are stitched.

So as long as you know this purpose, you will have a better understanding later.

The AbstractWrapper interface itself has provided many ways to construct where conditions.

The QueryWrapper interface is mainly used for queries.

The UpdateWrapper interface is used to set methods for setting values;

The official document is fairly detailed.

In the process of conditional construction, there is a boolean type conditional parameter that determines whether the final condition needs to be added to the where condition.

Lambda conditional constructor that supports lambda expressions.

There is nothing to say next, so you can refer to the documentation for specific operation.

Reference documents: https://mp.weixin.qq.com/s/SBkYZrBbGEgBe09erNr7tg

Tags: Spring Boot

Posted on Thu, 04 Nov 2021 15:00:43 -0400 by pfchin