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 SQL3, 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