(for personal use) mybatis plus easy to understand - 2021-10-15

1, Basic test preparation:

1. Springboot project: Quick create Spring Initializer
2. Import dependency: starter 
3. yml to configure: 
    (1). dataSource Four attributes
    (2). mybatis-plus: configuration: log-impl: org.apache.ibatis.logging.stdout. StaOutImpl  #Enable SQL statement printing
4. Entity class: @Data  //lombok annotation - > setter/getter/equals/hashCode/toString method of class
5. Database table user
6. mapper Interface: public interface UserMapper extends BaseMapper<User>{}
7. Startup class mapper Interface scan: @MapperScan("com.changgou.user.dao")
8. Test class: @SpringBootTest

1. Conclusion: for single table CRUD operation - > MP, database underline naming and hump naming are automatically transformed;

2, Core:

1. 8 notes:

@TableName(name="user")  //Class mapping database
public class User {  
    @TableId(value="id")  //Field - > primary key id
    private Integer id;
    @TableField(exist=false) //Extra fields, ignore fields when inserting
    private transient String unnecessary;    
    @TableField(insertStragtegy / updateStrategy / whereStrategy) //Controls how object fields assemble SQL   
    @TableField(fill) //Auto fill

    @Version //Optimistic lock
    @EnumValue  //Enumeration field
    @TableLogic  //Logical deletion

    @keySequence //oracle primary key does not increase automatically. Configure the sequence primary key policy

    @InterceptorIgnore  //Interceptor filtering rules      

2. CRUD interface:    Mapper CRUD/Service CRUD interface (encapsulating CRUD method + conditional constructor Wrapper)

Mapper CRUD Interface: 
Public interface UserMapper extends BaseMapper<User>{
(1) Mybatis start-up -> MP Resolve entity class and table relationship mapping -> injection CRUD mapper
    insert(T entity) Insert a record
    deleteById(Serializable id) According to primary key id Delete a record
    delete(Wrapper<T> wrapper) Conditional constructor wrapper Delete
    selectById(Serializable id) According to primary key id Find
    selectBatchIds(Collection idList) According to primary key id Perform batch search
    selectByMap(Map<String,Object> map) according to map Performs an equivalent match lookup for the column name and column value specified in
    selectMaps(Wrapper<T> wrapper) according to wrapper Conditions, query records, and encapsulate the query results into a Map´╝îMap of key Is the column of the result, value As value
    selectList(Wrapper<T> wrapper) Conditional constructor wrapper Make a query
    update(T entity, Wrapper<T> wrapper) Conditional constructor wrapper Update
    updateById(T entity)
(2) selectMap:  Query specified column, return Map<key Is the column name String, value Is column data Object>
(3) selectObjs: Only the first column is returned/field value
(4) selectCount: Total number of query conditions met, Method is automatically added select count(1)

Service CRUD Interface: //It belongs to the service layer and supports more batch operations

(1) public interface UserService extends IService<User> {    }

(2) @Service
    //I can't understand the notes here. What kind of inheritance?
    public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements     UserService{}
(3) IService Support chain call
    public void TestChain(){
        List<User> list =  userService.lambdaQuery()
                .gt(User::getAge, 39)
                .likeRight(User::getName, "king")
1. Conditional constructor:
   QueryWrapper: SELECT sentence -> select()method
   UpdateWrapper: UPDATE sentence -> set()method
   Parent interface: AbstractMapper
2. WHERE condition:
    eq / allEq / ne / gt / ge / lt / le / between / notBetween
    like("name", "yellow"), -> name like '%yellow%'
    likeRight("name", "yellow") -> name like 'yellow%'
    likeLeft("name", "yellow") -> name like '%yellow'
    notlike("name", "yellow") -> name not like '%yellow%'
    apply: Splicing SQL, Database function, Dynamic parameter transfer
3. Practice understanding:
    new QueryWrapper<>().like("name","yellow").lt("age", 25).isNotNull("email");
4.  appoint boolean Parameters of type condition -> true Then add the condition
    //When hasText is true, splice like - > where
    new QueryWrapper<>().like(StringUtils.hasText(name), "name", name);
5. New object -> Property is empty -> Assign partial attributes -> By non empty attribute = Equivalent matching -> query
    User user = new User();
    user.setName("Bing Bing Huang");
    List<User> users = userMapper.selectList(new QueryWrapper<>(user));  //Accurate query
   adopt@TableField(condition=SqlCondition.LIKE), use like Splice field
6. lambda Conditional constructor:
    new LamdaQueryWrapper<>().like(User::getName, "yellow").lt(User::getAge, 30);

7. update(T entity, Wrapper<T> wrapper):  More entity class and constructor updates
    new LambdaUpdateWrapper<>().between(User::getAge, 26,    31).likeRight(User::getName,"Wu");

8. delete: deleteById / deleteBatchIds / deleteByMap / delete(Wrapper<T> wrapper)

9. Primordial mybatis: custom SQL
    (1) annotation: @Select("select * from user ${ew.customSqlSegment}")
    (2) XML: <select id="findAll" resultType="com.example.mp.po.User">
                SELECT * FROM user ${ew.customSqlSegment}
10. paging:
    (1) selectPage -> java pojo
    (2) selectMapsPage -> encapsulation Map<String, Object>
    (3) Paging interceptor: MybatisPlusInterceptor
        new MybatisPlusInterceptor().addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
1. MP Primary key policy: @TableId(type="")
    (1) Default policy: Auto increment algorithm based on snowflake algorithm id
    (2) Policy type: Enumeration class IdType = AUTO / NONE / INPUT / ASSIGN_ID
        1) auto: Dependent database ID Self increasing
        2) none: Do not set primary key type
        3) input: Set primary key manually -> If none, the value is NULL(Insert operation SQL) -> Oracle
        4) assign_id: When the primary key is empty, Auto fill
    (3) yml Medium configuration: mybatis-plus: global-config: db-config: id-type: auto

