Mybatis – Plus detailed use tutorial

Java public welcome attention blogger, “master”, focus on dry share Java industry articles www.javaman.cn/jszw/mybati…

What is Mybatis – Plus

MyBatis-Plus (opens new window)MP for short is aMyBatis (opens new window)Enhancement tools, on the basis of MyBatis only do enhancement do not change, to simplify development, improve efficiency and born.

features

• No intrusion: only enhancements are made, no changes are made, and its introduction will not affect the existing project, as smooth as silk
• Low loss: Basic CURD will be injected automatically upon startup, with basically no loss in performance and direct object-oriented operation
• Powerful CRUD operations: built-in universal Mapper, universal Service, only through a small amount of configuration can achieve a single table most CRUD operations, more powerful condition constructor, to meet all types of use requirements
• Support Lambda form call: through Lambda expressions, it is convenient to write all kinds of query conditions, without worrying about field write errors
• Support automatic generation of primary keys: support up to four primary key policies (including distributed unique ID generator – Sequence), can be freely configured, perfect solution to the primary key problem
• Support for ActiveRecord mode: Support for ActiveRecord form calls, entity classes only need to inherit from Model classes to perform powerful CRUD operations
• Support custom global universal operations: support Write once (use anywhere)
• Built-in code generator: using code or Maven plug-in can quickly generate Mapper, Model, Service, Controller layer code, support template engine, more than a lot of custom configuration 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, write paging is equal to ordinary List query
• The paging plug-in supports a variety of databases: MySQL, MariaDB, Oracle, DB2, H2, HSQL, SQLite, Postgre, SQLServer, etc
• Built-in performance analysis plug-in: outputs 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: provides intelligent analysis and blocking of delete and UPDATE operations on all tables, and can customize interception rules to prevent misoperations

Quick start

steps

1, create database and database table

(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 'name',
	age INT(11) NULL DEFAULT NULL COMMENT 'age',
	email VARCHAR(50) NULL DEFAULT NULL COMMENT 'email'.PRIMARY KEY (id)
);
Copy the code

Insert table data

DELETE FROM user;

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

3. Create a Springboot project and introduce mubatis- Plus dependency

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

Application. Yml configure the 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
Copy the code

5. Use Mybatis – Plus

• entity

  @Data
  @AllArgsConstructor
  @NoArgsConstructor
  public class User {
      @TableId(type = IdType.AUTO) // Use database autoincrement
      private long id;
      private String name;
      private int age;
      private String email;
  }
  Copy the code

• Mapper interfaces

// Inherits the base class BaseMapper from the corresponding Mapper
@Repository
public interface UserMapper extends BaseMapper<User> {
	// All CRUD operations have been written
}
Copy the code

• The SpringBoot test boot class adds the @mapperscan scan annotation

  @MapperScan("com.mpstudy.mp.mapper")
  Copy the code

• test

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

    @Test
    void contextLoads(a) {
        List<User> users = userMapper.selectList(null); // The conditional constructor is not usedusers.forEach(System.out::println); }}Copy the code

• The test results

Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@75023c53]
User(id=1, name=Jone, age=18, [email protected], createTime=null, updateTime=null)
User(id=2, name=Jack, age=20, [email protected], createTime=null, updateTime=null)
User(id=3, name=Tom, age=28, [email protected], createTime=null, updateTime=null)
User(id=4, name=Sandy, age=21, [email protected], createTime=null, updateTime=null)
User(id=5, name=Billie, age=24, [email protected], createTime=null, updateTime=null)
Copy the code

The log configuration

Print out the SQL we execute for easy debugging

1. Add the configuration in application.yml

mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl // Standard output
Copy the code

2. After the log configuration is complete, corresponding logs 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, [email protected], null, null
<==        Row: 2, Jack, 20, [email protected], null, null
<==        Row: 3, Tom, 28, [email protected], null, null
<==        Row: 4, Sandy, 21, [email protected], null, null
<==        Row: 5, Billie, 24, [email protected], null, null
Copy the code

CRUD,

1. Insert operation

@Test
void insert(a){
    User user = new User();
    user.setName("Master Java 1");
    user.setAge(11);
    user.setEmail("[email protected]");
    int insert = userMapper.insert(user);
}
Copy the code

In the above example, the User id is not inserted. In the User class, it is set to automatic, that is, to follow the configuration of the database.

// Primary key policy supported by mybatis- Plus
public enum IdType {
    AUTO(0), // The database ID is automatically increased
    NONE(1), // The primary key is not set
    INPUT(2), // Enter it manually
    ID_WORKER(3), // The default global unique ID
    UUID(4), // Globally unique ID UUID
    ID_WORKER_STR(5);  //ID_WORKER string notation
Copy the code

​

2. Update operation

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

3. The timestamp is automatically filled

In real development, we want all create_time and update_time to be automatic, without human modification or maintenance

There are two implementation methods:

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

Method two: code level

• Write time-stamped 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".newDate(),metaObject); }}Copy the code

• Add a TableField annotation to the fields 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 annotations
      @TableField(fill = FieldFill.INSERT)
      private Date createTime;
  	// Modify the time annotation
      @TableField(fill = FieldFill.INSERT_UPDATE)
      private Date updateTime;
  }
  Copy the code

• test

  @Test
  void insert(a){
      User user = new User();
      user.setName("Public account: Java Master");
      user.setAge(2);
      user.setEmail("[email protected]");
      int insert = userMapper.insert(user);
  }
  Copy the code

