java - 处理 DATETIME 值 0000-00-00 00 :00:00 in JDBC

Question

如果我尝试这样做，我会遇到异常(见下文)

resultset.getString("add_date");

对于包含 DATETIME 值 0000-00-00 00:00:00(DATETIME 的准空值)的 MySQL 数据库的 JDBC 连接，即使我只是想将值作为字符串获取，不作为一个对象。

我通过这样做解决了这个问题

SELECT CAST(add_date AS CHAR) as add_date

这行得通，但看起来很傻......有没有更好的方法来做到这一点？

我的意思是我只想要原始的 DATETIME 字符串，所以我可以自己解析它按原样。

注意:这里是 0000 的来源:(来自 http://dev.mysql.com/doc/refman/5.0/en/datetime.html)

Illegal DATETIME, DATE, or TIMESTAMP values are converted to the “zero” value of the appropriate type ('0000-00-00 00:00:00' or '0000-00-00').

具体的异常(exception)是这个:

SQLException: Cannot convert value '0000-00-00 00:00:00' from column 5 to TIMESTAMP.
SQLState: S1009
VendorError: 0
java.sql.SQLException: Cannot convert value '0000-00-00 00:00:00' from column 5 to TIMESTAMP.
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
    at com.mysql.jdbc.ResultSetImpl.getTimestampFromString(ResultSetImpl.java:6343)
    at com.mysql.jdbc.ResultSetImpl.getStringInternal(ResultSetImpl.java:5670)
    at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5491)
    at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5531)

Answer 1

替代答案，您可以直接在数据源配置中使用此 JDBC URL:

jdbc:mysql://yourserver:3306/yourdatabase?zeroDateTimeBehavior=convertToNull

编辑:

来源:MySQL Manual

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.

更新:Alexander 报告了一个影响该功能的 mysql-connector-5.1.15 的错误。见 CHANGELOGS on the official website .