Detailed explanation of Mybatis-Plus knowledge points

Mybatis-plus (MP for short), an enhancement tool based on Mybatis,All Mybatis functions are retained, and general CRUD, condition constructor, paging plug-ins and other practical tools are added.

characteristic

• Get it right away: Through general mapper and service, you can complete single table CURE operations without writing XML
• Lambda support: Use Lambda expressions to build query conditions, avoid hard-coded field names, and improve code security
• More primary key strategies: Supports multiple primary key generation methods, such as snowflake algorithm, self-increase, etc.
• Paging plug-in: Built-in paging plug-in, supports multiple databases, simplifying paging query operations

Compared to Mybatis

• Provides a large number of automation functions, such as general CRUD, conditional constructor, paging support greatly reduces operation code and improves development efficiency
• Mybatis needs to manually write SQL, write XML files and map interface DAO

Quick Start

Log configuration

• Log output to standard output stream

mybatis-plus:
   # mapper configuration file
   mapper-locations: classpath:mapper/*.xml
   # resultType alias. Without this configuration, the resultType package name must be written in full. After configuration, just write the class name.
   type-aliases-package: .
   //
   configuration:
     log-impl:

Add dependencies and configuration

• I useSpringBoot 2.7.18,JDK1.8:The import dependencies are as follows

        <dependency>
            <groupId></groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.3.2</version>
        </dependency>

• as follows:

# is also a springboot configuration file, both can be used
 server:
   port: 8080
   servlet:
     #Configure the root path
     context-path: /
 # Configure the data source
 spring:
   datasource:
     driver-class-name:
     url: jdbc:mysql://localhost:3306/xiaomi_store?userSSL=false;serverTimezone=Asia/Shanghai
     username: root
     password: root
 mybatis-plus:
   # mapper configuration file
   mapper-locations: classpath:mapper/*.xml
   # resultType alias. Without this configuration, the resultType package name must be written in full. After configuration, just write the class name.
   type-aliases-package: .

Define entity classes and Mapper interfaces

Entity Class

• Using Mybatis-plus annotation on entity classes
• @TableName()→ Specify the table for database query, do not add the default class name to the underlined database name
• @TableId(name,type)→ Used to mark the primary key, name is the corresponding name of the database, and used to specify the auto-increment algorithm
  • Snowflake algorithm is a distributed ID generation algorithm open sourced by Twitter.The core idea is to use a 64-bit long IDIt is divided into four parts: symbol bit, time stamp, working machine ID, and serial number

@Data
 @TableName("user") // Specify the database table name
 public class User {
     @TableId(type = ) // The primary key is increased by itself
     private Long id;
     private String name;
     private Integer age;
 }

Mapper interface

• Mapper interface needs to be inheritedBaseMapper<T>class, then this interface can obtain all basic CRUD methods

public interface UserMapper extends BaseMapper<User> {
     // If you need to extend the custom method, you can also add it here
 }

Service layer and Service implementation layer

MP also providesIService interface and ServiceImpl classConvenient to use encapsulated CRUD methods at the Service layer

Service layer

• extends(inherited) IServiceinterface

public interface UserService extends IService<User> {
     // More business methods can be defined here
 }

ServiceImpl layer

• inheritServiceImpl<UserMapper, User>and implementationIService<User>interface,You can directly call the encapsulated CRUD method

@Service
 public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {
     // After inheriting ServiceImpl, you already have all the CRUD methods in BaseMapper
 }

BaseMapperCommonly used CRUD methods in interfaces

• That is, the common methods in the Mapper layer have been integrated, and the atomic method of directly operating the database

Insert operation

// Insert a record
 int insert(T entity);

Query operation

// Query by ID
 T selectById(Serializable id);
 // Query a record according to entity conditions
 T selectOne(@Param() Wrapper<T> queryWrapper);

 // Query (batch query based on ID)
 List<T> selectBatchIds(@Param() Collection<? extends Serializable> idList);
 // Query all records according to entity conditions
 List<T> selectList(@Param() Wrapper<T> queryWrapper);
 // Query (according to columnMap conditions)
 List<T> selectByMap(@Param(Constants.COLUMN_MAP) Map<String, Object> columnMap);
 // Query all records according to Wrapper conditions
 List<Map<String, Object>> selectMaps(@Param() Wrapper<T> queryWrapper);
 // Query all records according to Wrapper conditions.  Note: Only the value of the first field is returned
 List<Object> selectObjs(@Param() Wrapper<T> queryWrapper);

 // According to the entity conditions, query all records (and turn pages)
 IPage<T> selectPage(IPage<T> page, @Param() Wrapper<T> queryWrapper);
 // According to Wrapper conditions, query all records (and turn pages)
 IPage<Map<String, Object>> selectMapsPage(IPage<T> page, @Param() Wrapper<T> queryWrapper);
 // According to Wrapper conditions, query the total number of records
 Integer selectCount(@Param() Wrapper<T> queryWrapper);

Delete operation

// Delete the record according to entity conditions
 int delete(@Param() Wrapper<T> wrapper);
 // Delete (batch deletion according to ID)
 int deleteBatchIds(@Param() Collection<? extends Serializable> idList);
 // Delete by ID
 int deleteById(Serializable id);
 // Delete the record according to the columnMap condition
 int deleteByMap(@Param(Constants.COLUMN_MAP) Map<String, Object> columnMap);

Update operation

// Update records according to whereWrapper conditions
 int update(@Param() T updateEntity, @Param() Wrapper<T> whereWrapper);
 // Modify according to ID
 int updateById(@Param() T entity);

IServiceCommon methods in interfaces

• That is, common methods of the service layer, business logic layer, multiple mapper methods can be combined, transactions, etc.

Insert operation (save)

// Insert a record (select field, policy insertion)
 boolean save(T entity);
 // Insert (batch)
 boolean saveBatch(Collection<T> entityList);
 // Insert (batch)
 boolean saveBatch(Collection<T> entityList, int batchSize);

There is an update, no insertion exists (saveOrUpdate)

• boolean saveOrUpdate(T entity);: Judging from the primary key identified by @TableId, if the primary key does not exist → ExecuteINSERT, If the primary key exists → executeUPDATE
• boolean saveOrUpdate(T entity, Wrapper<T> updateWrapper);: First try to perform an update operation according to updateWrapper. If the number of rows affects is 0, it will fall back tosaveOrUpdate(T entity)Logic (judging whether to update or insert based on the primary key)
• saveOrUpdateBatch(Collection<T>entityList)andsaveOrUpdateBatch(Collection<T> entityList, int batchSize):Batch processing of entities, logic andsaveOrUpdate(T)Consistent

Remove operation (Remove)

Can cooperate@TableLogicImplement logical deletion

// Delete records according to the conditions constructed by Wrapper Support complex conditions
 boolean remove(Wrapper<T> queryWrapper);
 // Delete by ID
 boolean removeById(Serializable id);
 //Delete records based on the field values ​​in columnMap. Only equivalent values ​​are supported.
 boolean removeByMap(Map<String, Object> columnMap);
 // Delete (batch deletion according to ID)
 boolean removeByIds(Collection<? extends Serializable> idList);

Update operation (Update)

Must be setSETFields,If not called.set(), SQL will lack update content to cause errors

// According to UpdateWrapper conditions, update records requires setting sqlset
 boolean update(Wrapper<T> updateWrapper);
 // Update records according to whereWrapper conditions
 boolean update(T updateEntity, Wrapper<T> whereWrapper);
 // Select Modify according to ID
 boolean updateById(T entity);
 // Batch updates according to ID
 boolean updateBatchById(Collection<T> entityList);
 // Batch updates according to ID
 boolean updateBatchById(Collection<T> entityList, int batchSize);

Query a single object (getOne)

If the entity class is configured@TableLogic, the deleted numbers will be automatically filtered during querydeleted = 0

// Query by ID
 T getById(Serializable id);

 // According to Wrapper, query a record and result set. If there are multiple, an exception will be thrown. Take one randomly and add a limit condition ("LIMIT 1")
 T getOne(Wrapper<T> queryWrapper);

 // According to Wrapper, query a record to control whether an exception is thrown when the result is not unique
 //No exception is thrown, return the first record
 T getOne(Wrapper<T> queryWrapper, boolean throwEx);

 // According to Wrapper, query a record
 Map<String, Object> getMap(Wrapper<T> queryWrapper);

 // According to Wrapper, query a record
 <V> V getObj(Wrapper<T> queryWrapper, Function<? super Object, V> mapper);

Query list (list)

// Query all
 List<T> list();

 // Query list
 List<T> list(Wrapper<T> queryWrapper);

 // Query (batch query based on ID)
 Collection<T> listByIds(Collection<? extends Serializable> idList);

 // Query (according to columnMap conditions)
 Collection<T> listByMap(Map<String, Object> columnMap);

 // Query all lists
 List<Map<String, Object>> listMaps();

 // Query list
 List<Map<String, Object>> listMaps(Wrapper<T> queryWrapper);

 // Query all records
 List<Object> listObjs();

 // Query all records
 <V> List<V> listObjs(Function<? super Object, V> mapper);

 // Query all records according to Wrapper conditions
 List<Object> listObjs(Wrapper<T> queryWrapper);

 // Query all records according to Wrapper conditions
 <V> List<V> listObjs(Wrapper<T> queryWrapper, Function<? super Object, V> mapper);

Page query

• Pagination ObjectsPagekind

  • Main attributes:
    • current:Current page number, starting from 1
    • size: Number of records displayed per page
    • searchCount: Whether to executeSELECT COUNT(*)Query the total number of records, not counting the total number is suitable for large data scenarios

  Page<User> pageParam = new Page<>(1, 10);
  LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
  (User::getAge, 25)
         .orderByDesc(User::getCreateTime);
  
  IPage<User> userPage = (pageParam, wrapper);
  

// Unconditional pagination query
 IPage<T> page(IPage<T> page);
 // Conditional pagination query
 IPage<T> page(IPage<T> page, Wrapper<T> queryWrapper);
 // Unconditional pagination query
 IPage<Map<String, Object>> pageMaps(IPage<T> page);
 // Conditional pagination query
 IPage<Map<String, Object>> pageMaps(IPage<T> page, Wrapper<T> queryWrapper);

Query records (count)

// Query the total number of records
 int count();
 // According to Wrapper conditions, query the total number of records
 int count(Wrapper<T> queryWrapper);

// Statistics the number of users older than 25 and the status is activated
 LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
 (User::getAge, 25)
        .eq(User::getStatus, 1);

 int count = (wrapper);

Conditional constructor

• Conditional constructorUsed to dynamically generate WHERE conditions in SQL, Instead of manually splicing SQL statements, MP provides two main condition constructors: QueryWrapper and LambdaQueryWrapper
  • QueryWrapper: a conditional constructor based on string names, with intuitive writing, but there is a risk of field spelling errors
  • LambdaQuertWrapped: A constructor based on Lambda expressions avoids hard-coded fields, and it is more recommended to use it.

Common methods

equal to .eq()

• Used for generationField = valueConditions

  • Using strings

  new QueryWrapper<User>().eq("age", 25); // Generate SQL: WHERE age = 25

  • Using Lambda Expressions

  new LambdaQueryWrapper<User>().eq(User::getAge, 25);
  

Not equal to .nq()

• generateField <> Valuecondition

new LambdaQueryWrapper<User>().ne(User::getStatus, 0); // SQL: WHERE status <> 0

Fuzzy query.like()

• generateField LIKE ‘%value%’condition

  new LambdaQueryWrapper<User>().like(User::getName, "Zhang"); // SQL: WHERE name LIKE '%Zhang%'

Logical connection or() and and()

• or(): General usage

new LambdaQueryWrapper<User>()
     .eq(User::getAge, 25)
     .or()
     .eq(User::getName, "Zhang San");
 // Generate SQL: WHERE age = 25 OR name = 'Zhang San'

• Nesting usage

new LambdaQueryWrapper<User>()
     .or(wrapper -> wrapper
         .eq(User::getAge, 25)
         .ne(User::getStatus, 0)
     );
 // Generate SQL: OR (age = 25 AND status <> 0)

• and(): The function is to splice multiple conditions. Use AND to connect the default condition to connect AND, and generally there is no need to explicitly call it.

Dynamic query

• Add Boolean judgment before querying conditions, and true is executed

new LambdaQueryWrapper<User>()
     .eq(age != null, User::getAge, age) // Effective when age is not null
     .like((name), User::getName, name); // Take effect when name is not empty

Pagination query

• A paging plug-in is built in MP, using Page object to encapsulate paging parameters and results

• Start the paging plugin by configuring the class in Springboot

  • Enable MyBatis-Plus paging function so that the paging method can be used directly during querying

  @Configuration
   public class MyBatisPlusConfig {
       // MybatisPlus configuration
       @Bean
       public MybatisPlusInterceptor mybatisPlusInterceptor() {
           MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
           // Added a paged interceptor to the interceptor
           // Specify the database type as MySQL
           (new PaginationInnerInterceptor());
           return interceptor;
       }
   }

  LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
  (User::getAge, 20);
  Page<User> pageParam = new Page<>(1, 10);
  IPage<User> userPage = (pageParam, wrapper);
  

Advanced Operation

Sensitive field blocking

• Some fields are sent directly to the front end and will be maliciously used by the user, resulting in security risks. Some fields (such as log fields, update_time, internal status, etc.) are not necessary for the front end. Filtering these can reduce the amount of network data and improve performance.

Solution 1: Manual assignment

• Create VO objects manually and assign verbatim values

List<StudentVo> studentVos = new ArrayList<>();
 for (Student s : students) {
     StudentVo vo = new StudentVo();
     (());
     (());
     // Other fields also need to be assigned manually
     (vo);
 }

• Advantages: controllable, no reflection performance overhead
• Disadvantages: When there are too many fields, the code will be long and the maintenance cost will be high. If the entity class changes, multiple places need to be changed.

Plan 2:

• Implementation method: Use reflection mechanism to automatically copy attributes of the same name

List<StudentVo> studentVos = new ArrayList<>();
for (Student s : students) {
    StudentVo vo = new StudentVo();
		(student, vo);
    (vo);
}

• Advantages: Concise code, quick copy
• Disadvantages: Reflection operation performance is slow, about 10 times that of manual replication, lack of flexibility

Solution 3: MapStruct Recommended

• Import MapStruct dependencies

  <dependency>
      <groupId></groupId>
      <artifactId>mapstruct</artifactId>
      <version>1.4.</version>
  </dependency>
  <dependency>
      <groupId></groupId>
      <artifactId>mapstruct-processor</artifactId>
      <version>1.4.</version>
  </dependency>
  

• Build a folder mapping specifically for mapping, write a mapping interface, and use MapStruct to automatically generate efficient mapping code during the compilation stage

@Mapper//Note that mapper annotation should not be incorrectly quoted, you should introduce the mapstruct package
 public interface StudentMapping {
     StudentMapping INSTANCE = ();
     StudentVo toStudentVo(Student student);
     List<StudentVo> toStudentVoList(List<Student> list);
 }

//How to use it is as follows:
 List<StudentVo> vos = (students);

• Advantages: High performance, the generated code is similar to manual assignment, no reflection overhead during runtime, and high flexibility

Joint table pagination query

• Assume that you need to query the main table data (such as products) and its associated subtable data (such as comments), it supports paging and conditional filtering. Use the product (Main table) and comments (Sub-table) as an example

Centralized

• The core logic is to provide JOIN query main table and sub-data, and complete all data acquisition in a single SQL.

• Code example

  <!-- Product and Comment Contract Table Query -->
   <select resultMap="productWithCommentsMap">
       SELECT
           ,
           ,
           ,
            AS comment_id,
           ,
           c.user_id
       FROM product p
       LEFT JOIN comment c ON = c.product_id
       ${} <!-- Dynamic Condition -->
   </select>
  
   <!-- Result Mapping -->
   <resultMap type="ProductVO">
       <id property="id" column="id"/>
       <result property="name" column="name"/>
       <result property="price" column="price"/>
      
       <collection property="comments" ofType="Comment">
           <id property="id" column="comment_id"/>
           <result property="content" column="content"/>
           <result property="userId" column="user_id"/>
       </collection>
   </resultMap>

  • Core points:

    • useProcess one-to-many relationship and map subtable data into sets

    • ${}

      • It is used in MPSpecial placeholders for dynamic splicing,WillQueryWrapperorLambdaQueryWrapperThe query conditions defined in theeq,like,between), convert to a legal Sql fragment, and insert it into the SQL statement of the XML mapping file
      • mapper interface definition

      // @Param() must be declared in the method parameters
       Page<ProductVO> selectPage(
           @Param("page") Page<ProductVO> page,
           @Param() Wrapper<ProductVO> wrapper // Key parameters
       );

      • Use of XML

      <select resultMap="productMap">
           SELECT * FROM product
           ${} <!-- Dynamic Insert WHERE Condition -->
       </select>

      • Core mechanism
        • ew is an alias for Wrapper, corresponding to the Mapper interface method@Param()parameter
        • When called("name", "Zhang San")When the value is automatically assigned toWHERE name= ‘Zhang San’, if WHERE name = ‘Mobile phone’Need to be spliced ​​directly, not used as precompiled parameters

Distributed (Nested Query)

• It is divided into two queries: 1. Pagination query main table () product table, 2. Subquery:Batch query of associated subtable data based on the main table id (comment table)
• Code example

<!-- Main query -->
 <select resultMap="productPageMap">
     SELECT id, name, price FROM product
     ${} <!-- Dynamic Condition -->
 </select>

 <!-- Nested subquery Check comments based on product ID -->
 <select resultType="Comment">
     SELECT id, content, user_id, product_id
     FROM comment
     WHERE product_id IN
     <foreach collection="productIds" item="id" open="(" separator="," close=")">
         #{id}
     </foreach>
 </select>

 <!-- Result Mapping -->
 <resultMap type="ProductVO">
     <id property="id" column="id"/>
     <result property="name" column="name"/>
     <result property="price" column="price"/>
     <collection
         property="comments"
         ofType="Comment"
         select="selectCommentsByProductIds" <!-- Associated subquery method -->
         column="{productIds=id}" <!-- Pass parameters -->
     />
 </resultMap>

Field fill

• When updating or adding new rows, you usually need to add some attributes such as creation time, update time, operator, MP provides a method to automatically process fill

Code-level implementation

1. Mark the fields that need to be filled in the entity class

   1. use@TableFiledPrimary keyfillProperties specify fill policy

   @Data
    public class Orders implements Serializable {
        private static final long serialVersionUID = -29854105041572661L;
   
        @TableId(type = IdType.ASSIGN_ID)
        private Long orderId;
   
        private String orderNum;
   
        private Integer userId;
   
        @TableField(fill = )
        private Date orderTime;
        /**
         * Creator
         */
        @TableField(fill = )
        private String createBy;
        /**
         * Creation time
         */
        @TableField(fill = )
        private Date createTime;
        /**
         * Updater
         */
        @TableField(fill = FieldFill.INSERT_UPDATE)
        private String updateBy;
        /**
         * Update time
         */
        @TableField(fill = FieldFill.INSERT_UPDATE)
        private Date updateTime;
        /**
         * Remark
         */
        private String remark;
   
    }

   b. Implement the MetaObjectHandler interface

   @Slf4j
    @Component
    public class FieldHandler implements MetaObjectHandler {
        // Automatically fill in createTime and createBy fields when inserting data
        @Override
        public void insertFill(MetaObject metaObject) {
   
            ("start insert fill...");
            // Set the value of the createTime field to the current time
            ("createTime", new Date(), metaObject);
   
            // Set the value of the createBy field to admin
            ("createBy", "admin", metaObject);
   
            // Set the value of the orderTime field to the current time
            ("orderTime", new Date(), metaObject);
   
            // Set the value of the updateTime field to the current time
            ("updateTime", new Date(), metaObject);
   
            // Set the value of the updateBy field to admin
            ("updateBy", "admin", metaObject);
        }
   
        // Automatically fill in updateTime and updateBy fields when updating data
        @Override
        public void updateFill(MetaObject metaObject) {
   
            ("start update fill...");
            // Set the value of the updateTime field to the current time
            ("updateTime", new Date(), metaObject);
   
            // Set the value of the updateBy field to admin
            ("updateBy", "admin", metaObject);
        }
    }