4. Delete operations

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

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

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

5. Logical deletion

Compared with physical delete, we need to save the data, so we need to save the deleted data, which uses logical delete

Physical delete: Removes a logical delete from a database directly: invalidates a variable instead of removing it from the database! sfyx = 0 => sfyx = 1

• Add sFYX (valid or not) fields to the database table

  

• The entity class adds attributes

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

• Configuration logic removes components and is managed by SpringBoot

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

• 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
  Copy the code

• Test (see that while the DELETE method is executed, the actual SQL is an UPDATE statement)

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

  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
  Copy the code

6, paging query

• Configure a page interceptor

  @Bean
  public PaginationInterceptor paginationInterceptor(a){
      return new PaginationInterceptor();
  }
  Copy the code

• Using Page objects

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

7, multiple table query

• Create a VO object

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

• Add getAllUsers method to UserMapper, write SQL by 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(a);
}
Copy the code

• test

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

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, [email protected], null, 2021-09-20 18:05:06.0, 0, small 2 class <== Row: 3, Tom 28, [email protected], NULL, 2021-09-20 18:04:27.0, 1, Class 1Copy the code

8, multi-table paging query

• Create a VO object

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

• Add the getUsersByPage method to UserMapper and write SQL with select annotations

  @Repository
  public interface UserMapper extends BaseMapper<User> {
      // Pass in the IPage object and the QueryWrapper conditional constructor
      ${ew.customSQLSegment} ${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);
  }
  Copy the code

• test

  @Test
  void testGetUsersByPage(a){
      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);
  }
  Copy the code

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, [email protected], null, 2021-09-20 18:04:29.0, 1, small 2 class <== Row: 7, Java Master, 11, [email protected], null, 2021-09-20 18:04:29.0, 1, big class <== Total: 2Copy the code

Conditional constructor

Description:

The parent classes of QueryWrapper(LambdaQueryWrapper) and UpdateWrapper(LambdaUpdateWrapper) are used to generate WHERE conditions for SQL, Entity attributes are also used to generate SQL WHERE conditions Note: The WHERE conditions generated by entity do not have any behavior associated with where conditions generated using the various apis

Support: allEq, eq, isNull etc, specific see: conditions for the constructor | MyBatis – Plus (baomidou.com)

• test

@Test
void testWrapper01(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 records
    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(a){
    Select * from age 18 to 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(a){
    // Record 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);
}
Copy the code

11. Performance analysis plug-in

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

• The import plug-in

  @Bean
  @Profile({"dev"}) // The development environment runs
  public PerformanceInterceptor performanceInterceptor(a){
      PerformanceInterceptor interceptor = new PerformanceInterceptor();
      interceptor.setFormat(true); // Format the SQL
      interceptor.setMaxTime(20);  // Set the timeout period in milliseconds
      return interceptor;
  }
  Copy the code

• Application.xml is set to dev development mode

  # DataSource Config
  spring:
    profiles:
      active: dev
  Copy the code

• Test (the last 1 line of the log shows a timeout error)

  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 ! 
  Copy the code

12. Code automatic generator

Dao, Entity, Service, Controller Automatic generation AutoGenerator is MyBatis-Plus code generator, The AutoGenerator can quickly generate the code of Entity, Mapper, Mapper XML, Service, Controller and other modules, greatly improving the development efficiency.

• Import dependence

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

• 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 a code automatic generator object
          AutoGenerator autoGenerator = new AutoGenerator();
  
          //1
          GlobalConfig gc = new GlobalConfig();
          String oPath = System.getProperty("user.dir");// Get the path to the current project
          gc.setOutputDir(oPath + "/src/main/java");   // Generate file output root directory
          gc.setOpen(false);// No file box is displayed after the file is generated
          gc.setFileOverride(true);  // File overwrite
          gc.setAuthor("ryan");/ / the author
          gc.setServiceName("%sService");    // Remove the I prefix of Service
          gc.setIdType(IdType.ID_WORKER);
          gc.setDateType(DateType.ONLY_DATE);
          gc.setSwagger2(true);
          autoGenerator.setGlobalConfig(gc);
  
          //2. Configure the data source
          DataSourceConfig dsc = new DataSourceConfig();
          dsc.setDbType(DbType.MYSQL);   // Set the 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);
  
  
  
          // 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
          StrategyConfig strategy = new StrategyConfig();
          strategy.setInclude("user"."user_class"."class"); // 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");
          strategy.setTablePrefix("tb_"); // Remove the table prefix
          // Automatically populate the 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 locking
          strategy.setVersionFieldName("version"); strategy.setRestControllerStyle(true);
          strategy.setControllerMappingHyphenStyle(true);
          autoGenerator.setStrategy(strategy);
  
          // not adding this will result in a null pointer exception
          InjectionConfig injectionConfig = new InjectionConfig() {
              // Custom attribute injection: ABC
              // In the.ftl(or.vm) template, get the attributes with ${cfg. ABC}
              @Override
              public void initMap(a) {
                  Map<String, Object> map = new HashMap<>();
                  map.put("abc".this.getConfig().getGlobalConfig().getAuthor() + "-mp");
                  this.setMap(map); }};// Custom configuration
          autoGenerator.setCfg(injectionConfig);
  
          // Perform the buildautoGenerator.execute(); }}Copy the code