Mybatis plus tutorial

Welcome to the "Java master" of the blogger official account, focusing on dry cargo articles in Java field. http://www.javaman.cn/jszw/mybatis-plus

What is mybatis plus

MyBatis-Plus (opens new window) (MP) is a MyBatis (opens new window) On the basis of MyBatis, the enhancement tool is only enhanced without change. It is born to simplify development and improve efficiency.

characteristic

• No invasion: it is only enhanced without change, and its introduction will not affect the existing project, which is as smooth as silk
• Low loss: the basic CURD will be injected automatically upon startup, with basically no loss of performance and direct object-oriented operation
• Powerful crud operation: built in general Mapper and general Service, most CRUD operations of a single table can be realized only through a small number of configurations, and there is a powerful condition constructor to meet various use requirements
• Support Lambda formal call: it is convenient to write various query conditions through Lambda expression, and there is no need to worry about wrong fields
• Support automatic generation of primary key: support up to 4 primary key strategies (including distributed unique ID generator - Sequence), which can be configured freely to perfectly solve the primary key problem
• Support ActiveRecord mode: support ActiveRecord formal calls. Entity classes only need to inherit Model classes to perform powerful CRUD operations
• Support custom global general operations: support global general method injection (Write once, use anywhere)
• Built in code generator: code or Maven plug-in can be used to quickly generate Mapper, Model, Service and Controller layer code, support template engine, and more custom configurations for you to use
• Built in paging plug-in: Based on MyBatis physical paging, developers do not need to care about specific operations. After configuring the plug-in, writing paging is equivalent to ordinary List query
• The paging plug-in supports multiple databases: MySQL, MariaDB, Oracle, DB2, H2, HSQL, SQLite, Postgre, SQLServer and other databases
• Built in performance analysis plug-in: it can output SQL statements and their execution time. It is recommended to enable this function during development and testing to quickly find out slow queries
• Built in global interception plug-in: it provides intelligent analysis and blocking of full table delete and update operations, and can also customize interception rules to prevent misoperation

quick get start

step

1. Creating databases and database tables

(examples from the official website)

DROP TABLE IF EXISTS user;

CREATE TABLE user
(
	id BIGINT(20) NOT NULL COMMENT 'Primary key ID',
	name VARCHAR(30) NULL DEFAULT NULL COMMENT 'full name',
	age INT(11) NULL DEFAULT NULL COMMENT 'Age',
	email VARCHAR(50) NULL DEFAULT NULL COMMENT 'mailbox',
	PRIMARY KEY (id)
);

2. Insert table data

DELETE FROM user;

INSERT INTO user (id, name, age, email) VALUES
(1, 'Jone', 18, 'test1@baomidou.com'),
(2, 'Jack', 20, 'test2@baomidou.com'),
(3, 'Tom', 28, 'test3@baomidou.com'),
(4, 'Sandy', 21, 'test4@baomidou.com'),
(5, 'Billie', 24, 'test5@baomidou.com');

3. Create a new springboot project and introduce mubatis plus dependency

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.1.0</version>
</dependency>

4. application.yml configuring mysql data source

# DataSource Config
spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost/mp_plus?serverTimezone=GMT%2B8&characterEncoding=UTF-8&allowMultiQueries=true
    username: root
    password: 123456

5. Using mybatis plus

• entity

  @Data
  @AllArgsConstructor
  @NoArgsConstructor
  public class User {
      @TableId(type = IdType.AUTO) //Using database self augmentation
      private long id;
      private String name;
      private int age;
      private String email;
  }
  

• mapper interface

// Inherit the basic class BaseMapper on the corresponding Mapper
@Repository
public interface UserMapper extends BaseMapper<User> {
	// All CRUD operations have been written
}

• Add @ MapperScan scan annotation to springboot test startup class

  @MapperScan("com.mpstudy.mp.mapper")
  

• test

@MapperScan("com.mpstudy.mp.mapper")
@SpringBootTest
class MpApplicationTests {
    
    // It inherits BaseMapper, and all methods use their own parent class
    // We can also write our own extension methods!
    @Autowired
    UserMapper userMapper;

    @Test
    void contextLoads() {
        List<User> users = userMapper.selectList(null); //The condition constructor is not used first
        users.forEach(System.out::println);
    }
}

