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") .list(); list.forEach(System.out::println);
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%' isNull isNotNull in and apply: Splicing SQL, Database function, Dynamic parameter transfer 3. Practice understanding: new QueryWrapper<>().like("name","yellow").lt("age", 25).isNotNull("email"); wrapper.likeRight("name","yellow").or().ge("age",40).orderByDesc("age").orderByAsc("id"); 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"); user.setAge(18); List<User> users = userMapper.selectList(new QueryWrapper<>(user)); //Accurate query adopt@TableField(condition=SqlCondition.LIKE), use like Splice field user.setName("yellow"); 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} </select> 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 { @Test 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"); config.setAuthor("yogurt"); // Open folder after generation config.setOpen(false); // Add global configuration to generator generator.setGlobalConfig(config); // Data source configuration DataSourceConfig dataSourceConfig = new DataSourceConfig(); dataSourceConfig.setUrl("jdbc:mysql://localhost:3306/yogurt?serverTimezone=Asia/Shanghai"); dataSourceConfig.setDriverName("com.mysql.cj.jdbc.Driver"); dataSourceConfig.setUsername("root"); dataSourceConfig.setPassword("root"); // Add data source configuration to generator generator.setDataSource(dataSourceConfig); // Package configuration, under which package is the generated code placed PackageConfig packageConfig = new PackageConfig(); packageConfig.setParent("com.example.mp.generator"); // Add package configuration to generator generator.setPackageInfo(packageConfig); // Policy configuration StrategyConfig strategyConfig = new StrategyConfig(); // Underline hump naming conversion strategyConfig.setNaming(NamingStrategy.underline_to_camel); strategyConfig.setColumnNaming(NamingStrategy.underline_to_camel); // Open lombok strategyConfig.setEntityLombokModel(true); // Start RestController strategyConfig.setRestControllerStyle(true); generator.setStrategy(strategyConfig); generator.setTemplateEngine(new FreemarkerTemplateEngine()); // Start build generator.execute(); } }
4, Advanced features:
1. Logical deletion: data recovery, data protection
mybatis-plus: global-config: db-config: 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; @Configuration public class MybatisPlusConfig { /** 3.4.0 The following configuration method is recommended for future mp versions**/ @Bean 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**/ /* @Bean public OptimisticLockerInterceptor opLocker() { return new OptimisticLockerInterceptor(); } */ }
Annotate transactions representing versions in entity classes @Version @Data public class User2 { private Long id; private String name; private Integer age; private String email; private Long managerId; private LocalDateTime createTime; private LocalDateTime updateTime; @Version 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: #spy.properties #3.2.1 use of the above modulelist=com.baomidou.mybatisplus.extension.p6spy.MybatisPlusLogFactory,com.p6spy.engine.outage.P6OutageFactory # 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 #driverlist=com.mysql.cj.jdbc.Driver # Custom log printing logMessageFormat=com.baomidou.mybatisplus.extension.p6spy.P6SpyLogger #Log output to console appender=com.baomidou.mybatisplus.extension.p6spy.StdoutLogger #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 #appender=com.p6spy.engine.spy.appender.FileLogger #logfile=log.log # Set p6spy driver agent deregisterdrivers=true # Remove JDBC URL prefix useprefix=true # Log exceptions are configured. The result sets that can be removed include error,info,batch,debug,statement,commit,rollback,result,resultset excludecategories=info,debug,result,commit,resultset # Date format dateformat=yyyy-MM-dd HH:mm:ss # Enable slow SQL logging outagedetection=true # Slow SQL record standard 2 seconds outagedetectioninterval=2 # Set the execution time. Only those exceeding this execution time will be recorded. The default value is 0. The unit is milliseconds executionThreshold=10