New MyBatis Plus | add deletion operation

preface

C: In the last article, Teacher Zha gave you a quick introduction to MP. I wonder if you have mastered the steps of MP and are impressed by its power? This article, Teacher Zha will continue to take you to learn MP on the basis of the previous Demo, master the common CUD operation API in MP.

Teacher Zha said: CUD do not know what it means? In our back end circles, there’s a word that gets around a lot: “CRUD.” Some students even admitted that they were doing THE “CRUD” of XXX in a certain project during the interview.

As for its meaning, you can find it by using Youdao Dictionary. CRUD stands for Create, Read (Retrieve), Update, Delete, commonly known as “add, Delete, change, search”.

Because we do the back end, it is unavoidable to operate the database, and the basic operation of the database is these four categories. In addition, in the industry, it has once become a relatively “self-deprecating” noun in our circle. Some young partners often call themselves “CRUD engineers “, aiming to express the meaning that what they usually do is very simple.

CUD is naturally removed from CRUD by Teacher Cha, which means the operation of adding, deleting and modifying data.

The insert

Do you think Mr. Cha has to do a lot of pre-emptive work? You’re wrong. In MP, none of this is necessary. Be direct.

In UserMapper, the last Demo project, because it already inherits the BaseMapper interface, you don’t even need to write the usermapper.xml file to get a generic CRUD API.

In the BaseMapper interface, there is only one API for insert operations.

// Other apis are omitted
public interface BaseMapper<T> extends Mapper<T> {
    
    /** * Insert a record **@paramEntity Entity object *@returnAffects the number of rows */
    int insert(T entity);

}
Copy the code

Insert 1 record

Next, we are ready to test the insert operation API by copying and pasting a unit test class specifically designed to test CUD operations from the previous Demo project.

Test code:

Teacher Cha prepared a test data in advance.

The name	age	email
Charles	18	[email protected]

import org.junit.Assert;

@SpringBootTest
class MybatisPlusCUDTests {
    
    @Autowired
    private UserMapper userMapper;

    @Test
    public void testInsert(a){
        // Create a user object
        User user = new User();
        user.setName("Charles");
        user.setAge(18);
        user.setEmail("[email protected]");
        
        // Call the insert operation API
        int rows = userMapper.insert(user);
        Assert.assertEquals(1, rows);
        
        // MP automatically backfills the generated primary key by defaultSystem.out.println(user.getId()); }}Copy the code

Console output:

==>  Preparing: INSERT INTO user ( id, name, age, email ) VALUES(? ,? ,? ,?)==> Parameters: 1352882704631181313(Long), Charles(String), 18(Integer), charles7c@ 126.com(String)
< ==    Updates: 1
Copy the code

1352882704631181313
Copy the code

ID Generation Policy

The user id value is a long number. If you do not look carefully, you will think it is the id number. In fact, this is the default primary key generation strategy for MP: generate POTS with distributed unique ids.

Teacher Zha said: the so-called distributed unique ID, referred to as distributed ID. We all know that we need to define a unique ID for each item in a database. Databases such as MySQL provide primary key increment to help us automatically generate 1, 2, 3… This simple unique ID. However, as the system business becomes more and more complex, the database starts to be divided into different databases and tables. This traditional ID generation strategy is highly likely to have duplicate ids in the distributed case, so the concept of distributed ID is born. Common distributed ID solutions include Redis generating ids, UUID, Snowflake, etc.

Since MP 3.3.0, the default primary key generation policy is the combination of the snowflake algorithm and UUID(not including the hyphen).

UUID: UUID is a concept developed by the International Organization for Standardization (ISO). It is short for Universally Unique Identifier. Regarded as a unique identifier in all Spaces and time, a UUID is usually represented by 32 hexadecimal digits, which are displayed in groups of five characters separated by hyphens (-). For example, 6DB55EC5-FF6F-478A-911D-313DE67ed563.

Snowflake: Snowflake is Twitter’s open source distributed ID generation algorithm that results in a long ID.

If our requirements do not require distributed ids, we can replace them with other primary key generation strategies in MP. We can look at MP’s ID type enumeration class source code to see what primary key generation strategies it has.

/** * Generates the ID type enumeration class **@author hubin
 * @sinceThe 2015-11-10 * /
@Getter
public enum IdType {
    /** * database ID increment * This type please ensure that the database support and set primary key increment otherwise invalid */
    AUTO(0),
    
    /** * This type is an unset primary key */
    NONE(1),
    
    /** * The user enters the ID * this type can be filled by registering the autofill plug-in */
    INPUT(2),

    /* Note: the following types are automatically populated only if the ID of the inserted object is empty. * /
    /** ** * allocate ID (primary key type is numeric or string) by snowflake algorithm, *@since3.3.0 (added in version 3.3.0) */
    ASSIGN_ID(3),
    
    /** * Allocate ID by UUID (primary key type is string) */
    ASSIGN_UUID(4),
    
    /* --------- is out of date and --------- */ is not recommended
    /** * Since version 3.3.0, ASSIGN_ID can be used instead of */
    @Deprecated
    ID_WORKER(3),
    /** * Since version 3.3.0, ASSIGN_ID can be used instead of */
    @Deprecated
    ID_WORKER_STR(3),
    /** * Since version 3.3.0, ASSIGN_UUID can be used instead of */
    @Deprecated
    UUID(4);

    private final int key;

    IdType(int key) {
        this.key = key; }}Copy the code

When you want to change the default primary key generation strategy, add the @TableID annotation to the primary key attribute of the corresponding entity class.

@Data
public class User {
    