• test result

Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@75023c53]
User(id=1, name=Jone, age=18, email=test1@baomidou.com, createTime=null, updateTime=null)
User(id=2, name=Jack, age=20, email=test2@baomidou.com, createTime=null, updateTime=null)
User(id=3, name=Tom, age=28, email=test3@baomidou.com, createTime=null, updateTime=null)
User(id=4, name=Sandy, age=21, email=test4@baomidou.com, createTime=null, updateTime=null)
User(id=5, name=Billie, age=24, email=test5@baomidou.com, createTime=null, updateTime=null)

Log configuration

Print out the sql executed by us to facilitate debugging

1. Add a new configuration in application.yml

mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl //standard output 

2. After the log configuration is completed, the corresponding log will be generated on the console

Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@75023c53] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@688197093 wrapping com.mysql.jdbc.JDBC4Connection@3610f277] will not be managed by Spring
==>  Preparing: SELECT id,name,age,email,create_time,update_time FROM user 
==> Parameters: 
<==    Columns: id, name, age, email, create_time, update_time
<==        Row: 1, Jone, 18, test1@baomidou.com, null, null
<==        Row: 2, Jack, 20, test2@baomidou.com, null, null
<==        Row: 3, Tom, 28, test3@baomidou.com, null, null
<==        Row: 4, Sandy, 21, test4@baomidou.com, null, null
<==        Row: 5, Billie, 24, test5@baomidou.com, null, null

CRUD details

1. Insert operation

@Test
void insert(){
    User user = new User();
    user.setName("java Master 1");
    user.setAge(11);
    user.setEmail("111000@qq.com");
    int insert = userMapper.insert(user);
}

In the above example, the User id is not inserted, and automatic has been set in the User class, that is, follow the configuration of the database (automatic increment has been set in the database)

//Primary key policies supported by mybatis plus
public enum IdType {
    AUTO(0), // Database id self increment
    NONE(1), // No primary key set
    INPUT(2), // Manual input
    ID_WORKER(3), // Default globally unique id
    UUID(4), // Globally unique id uuid
    ID_WORKER_STR(5);  //ID_WORKER string representation

​

2. Update operation

@Test
void update(){
    User user = new User();
    user.setId(6);
    user.setAge(38);
    int i = userMapper.updateById(user); //An object was passed in
    System.out.println(i);
}

3. Timestamp auto fill

In the actual development, we hope that all the create_time and update_time are completed automatically without manual modification and maintenance

There are two implementation methods:

Method 1: database level (add default field to the database)

Method 2: code level

• Write timestamps and auto fill annotations

  import com.baomidou.mybatisplus.core.handlers.MetaObjectHandler;
  import lombok.extern.slf4j.Slf4j;
  import org.apache.ibatis.reflection.MetaObject;
  import org.springframework.stereotype.Component;
  
  import java.util.Date;
  
  @Slf4j
  @Component
  public class MyMetaObjectHandler implements MetaObjectHandler {
      @Override
      public void insertFill(MetaObject metaObject) {
          log.info("Start filling");
          this.setFieldValByName("createTime",new Date(),metaObject);
          this.setFieldValByName("updateTime",new Date(),metaObject);
      }
  
      @Override
      public void updateFill(MetaObject metaObject) {
          log.info("Update fill");
          this.setFieldValByName("updateTime",new Date(),metaObject);
      }
  }
  

• Add a TableField annotation on the field of the entity class

  @Data
  @AllArgsConstructor
  @NoArgsConstructor
  public class User {
  
      @TableId(type = IdType.AUTO)
      private long id;
      private String name;
      private int age;
      private String email;
  
      //Create time annotation
      @TableField(fill = FieldFill.INSERT)
      private Date createTime;
  	//Modify time annotation
      @TableField(fill = FieldFill.INSERT_UPDATE)
      private Date updateTime;
  }
  

• test

