关于Value ‘0000-00-00 00:00:00‘ can not be represented as java.sql.Timestamp异常问题的解读

前提

对于必传的字段, 新需求改为非必填的时候,在测试环境出现了 Value ‘0000-00-00 00:00:00‘ can not be represented as java.sql.Timestamp 的异常情况.

问题定位

1. 断点到, dao层发现,mybatis的日志照常打印, 但是产出的数据集报错, 提示 xxx_time 字段异常
2. 查看mybatis中的 sql 没有异常, 在Navicat中能正常执行得到数据集, 但是数据集中出现了 xxx_time 字段展示的值为: 0000-00-00 00:00:00 和报错信息中的 ‘0000-00-00 00:00:00‘ can not be represented as java.sql.Timestamp 异常一致

寻找解决方案

百度一下,参考文档 = https://blog.csdn.net/a1029573879a/article/details/124197684

与自己的问题对比

1. 数据库中给的 xxx_time 字段类型为 TIMESTAMP default '0000-00-00 00:00:00' , 嗯, 找到问题点了
2. 就是 插入的时候mysql根据给定要求给 xxx_time 赋予了默认值 0000-00-00 00:00:00 但是在jdbc中认为这个值是异常的时间格式.

Datetimes with all-zero components (0000-00-00 …) — These values can not be represented reliably in Java. Connector/J 3.0.x always converted them to NULL when being read from a ResultSet.
.
Connector/J 3.1 throws an exception by default when these values are encountered as this is the most correct behavior according to the JDBC and SQL standards. This behavior can be modified using the zeroDateTimeBehavior configuration property. The allowable values are:
.
exception (the default), which throws an SQLException with an SQLState of S1009.
.
convertToNull, which returns NULL instead of the date.
.
round, which rounds the date to the nearest closest value which is 0001-01-01.
.
Starting with Connector/J 3.1.7, ResultSet.getString() can be decoupled from this behavior via noDatetimeStringSync=true (the default value is false) so that you can retrieve the unaltered all-zero value as a String. It should be noted that this also precludes using any time zone conversions, therefore the driver will not allow you to enable noDatetimeStringSync and useTimezone at the same time.

解决方案:

1. 给 xxx_time 默认值为: TIMESTAMP 的最大值 2038-01-19 00:00:00, 或者 2038-01-01 00:00:00
2. 给 jdbc 的连接配置新增属性: &zeroDateTimeBehavior=convertToNull

 jdbc:mysql://localhost:3306/test?characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull

对于新增的属性的疑问

zeroDateTimeBehavior 的作用

convertToNull：出现时间0000-00-00 00:00:00时转为 null

round：出现时间 0000-00-00 00:00:00 时转为 0001-01-01 00-00-00

exception：默认值，抛出异常

参考文档 = https://blog.csdn.net/a1029573879a/article/details/124197684