在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的MySQL数据库的JDBC连接：00（DATETIME的准空值），即使我只是想将该值作为字符串，而不是一个对象。

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.

我绕过了通过执行

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，所以我不得不这样做一种不同的方式。对于基本情况，您应该可以根据 zeroDateTimeBehavior = convertToNull 添加到您的连接URI /connector-j/5.1/en/connector-j-reference-configuration-properties.htmlrel =nofollow noreferrer>配置属性页面。

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.

我发现其他地方的建议是指把参数放在你的hibernate配置中：

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

在hibernate.cfg.xml中

In hibernate.cfg.xml:

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

在hibernate.properties 中：

In hibernate.properties:

hibernate.connection.zeroDateTimeBehavior=convertToNull

但是我不得不将它放在我的JBoss的mysq-ds.xml文件中：

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屋！