  @Test
  void insert(){
      User user = new User();
      user.setName("Official account: java master");
      user.setAge(2);
      user.setEmail("javadashi@qq.com");
      int insert = userMapper.insert(user);
  }
  

4. Delete operation

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

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

// Delete by map
@Test
public void testDeleteMap(){
HashMap<String, Object> map = new HashMap<>();
map.put("name","java master");
userMapper.deleteByMap(map);

5. Logical deletion

Compared with physical deletion, we need to retain the data, so we need to retain the deleted data, which requires logical deletion

Physical delete: remove directly from the database
Logical deletion: it is not removed from the database, but invalidated by a variable! sfyx = 0 => sfyx = 1

• Add sfyx (valid or not) field in database table

  

• Add attribute to entity class

  @TableLogic //Logical deletion
  private Integer sfyx;
  

• The configuration logic removes components and is managed by spring boot

  // Delete components logically!
  @Bean
  public ISqlInjector sqlInjector() {
  	return new LogicSqlInjector();
  }
  

• Modify application.yml to add configuration

  mybatis-plus:
    configuration:
      log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
    global-config:
      db-config:
        logic-delete-value: 0  //Deleted
        logic-not-delete-value: 1  //Not deleted
  

• Test (see that although the delete method is executed, the actual sql is an update statement)

  @Test
      void testDelete(){
          int i = userMapper.deleteById(2);
      }
  

  Creating a new SqlSession
  SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@456bcb74] was not registered for synchronization because synchronization is not active
  JDBC Connection [HikariProxyConnection@66747889 wrapping com.mysql.jdbc.JDBC4Connection@4dd90166] will not be managed by Spring
  ==>  Preparing: UPDATE user SET sfyx=0 WHERE id=? AND sfyx=1 
  ==> Parameters: 2(Integer)
  <==    Updates: 1
  

6. Paging query

• Configure paging interceptor

  @Bean
  public PaginationInterceptor paginationInterceptor(){
      return new PaginationInterceptor();
  }
  

• Using Page objects

  @Test
  void testPage(){
      Page<User> page = new Page<>(1,3);
      userMapper.selectPage(page, null);
      page.getRecords().forEach(System.out::println);
      System.out.println(page.getTotal());
  }
  

7. Multi table query

• Create VO object

  import com.mpstudy.mp.entity.User;
  import lombok.Data;
  
  @Data
  public class UserClassVo extends User {
      private String className;
  }
  

• Add getAllUsers method in UserMapper and write sql through select annotation

@Repository
public interface UserMapper extends BaseMapper<User> {
    @Select("select a.*,b.name as class_name from user a,class b,user_class c where a.id=c.user_id and b.id=c.class_id ")
    List<UserClassVo> getAllUsers();
}

• test

  @Test
  void testGetAllUsers(){
      List<UserClassVo> allUsers = userMapper.getAllUsers();
      allUsers.forEach(System.out::println);
  }
  

Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@47f04e4d] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@843410864 wrapping com.mysql.jdbc.JDBC4Connection@5f5827d0] will not be managed by Spring
==>  Preparing: select a.*,b.name as class_name from user a,class b,user_class c where a.id=c.user_id and b.id=c.class_id 
==> Parameters: 
<==    Columns: id, name, age, email, create_time, update_time, sfyx, class_name
<==        Row: 2, Jack, 20, test2@baomidou.com, null, 2021-09-20 18:05:06.0, 0, Class two
<==        Row: 3, Tom, 28, test3@baomidou.com, null, 2021-09-20 18:04:27.0, 1, Freshman class

8. Multi table paging query

• Create VO object

  import com.mpstudy.mp.entity.User;
  import lombok.Data;
  
  @Data
  public class UserClassVo extends User {
      private String className;
  }
  

• Add getUsersByPage method in UserMapper and write sql through select annotation

  @Repository
  public interface UserMapper extends BaseMapper<User> {
      //Pass in the IPage object and QueryWrapper condition constructor
      //sql parses constructor content through ${ew.customSqlSegment}
      @Select("select a.*,c.name as class_name from user a left join user_class b on a.id=b.user_id left join class c on b.class_id = c.id " + "${ew.customSqlSegment} ")
      IPage<UserClassVo> getUsersByPage(IPage<UserClassVo> page,@Param(Constants.WRAPPER) QueryWrapper wrapper);
  }
  

• test

  @Test
  void testGetUsersByPage(){
      Page<UserClassVo> page = new Page<>(2,2);
      QueryWrapper<UserClassVo> wrapper = new QueryWrapper<>();
      wrapper.likeRight("a.name","java");
      userMapper.getUsersByPage(page,wrapper);
      page.getRecords().forEach(System.out::println);
  }
  

Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@f438904] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@766089249 wrapping com.mysql.jdbc.JDBC4Connection@62cba181] will not be managed by Spring
 JsqlParserCountOptimize sql=select a.*,c.name as class_name from user a left join user_class b on a.id=b.user_id left join class c on b.class_id = c.id WHERE a.name LIKE ?
==>  Preparing: SELECT COUNT(1) FROM user a LEFT JOIN user_class b ON a.id = b.user_id LEFT JOIN class c ON b.class_id = c.id WHERE a.name LIKE ? 
==> Parameters: java%(String)
<==    Columns: COUNT(1)
<==        Row: 3
==>  Preparing: select a.*,c.name as class_name from user a left join user_class b on a.id=b.user_id left join class c on b.class_id = c.id WHERE a.name LIKE ? LIMIT ?,? 
==> Parameters: java%(String), 0(Long), 2(Long)
<==    Columns: id, name, age, email, create_time, update_time, sfyx, class_name
<==        Row: 6, java master, 38, javadashi@qq.com, null, 2021-09-20 18:04:29.0, 1, Class two
<==        Row: 7, java master, 11, javadashi@qq.com, null, 2021-09-20 18:04:29.0, 1, Freshman class
<==      Total: 2

10. Conditional constructor

explain:

Parent classes of QueryWrapper(LambdaQueryWrapper) and UpdateWrapper(LambdaUpdateWrapper)
The where condition is used to generate sql, and the entity attribute is also used to generate the where condition of sql
Note: the where condition generated by entity does not have any associated behavior with the where condition generated using various APIs

Support: allEq,eq,isNull, etc. see: Conditional constructor | mybatis plus (baomidou. Com)

• test

@Test
void testWrapper01(){
    // Query records of users 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").isNotNull("create_time").gt("age",12);
    List<User> users = userMapper.selectList(wrapper);
    users.forEach(System.out::println);
}

@Test
void testWrapper02(){
    // Query records between ages 18 and 24
    QueryWrapper<User> wrapper = new QueryWrapper<>();
    wrapper.between("age",18,24);
    List<User> users = userMapper.selectList(wrapper);
    users.forEach(System.out::println);
}

@Test
void testWrapper03(){
    // Records with java in the name
    QueryWrapper<User> wrapper = new QueryWrapper<>();
    wrapper.likeRight("name","java");
    List<User> users = userMapper.selectList(wrapper);
    users.forEach(System.out::println);
}

11. Performance analysis plug-in

In actual work, you may encounter some cases of slow sql. Through the performance analysis plug-in, you can locate the slow sql and its running time

• Import plug-in

  @Bean
  @Profile({"dev"}) //Development environment operation
  public PerformanceInterceptor performanceInterceptor(){
      PerformanceInterceptor interceptor = new PerformanceInterceptor();
      interceptor.setFormat(true); //Format sql
      interceptor.setMaxTime(20);  //Set the timeout duration in milliseconds
      return interceptor;
  }
  

• Set application.xml to dev development mode

  # DataSource Config
  spring:
    profiles:
      active: dev
  

• Test (the last line of the log shows an error when it is exceeded)

  org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException: 
  ### Error querying database.  Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException:  The SQL execution time is too large, please optimize ! 
  ### The error may exist in com/mpstudy/mp/mapper/UserMapper.java (best guess)
  ### The error may involve com.mpstudy.mp.mapper.UserMapper.getUsersByPage
  ### The error occurred while handling results
  ### SQL: select a.*,c.name as class_name from user a left join user_class b on a.id=b.user_id left join class c on b.class_id = c.id WHERE a.name LIKE ? LIMIT ?,?
  ### Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException:  The SQL execution time is too large, please optimize ! 
  

12. Automatic code generator

dao, entity, service and controller are automatically generated
AutoGenerator is the code generator of mybatis plus, which can quickly generate Entity
The code of Mapper, Mapper XML, Service, Controller and other modules greatly improves the development efficiency.

• Import dependency

  <dependency>
      <groupId>com.baomidou</groupId>
      <artifactId>mybatis-plus-generator</artifactId>
      <version>3.1.0</version>
  </dependency>
  

