解决java.sql.SQLException: Zero date value prohibited

最新推荐文章于 2024-09-19 20:31:15 发布

转载最新推荐文章于 2024-09-19 20:31:15 发布 · 1.4k 阅读

3 ·

CC 4.0 BY-SA版权

原文链接：https://www.cnblogs.com/ooo888ooo/p/13582407.html

文章标签：

#mysql #java #mybatis

Java 同时被 2 个专栏收录

35 篇文章

订阅专栏

SQL

7 篇文章

订阅专栏

本文介绍了如何解决前端与后端开发中遇到的java.sql.SQLException: Zerodatavalueprohibited报错，通过调整数据库连接参数zeroDateTimeBehavior来解决，同时提供了详细的技术排查和解决步骤。

点击第二页的时候报错java.sql.SQLException: Zero date value prohibited

前端页面报错如图：

后端报错如图：

控制台：

复制代码

org.springframework.dao.TransientDataAccessResourceException: Error attempting to get column 'gmt_create' from result set.  Cause: java.sql.SQLException: Zero date value prohibited
; Zero date value prohibited; nested exception is java.sql.SQLException: Zero date value prohibited
    at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:110)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
    at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
    at com.sun.proxy.$Proxy107.selectList(Unknown Source)
。。。。。。。。。。。
Caused by: com.mysql.cj.exceptions.DataReadException: Zero date value prohibited
    at com.mysql.cj.result.SqlTimestampValueFactory.localCreateFromTimestamp(SqlTimestampValueFactory.java:101)
    at com.mysql.cj.result.SqlTimestampValueFactory.localCreateFromTimestamp(SqlTimestampValueFactory.java:50)
    at com.mysql.cj.result.AbstractDateTimeValueFactory.createFromTimestamp(AbstractDateTimeValueFactory.java:87)
    at com.mysql.cj.protocol.a.MysqlTextValueDecoder.decodeTimestamp(MysqlTextValueDecoder.java:79)
    at com.mysql.cj.protocol.result.AbstractResultsetRow.decodeAndCreateReturnValue(AbstractResultsetRow.java:87)
    at com.mysql.cj.protocol.result.AbstractResultsetRow.getValueFromBytes(AbstractResultsetRow.java:241)
    at com.mysql.cj.protocol.a.result.ByteArrayRow.getValue(ByteArrayRow.java:91)
    ... 94 more

复制代码

解决方式：

在数据库连接的URL后面加上&zeroDateTimeBehavior=convertToNull

spring.datasource.url=jdbc:mysql://localhost:3306/guli?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull