MyBatisPlus API is commonly used

Introduction to the

MyBatis-Plus (MP for short) is a MyBatis enhancement tool, on the basis of MyBatis only do enhancement do not change, to simplify the development and improve efficiency. Many domestic Internet companies use MyBatis or do their own persistence layer framework based on MyBatis, which is enough to show that MyBatis is relatively popular in persistence layer framework. Although MyBatis can operate database directly in XML through SQL statements, more flexible, but compared with JPA, it is much more troublesome, need to write a large number of XML files, and the emergence of MyBatis-Plus is a good solution to this problem.

This article will document the apis that individuals often use when using MP

Creating a database

The database name is mybatis_plus

Create a table

Create a user table

DROP TABLE IF EXISTS user;
CREATE TABLE user
(
id BIGINT(20) NOT NULL COMMENT 'primary key ID',
name VARCHAR(30) NULL DEFAULT NULL COMMENT 'name',
age INT(11) NULL DEFAULT NULL COMMENT 'age',
email VARCHAR(50) NULL DEFAULT NULL COMMENT 'email'.PRIMARY KEY (id)
);
INSERT INTO user (id, name, age, email) VALUES
(1.'Jone'.18.'[email protected]'),
(2.'Jack'.20.'[email protected]'),
(3.'Tom'.28.'[email protected]'),
(4.'Sandy'.21.'[email protected]'),
(5.'Billie'.24.'[email protected]');
Copy the code

Note: — Create_time (created time) and update_time (modified time) are commonly used in corporate development, while Version (optimistic lock) and deleted (logical deletion) depend on business requirements

Initialize the project

Java is initialized using SpringBoot

Import dependence

<! -- Database driver -->
<dependency>  
    <groupId>mysql</groupId>  
    <artifactId>mysql-connector-java
    </artifactId>
</dependency>

<! -- lombok -->
<dependency>  
    <groupId>org.projectlombok</groupId>  
    <artifactId>lombok</artifactId>
</dependency>

<! -- mybatis-plus -->
<! Mybatis - Plus is self-developed, not official! -->
<dependency>  
    <groupId>com.baomidou</groupId>  
    <artifactId>mybatis-plus-boot-starter</artifactId>  
    <version>3.0.5</version>
</dependency>
Copy the code

Note: Try not to import both Mybatis and Mybatis – Plus! Avoid unexpected problems caused by version differences

Connecting to a Database

spring:
    profiles:
        action: dev
    datasource:
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/mybatis_plus? serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8&useSSL=false&allowPublicKeyRetrieval=true
        username: root
        password: 123456
        type:com.zaxxer.hikari.HikariDataSource
        hikari:
            maximun-pool-size: 15
            minimun-idle: 5
            idle-timeout: 30000