• code generation

  import com.baomidou.mybatisplus.annotation.DbType;
  import com.baomidou.mybatisplus.annotation.FieldFill;
  import com.baomidou.mybatisplus.annotation.IdType;
  import com.baomidou.mybatisplus.generator.AutoGenerator;
  import com.baomidou.mybatisplus.generator.InjectionConfig;
  import com.baomidou.mybatisplus.generator.config.DataSourceConfig;
  import com.baomidou.mybatisplus.generator.config.GlobalConfig;
  import com.baomidou.mybatisplus.generator.config.PackageConfig;
  import com.baomidou.mybatisplus.generator.config.StrategyConfig;
  import com.baomidou.mybatisplus.generator.config.po.TableFill;
  import com.baomidou.mybatisplus.generator.config.rules.DateType;
  import com.baomidou.mybatisplus.generator.config.rules.NamingStrategy;
  
  import java.util.ArrayList;
  import java.util.HashMap;
  import java.util.Map;
  
  //Automatic code generator
  public class CodeGenerator {
  
      public static void main(String[] args) {
          //Build an automatic code generator object
          AutoGenerator autoGenerator = new AutoGenerator();
  
          //1. Global configuration
          GlobalConfig gc = new GlobalConfig();
          String oPath = System.getProperty("user.dir");//Get the path of the current project
          gc.setOutputDir(oPath + "/src/main/java");   //Generate file output root directory
          gc.setOpen(false);//The file box does not pop up after generation
          gc.setFileOverride(true);  //File overwrite
          gc.setAuthor("ryan");// author
          gc.setServiceName("%sService");    //Remove Service I prefix
          gc.setIdType(IdType.ID_WORKER);
          gc.setDateType(DateType.ONLY_DATE);
          gc.setSwagger2(true);
          autoGenerator.setGlobalConfig(gc);
  
          ///2. Data source configuration
          DataSourceConfig dsc = new DataSourceConfig();
          dsc.setDbType(DbType.MYSQL);   //Set database type
          dsc.setUrl("jdbc:mysql://localhost:3306/mp_ Plus? Usessl = false & useunicode = true & characterencoding = UTF-8 & servertimezone = GMT% 2B8 "); / / specify the database
          dsc.setDriverName("com.mysql.jdbc.Driver");
          dsc.setUsername("root");
          dsc.setPassword("root");
          autoGenerator.setDataSource(dsc);
  
  
  
          //3. Package configuration
          PackageConfig pc = new PackageConfig();
          pc.setModuleName("mp");
          pc.setParent("com.mpstudy");
          pc.setEntity("pojo");
          pc.setMapper("mapper");
          pc.setService("service");
          pc.setController("controller");
          autoGenerator.setPackageInfo(pc);
  
          // 4. Policy configuration
          StrategyConfig strategy = new StrategyConfig();
          strategy.setInclude("user","user_class","class"); // Set the table name to map
          strategy.setNaming(NamingStrategy.underline_to_camel);
          strategy.setColumnNaming(NamingStrategy.underline_to_camel);
          strategy.setEntityLombokModel(true); // Automatic lombok;
          strategy.setLogicDeleteFieldName("deleted");
          strategy.setTablePrefix("tb_"); //Remove table prefix
          // Auto fill configuration
          TableFill gmtCreate = new TableFill("create_time", FieldFill.INSERT);
          TableFill gmtModified = new TableFill("update_time", FieldFill.INSERT_UPDATE);
          ArrayList<TableFill> tableFills = new ArrayList<>();
          tableFills.add(gmtCreate);
          tableFills.add(gmtModified);
          strategy.setTableFillList(tableFills);
          // Optimistic lock
          strategy.setVersionFieldName("version");strategy.setRestControllerStyle(true);
          strategy.setControllerMappingHyphenStyle(true);
          autoGenerator.setStrategy(strategy);
  
          //If this is not added, a null pointer exception will be reported
          InjectionConfig injectionConfig = new InjectionConfig() {
              //Custom attribute injection: abc
              //In the. FTL (or. vm) template, get the attributes through ${cfg.abc}
              @Override
              public void initMap() {
                  Map<String, Object> map = new HashMap<>();
                  map.put("abc", this.getConfig().getGlobalConfig().getAuthor() + "-mp");
                  this.setMap(map);
              }
          };
          //Custom configuration
          autoGenerator.setCfg(injectionConfig);
  
          // Execute build
          autoGenerator.execute();
      }
  }