Seven, MyBatis-Plus extensions: optimistic locking, code generator, the execution of SQL analysis print (hands-on detailed use)

Seven, MyBatis-Plus extensions: optimistic locking, code generator, the execution of SQL analysis print (hands-on detailed use)

@

1. Optimistic lock

First we need to start by understanding a common scenario in development called concurrent requests.

Concurrent requests are multiple requests at the same time, requesting server resources at the same time. If it's to get information, it's not a problem, but if it's to make changes to the information, then it's a problem.

Here we give an example. For example: the current inventory of the product is only 1 piece left, this time there are multiple users want to buy this product, have initiated the purchase of goods request, then can make this multiple users to buy it, certainly not, because multiple users have bought the product, then there will be oversold, not enough inventory can not be shipped. So we need to solve this overselling problem in the development.

Putting aside the overselling of this scenario, there are many such concurrent access scenarios, the core problem of such scenarios is that, during the execution of a request, other requests can not change the data, if it is a complete request, in the process of the request, other requests do not have to modify the data, then the request can modify the data normally. If the request has already been changed by other requests during the process of changing the data, then the request will not change the data.

To solve this type of problem, the most common is the idea of adding locks, which can be used to verify that no data has changed during the execution of the request.

There are two common types of database locks, pessimistic locks and optimistic locks.

A single modification operation is completed by first querying the data and then modifying it.

The operation done in this way ensures that the information read is the current information, which guarantees the correctness of the information, but the concurrency efficiency is very low, and there are very few scenarios in which pessimistic locks are used in actual development, because at concurrency.

Optimistic lock: Optimistic locking is designed to be accomplished through table fields, and his core idea is that, when reading without locking, other requests can still read this data, and when modifying to determine whether a data has been modified, if it has been modified, then the modification operation of this request fails.

This is done via SQL by adding an where version = 1

This operation will not have an impact on the data read, concurrently higher efficiency, but may not see the current data is not the real information data, is modified before, but in many scenarios can be tolerated, and does not have a great impact. For example: many times we see that there is inventory, or are added to the shopping cart, but after clicking in the inventory is gone.

Add a field, version, to the database table, with a default value of 1.

Generated effect

Find the entity class, add the corresponding properties, and use the@Version labeling for this is an optimistic lock field message.

Because statement enhancement is to be accomplished for each modified statement, here we configure the interceptor so that each modifiedsql statements are executed with version control added.

import ;
import ;
import ;
import ;
import ;import ;import ;import ;import
import ;import ;import ;import ;import
import ;import ;import ;import ;import
import ;import ;import ;import ;import
import ;

@Configuration
public class MybatisPlusConfig {



    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor mybatisPlusInterceptor = new MybatisPlusInterceptor();

        /*
        Specify a database-specific paging plug-in through the configuration class, because different databases have different dialects, the specific
        body astringent will give you from the paging statement will also be different, here we specify the database for the MySQL database
         */
        (new PaginationInnerInterceptor()); (new OptimisticLocker()); (new OptimisticLocker())
        (new OptimisticLockerInnerInterceptor()); // Optimistic locking
        return mybatisPlusInterceptor;
    }

}

To test the effect, here we simulate the query first, and then modify the

@Test
void updateTest(){
    User user = (6L);
    ("li");
    (user);
}

Looking at the stitched SQL statement, we see that the query looks up the User's data with the version information.

When we're done making changes, he'll add + 1 to the version number

At this point, checking the data shows that after changing the name, the version is already 2

Next we simulate whether we can do optimistic locking when there are multiple modification requests.

The effect of optimistic locking is that a request is allowed to be queried by another request in the process of modification, but the modification will be decided by whether the version number is changed or not, if the version number is changed, it proves that there has been a request to modify the data, so the modification does not take effect, and if the version number is not changed, then the modification is completed.

package ;


import ;
import ;
import ;
import ;
import ;

@SpringBootTest
public class LockTest {

    @Autowired
    private UserMapper userMapper;

    @Test
    void updateTest2() {
        // Simulate a query for operation 1
        
        ("Query Result:" + user1);

        // Simulate the query for operation 2
        User user2 = ("5"); ("Query result:" + user1); // Simulate the query operation of operation 2.
        ("Query result:" + user2); // Simulate the query operation of operation 2.

        // Simulate modification for operation 2
        ("liHua"); // Simulate operation 2's modify operation.
        (user2);

        // Simulate the modification of operation 1
        ("zhangsan"); (user1); // Simulate the modification of operation 1.
        (user1); }
    }

}

