[JDBC Error] Java.sql.SQLException: Zero Date value Prohibited

Posted on Edited on In

Background

Using MyBatis to query DATETIME type column data from MySQL table, if the column value is 0000-00-00 00:00:00, the program will throw exception Java.sql.SQLException. The following are the column properties.

pubtime DATETIME NULL DEFAULT NULL

Error Info

Error attempting to get column 'pubtime' 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
org.springframework.dao.TransientDataAccessResourceException: Error attempting to get column 'pubtime' 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

Solutions

To set up zeroDateTimeBehavior=convertToNull in JdbcUrl. zeroDateTimeBehavior values can be EXCEPTION, ROUND, and CONVERT_TO_NULL. The default value of zeroDateTimeBehavior is EXCEPTION.

  1. Zero date will be converted to null
driver-url=jdbc:mysql://127.0.0.1/test?zeroDateTimeBehavior=convertToNull
  1. Zero date will be converted to 0001-01-01 00:00:00.0, equivalent to one year
driver-url=jdbc:mysql://127.0.0.1/test?zeroDateTimeBehavior=round

Reasons

When MySQL database is in the face of 0000-00-00 00:00:00 date processing, if not set corresponding countermeasures, it will produce an exception.

References