Getting started with MybatisPlus (involving most common operations)

MybatisPlus

1, MybatisPlus overview

1. Knowledge required before learning MybatisPlus

​ Mybatis,Spring,SpringMVC

2. Why learn MybatisPlus

Mybatis itself is used as a framework to simplify our CRUD process, and MybatisPlus is used together with mybatis to further simplify our CRUD process and complete CRUD automatically.

Original words of the official website: MyBatis plus (MP for short) is an enhancement tool of MyBatis. On the basis of MyBatis, it only makes enhancement without change, and is born to simplify development and improve efficiency. MybatisPlus's vision is to become the best partner of MyBatis, just like 1P and 2P in soul duel. With the combination of friends and friends, the efficiency will be doubled.

3. Features of MybatisPlus

  • No invasion: it is only enhanced without change, and its introduction will not affect the existing project, which is as smooth as silk
  • Low loss: the basic CURD will be injected automatically 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 only through a small number of configurations, and there is a powerful condition constructor to meet various use requirements
  • Support Lambda formal call: it is convenient to write various query conditions through Lambda expression, and there is no need to worry about wrong fields
  • Support automatic generation of primary key: support up to 4 primary key strategies (including distributed unique ID generator - Sequence), which can be configured freely 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 and Controller layer code, support template engine, and more custom 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 multiple 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

4. Official document website

https://mp.baomidou.com/guide/

5. Environment introduction

  1. springboot2.X
  2. mybatisplus3.0.5
  3. mysql8.0.12
  4. jdk1.8

6. Tips before entering the article (required)

When writing this article, I didn't use the latest version of MybatisPlus. At present, many versions of MybatisPlus have been updated. Many of the operations designed below will be different, but generally speaking, they are similar. The 3.0.5MP I use shows more configuration details, while the latest version omits or changes some configuration methods, If you are using the latest version or there is a big gap with my version, you only need to go to the official documents to check the usage methods, which are basically the same.

2, Hello, MybatisPlus (introduction and actual combat)

1. Learning ideas

General ideas for learning new components:

  1. Find out what dependencies should be imported
  2. Study how to import. There may be problems such as version incompatibility
  3. Try how to use
  4. After successful use, study the characteristics of the component and its expansion / enhancement compared with that without the component

2. Get started

  1. Create a springboot project (just check web)

  2. Introduce dependency

    The dependency of mysql and the dependency of mybatisplus are required here

            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
            </dependency>
    
            <dependency>
                <groupId>com.baomidou</groupId>
                <artifactId>mybatis-plus-boot-starter</artifactId>
                <version>3.0.5</version>
            </dependency>
    
  3. Create a database and insert data. The instance database creation and insert statements are as follows

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)
)ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=DYNAMIC; 
 

 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');

  1. Connect to database

    Configure database parameters in the application.yml or properties file

spring:
  datasource:
    username: root
    password: 123456
    #? serverTimezone=UTC resolves the error in the time zone
    url: jdbc:mysql://localhost:3306 / your database name
?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8
    driver-class-name: com.mysql.cj.jdbc.Driver

  1. Compare the difference between traditional mybatis and using mybatis plus

    Traditional: POJO - > Dao (mapping with mybatis) - > Service - > controller

    Mybatis plus: POJO - > mapper (inheriting BaseMapper class) - > can complete most simple tasks such as adding, deleting, modifying, querying pages

@Mapper
@Repository
//Just inherit the BaseMapper class
public interface UserMapper extends BaseMapper<User> {
    
}
    @Autowired
    private UserMapper userMapper;

    @Test
    void contextLoads() {
        List<User> userList = userMapper.selectList(null);
        userList.forEach(System.out::println);
    }

The test results are:

[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-CCs02Flx-1637981062998)(C:\Users \ limru \ appdata \ roaming \ typora user images \ image-20211120113947137. PNG)]

3, Configure MybatisPlus log

1. Why configure logs

