MyBatis-Flex Usage Summary of Logical Deletion

1. @Column

MyBatis-Flex provides @Column for more configuration of fields. The following is the code definition of @Column:

public @interface Column {

    /**
      * Field name
      */
    String value() default "";

    /**
      * Whether this field is ignored may be just a business field, not a database corresponding field
      */
    boolean ignore() default false;

    /**
      * When insert is default, this value will be spliced ​​directly into sql without setting the parameters.
      */
    String onInsertValue() default "";

    /**
      * Automatically assign value when update, this value will be spliced ​​directly into sql without setting parameters
      */
    String onUpdateValue() default "";

    /**
      * Whether it is a large field, the large field APT will not be generated in DEFAULT_COLUMNS
      */
    boolean isLarge() default false;

    /**
      * Whether it is a logical delete field, only 1 logical delete field can exist in a table.
      * When the fields deleted by logic are deleted, they will be set to 1 and the normal state is 0.
      */
    boolean isLogicDelete() default false;

    /**
      * Is it an optimistic lock field? If it is an optimistic lock field, the current version number will be detected when the data is updated. If the update is successful, the current version number will be set +1
      * fields that can only be used for numeric values
      */
    boolean version() default false;

    /**
      *Configured jdbcType
      */
    JdbcType jdbcType() default ;

    /**
      * Custom TypeHandler
      */
    Class<? extends TypeHandler> typeHandler() default ;

}

two,Logical deletion related scenarios

Setting field logic delete via @Column

Logical deletion refers to when deleting data, it does not really delete it, but instead modifying the status field (status) corresponding to the columns in the table, and actually does not delete the target data.

When designing the field of the table, we can use a column to identify the "delete state" of the data. In mybatis-flex, the value of the normal state is 0 and the value of the deleted value is 1 (this value can be modified by setting FlexGlobalConfig).

@Column(isLogicDelete = true)
private int isDeleted;

/*
 The corresponding database table structure is:
 is_deleted tinyint(1) default 0 numm comment 'Whether it has been logically deleted 0-No 1-Yes';
 */

When the data of "tb_account" is deleted (is_delete = 1), when we search for data through selectOneById of MyBatis-Flex, we cannot query the data. The reason isselectOneByIdWill be added automaticallyis_delete = 0Conditions, the executed sql is as follows:

SELECT * FROM tb_account where id = ? and is_delete = 0

Not only the selectOneById method will be addedis_delete = 0Conditions, the following methods of BaseMapper will also add this condition:

• selectOneBy**
• selectListBy**
• selectCountBy**
• paginate

At the same time, such as Left Join or subquery, etc., ifThe subtable also has logical deletion fields, then the subtable will also add corresponding logical deletion conditions, such as:

QueryWrapper query1 = ()
    .select()
    .from(ACCOUNT)
    .leftJoin(ARTICLE).as("a").on((ARTICLE.ACCOUNT_ID))
    .where((10));
/*
 The SQL it executes is as follows:
 SELECT *
 FROM `tb_account`
          LEFT JOIN `tb_article` AS `a`
          ON `a`.`is_delete` = 0 and `tb_account`.`id` = `a`.`account_id`
 WHERE `tb_account`.`age` >= 10
   AND `tb_account`.`is_delete` = 0
 */

In the left join on condition automatically add a.is_delete = 0, and in the where condition add tb_account.is_delete = 0.

On the basis of logical deletion, update time automatically update_time

(1) Data filling refers to the fact that when Entity data is inserted or updated, some default data settings will be made for the field. This is very useful, such as when an entity is inserted, it will set the time for some data insertion, the user id of the data insertion, and set the current tenant information in a multi-tenant scenario, etc.

MyBatis-Flex provides two ways to help developers fill data.

• Operation is performed through the onInsert and onUpdate configurations annotated by @Table.
• Operation is performed through the onInsertValue and onUpdateValue configuration annotated by @Column  .

What is the difference between @Table's annotation and @Column's annotation?

• @Table annotated onInsert mainly sets data at the Java application level.
• @Column annotated onInsertValue is to set data at the database level.

First, in the model entity class of the backend project, the object properties of the class are mapped to the default value settings of the create_time and update_time in the database table through annotation configuration.

@Column(onInsertValue = "now()")
private Date createTime;