    // @tableId is an annotation for the primary key attribute
    // value: specifies the column name of the data table. This value is used if the entity class attribute and the column name of the data table are inconsistent
	// type: primary key generation policy type
    @TableId(type = IdType.AUTO)
    private Long id;
    private String name;
    private Integer age;
    private String email;

}
Copy the code

Of course, if you want to change the primary key generation policy for each entity, the best way to do this is directly in the global configuration of application.yml.

# MyBatis Plus configuration
mybatis-plus:
  # global configuration
  global-config:
    db-config:
      # Primary key generation strategy
      id-type: auto
Copy the code

If you choose global configuration, you do not need to configure the first configuration.

After changing the primary key generation policy, do not forget to reset the current user table data with Truncate before Insert. Otherwise, when testing inserts again, the database primary key increment sequence starts from the current maximum ID.

Once the reset is complete, let’s test the insert again.

Console output:

==>  Preparing: INSERT INTO user ( name, age, email ) VALUES(? ,? ,?)==> Parameters: Charles(String), 18(Integer), charles7c@ 126.com(String)
< ==    Updates: 1
Copy the code

6
Copy the code

Obviously, this time, MP is not assigning us an ID, but an ID generated by the primary key increment performed by the database.

Modify the operating

There are two apis for modifying operations in the BaseMapper interface, but we’ll cover only one in this article and the other one after we learn about conditional constructors in the next article.

// Other apis are omitted
public interface BaseMapper<T> extends Mapper<T> {
    
    /** * Change ** according to ID@paramEntity Entity object *@returnAffects the number of rows */
    int updateById(@Param(Constants.ENTITY) T entity);

    /** * Update the record ** according to the whereEntity condition@paramEntity Entity object (set conditional value, which can be null) *@paramThe updateWrapper entity object encapsulates the action class (which can be null, where the entity is used to generate the WHERE statement) *@returnAffects the number of rows */
    int update(@Param(Constants.ENTITY) T entity, @Param(Constants.WRAPPER) Wrapper<T> updateWrapper);
    
}
Copy the code

Modify by ID

In order to test modify operation API, Teacher Zha also prepared a test data in advance.

A primary key	The name	age	email
5	Billie	18	[email protected]

Test code:

@SpringBootTest
class MybatisPlusCUDTests {
    
    @Autowired
    private UserMapper userMapper;

    @Test
    void testUpdateById(a){
        // Create a user object
        User user = new User();
        user.setId(5L);
        user.setAge(18);
        
        // Execute the modify operation API
        int rows = userMapper.updateById(user);
        Assert.assertEquals(1, rows); }}Copy the code

Console output:

==>  Preparing: UPDATE user SET age=? WHERE id=?
==> Parameters: 18(Integer), 5(Long)
< ==    Updates: 1
Copy the code

Automatic filling

In the process of project development, we often need to do some data filling before the final storage of data, such as audit information: creator, creation time, update person, update time and so on.

The filling of this data is repetitive, tedious, and sometimes forgettable. MP provides automatic filling to end this problem.

Next, we will use the creation time and update time of the auto-fill entity class as examples to demonstrate the auto-fill function of MP.

Step 1: We need to make some structural adjustments to the entity classes and database tables first.

Add columns create_time and update_time to the user table
ALTER TABLE `mybatisplus_demodb`.`user` 
ADD COLUMN `create_time` datetime(0) NULL COMMENT 'Creation time' AFTER `email`,
ADD COLUMN `update_time` datetime(0) NULL COMMENT 'Update Time' AFTER `create_time`;
Copy the code

@Data
public class User {
    
    private Long id;
    private String name;
    private Integer age;
    private String email;
    // Add the corresponding entity attributes: createTime and updateTime
    private LocalDateTime createTime;
    private LocalDateTime updateTime;

}
Copy the code

Step 2: Add the @TableField annotation to the User class to specify the auto-fill type for the property.

@Data
public class User {
    