# mybatisPlus configuration
Mybatis - plus:
    configuration:
        # print log
        log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
        # Hump naming
        map-underscore-to-camel-case: true
    mapper-locations: classpath:/mapper/*.xml
Copy the code

Business code

Entity class

@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {  
    private Long id;  
    private String name;  
    private Integer age;  
    private String email;
}
Copy the code

Mapper interfaces

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xiaomai.pojo.User;import org.springframework.stereotype.Repository;
// Inherits the base class BaseMapper@Repository from the corresponding Mapper
// represents the persistence layer
public interface UserMapper extends BaseMapper<User> {  

// All CRUD operations have been written

}
Copy the code

MapperScan(” com.xiaomai.mapper “);

test

@SpringBootTestclass 
MybatisPlusApplicationTests {  

// BaseMapper inherits all methods from its parent class
// We can also write our own extension methods!
@Autowired  private UserMapper userMapper; 

     @Test  void contextLoads(a) {    
       // The argument is a Wrapper, the condition constructor. Here we set the condition to null and query all.
        List<User> users = userMapper.selectList(null); users.forEach(System.out::println); }}Copy the code

All data output

Mybatis – plus the CRUD

The insert

// Test the insert
    @Test
    public void testInsert(a){
        User user = new User();
        user.setName(kwhua_mybatis-plus_insertTest);
        user.setAge(15);
        user.setEmail(310697723@qq.com);

        int result = userMapper.insert(user); // Help us automatically generate id
        System.out.println(result); // The number of rows affected
        System.out.println(user); // The id is automatically populated when it is seen. }
Copy the code

If you see the ID, it will fill in automatically. The default value of the database insert ID is the global unique ID

The update operation

 @Test
    public void testUpdate(a){
        User user = new User();
        // Automatically concatenate dynamic SQL with conditions
        user.setId(1302223874217295874L);
        user.setName(kwhua_mybatis-plus_updateTest);
        user.setAge(20);
        // Note that the updateById argument is an object!
        int i = userMapper.updateById(user);
        System.out.println(i);
    }

Copy the code

Automatic filling

Build time, modify time! Both field operations are automated and we don’t want to update them manually!

All database tables must be configured with GMT_CREATE, GMT_Modified! And it needs automation!

Mode 1: Database level (generally not used in work)

Alter table gmT_create, gmT_modified

2. Synchronize entity classes

private Date gmtCreate;
private Date gmtModified;
Copy the code

3. Check again

Mode 2: code level

1, delete database default value, update operation!

2, the entity class field attributes need to add annotations

 // Add padding to the field
    @TableField(fill = FieldFill.INSERT)
    private Date gmt_create;

    @TableField(fill = FieldFill.INSERT_UPDATE)
    private Date gmt_modified;
Copy the code

3. Write a processor to handle this annotation!

@Slf4j
@Component // Don't forget to add the handler to the IOC container!
public class MyMetaObjectHandler implements MetaObjectHandler {
    // The padding strategy for insertion
    @Override
    public void insertFill(MetaObject metaObject) { log.info(start insert fill.....) ;// setFieldValByName(String fieldName, Object fieldVal, MetaObject metaObject
        this.setFieldValByName(gmt_create,new Date(),metaObject);
        this.setFieldValByName(gmt_modified,new Date(),metaObject);
    }

    // The populating strategy for the update
    @Override
    public void updateFill(MetaObject metaObject) { log.info(start update fill.....) ;this.setFieldValByName(gmt_modified,newDate(),metaObject); }}Copy the code

4. Test inserts and updates to check time changes.

Optimistic locking

Optimistic lock: name think meaning, very optimistic, it always think there will be no problem, no matter what do not lock! If there is a problem, update the value test again

Pessimistic lock: reason name think meaning, very pessimistic, it always think always appear a problem, no matter what do can lock! Operate again!

Optimistic lock implementation: When retrieving records and obtaining the current version update,set version = newVersion where version = oldVersion when performing update. If the version is incorrect, the update fails

Optimistic locking:1, obtain the version number version =1
-- A
update user set name = kwhua, version = version + 1
where id = 2 and version = 1-- Thread B completes first, and version =2, will result in A modification failure! update user set name = kwhua, version = version +1
where id = 2 and version = 1
Copy the code

Optimistic lock test

Add version to database!

2. Add the corresponding field to the entity class

 @Version // Optimistic lock Version annotation
    private Integer version;
Copy the code

3. Register components

// Scan our mapper folder
@MapperScan(com.kwhua.mapper)
@EnableTransactionManagement
@Configuration / / configuration class
public class MyBatisPlusConfig {
    // Register optimistic lock plug-in
    @Bean
    public OptimisticLockerInterceptor optimisticLockerInterceptor(a) {
        return newOptimisticLockerInterceptor(); }}Copy the code

4, test,

// Test optimistic lock successfully!
    @Test
    public void testOptimisticLocker(a){
        // 1. Query user information
        User user = userMapper.selectById(1L);
        // 2. Modify user information
        user.setName(kwhua);
        user.setEmail(123456@qq.com);
        // 3. Perform the update
        userMapper.updateById(user);
    }
Copy the code

The version field has changed from 1 to 2

// Test optimistic lock failed! Under the multithreading
    @Test
    public void testOptimisticLocker2(a){

        / / thread 1
        User user = userMapper.selectById(1L);
        user.setName(kwhua111);
        user.setEmail(123456@qq.com);

        // Another thread executes queue jumping
        User user2 = userMapper.selectById(1L);
        user2.setName(kwhua222);
        user2.setEmail(123456@qq.com);
        userMapper.updateById(user2);

        // Spin lock to try to commit many times!
        userMapper.updateById(user); // If there is no optimistic lock, the value of queue-jumping thread will be overwritten!
    }

Copy the code

You can see that thread 1 failed to perform the update

Query operation

// Test the query
    @Test
    public void testSelectById(a){
        User user = userMapper.selectById(1L);
        System.out.println(user);
    }

    // Test batch query!
    @Test
    public void testSelectByBatchId(a){
        List<User> users = userMapper.selectBatchIds(Arrays.asList(1.2.3));
        users.forEach(System.out::println);
    }

    // Use the map operation as one of the conditional queries
    @Test
    public void testSelectByBatchIds(a){
        HashMap<String, Object> map = new HashMap<>();
        // Custom to query
        map.put(name,kwhua);
        map.put(age,15);

        List<User> users = userMapper.selectByMap(map);
        users.forEach(System.out::println);
    }

Copy the code

Paging query

1. Configure the interceptor component

// Paging plug-in
@Bean
public PaginationInterceptor paginationInterceptor(a) {
  return  new PaginationInterceptor();
}

Copy the code

2, use the Page object directly!

// Test paging queries
@Test
public void testPage(a){
  // Parameter one: current page
  // Parameter two: page size
  Page<User> page = new Page<>(2.5);
  userMapper.selectPage(page,null);
  page.getRecords().forEach(System.out::println);
  System.out.println(page.getTotal());
}
Copy the code

Physically deleted

// Test delete
    @Test
    public void testDeleteById(a){
        userMapper.deleteById(1L);
    }

    // Batch delete by id
    @Test
    public void testDeleteBatchId(a){
        userMapper.deleteBatchIds(Arrays.asList(2L.3L));
    }

    // Delete using map
    @Test
    public void testDeleteMap(a){
        HashMap<String, Object> map = new HashMap<>();
        map.put(name,kwhua);
        userMapper.deleteByMap(map);
    }
Copy the code

Logic to delete

Physical delete: Directly remove from the database

Logical delete: not removed from the database, but invalidated by a variable! deleted = 0 => deleted = 1

Administrators can view deleted records! Prevent data loss, similar to the recycle bin!

1. Add a deleted field to the table

2. Add attributes to the entity class

 @TableLogic // Logical delete
 private Integer deleted;
Copy the code

3, configuration,

    // Logical delete component!
    @Bean
    public ISqlInjector sqlInjector(a) {
        return new LogicSqlInjector();
    }
Copy the code

Configuration file Configuration

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

4, test,

Test to delete

The field value is also changed from 0 to 1

Test the query

Performance analysis plug-in

Purpose: Performance analysis interceptor, used to output each SQL statement and its execution time

MP also provides performance analysis plugins, stop running if you exceed this time!

1. Import the plug-in

/** * SQL execution efficiency plug-in */
    @Bean
    @Profile({dev,test})// Set the dev test environment on to ensure our efficiency
    public PerformanceInterceptor performanceInterceptor(a) {
        PerformanceInterceptor performanceInterceptor = new PerformanceInterceptor();
        performanceInterceptor.setMaxTime(100); //ms Sets the maximum time for SQL execution
        performanceInterceptor.setFormat(true);
        return performanceInterceptor;
    }
Copy the code

Conditional constructor (Wrapper)

.isNotNull .gt

@Test
    void contextLoads(a) {
        Query the user whose name is not empty and whose mailbox is not empty and whose age is greater than or equal to 12
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper
                .isNotNull(name) / / not be empty
                .isNotNull(email)
                .ge(age,18);
        userMapper.selectList(wrapper).forEach(System.out::println); // Compare this to the map we just learned
    }

Copy the code

.eq

 @Test
    void test2(a){
        // query the name kwhua
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.eq(name,kwhua);
        User user = userMapper.selectOne(wrapper); // Use selectOne to query one data and List or Map to query multiple results
        System.out.println(user);
    }

Copy the code

.between

@Test
    void test3(a){
        // Query the user whose age is between 20 and 30
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.between(age,20.30); / / range
        Integer count = userMapper.selectCount(wrapper);// Number of query results
        System.out.println(count);
    }
Copy the code

.like

 // fuzzy query
    @Test
    void test4(a){
        // Query data whose names do not contain e and whose mailboxes start with t
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        // Likelift %t, likeRight %t
        wrapper
                .notLike(name,e)
                .likeRight(email,t);

        List<Map<String, Object>> maps = userMapper.selectMaps(wrapper);
        maps.forEach(System.out::println);
    }