@Column(onUpdateValue = "now()", onInsertValue = "now()")
private Date updateTime;

@Column(isLogicDelete = true)
private int isDeleted;

/*
 The corresponding database table structure is:
 create_time timestamp null comment 'create time';
 update_time timestamp null comment 'update time';
 is_deleted tinyint(1) default 0 numm comment 'Whether it has been logically deleted 0-No 1-Yes';
 */

Where, onInsertValue represents the default value set when the data is updated. The content of the onInsertValue configuration "now()" will directly participate in the SQL assignment stitching. And onUpdateValue represents the default value set when the data is updated. The content of the onUpdateValue configuration "now()" will directly participate in the SQL assignment stitching.

In insert, the content configured by onInsertValue will directly participate in SQL stitching, rather than setting through JDBC Statement parameter. Developers need to pay attention to the content of onInsertValue, otherwise SQL errors may occur.

(2) UpdateChain is a tool class that encapsulates UpdateEntity, UpdateWrapper, etc., which is convenient for users to use for chain operations.

Suppose we want to update the userName of the Account to "Zhang San", and add 1 on the previous basis to update the code as follows:

@Test
public void testUpdateChain() {
    ()
        .set(Account::getUserName, "Zhang San")
        .setRaw(Account::getAge, "age + 1")
        .where(Account::getId).eq(1)
        .update();
}

/*
 When the above method is called, the SQL executed internally by MyBatis-Flex is as follows:
 sql
 UPDATE `tb_account` SET `user_name` = 'Zhang San' , `age` = age + 1
 WHERE `id` = 1
 */

After testing, it was found that (idsList) does not seem to automatically update the time update_time, I don't know why. Isn't it that when the deleteBatchById() method is called, MyBatis-Flex will dynamically generate an update statement based on entity class information and annotation configuration instead of a delete statement? ? Logically speaking, shouldn't the update statement trigger the automatic update of the update_time field? (I didn't understand.)

Therefore, the basic deletedById() method can be replaced by the chain operation method provided by MyBatis-Flex.

// (idsList);
(dataMapper)
	.set(DataModel::getIsDeleted, 1)
	.where(DataModel::getIsDeleted, 0)
	.and(DataModel::getId).in(idsList)
	.update();

In this way, the MyBatis-Flex framework will follow the process of update statements, and the automatic update_time update time field can be completed in the database table.

Skip logical deletion processing

In some scenarios, such as building a recycling bin function, it is necessary to skip the relevant conditions for logical deletion automatically added by MyBatis-Flex when we execute queries, updates or delete data.

At this time, we can use the () method to handle it. This method is very useful when all data needs to be operated, such as batch export of data, data recovery and other scenarios.

LogicDeleteManager is a manager for handling logical deletion. The function of the execWithoutLogicDelete() method is to ignore the rules of logical deletion when performing certain operations. This means that when using this method to perform query, insert, update or delete operations, the system does not consider the logical deletion flag, that is, all data, including those marked as deleted.

for example,

(()->
        (1)
        );

In the above code, accountMapper will directly physically delete the Account data, ignoring the logical deletion field configuration.

The code is as follows:

(()->{
	// Write logic here	(dataMapper)
	.set(DataModel::getIsDeleted, 0)
	.where(DataModel::getIsDeleted, 1)
	.and(DataModel::getId).in(idsList)
	.update();
	});

In the above code, (dataMapper) creates an update chain object of the datamapper object dataMapper. .set(DataModel::getIsDeleted, 0) Set the isDeleted field value of DataModel to 0, indicating that the status is not deleted. .where(DataModel::getIsDeleted, 1) One of the specified update conditions is that the isDeleted field value is 1, that is, data that has been logically deleted. .and(DataModel::getId).in(idsList) Add an extra condition: The value of the id field must be in the idsList list. .update() performs an update operation.

The purpose of this code is to restore the data with the specified id from the logical deletion state to the undelete state when the logical deletion is ignored. Specifically, only those data whose isDeleted field has a value of 1 and whose id is in idsList will be updated. After update, the isDeleted field value of these data will be set to 0, indicating that the data is no longer considered deleted.

This is the end of this article about the usage of MyBatis-Flex logical deletion. For more related content about MyBatis-Flex logical deletion, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!