    private Long id;
    private String name;
    private Integer age;
    private String email;

    // @tableField is an annotation used to annotate common attributes
    // value: specifies the column name of the data table. This value is used if the entity class attribute and the column name of the data table are inconsistent
    // fill: automatic fill type
    // INSERT: automatic filling during insertion
    // UPDATE: Automatically fills in updates
    // INSERT_UPDATE: automatically fills when inserting or updating
    @TableField(fill = FieldFill.INSERT)
    private LocalDateTime createTime;
    @TableField(fill = FieldFill.INSERT_UPDATE)
    private LocalDateTime updateTime;

}
Copy the code

Step 3: Create an automatic audit handler that implements the meta-object handler interface.

com.baomidou.mybatisplus.core.handlers.MetaObjectHandler

/** * Automatic audit through MP auto-fill function */
@Component
public class AutoAuditHandler implements MetaObjectHandler {

    @Override
    public void insertFill(MetaObject metaObject) {
        this.strictInsertFill(metaObject, "createTime", LocalDateTime.class, LocalDateTime.now()); // Version 3.3.0(recommended)
        this.strictInsertFill(metaObject, "updateTime", LocalDateTime.class, LocalDateTime.now()); // Version 3.3.0(recommended)
    }

    @Override
    public void updateFill(MetaObject metaObject) {
        this.setFieldValByName("updateTime", LocalDateTime.now(), metaObject); }}Copy the code

Once we’ve done that, let’s test the change again.

Console output:

==>  Preparing: UPDATE user SET age=? , update_time=? WHERE id=?
==> Parameters: 18(Integer), 2021- 01- 23T16:51:18.413(LocalDateTime), 5(Long)
< ==    Updates: 1
Copy the code

Obviously, there is an update time change in the SQL being executed, and the value passed is the current time.

You can also test the insert API to see if it automatically fills in the creation time and update time data when adding data.

Delete operation

In BaseMapper interface, there are a total of four apis for deleting operations. We will introduce the first three in this article and the last one in the next article.

// Other apis are omitted
public interface BaseMapper<T> extends Mapper<T> {
    /** * delete ** based on ID@paramId Primary key ID *@returnAffects the number of rows */
    int deleteById(Serializable id);
    
    /** * delete (batch delete by ID) **@paramIdList List of primary key ids (cannot be null or empty) *@returnAffects the number of rows */
    int deleteBatchIds(@Param(Constants.COLLECTION) Collection<? extends Serializable> idList);

    /** * Drop the record ** according to the columnMap condition@paramColumnMap Table field Map object *@returnAffects the number of rows */
    int deleteByMap(@Param(Constants.COLUMN_MAP) Map<String, Object> columnMap);

    /** * Delete record ** according to entity condition@paramThe queryWrapper entity object encapsulates the action class (which can be null, where the entity is used to generate the WHERE statement) *@returnAffects the number of rows */
    int delete(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);
    
}
Copy the code

Delete by ID

Test code:

@SpringBootTest
class MybatisPlusCUDTests {
    
    @Autowired
    private UserMapper userMapper;
    
    @Test
    void testDeleteById(a) {
        // Run the delete operation API to delete user data 1
        int rows = userMapper.deleteById(1L);
        Assert.assertEquals(1, rows); }}Copy the code

Console output:

==>  Preparing: DELETE FROM user WHERE id=?
==> Parameters: 1(Long)
< ==    Updates: 1
Copy the code

Batch ID Deletion

Test code:

@SpringBootTest
class MybatisPlusCUDTests {
    
    @Autowired
    private UserMapper userMapper;
    
    @Test
    void testDeleteBatchIds(a) {
        // Delete user data 2 and 3
        List<Integer> ids = Arrays.asList(2.3);
        int rows = userMapper.deleteBatchIds(ids);
        Assert.assertEquals(2, rows); }}Copy the code

Console output:

==>  Preparing: DELETE FROM user WHERE id IN(? ,?)==> Parameters: 2(Integer), 3(Integer)
< ==    Updates: 2
Copy the code

Simple conditional deletion

Test code:

@SpringBootTest
class MybatisPlusDemoApplication {
    
    @Autowired
    private UserMapper userMapper;
    
