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

Contents

1. 1. Background
2. 2. Error Info
3. 3. Solutions
4. 4. Reasons
5. 5. References

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

2. 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

• Java.sql.SQLException:Zero Date value Prohibited exception handling
• Datetime types processing - MySQL Documentation