Logical deletion

• The data is still retained in the database after being "deleted", filtered by marking is_deleted field, and used in entity classes@TableLogicannotation
• Avoid permanent loss of data caused by mistaken deletion, and prevent the associated subtable data from being invalid after deleting the main table data (if the user deletes it, historical orders need to be retained)

public class User {
     @TableLogic
     private Integer deleted; // 1 means deleted, 0 means not deleted
 }

• When deletion is performed, the update operation is actually being performed.

(1);
 //Practical execution
  UPDATE user SET is_deleted = 1 WHERE id = 1;

• Query operation is filtered by is_deteted

List<User> list = (null);
 //Practical execution
 SELECT * FROM user WHERE is_deleted = 0;

Optimistic lock

• Pass the version number (such as Vesion field) or timestamp, and check whether there is no conflict when submitting the data. If the conflict is a conflict, the operation will be rejected.Prompt to try again or roll back, operate first, then check for conflicts when submitting, and use for low concurrency

MP implements optimistic locking

• Add version number in entity class and use @Version annotation token

public class Product {
     private Long id;
     private String name;
    
     @Version // Version number field (MP automatic management)
     private Integer version;
 }

• Add optimistic lock plugin to configuration class

@EnableTransactionManagement
 public class MPConfig {

     // Create MybatisPlusInterceptor object
     @Bean
     public MybatisPlusInterceptor mybatisPlusInterceptor() {

         MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();

         // Add a paging plugin
         (new PaginationInnerInterceptor());
         // Add optimistic lock plugin
         (new OptimisticLockerInnerInterceptor());
         return interceptor;
     }
 }

When there is a multi-threaded modification operation, MP will check whether the version version is consistent through SQL statements., If the version is 1 when querying the product, MP will execute when the modification is submitted.UPDATE product SET name='New name', version=2 WHERE id=1 AND version=1;, If another thread is modified first, the version becomes 2 and your update will be invalid