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 column value is 0000-00-00 00:00:00, program will throw exception Java.sql.SQLException. The following is the column properties.

1
pubtime DATETIME NULL DEFAULT NULL

Error Info

1
2
3
4
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
1
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
1
driver-url=jdbc:mysql://127.0.0.1/test?zeroDateTimeBehavior=round

Reasons

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

References

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