背景
数据库里用varchar存储的日期数据,有的是null,有的是空值,有的是正常数据。java类里用Date接收,在mybatis转化后报了异常:
Error attempting to get column 'stage4StartTime' from result set. Cause: java.sql.SQLDataException: Unsupported conversion from LONG to java.sql.Timestamp
; Unsupported conversion from LONG to java.sql.Timestamp; nested exception is java.sql.SQLDataException: Unsupported conversion from LONG to java.sql.Timestamp
org.springframework.dao.DataIntegrityViolationException: Error attempting to get column 'stage4StartTime' from result set. Cause: java.sql.SQLDataException: Unsupported conversion from LONG to java.sql.Timestamp
解决思路
利用cast函数处理,这样空值会变成null就不会报转换异常了,例如:
cast(stage4sjrq as date) as stage4StartTime
(其中数据库字段stage4sjrq为varchar类型,java中stage4StartTime字段为Date类型)
贴上sql示例
select
syjhbh as number,
syjhmc as name,
cast(stage1sjrq as date) as createTime,
khqc as clientName,
syjhszbm as buName,
ywxbianma as businessLine,
sjzhuangt as businessStatus,
-- 数据库里可能有null和空值,varchar的空值在转换成java类的Date类型时会报转换错误,
-- 但是用cast函数以后,空值会变成null,就不会报错
cast(modedatamodifydatetime as date) as updateTime,
cast(stage1sjrq as date) as stage1StartTime,
cast(stage2sjrq as date) as stage2StartTime,
cast(stage3sjrq as date) as stage3StartTime,
cast(stage4sjrq as date) as stage4StartTime,
cast(stage5sjrq as date) as stage5StartTime,
cast(stage6sjrq as date) as stage6StartTime
from uf_sjgl ```