[JDBC Error] Java.sql.SQLException: Zero Date value Prohibited
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.
1 | pubtime DATETIME NULL DEFAULT NULL |
Error Info
1 | Error attempting to get column 'pubtime' from result set. Cause: 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
.
- Zero date will be converted to null
1 | driver-url=jdbc:mysql://127.0.0.1/test?zeroDateTimeBehavior=convertToNull |
- Zero date will be converted to 0001-01-01 00:00:00.0, equivalent to one year
1 | 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.