如何处理0000-00-00日期在jdbc MySQL查询 [英] How to process 0000-00-00 date in jdbc MySQL query

问题描述

我收到这个例外：

java.sql.SQLException: Value '0000-00-00' can not be represented as java.sql.Date

源自此代码：

Date internalDate = rs.getDate(idx++);

其中 rs 是一个 ResultSet 。

所以这对我来说很好 - 我知道数据库中有零的日期，我需要能够读取它们并将其转换成我的下游数据结构中的一个适当的（可能为空的）数据。问题是我不知道如何检索它并得到一个软错误。我想到将这行换成一个try / catch用于SQLException，但是明白这会打破ResultSet的有效性。

So this is fine to me - I know there are zero'ed dates in the database and I need to be able to read these and convert them into an appropriate (probably null) data in my downstream data structures. The problem is I don't know how to retrieve it and get a "soft" error. I thought about wrapping this line in a try/catch for SQLException but understand this will break validity of the ResultSet.

是否可以以另一种方式读取这个值而不会抛出一个SQLException？

Is it possible to read this value in another way without throwing a SQLException?

推荐答案

我喜欢 @ a_horse_with_no_name 的答案，但是如果您无法控制连接，则可以更改查询以返回 null 代替：

I like @a_horse_with_no_name's answer, however if you don't have control over the connection, you could change the query to return a null instead:

select
    ...
    case when my_date_col = '0000-00-00' then null else my_date_col end as my_date_col,
    ...

或稍微更简洁，但是mysql-only，选项：

or the slightly more terse, but mysql-only, option:

    if(my_date_col = '0000-00-00', null, my_date_col) as my_date_col

此外，建议您谨慎改变整个应用程序的JDBC行为，可能会破解代码依赖于这样的日期被返回 - 也许他们使用 rs.getString（i）。你必须回归测试所有其他查询才能确定。

Also, caution is advised changing the entire application's JDBC behaviour as you may break code that relies on such dates being returned - perhaps they use rs.getString(i) instead. You would have to regression test all other queries to be sure.

这篇关于如何处理0000-00-00日期在jdbc MySQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！