Copy the code

.insql

 // fuzzy query
    @Test
    void test5(a){

        QueryWrapper<User> wrapper = new QueryWrapper<>();
        // id is found in subquery
        wrapper.inSql(id,select id from user where id<3);

        List<Object> objects = userMapper.selectObjs(wrapper);
        objects.forEach(System.out::println);
    }

Copy the code

.orderByAsc

/ / test 6
    @Test
    void test6(a){
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        // Sort by ID
        wrapper.orderByAsc(id);

        List<User> users = userMapper.selectList(wrapper);
        users.forEach(System.out::println);
    }
Copy the code

Automatic code generator

You can create a Java class in the Test folder

// Automatic code generator
public class generateCode {
   public static void main(String[] args) {
    // A code automatic generator object needs to be built
    AutoGenerator mpg = new AutoGenerator();
    // Configure the policy
    // 1
    GlobalConfig gc = new GlobalConfig();
    String projectPath = System.getProperty(user.dir);
    gc.setOutputDir(projectPath+/src/main/java);
    gc.setAuthor(kwhua);// Author name
    gc.setOpen(false);
    gc.setFileOverride(false); // Whether to override
    gc.setIdType(IdType.ID_WORKER);
    gc.setDateType(DateType.ONLY_DATE);
    gc.setSwagger2(true);// Entity attribute Swagger2 annotation

    // Customize the file name, note that %s will automatically populate the table entity attributes!
    gc.setServiceName(%sService); 
    gc.setControllerName(%sController);
    gc.setServiceName(%sService);
    gc.setServiceImplName(%sServiceImpl);
    gc.setMapperName(%sMapper);
    gc.setXmlName(%sMapper);

    mpg.setGlobalConfig(gc);

    //2. Set the data source
    DataSourceConfig dsc = new DataSourceConfig();
    dsc.setUrl(jdbc:mysql://localhost:3306/kwhua_test?
useSSL=false&useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8);
    dsc.setDriverName(com.mysql.cj.jdbc.Driver);
    // dsc.setDriverName(com.mysql.jdbc.Driver); //mysql5.6
    dsc.setUsername(root);
    dsc.setPassword(root);
    dsc.setDbType(DbType.MYSQL);
    mpg.setDataSource(dsc);
    // Package configuration
    PackageConfig pc = new PackageConfig();
    pc.setParent(com.kwhua); / / package name
    pc.setModuleName(model); / / module name
    pc.setEntity(entity);
    pc.setMapper(mapper);
    pc.setService(service);
    pc.setController(controller);
    mpg.setPackageInfo(pc);

    //4
    StrategyConfig strategy = new StrategyConfig();
    strategy.setInclude(user,course); // Set the name of the table to be mapped
    strategy.setNaming(NamingStrategy.underline_to_camel);
    strategy.setColumnNaming(NamingStrategy.underline_to_camel);
    strategy.setEntityLombokModel(true); // Automatic lombok;
    strategy.setLogicDeleteFieldName(deleted);
    // Automatically populate the configuration
    TableFill gmtCreate = new TableFill(gmt_create, FieldFill.INSERT);
    TableFill gmtModified = new TableFill(gmt_modified,FieldFill.INSERT_UPDATE);
    ArrayList<TableFill> tableFills = new ArrayList<>();
    tableFills.add(gmtCreate);
    tableFills.add(gmtModified);
    strategy.setTableFillList(tableFills);
    / / optimistic locking
    strategy.setVersionFieldName(version);
    // Create the corresponding class name based on your table name. If your table name does not have an underscore, such as test, then you can cancel this step
    strategy.setTablePrefix(t_);
    strategy.setRestControllerStyle(true); / / rest requests
    // Automatically underline, such as localhost:8080/hello_id_2
    strategy.setControllerMappingHyphenStyle(true); 
    mpg.setStrategy(strategy);
    mpg.execute(); / / execution}}Copy the code

Execute the main method to generate the corresponding code