Requirements:

Requirement: update a Date type field in the SQL Server library table to null
Environmental Science:

• SQL Server Version:

Microsoft SQL Server 2016 (SP2-CU17) (KB5001092) - 13.0.5888.11 (X64) 
	Mar 19 2021 19:41:38 
	Copyright (c) Microsoft Corporation
	Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)

• Mybatis plus version:

<dependency>
     <groupId>com.baomidou</groupId>
     <artifactId>mybatis-plus-boot-starter</artifactId>
     <version>3.2.0</version>
 </dependency>

Official website documents:

• Mybatis plus official document about setting null or empty character Q & A .
  

choice:

• The three solutions on the official website have their own advantages and disadvantages
  Scheme I:
  Global settings may affect other functions. This scheme is not considered, with poor compatibility and high risk. Imagine that if you change the global settings and the project manager goes online without code review (especially for small teams, everyone has administrator rights), many data losses or exceptions occur during operation. After troubleshooting, it is found that you set the mybatis plus global empty processing policy to enabled. The next step is data recovery. To be more serious, the whole team should restore data together. For projects that do not take security measures such as database backup and system operation log recording, it is hell.
  Scheme II:
  The limit granularity is reduced to the field, and whether to use it depends on the situation. The maintainability is poor. For independent modules, the modified configuration will not affect the existing functions and will not be referenced by other modules in the future. In this case, it can be used, but this relatively hidden configuration may introduce problems to later development and maintenance.
  Scheme III:
  The optimal solution only affects this operation.

realization

• Scheme 2 implementation (invalid)

@Data
@TableName("NOTICE_RECEIPT")
public class NoticeReceipt {
	@TableId(type = IdType.AUTO)
	private Integer id;
	/** Warehouse code */
	private String stockNumber;
	/**  Validity period */
	@TableField(updateStrategy = FieldStrategy.IGNORED)
	private Date validityPeriod;
	/** Delete, 0: No, 1: Yes */
    @TableLogic(value = "0", delval = "1")
    private Integer isDelete;
}

@Service
public class NoticeReceiptServiceImpl extends ServiceImpl<NoticeReceiptMapper, NoticeReceipt> implements NoticeReceiptService {
 	@Override
    public R update(NoticeReceiptVO noticeReceiptVO) {
    	NoticeReceipt noticeReceipt = new NoticeReceipt();
    	noticeReceipt.setId(23);
    	noticeReceipt.setStockNumber("SN20211001");
    	noticeReceipt.setValidityPeriod(null);
		updateById(noticeReceipt);
	}
}

MyBatis Log:

UPDATE NOTICE_RECEIPT SET stock_number='SN20211001', validity_period=null WHERE id=23 AND is_delete=0;

Exception:

org.apache.ibatis.exceptions.PersistenceException: 
### Error updating database. Cause: com.microsoft.sqlserver.jdbc.sqlserverexception: implicit conversion from data type varbinary to date is not allowed. Use the CONVERT function to run this query.

• Scheme 3 Implementation (invalid)
  Modify update method

@Override
public R update(NoticeReceiptVO noticeReceiptVO) {
	NoticeReceipt noticeReceipt = new NoticeReceipt();
	noticeReceipt.setId(23);
	noticeReceipt.setStockNumber("SN20211001");
	noticeReceipt.setValidityPeriod(null);
	baseMapper.update(
    noticeReceipt,
    Wrappers.<NoticeReceipt>lambdaUpdate()
            .set(NoticeReceipt::getValidityPeriod, noticeReceipt.getValidityPeriod())
            .eq(NoticeReceipt::getId, noticeReceipt.getId())
   );
}

MyBatis Log:

UPDATE NOTICE_RECEIPT SET stock_number='SN20211001', validity_period=null, validity_period=null  WHERE id=23 AND is_delete=0;

It is no different from direct update. It still reports type conversion exceptions.

• SQL scheme implementation (feasible scheme)
  It seems that the pot is not MyBatis plus. It may be due to the underlying type conversion of MyBatis. There is no move. It is the worst choice to write SQL by yourself and write null directly. The problem is solved, but the cause of the problem is not found. If there is a better solution in the future.

<update id="cusUpdate">
     UPDATE YUNYAN_NOTICE_RECEIPT
     SET stock_number = #{noticeReceipt.stockNumber},
     <choose>
         <when test="noticeReceipt.validityPeriod == null">
             validity_period = NULL,
         </when>
         <otherwise>
             validity_period = #{noticeReceipt.validityPeriod},
         </otherwise>
     </choose>
     WHERE
     is_delete = 0 AND id = #{noticeReceipt.id}
 </update>