MyBatisPlus Learning to Organize (2)

I suggest you take a look at MyBatisPlus before you read this.

Not much bibi, just build the table

DROP TABLE IF EXISTS `user`; CREATE TABLE 'user' (' id 'bigint(20) NULL DEFAULT NULL COMMENT' primary key ', 'name' varchar(32) CHARACTER SET UTF8 COLLATE UTF8_general_CI NULL DEFAULT NULL COMMENT 'name ', 'age' int(11) NULL DEFAULT NULL COMMENT 'age ', 'email' varchar(64) CHARACTER SET UTF8 COLLATE UTF8_general_CI NULL DEFAULT NULL COMMENT 'email ', 'manager_id' bigint(20) NULL DEFAULT NULL COMMENT 'manager_id' bigint(20) NULL DEFAULT NULL COMMENT 'Create_time' datetime(0) NULL DEFAULT NULL COMMENT 'Create time ', 'update_time' datetime(0) NULL DEFAULT NULL COMMENT 'update_time ',' version 'int(11) NULL DEFAULT 1 COMMENT' version ', 'deleted' int(1) NULL DEFAULT 0 COMMENT 'Logical deleted id (0, not deleted; 1, deleted) ') ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = UTf8_general_CI ROW_FORMAT = Dynamic; INSERT INTO 'user' VALUES (1234, 'user ', 40, '[email protected]', NULL,' cialis 10-02 10:08:02', 'cialis 10-02 10:08:05', 1, 0); INSERT INTO 'user' VALUES (2345, 'cialis ', 25, '[email protected]', 1234,' cialis 10-02 10:09:07', 'cialis 10-02 10:09:10', 1, 0); INSERT INTO 'user' VALUES (2346, '[email protected]', 'cialis 10-02 10:10:09',' cialis 10-02 10:10:12', 1, 0); INSERT INTO 'user' VALUES (3456, 'cialis ', 31, '[email protected]', 2345,' cialis 10-02 10:10:54', 1, 0); INSERT INTO 'user' VALUES (4566, '[email protected]', 'cialis 10-02 10:11:51',' cialis 10-02 10:11:55', 1, 0); SET FOREIGN_KEY_CHECKS = 1;Copy the code

Project extension with MyBatisPlus learning collation (I) github.com/xiao-ren-wu…

Logic to delete

1. Set logical delete rules

Configure logically deleted and logically undeleted values in the configuration file

mybatis-plus:
  global-config:
      logic-not-delete-value: 0
      logic-delete-value: 1
Copy the code

2. Annotate logically deleted fields in the POJO class @TableLogic

@Data @EqualsAndHashCode(callSuper = false) public class User extends Model<User> { @TableId(type = IdType.AUTO) private  Long id; @TableField(condition = SqlCondition.LIKE) private String name; private Integer age; private String email; private Long managerId; private LocalDateTime createTime; private LocalDateTime updateTime; private Integer version; @TableLogic private Integer deleted; }Copy the code

1. Delete by ID logic

    @Test
    public void deleteById(){
        userMapper.deleteById(4566L);
    }
Copy the code

2. Exclude the identifier field and precautions from the query. The logical deletion of the field is only to identify whether the data is logically deleted. We just need to add on the DELETE field@TableField(select = false)Mybatisplus will automatically ignore this field during the query.

    @Test
    public void selectIgnoreDeleteTest(){
        userMapper.selectById(3456L);
    }
Copy the code

The deleted attribute in MybatisPlus is not ignored by the user

Automatic filling

MybaitsPlus gives us autofill when we insert data or update data. Similar to the default values provided by MySQL. If we need to use autofill, we need to annotate the @tableField attribute of the entity class and specify when autofill will occur. MybatisPlus gives us three options for filling, in the FieldFill enumeration

Public enum FieldFill {/** * do not process */ DEFAULT, /** * fill field */ INSERT, /** * fill field */ UPDATE, /** * fill field when insert and update */ INSERT_UPDATE}Copy the code

Once that’s set up, we need to write the concrete padding rules, which is to write a padding class and give it to Spring to manage, and then implement the insertFill and updateFill methods in the MetaObjectHandler interface. eg:

1. The insert time is automatically filled when the User object is inserted, and the update time is automatically filled when the User object is updated.

• Specifies the fields in the entity class that need to be automatically populated, and sets the padding timing

@Data @EqualsAndHashCode(callSuper = false) public class User extends Model<User> { ... @TableField(fill = INSERT) private LocalDateTime createTime; @TableField(fill = UPDATE) private LocalDateTime updateTime; . }Copy the code

• Writing padding rules

@Component public class MyMetaObjHandler implements MetaObjectHandler { @Override public void insertFill(MetaObject metaObject) { if(metaObject.hasSetter("createTime")){ setInsertFieldValByName("createTime", LocalDateTime.now(),metaObject); } } @Override public void updateFill(MetaObject metaObject) { if(metaObject.hasSetter("updateTime")){ setUpdateFieldValByName("updateTime",LocalDateTime.now(),metaObject); }}}Copy the code

Why do we use if to determine if there is a corresponding attribute

MybatisPlus executes this method every time it performs an insert or update operation. Some tables have no auto-filling fields, and the acquisition of values of some auto-filling fields consumes system performance. Therefore, in order to unnecessary consumption, the if judgment is performed to determine whether the auto-filling is required.

Sometimes we’ve already set the value of the property. I don’t want mybatisPlus to automatically populate, which means we didn’t set the value of the property, so mybatisPlus will populate, and if it’s set then it’s going to use the value that we set. In this case, we just need to get the default value in advance in the padding class and then use it.

