在 JDBC 中处理 DATETIME 值 0000-00-00 00:00:00 [英] handling DATETIME values 0000-00-00 00:00:00 in JDBC

问题描述

如果我尝试这样做，我会得到一个例外(见下文)

I get an exception (see below) if I try to do

resultset.getString("add_date");

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

for a JDBC connection to a MySQL database containing a DATETIME value of 0000-00-00 00:00:00 (the quasi-null value for DATETIME), even though I'm just trying to get the value as string, not as an object.

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

I got around this by doing

SELECT CAST(add_date AS CHAR) as add_date

哪个有效，但看起来很傻……有没有更好的方法来做到这一点?

which works, but seems silly... is there a better way to do this?

我的观点是我只想要原始的 DATETIME 字符串，所以我可以自己解析它原样.

My point is that I just want the raw DATETIME string, so I can parse it myself as is.

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

note: here's where the 0000 comes in: (from http://dev.mysql.com/doc/refman/5.0/en/datetime.html)

非法的 DATETIME、DATE 或 TIMESTAMP值转换为零"适当类型的值('0000-00-00 00:00:00' 或'0000-00-00').

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').

具体的例外是这个:

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)

推荐答案

我偶然发现了这个试图解决相同的问题.我正在使用的安装使用 JBOSS 和 Hibernate，因此我必须以不同的方式执行此操作.对于基本情况，您应该能够按照此 配置属性页面.

I stumbled across this attempting to solve the same issue. The installation I am working with uses JBOSS and Hibernate, so I had to do this a different way. For the basic case, you should be able to add zeroDateTimeBehavior=convertToNull to your connection URI as per this configuration properties page.

我发现其他关于将该参数放入休眠配置的建议:

I found other suggestions across the land referring to putting that parameter in your hibernate config:

在 hibernate.cfg.xml 中:

<property name="hibernate.connection.zeroDateTimeBehavior">convertToNull</property>

在hibernate.properties中:

hibernate.connection.zeroDateTimeBehavior=convertToNull

但我不得不把它放在我的 mysql-ds.xml 文件中，用于 JBOSS:

But I had to put it in my mysql-ds.xml file for JBOSS as:

<connection-property name="zeroDateTimeBehavior">convertToNull</connection-property>

希望这对某人有所帮助.:)

Hope this helps someone. :)

这篇关于在 JDBC 中处理 DATETIME 值 0000-00-00 00:00:00的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！