Java学习-084-java.sql.SQLException: Zero date value prohibited 问题解决

在执行程序的时候，发现 java.sql.SQLException: Zero date value prohibited，提示创建日期 created_time 数据异常。

 

查询数据库中的日期，发现 created_time 存在为 0000-00-00 00:00:00 的脏数据，导致数据查询异常。同时，多方查询验证后，发现若日期小于 1900-01-01 00:00:00 时，SQL 就会报错。

 

解决方法：

1、删除或修复异常的脏数据；

2、查验相关代码，确保非程序问题导致；

 

另，此种问题也可在 mysql 的 URL 里添加 zeroDateTimeBehavior=convertToNull 即可解决。当然，推荐大家还是从应用服务方面杜绝此种日期数据的产生，保持应用程序的健壮性。

  

 

附录：详细日志文本输出如下所示：

org.springframework.dao.TransientDataAccessResourceException: Error attempting to get column 'created_time' from result set.  Cause: java.sql.SQLException: Zero date value prohibited
; SQL []; Zero date value prohibited; nested exception is java.sql.SQLException: Zero date value prohibited
        at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:108)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:82)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:82)
        at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
        at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
        at com.sun.proxy.$Proxy116.selectList(Unknown Source)
        at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:230)
        at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.executeForMany(MybatisMapperMethod.java:158)
        at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:76)
        at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:62)
        at com.sun.proxy.$Proxy126.selectList(Unknown Source)
        at com.baomidou.mybatisplus.extension.service.impl.ServiceImpl.list(ServiceImpl.java:276)
        at com.fanfengping.service.impl.TestCaseServiceImpl.list(TestCaseServiceImpl.java:874)
        at com.fanfengping.service.impl.TestCaseServiceImpl.batchChangeState(TestCaseServiceImpl.java:519)
        at com.fanfengping.service.impl.TestCaseServiceImpl$$FastClassBySpringCGLIB$$4e09a5bf.invoke(<generated>)
        at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
        at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:736)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
        at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
        at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:282)
        at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
        at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:671)
        at com.fanfengping.service.impl.TestCaseServiceImpl$$EnhancerBySpringCGLIB$$f6abe132.batchChangeState(<generated>)
        at com.fanfengping.service.impl.TestCaseServiceImpl.lambda$batchChangeState$29(TestCaseServiceImpl.java:506)
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
        at java.util.concurrent.FutureTask.run(FutureTask.java:266)
        at com.fanfengping.common.thread.TransmitTaskDecorator.lambda$decorate$0(TransmitTaskDecorator.java:17)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
        at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLException: Zero date value prohibited
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:73)
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:99)
        at com.mysql.cj.jdbc.result.ResultSetImpl.getTimestamp(ResultSetImpl.java:1019)
        at com.alibaba.druid.filter.FilterChainImpl.resultSet_getTimestamp(FilterChainImpl.java:1084)
        at com.alibaba.druid.wall.WallFilter.resultSet_getTimestamp(WallFilter.java:1040)
        at com.alibaba.druid.filter.FilterChainImpl.resultSet_getTimestamp(FilterChainImpl.java:1080)
        at com.alibaba.druid.filter.FilterAdapter.resultSet_getTimestamp(FilterAdapter.java:1807)
        at com.alibaba.druid.filter.FilterChainImpl.resultSet_getTimestamp(FilterChainImpl.java:1080)
        at com.alibaba.druid.proxy.jdbc.ResultSetProxyImpl.getTimestamp(ResultSetProxyImpl.java:733)
        at com.alibaba.druid.pool.DruidPooledResultSet.getTimestamp(DruidPooledResultSet.java:220)
        at org.apache.shardingsphere.core.execute.sql.execute.result.StreamQueryResult.getValue(StreamQueryResult.java:84)
        at org.apache.shardingsphere.underlying.merge.impl.StreamMergedResult.getValue(StreamMergedResult.java:49)
        at org.apache.shardingsphere.encrypt.merge.dql.EncryptMergedResult.getValue(EncryptMergedResult.java:53)
        at org.apache.shardingsphere.shardingjdbc.jdbc.core.resultset.EncryptResultSet.getTimestamp(EncryptResultSet.java:312)
        at sun.reflect.GeneratedMethodAccessor116.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:497)
        at org.apache.ibatis.logging.jdbc.ResultSetLogger.invoke(ResultSetLogger.java:69)
        at com.sun.proxy.$Proxy212.getTimestamp(Unknown Source)
        at org.apache.ibatis.type.DateTypeHandler.getNullableResult(DateTypeHandler.java:39)
        at org.apache.ibatis.type.DateTypeHandler.getNullableResult(DateTypeHandler.java:28)
        at org.apache.ibatis.type.BaseTypeHandler.getResult(BaseTypeHandler.java:81)
        at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.applyAutomaticMappings(DefaultResultSetHandler.java:521)
        at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.getRowValue(DefaultResultSetHandler.java:402)
        at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleRowValuesForSimpleResultMap(DefaultResultSetHandler.java:354)
        at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleRowValues(DefaultResultSetHandler.java:328)
        at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSet(DefaultResultSetHandler.java:301)
        at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSets(DefaultResultSetHandler.java:194)
        at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:65)
        at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
        at sun.reflect.GeneratedMethodAccessor278.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:497)
        at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)
        at com.sun.proxy.$Proxy204.query(Unknown Source)
        at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.doQuery(MybatisSimpleExecutor.java:67)
        at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324)
        at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
        at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
        at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:83)
        at sun.reflect.GeneratedMethodAccessor215.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:497)
        at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)
        at com.sun.proxy.$Proxy203.query(Unknown Source)
        at sun.reflect.GeneratedMethodAccessor215.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:497)
        at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)
        at com.sun.proxy.$Proxy203.query(Unknown Source)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
        at sun.reflect.GeneratedMethodAccessor348.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:497)
        at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)
        ... 26 common frames omitted
Caused by: com.mysql.cj.exceptions.DataReadException: Zero date value prohibited
        at com.mysql.cj.result.SqlTimestampValueFactory.createFromTimestamp(SqlTimestampValueFactory.java:93)
        at com.mysql.cj.result.SqlTimestampValueFactory.createFromTimestamp(SqlTimestampValueFactory.java:46)
        at com.mysql.cj.result.BaseDecoratingValueFactory.createFromTimestamp(BaseDecoratingValueFactory.java:61)
        at com.mysql.cj.result.BaseDecoratingValueFactory.createFromTimestamp(BaseDecoratingValueFactory.java:61)
        at com.mysql.cj.protocol.a.MysqlTextValueDecoder.decodeTimestamp(MysqlTextValueDecoder.java:183)
        at com.mysql.cj.protocol.result.AbstractResultsetRow.decodeAndCreateReturnValue(AbstractResultsetRow.java:87)
        at com.mysql.cj.protocol.result.AbstractResultsetRow.getValueFromBytes(AbstractResultsetRow.java:250)
        at com.mysql.cj.protocol.a.result.ByteArrayRow.getValue(ByteArrayRow.java:91)
        at com.mysql.cj.jdbc.result.ResultSetImpl.getNonStringValueFromRow(ResultSetImpl.java:656)
        at com.mysql.cj.jdbc.result.ResultSetImpl.getDateOrTimestampValueFromRow(ResultSetImpl.java:679)
        ... 80 common frames omitted