@Override public void updateFill(MetaObject metaObject) { if(metaObject.hasSetter("updateTime")){ Object updateTime = getFieldValByName("updateTime", metaObject); if(Objects.nonNull(updateTime)){ setUpdateFieldValByName("updateTime",updateTime,metaObject); }else{ setUpdateFieldValByName("updateTime",LocalDateTime.now(),metaObject); }}}Copy the code

Optimistic locking

Optimistic locking is applicable when there is more read than write. When updating data, the version number is used instead of the lock to determine whether the data can be updated. By not locking to reduce the data update time and system performance consumption, and thus improve the database throughput. The CAS mechanism is a typical form of optimistic locking. Optimistic locking is a concept that exists logically. If we use optimistic locking, we need to manually add the version field in the table.

1. Example of pseudocode using optimistic locking for mysql:

update user 
set balabala....
where balabala... and version = xxx
Copy the code

Optimistic locking

1. Inject the optimistic locking plug-in into the configuration class

    @Bean
    public OptimisticLockerInterceptor optimisticLockerInterceptor(){
        return new OptimisticLockerInterceptor();
    }
Copy the code

2. Version field in entity class added@versionannotations

@Data @EqualsAndHashCode(callSuper = false) public class User extends Model<User> { ... @Version private Integer version; . }Copy the code

3. test

Update wang Tianfeng’s age

    @Test
    public void testLock(){
        int version = 1;
        User user = new User();
        user.setEmail("[email protected]");
        user.setAge(34);
        user.setId(2345L);
        user.setManagerId(1234L);
        user.setVersion(1);
        userMapper.updateById(user);

    }
Copy the code

The version in the database has changed to 2

Notes:

1. Only support types: int, Integer, long, long, the Date and Timestamp, LocalDateTime
2. In integer type, newVerison = oldVersion+1
3. NewVersion is written to the entity
4. Only updateById(ID) and Update (Entity,wrapper) methods are supported
5. Under the Update (entiry,wrapper) method, the wrapper is not reusable

Performance analysis

1. Inject the performance analysis plug-in into the configuration class

@Bean // @Profile({"dev,test"}) public PerformanceInterceptor performanceInterceptor() { PerformanceInterceptor performanceInterceptor = new PerformanceInterceptor(); / / formatting SQL output performanceInterceptor. SetFormat (true); / / set the SQL execution time, biggest unit (ms) performanceInterceptor. SetMaxTime (5 l); return performanceInterceptor; }Copy the code

Execute the SQL to print information about the EXECUTION of the SQL

Rely on third-party plug-ins to beautify SQL output

Mp.baomidou.com/guide/p6spy…

1. Third party dependence

        <dependency>
            <groupId>p6spy</groupId>
            <artifactId>p6spy</artifactId>
            <version>3.8.5</version>
        </dependency>
Copy the code

2. Change the dirver and URL in the configuration file

spring: datasource: # driver-class-name: com.mysql.cj.jdbc.Driver username: root password: root # url: jdbc:mysql://localhost:3306/test? serverTimezone=CTT&useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true driver-class-name: com.p6spy.engine.spy.P6SpyDriver url: jdbc:p6spy:mysql://localhost:3306/test? serverTimezone=CTT&useUnicode=true&characterEncoding=utf-8&allowMultiQueries=trueCopy the code

3. Add the spy.properties configuration file

Module. The log = com. P6spy. Engine. Logging. P6LogFactory, com. P6spy. Engine. The outage. P6OutageFactory # custom log print LogMessageFormat = com. Baomidou. Mybatisplus. The extension. P6spy. # P6SpyLogger log output to the console Appender = com. Baomidou. Mybatisplus. The extension. P6spy. StdoutLogger # using logging system record SQL # appender = com. P6spy. Engine. Spy. Appender. Slf4JLogger # set p6spy driver agent deregisterdrivers JDBC URL prefix = true # cancelled Useprefix = true # configuration record Log exceptions, can get rid of the result set of the error, the info of batch, debug, statement, commit, rollback, the result, the resultset. Excludecategories = info, debug, result, batch, the resultset # date format dateformat = yyyy - MM - dd HH: MM: ss # actual drive is multiple Outagedetection =true # OutageDetectionInterval =2Copy the code

4. test

Pay attention to

Enabling performance analysis is a performance drain on the system, so the performance analysis plug-in should be used in conjunction with the @Profile annotation environment.

SQL injector ->_-> encapsulates custom generic SQL

Implementation steps:

1. Create the class that defines the method
2. Creating an injector
3. Add custom methods to mapper

Eg: write a method to delete all data from a table

1. Create the class that defines the method

public class DeleteAllMethod extends AbstractMethod { @Override public MappedStatement injectMappedStatement(Class<? > mapperClass, Class<? > modelClass, TableInfo TableInfo) {// Execute SQL String SQL = "delete from "+ tableInfo.gettablename (); String method = "deleteAll"; SqlSource sqlSource = languageDriver.createSqlSource(configuration, sql, mapperClass); return addDeleteMappedStatement(mapperClass, method, sqlSource); }}Copy the code

2. Create an injector. Add your own methods

@Component
public class MySqlInject extends DefaultSqlInjector {
    @Override
    public List<AbstractMethod> getMethodList(Class<?> mapperClass) {
        List<AbstractMethod> methodList = super.getMethodList(mapperClass);
        methodList.add(new DeleteAllMethod());
        return methodList;
    }
}
Copy the code

3. Add custom methods to mapper

Public interface UserMapper extends BaseMapper < User > {/ * * @ * * * to delete all the table data influence the return line number * / int deleteAll (); }Copy the code

4. test

    @Test
    public void deleteAll(){
        userMapper.deleteAll();
    }
Copy the code

• The appendix

1. Refer to the source github.com/xiao-ren-wu…