【数据库----MySQL】解决java.sql.SQLException: Zero date value prohibited

这种错误是由于数据库的字段为datetime，但是数据的值为0000-00-00 00:00:00，这时zeroDateTimeBehavior默认为exception，会抛出一个SQLException异常

 

解决方式：

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

jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false&zeroDateTimeBehavior=convertToNull

 

mysql文档原文

Datetimes with all-zero components (0000-00-00 ...): These values cannot be represented reliably in Java. Connector/J 3.0.x always converted them to NULL when being read from a ResultSet.

Connector/J 3.1 throws an exception by default when these values are encountered, as this is the most correct behavior according to the JDBC and SQL standards.

This behavior can be modified using the zeroDateTimeBehavior configuration property. The permissible values are:

• exception (the default), which throws an SQLException with an SQLState of S1009.

• convertToNull, which returns NULL instead of the date.

• round, which rounds the date to the nearest closest value which is 0001-01-01.