MySQL錯誤:Value '0000-00-00' can not be represented as java.sql.Date解決方法

Bertha 。 2022-05-30 07:52 128阅读 0赞

原文地址：[MySQL錯誤:Value '0000-00-00' can not be represented as java.sql.Date解決方法][MySQL_Value _0000-00-00_ can not be represented as java.sql.Date]

jsp+Tomcat+Mysql開發中采用ResultSet取值時,不管是採用getString()還是採用getDate()時,均會拋出如題所述錯誤.查閱Mysql官方Bug資訊:

[http://dev.mysql.com/doc/refman/5.1/en/connector-j-installing-upgrading.html][http_dev.mysql.com_doc_refman_5.1_en_connector-j-installing-upgrading.html]

是因為日期型(Date或DateTime類型)字串為'000-00-00'時,MySQL預設處理方式是拋出錯誤(exception).只要將jdbc數據庫連結字串作如下修改即可:

修改前jdbc連結字串為:  jdbc:mysql://localhost:3306/test?characterEncoding=UTF-8&characterSetResults=UTF-8

修改後jdbc連結字串為 :  jdbc:mysql://localhost:3306/test?characterEncoding=UTF-8&characterSetResults=UTF-8&zeroDateTimeBehavior=convertToNull

原文如下:

Datetimes with all-zero components (`0000-00-00 ...`) — These values can not 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 allowable 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`.

Starting with Connector/J 3.1.7, `ResultSet.getString()` can be decoupled from this behavior via noDatetimeStringSync=true (the default value is `false`) so that you can retrieve the unaltered all-zero value as a String. It should be noted that this also precludes using any time zone conversions, therefore the driver will not allow you to enable noDatetimeStringSync and useTimezone at the same time.

[MySQL_Value _0000-00-00_ can not be represented as java.sql.Date]: http://blog.csdn.net/jackterq/article/details/4559394
[http_dev.mysql.com_doc_refman_5.1_en_connector-j-installing-upgrading.html]: http://dev.mysql.com/doc/refman/5.1/en/connector-j-installing-upgrading.html