Because our current SQL statements are automated, we can't see its operation, but we often want to see what SQL statements it uses to execute, and there are many other running information. Therefore, logs are needed to help us visualize the operation of the database.

2. Configuration and use

  1. Configure database parameters in the application.yml or properties file

    mybatis-plus:
      configuration:
        log-impl: org.apache.ibatis.logging.stdout.StdOutImpl # Other logging methods can also be used for console output
    
  2. test result

We can see that the executed SQL statements are:

The information found is:

The connections and connection pools used are:

4, Some excellent functions of MP are derived from CRUD

1.1 insertion

  1. We did not set the ID when inserting. Let's observe what the ID will be after inserting
        User user = new User();
        user.setName("dt");
        user.setAge(3);
        user.setEmail("123456@126.com");

        userMapper.insert(user);
  1. console log

We found that when inserting, mybatis plus automatically sets us an ID (primary key), and this ID looks irregular to the naked eye.

1.2. Primary key insertion strategy

During insertion, because the ID is the primary key, mp automatically uses the distributed global unique ID algorithm to get a global unique ID. After reading the document, we found that an annotation @ TableId can be added to the primary key attribute in the POJO entity class, which can set the generation method / algorithm of the primary key. The specific parameters are as follows

2.1. Update

  1. Update a row of data
        User user = new User();
        user.setName("faker");
        user.setId(0L);
        user.setEmail("123456@qq.com");

        userMapper.updateById(user);
  1. On the console, the pre SQL statements and insert elements are:

  2. We still update this row of data, but the updated fields are different (email is missing)

        User user = new User();
        user.setName("rookie");
        user.setId(0L);

        userMapper.updateById(user);
  1. On the console, the pre SQL statements and insert elements are:

  2. We found that our update function and parameters have not changed. It is a user object, but the executed SQL statement is different. It will change the SQL statement according to our assignment to user, that is, dynamic SQL. MP directly helps us with dynamic SQL, If you still use mybatis, you need to write dynamic SQL manually, which is more convenient for MP than traditional mybatis

5, Auto fill

1. Why auto fill

When we operate the data in database tables, we often need to record the creation time and modification time of the operated data, so as to query when a data is created and modified in the future. As stated in Alibaba's development manual, they will add GMT to each table_ create .gmt_ Modified two fields as creation time and modification time.

This field, which is only used to record time, should not be called to write code every time. This is too inefficient. Because it is a fixed task, we can make it automatic. That's why we need to fill in the field automatically.

2. The method of automatic filling given by mybatis plus

  1. Add the annotation @ TableField on the field of POJO entity class to be filled. The fill attribute in the annotation is used to control whether and when the field is filled automatically, such as:
    private Date createTime;
    @TableField(fill = FieldFill.INSERT_UPDATE)
    private Date updateTime;
  1. Write a class to implement the MetaObjectHandler interface. The interface needs to implement two methods: insertFill and updateFill, which are used to control the automatic filling operation during insertion and update. Here, the * * @ Component * * annotation is mainly added, otherwise it will not be injected into the springboot
@Component
public class MyMetaObjectHandler implements MetaObjectHandler {
    @Override
    public void insertFill(MetaObject metaObject) {
        this.setFieldValByName("createTime",new Date(),metaObject);
        this.setFieldValByName("updateTime",new Date(),metaObject);
    }

    @Override
    public void updateFill(MetaObject metaObject) {
        this.setFieldValByName("updateTime",new Date(),metaObject);
    }
}
  1. test data
        User user = new User();
        user.setName("meiko");
        user.setAge(10);
        user.setEmail("123456@126.com");

        userMapper.insert(user);
  1. test result

  1. Possible problems: the time is wrong. This is probably because the time difference parameter of our database is wrong. Change the serverTimezone parameter in the url attribute in the database configuration to serverTimezone=GMT%2B8 to solve the problem

3. Another method of automatic filling

You can also directly modify the fields to auto fill when creating the database, or set it to auto fill when adding fields to the table when necessary.