2. to configure:
    (1) configLocation: Global profile
    (2) mapperLocation: xml file location
    (3) typeAliasesPackage: Alias package scan
    (4) mapUnderscoreCamelCase: Agreed automatic hump type
    (5) dbType: Database type
    (6) Field validation policy:
        1) INGNORED: No verification
        2) NOT_NULL: wrong null check
        3) NOT_EMPTY: Non null check
        4) NEVER: No SQL

3, MP code generator:

public class Generator {
	public void generate() {
		AutoGenerator generator = new AutoGenerator();

		// Global configuration
		GlobalConfig config = new GlobalConfig();
		String projectPath = System.getProperty("user.dir");
		// Set the directory to export to
		config.setOutputDir(projectPath + "/src/main/java");
		// Open folder after generation

		// Add global configuration to generator

		// Data source configuration
		DataSourceConfig dataSourceConfig = new DataSourceConfig();

		// Add data source configuration to generator

		// Package configuration, under which package is the generated code placed
		PackageConfig packageConfig = new PackageConfig();

		// Add package configuration to generator

		// Policy configuration
		StrategyConfig strategyConfig = new StrategyConfig();
		// Underline hump naming conversion
		// Open lombok
		// Start RestController
		generator.setTemplateEngine(new FreemarkerTemplateEngine());

        // Start build

4, Advanced features:

1. Logical deletion: data recovery, data protection

      logic-delete-field: deleted  # Global logically deleted entity field name
      logic-delete-value: 1 # Logical deleted value (default is 1)
      logic-not-delete-value: 0 # Logical undeleted value (0 by default)
      # If the deleted and undeleted values are the same as the default values, these two items can not be configured
//Global configuration: multiple tables will logically delete field names, and logically delete and undeleted values
@TableLogic(value = "0", delval = "1")
private Integer deleted;

  2. MP logical deletion,   This will have the following impact on SQL

  • INSERT statement: no effect
  • SELECT statement: append WHERE condition to filter out deleted data
  • UPDATE statement: append a WHERE condition to prevent updating to deleted data
  • DELETE statement: change to UPDATE statement
  • Only valid for mp auto injected SQL** If you manually add custom SQL, it will not take effect

three    Auto fill: automatically fill in the addition and modification of some fields

public class User2 {
	private Long id;
	private String name;
	private Integer age;
	private String email;
	private Long managerId;
	@TableField(fill = FieldFill.INSERT) // Auto fill on insertion
	private LocalDateTime createTime;
	@TableField(fill = FieldFill.UPDATE) // Auto fill on update
	private LocalDateTime updateTime;
	private Integer version;
	private Integer deleted;

4. Optimistic lock plug-in: - > concurrent operation - > data conflict - > control concurrency

    (1)   The pessimistic locking method is to directly lock a record in the database (the locking mechanism of the database), lock the data, and then operate;

    (2)   Optimistic lock, as its name implies, first assumes that there is no conflict, and then checks whether there is a conflict during actual data operations   

    (3)   A common implementation of optimistic locking is version number, which is also called MVCC in MySQL

  In the scenario of more reads and less writes, optimistic locking is more applicable, which can reduce the performance overhead caused by locking and improve the system throughput.

In the scenario of more writes and less reads, pessimistic locks are used. Otherwise, optimistic locks will continue to fail and retry, resulting in performance degradation.

    (4) Optimistic lock implementation:

(1) Take out the record, obtain version
(2) Update with version
(3) Execute update, set version=newVersion where version = oldVersion
(4) If oldVersion != Database version, Update failed
package com.example.mp.config;

import com.baomidou.mybatisplus.extension.plugins.inner.OptimisticLockerInnerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

public class MybatisPlusConfig {
    /** 3.4.0 The following configuration method is recommended for future mp versions**/
	public MybatisPlusInterceptor mybatisPlusInterceptor() {
		MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
		interceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());
		return interceptor;
    /** The old version mp can be used in the following ways. Note that in the old and new versions, the name of the new version of the class has Inner, and the old version does not. Don't make a mistake**/
	public OptimisticLockerInterceptor opLocker() {
		return new OptimisticLockerInterceptor();
Annotate transactions representing versions in entity classes @Version
public class User2 {
	private Long id;
	private String name;
	private Integer age;
	private String email;
	private Long managerId;
	private LocalDateTime createTime;
	private LocalDateTime updateTime;
	private Integer version;
	private Integer deleted;

V   Performance analysis plug-in

The plug-in will output the execution time of SQL statements for performance analysis and tuning of SQL statements.

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

(1)Import dependency: p6spy
(2) yml to configure: spring: dataSource:
                dirver-class-name: com.p6spy.engine.spy.p6spyDriver #Replace with p6spy's drive
                url: jdbc:p6spy:mysql://localhost:3306/db?serverTimezone=Asia/Shanghai
(3) stay src/main/resources Add under resource directory spy.properties:
#3.2.1 use of the above
# Real JDBC driver s, multiple of which are separated by commas, are empty by default. Because the modulelist is set above, you can not set the driverlist here
# Custom log printing
#Log output to console
#To output the log to a file, comment out the above appnder, or use the following appender, and then add the logfile configuration
#When an appender is not configured, it is output to a file by default
# Set p6spy driver agent
# Remove JDBC URL prefix
# Log exceptions are configured. The result sets that can be removed include error,info,batch,debug,statement,commit,rollback,result,resultset
# Date format
dateformat=yyyy-MM-dd HH:mm:ss
# Enable slow SQL logging
# Slow SQL record standard 2 seconds
# Set the execution time. Only those exceeding this execution time will be recorded. The default value is 0. The unit is milliseconds

Tags: Java

Posted on Fri, 15 Oct 2021 14:38:19 -0400 by NFClimber