Four ways to update Null fields in Mybatis-plus

This article introduces the reasons and solutions for the [Mybatis-plus] updateById() method that cannot update the field to null.

1. Problem description

During the daily project development process, the updateById() method of Mybatis-plus is often used to quickly update the fields that were not null in the received moral parameters or query results to null, and the field can be null in the database. At this time, using updateById() cannot achieve this operation and will not report an error, but the corresponding field is not updated to null.

2. Causes of the problem

There are three strategies for Mybatis-plus' field strategy (FieldStrategy):

IGNORED: 0 Ignored

NOT_NULL: 1 Non NULL, default policy

NOT_EMPTY:2 Non-empty

The default update strategy is NOT_NULL: non-NULL; that is, when the data is updated through the interface, it will not be updated into the database when the data is NULL value.

3. Solution

1. Write sql directly in the sql:

 update table A set Fieldsa = null where Fieldsb = condition

2. Set the global FieldStrategy

Modify global policies in configuration files

properties file format:

-strategy=ignored

#yml file format:mybatis-plus:
  global-config:
      #Field Strategy 0: "Ignore judgment", 1: "Non-NULL judgment", 2: "Non-null judgment"    field-strategy: 0

This is done by global configuration, and the judgment of all fields will be ignored when updating. However, if some fields do not pass values, they will be directly updated to null, which may affect the accuracy of other business data. This method is not recommended.

3. Set field-strategy for specified fields separately

According to the specific situation, adjust the verification annotation in the fields that need to be updated, such as verification not empty:

@TableField(strategy=FieldStrategy.NOT_EMPTY)

In this way, we only need to set the ignorance policy on the fields that need to be updated to null, as follows:

@TableField(updateStrategy = )
private String updateBy;

After setting up, you can use the updateById method in mybatis-plus to successfully update the field to null when updating. However, this has certain disadvantages, that is, when there are many fields that need to be processed like this, you must add such annotations to the corresponding fields.

4. Use the update method to update with the UpdateWrapper method

User user=().eq(User::getUserId,userId).one();
if(user!=null){
    (user,new UpdateWrapper<User>().lambda()
               .set(User::getUserName,null)
               .eq(User::getUserId,()));
}

This method will not affect other methods, and does not require modifying the global configuration, nor does it require individual annotations on the fields. You only need to set the field to be modified to null when using it to update successfully. Recommended method 4.

Expansion: Mybatis-Plus update time field does not take effect

1. Background

Mybatis-Plus only enhances and does not change based on Mybatis, mainly to simplify development. The background of the project is Mybatis-Plus, but a problem was found during the running project. When using Mybatis-Plus to update data, the update field did not update the corresponding time as we expected.
The statement that creates this field is as follows:

 `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update time'

2. Cause analysis

Use Mybatis-Plus update method to update the corresponding data entity, such as the following method:

()
// ....... Wait for methods

After these methods are used, it is found that no updateTime field data is updated.

Cause analysis:

When selectByld takes out old data from the database, then modify the field you want to modify, and then calls updateById, you will find that the updateTime field will not be updated. This is because selectByld can take out the old value of updateTime. When filling the policy path, it will determine that the attribute already has a value and will not be automatically filled, so updateTime will not be automatically updated. Moreover, the official does not currently provide a method of forced updates directly.

Check the fillStrategy method source code, we can also see that only if the attribute is not filled with values, the set method will be executed.

3. Solution

Solution 1: Set the update time during update

	TDZopenOrder openOrder = (orderId);
	(washOrderId);
	(());
	()

Solution 2: Set the update time on the entity

	public class TDZopenOrder {
	    @ApiModelProperty("Update time")
	    @TableField(update = "now()")
	    private LocalDateTime updateTime;
	}

This is the end of this article about how Mybatis-plus updates Null fields. For more related contents of Mybatis-plus updates Null fields, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!