However, I do not recommend using this method to do automatic filling, because in the real development environment, the permissions of the database are often not in the hands of the developer, so it is difficult to modify the database directly, and adding fields after the use of the database table is a great taboo, which generally does not happen. We use code to solve problems, which is more flexible and universal. If you want to understand this method, you can automatically find information as an understanding.

6, Optimistic lock

1. What is optimistic lock

Optimistic lock: as the name suggests, he is very optimistic. He always thinks that there will be no problem. No matter what he does, he won't lock it. If there is a problem, he will update the value again for testing

If there is an optimistic lock, there is a pessimistic lock. Pessimistic lock is opposite to optimistic lock

Pessimistic lock: as the name suggests, he is very pessimistic. He always thinks that there is a problem. No matter what he does, he will lock it first and then operate it

2. Implementation principle of optimistic lock

First, you need to add the version field in the database table where the optimistic lock is added, and manually add the optimistic lock component in MP

then:

When the data needs to be updated, check whether the version field after the where statement is correct. If it is correct, modify the data and increase the version automatically. Otherwise, the operation fails and the original SQL statement will not be executed (execution fails)

3. Code implementation of optimistic lock

1. Add version field

    @Version    
    private Integer version;

2. Register optimistic lock

@MapperScan("com.xiafan.mapper")
@EnableTransactionManagement
@Configuration // Configuration class
public class MybatisPlusConfig {
    // Register optimistic lock plug-in
    @Bean
    public OptimisticLockerInterceptor optimisticLockerInterceptor(){
        return new OptimisticLockerInterceptor();
    }
}

3. Test optimistic lock, test serial update and simulate multi-threaded update

//Serial, can operate normally   
@Test
    void testVersion1(){
        User user = userMapper.selectById(1461964912795344897L);

        user.setName("ququ");
        user.setAge(18);

        userMapper.updateById(user);;
    }

//Simulating multithreading, user and user1 update the same row of data, but before the user has updated, user1 has obtained the object obtained by the original user. At this time, the version of both objects is 2. However, when user1 submits, the version automatically increases to 3. When submitting the user, the version needs to be verified to be 2, but the version has automatically increased to 3, so the update statement is invalid
@Test
    void testVersion2(){
        User user = userMapper.selectById(1461964912795344897L);

        user.setName("qq");
        user.setAge(15);

        User user1 = userMapper.selectById(1461964912795344897L);

        user1.setName("wx");
        user1.setAge(16);

        userMapper.updateById(user1);

        userMapper.updateById(user);
    }

7, Simple single table query

1. Single query by single ID

2. Multiple queries by single ID

3. Use Map to encapsulate key value pairs to help us with simple conditional queries

The methods called are as follows:

Needless to say, the two methods of query by ID focus on how to perform conditional query. The demonstration code is as follows:

        Map<String, Object> map = new HashMap<>();
        //Query data with id=1 and age=20
        map.put("id",1L);
        map.put("age",20);

        userMapper.selectByMap(map);

8, Paging query

1. Implementation method of paging query

  1. It is implemented with the limit keyword in the original sql statement
  2. It is implemented using a third-party plug-in such as pageHelper
  3. Configuring the paging query plug-in in mybatis plus

2. How to configure paging query plug-in in mybatis plus

  1. Configure the plug-in in the configuration class, which is similar to the configuration of the optimistic lock plug-in
    //Register paging plug-in
    @Bean
    public PaginationInterceptor paginationInterceptor(){
        return new PaginationInterceptor();
    }
  1. Directly use the built-in Page object of the plug-in
        @Test
    void testPage(){
        //The first parameter is the current page, and the second parameter is the number of data (rows) per page
        Page<User> objectPage = new Page<>(1,5);
        userMapper.selectPage(objectPage,null);

        objectPage.getRecords().forEach(System.out::println);
    }

9, Delete

1. Delete by single ID

2. Delete multiple by single ID

3. Use Map to encapsulate key value pairs to help us with simple conditional deletion

The methods called are as follows:

Needless to say, the two methods of deleting by ID focus on how to delete conditions. The demonstration code is as follows:

        Map<String, Object> map = new HashMap<>();
        //Query data with id=1 and age=20
        map.put("id",0L);
        map.put("name","xiaohu");

        userMapper.deleteByMap(map);

10, Logical deletion

1. What is logical deletion

Logical deletion is called logical deletion because it is different from physical deletion. The differences are as follows:

Physical deletion: the data is completely erased from the storage medium. The data is permanently deleted and can no longer be obtained

Logical deletion: we use some methods, such as adding a field. When the field = 0, it exists by default. When the field = 1, it is deleted by default. However, it is not deleted, but it will be ignored when doing other operations. It is only deleted "logically", so it is called logical deletion

2. How to implement logical deletion

The implementation of logical deletion is very similar to that of optimistic lock.

  1. Add a deleted field in the database. The default value is set to 0 (it's best to do so. It will be configured later. You can change it according to your needs, but it is recommended to use 0)
  2. Add the deleted field to the entity class
    @TableLogic
    private Integer deleted;
  1. Similar to optimistic lock plug-in and paging plug-in, the plug-in is registered in the configuration class
    //Register logical deletion plug-in
    @Bean
    public ISqlInjector sqlInjector(){
        return new LogicSqlInjector();
    }
  1. Write the configuration related to logical deletion in the yml file of springboot
mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl # Other logging methods can also be used for console output
  global-config:
    db-config:
      logic-delete-value: 1
      logic-not-delete-value: 0
  1. Test logical deletion function

3. Summary

In essence, logical deletion is to change "delete" into "update". We just need to add a tag field, and then agree that this field is a value that means it should be "seen" and an individual value that means it cannot be "seen". It looks like it has been deleted, but in fact, the data still exists in our storage medium

11, Conditional constructor

1. What is a conditional constructor

The condition constructor is a tool for MP to perform complex SQL operations, such as fuzzy query and grouping query. It is not enough to use only the encapsulated methods in MP's BaseMapper for these complex operations (without Wrapper class parameters)

2. How to use conditional constructors

The use of conditional constructors is based on the Wrapper class, which encapsulates the operation of most SQL statements and supports chain programming (that is, parameters can be set continuously)

Example: only a part of the methods are illustrated here. For others, just read the official documents. The usage is simple and easy to understand

        //>=
    @Test
    void test1(){
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper
                .ge("age","28")
                .isNotNull("name")
                .select("name");
        userMapper.selectList(wrapper).forEach(System.out::println);
    }

    //between
    @Test
    void test2(){
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper
                .between("age",20,30);
        List<User> userList = userMapper.selectList(wrapper);

        for (User user : userList){
            System.out.println(user.getName());
        }
    }

    //Fuzzy query like
    @Test
    void test3(){
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper
                .likeRight("name","J")
                .notLike("name","a");
        List<User> userList = userMapper.selectList(wrapper);

        for (User user : userList){
            System.out.println(user.getName());
        }
    }

12, Automatic code generator

The essence is to use the classes encapsulated by MP to configure the code files to be generated. There are no technical requirements for this part. Here, the example of the official document is used as an illustration:

// For the demo example, execute the main method, enter the module table name on the console, and press enter to automatically generate the corresponding project directory
public class CodeGenerator {

    /**
     * <p>
     * Read console content
     * </p>
     */
    public static String scanner(String tip) {
        Scanner scanner = new Scanner(System.in);
        StringBuilder help = new StringBuilder();
        help.append("Please enter" + tip + ": ");
        System.out.println(help.toString());
        if (scanner.hasNext()) {
            String ipt = scanner.next();
            if (StringUtils.isNotBlank(ipt)) {
                return ipt;
            }
        }
        throw new MybatisPlusException("Please enter the correct" + tip + "!");
    }

    public static void main(String[] args) {
        // Code generator 
        AutoGenerator mpg = new AutoGenerator();

        // Global configuration
        GlobalConfig gc = new GlobalConfig();
        String projectPath = System.getProperty("user.dir");
        gc.setOutputDir(projectPath + "/src/main/java");
        gc.setAuthor("jobob");
        gc.setOpen(false);
        // gc.setSwagger2(true);  Entity attribute Swagger2 annotation
        mpg.setGlobalConfig(gc);

        // Data source configuration
        DataSourceConfig dsc = new DataSourceConfig();
        dsc.setUrl("jdbc:mysql://localhost:3306/ant?useUnicode=true&useSSL=false&characterEncoding=utf8");
        // dsc.setSchemaName("public");
        dsc.setDriverName("com.mysql.jdbc.Driver");
        dsc.setUsername("root");
        dsc.setPassword("password");
        mpg.setDataSource(dsc);

        // Package configuration
        PackageConfig pc = new PackageConfig();
        pc.setModuleName(scanner("Module name"));
        pc.setParent("com.baomidou.ant");
        mpg.setPackageInfo(pc);

        // Custom configuration
        InjectionConfig cfg = new InjectionConfig() {
            @Override
            public void initMap() {
                // to do nothing
            }
        };

        // If the template engine is freemaker
        String templatePath = "/templates/mapper.xml.ftl";
        // If the template engine is velocity
        // String templatePath = "/templates/mapper.xml.vm";

        // Custom output configuration
        List<FileOutConfig> focList = new ArrayList<>();
        // Custom configuration will be output first
        focList.add(new FileOutConfig(templatePath) {
            @Override
            public String outputFile(TableInfo tableInfo) {
                // Customize the output file name. If you set the Prefix suffix for Entity, note that the name of xml will change!!
                return projectPath + "/src/main/resources/mapper/" + pc.getModuleName()
                        + "/" + tableInfo.getEntityName() + "Mapper" + StringPool.DOT_XML;
            }
        });
        /*
        cfg.setFileCreate(new IFileCreate() {
            @Override
            public boolean isCreate(ConfigBuilder configBuilder, FileType fileType, String filePath) {
                // Determine whether custom folders need to be created
                checkDir("The directory created by calling the default method, and the user-defined directory is marked with "");
                if (fileType == FileType.MAPPER) {
                    // The mapper file has been generated. It is judged that it exists. If you do not want to regenerate it, false is returned
                    return !new File(filePath).exists();
                }
                // Allow template files to be generated
                return true;
            }
        });
        */
        cfg.setFileOutConfigList(focList);
        mpg.setCfg(cfg);

        // Configuration template
        TemplateConfig templateConfig = new TemplateConfig();

        // Configure custom output templates
        //Specify a custom template path. Be careful not to bring. ftl/.vm. It will be automatically recognized according to the template engine used
        // templateConfig.setEntity("templates/entity2.java");
        // templateConfig.setService();
        // templateConfig.setController();

        templateConfig.setXml(null);
        mpg.setTemplate(templateConfig);

        // Policy configuration
        StrategyConfig strategy = new StrategyConfig();
        strategy.setNaming(NamingStrategy.underline_to_camel);
        strategy.setColumnNaming(NamingStrategy.underline_to_camel);
        strategy.setSuperEntityClass("Your own parent entity,No, you don't have to set it!");
        strategy.setEntityLombokModel(true);
        strategy.setRestControllerStyle(true);
        // Public parent class
        strategy.setSuperControllerClass("Your own parent controller,No, you don't have to set it!");
        // Public fields written in the parent class
        strategy.setSuperEntityColumns("id");
        strategy.setInclude(scanner("Table name, separated by multiple English commas").split(","));
        strategy.setControllerMappingHyphenStyle(true);
        strategy.setTablePrefix(pc.getModuleName() + "_");
        mpg.setStrategy(strategy);
        mpg.setTemplateEngine(new FreemarkerTemplateEngine());
        mpg.execute();
    }

}

Tags: Java Mybatis Back-end MybatisPlus

Posted on Tue, 30 Nov 2021 19:45:09 -0500 by brownca