    @Test
    void testDeleteByMap(a) {
        // Delete user data named Sandy
        // Map set key: represents database column names, not entity class attribute names
        Map<String, Object> columnMap = new HashMap<>();
        columnMap.put("name"."Sandy");
        int rows = userMapper.deleteByMap(columnMap);
        Assert.assertEquals(1, rows); }}Copy the code

Console output:

==>  Preparing: DELETE FROM user WHERE name = ?
==> Parameters: Sandy(String)
< ==    Updates: 1
Copy the code

Logic to delete

In order to preserve user data during the development of the project, we chose logical deletion over physical deletion when deleting user data.

• Physical delete: Deletes the corresponding data from the database. That is, delete SQL is used.

• Logical deletion: False deletion. The column representing whether the data is deleted is changed to “deleted status value”, that is, UPDATE SQL is used.

Next, let’s also implement the logical delete function.

Step 1: We need to make some structural adjustments to the entity classes and database tables first.

Add is_delete column to user table
ALTER TABLE `mybatisplus_demodb`.`user` 
ADD COLUMN `is_delete` int(2) NULL COMMENT 'Deleted or not' AFTER `update_time`;
Copy the code

@Data
public class User {
    
    private Long id;
    private String name;
    private Integer age;
    private String email;
    @TableField(fill = FieldFill.INSERT)
    private LocalDateTime createTime;
    @TableField(fill = FieldFill.INSERT_UPDATE)
    private LocalDateTime updateTime;
   
    // Add the corresponding entity attribute: isDelete
    // Specify auto-fill when inserting data for logical delete properties, and adjust the data fill handler
    @TableField(fill = FieldFill.INSERT)
    private Integer isDelete;
    
}
Copy the code

/** * Automatic audit through MP auto-fill function */
@Component
public class AutoAuditHandler implements MetaObjectHandler {

    @Override
    public void insertFill(MetaObject metaObject) {
        this.strictInsertFill(metaObject, "createTime", LocalDateTime.class, LocalDateTime.now());
        this.strictInsertFill(metaObject, "updateTime", LocalDateTime.class, LocalDateTime.now()); 
        // When data is inserted, the logical delete property is automatically filled with values
        this.strictInsertFill(metaObject, "isDelete", Integer.class, 0);
    }

    @Override
    public void updateFill(MetaObject metaObject) {
        this.setFieldValByName("updateTime", LocalDateTime.now(), metaObject); }}Copy the code

Step 2: In application.yml, globally configure the default and delete values for logical deletion.

This step can also be implemented by adding @tablelogic annotations above the logical delete attribute, but global configuration is recommended.

mybatis-plus:
  global-config:
    db-config:
      # logical delete attribute
      logic-delete-field: isDelete
      The status value is not deleted
      logic-not-delete-value: 0
      # delete status value
      logic-delete-value: 1
Copy the code

After the configuration, let’s test the delete by ID operation.

Test code:

@SpringBootTest
class MybatisPlusCUDTests {

    @Autowired
    private UserMapper userMapper;
    
    @Test
    void testDeleteById(a) {
        // Run the delete operation API to delete user data 5
        int rows = userMapper.deleteById(5L);
        // int rows = userMapper.deleteById(1L);
        Assert.assertEquals(1, rows); }}Copy the code

Console output:

==>  Preparing: UPDATE user SET is_delete=1 WHERE id=? AND is_delete=0
==> Parameters: 5(Long)
< ==    Updates: 0
Copy the code

java.lang.AssertionError: expected:<1> but was:<0>
Expected :1
Actual   :0
Copy the code

This time the unit test was executed, and an error was reported! On closer inspection, it turns out that the assertion tells us that the actual result is different from the expected result.

What causes failure? This is because the logical drop column was added to the database table, but it has not been set yet.

SQL > alter table alter table alter table alter table alter table alter table alter table alter table alter table alter table alter table

Once you know why, manually set the logical deletion columns of the table to 0.

Run the delete test again.

Console output:

==>  Preparing: UPDATE user SET is_delete=1 WHERE id=? AND is_delete=0
==> Parameters: 5(Long)
< ==    Updates: 1
Copy the code

This time the error is no longer reported, and it is obvious that the SQL being executed is now an UPDATE operation instead of a DELETE operation before the logical DELETE configuration.

After the logical delete is configured, some of the original operations, such as the query operation, will be automatically executed with the condition where is_delete = 0.

reference

[1]MyBatis Plus 官网. 指南[EB/OL]. baomidou.com/guide/. 2021-01-18

Afterword.

C: We didn’t worry about CUD when we learned MyBatis, and it certainly doesn’t exist in MP now. And MP also provides us with so many practical functions.

In the next article we will learn about more complex query operations, but MP is relatively simple, so stay tuned.

Teacher Zha said: For the learning of technology, teacher Zha has always followed the following steps: With a simple demo to let it run first, and then learn it the most commonly used API and configuration can let yourself up, finally, on the basis of proficiency in the spare time reading the source to try to make myself to be able to see clearly its running mechanism, part of the cause of the problem, at the same time, draw lessons from these technology to enhance your own code level.

So in the teacher’s article, the early basic are small white, only interspersed with a very small amount of source research. Of course, such as the small white update, you still like, the late will not regularly dedicated to part of the technology of the source code analysis.