赞
踩
需求:将SQL Server库表中,某一个Date类型字段更新为null
环境:
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)
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.2.0</version>
</dependency>
@Data @TableName("NOTICE_RECEIPT") public class NoticeReceipt { @TableId(type = IdType.AUTO) private Integer id; /** 仓库编码 */ private String stockNumber; /** 效期 */ @TableField(updateStrategy = FieldStrategy.IGNORED) private Date validityPeriod; /** 是否删除,0:否,1:是 */ @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;
异常:
org.apache.ibatis.exceptions.PersistenceException:
### Error updating database. Cause: com.microsoft.sqlserver.jdbc.SQLServerException: 不允许从数据类型 varbinary 到 date 的隐式转换。请使用 CONVERT 函数来运行此查询。
@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;
和直接update没什么区别,依然报类型转换异常。
<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>
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。