Let's look at the execution of this code, this code is actually two operations, only operation 1 in the process of execution, there is operation 2 to complete the modification of the data, then operation 1 can not be modified again

Query for operation 1: version 2 at this point

Query for operation 2: version 2 at this point

Modification of operation 2: check the version at this point, there is no change in the version, so complete the modification and change the version to 3

Modification of operation 1: check the version at this time, the version has already changed from the initial version information obtained, so the modification is eliminated.

2. Code generator

The difference between a code generator and reverse engineering is that a code generator can generate more structure, more content, and allows us to be able to configure the generation with more options. Here we demonstrate the usage of the code generator.

Referring to the official website, two dependencies need to be introduced to use the code generator; the

  <!-- mybatis-plus dependence-->
        <!-- /artifact//mybatis-plus-boot-starter -->
        <dependency>
            <groupId></groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.3</version>
        </dependency>


        <!--freemarkerTemplate dependencies-->
        <dependency>
            <groupId></groupId>
            <artifactId>freemarker</artifactId>
            <version>2.3.31</version>
        </dependency>

Writing code generator code

@SpringBootTest
class GeneratorApplicationTests {
    public static void main(String[] args) {
        ("jdbc:mysql://localhost:3306/mybatisplus?serverTimezone=UTC&characterEncoding=utf8&useUnicode=true&useSSL=false", " root", "root")
                .globalConfig(builder -> {
                    ("powernode") // set the author
                            //.enableSwagger() // enable swagger mode
                            .fileOverride() // Override the generated file.
                            .outputDir("D://"); // Specify the output directory.
                })
                .packageConfig(builder -> {
                    ("") // Set the parent package name
                            .moduleName("mybatisplus") // set the parent package module name
                            .pathInfo((, "D://")); // set the mapperXml generation path
                })
                .strategyConfig(builder -> {
                    ("powershop_user") // set the table name to be generated
                            .addTablePrefix("powershop"); // set the filter table prefix
                })
                .templateEngine(new FreemarkerTemplateEngine()) // Use the Freemarker engine template, the default is the Velocity engine template.
                .execute(); }
    }
}

Execute to see the generated results

3. Perform SQL analysis printing

In our daily development work, we can't avoid checking the SQL statement executed by the current program, as well as understanding its execution time, so that we can easily analyze whether there is a slow SQL problem. We can use MybatisPlus to provide SQL analysis print function to get the SQL statement execution time.

Since this feature relies on thep6spy component, so it needs to be added to the The component is introduced first in the

<dependency>
    <groupId>p6spy</groupId>
    <artifactId>p6spy</artifactId>
    <version>3.9.1</version>
</dependency>

existConfiguration in the

Combine the driver andurl modifications

spring:
  datasource:
    driver-class-name: com..P6SpyDriver
    url: jdbc:p6spy:mysql

existresourcesUnder Create Configuration file.

#3.2.1+ use modulelist=. .P6OutageFactory

# Customize log printing
logMessageFormat=.p6spy.P6SpyLogger

# Log output to console
appender=.

# Logging sql using the logging system
#appender=com..Slf4JLogger

# Set up the p6spy driver agent
deregisterdrivers=true

# Unprefix JDBC URLs
useprefix=true

# Configure logging Log exceptions, removable result sets error,info,batch,debug,statement,commit,rollback,result,resultset.
excludecategories=info,debug,result,commit,resultset

# Date Format
dateformat=yyyy-MM-dd HH:mm:ss

# Actual drivers can be multiple
#driverlist=org.

# Whether to enable slow SQL logging
outagedetection=true

# Slow SQL logging standard 2 seconds
outagedetectioninterval=2

beta (software)

Execute the query for all operations, you can see the execution time of the sql statement

4. Summary:

1. Note: Understand pessimistic and optimistic locks:
   1. Pessimistic locks: A pessimistic lock locks the data at the time of the query and does not release the lock until the request is completed. You must wait until the request is completed before releasing the lock, and only after the lock is released can other requests read or write to the data.
   2. Optimistic lock: Optimistic locking is designed to be accomplished through table fields, and his core idea is that, when reading without locking, other requests can still read this data, and when modifying to determine whether a data has been modified, if it has been modified, then the modification operation of this request fails.

5. Finally:

"In this final chapter, I would like to express my gratitude to each and every one of my readers. Your attention and responses have been a source of motivation for me to create, and I have drawn endless inspiration and courage from you. I will keep your encouragement in my heart and continue to struggle in other areas. Thanks to you all, we will always meet again at some point."