JDBC-选择列为NULL的位置 [英] JDBC - select where column is NULL

问题描述

我在Postgres 9.0数据库中有一个简单的表：

I have a simple table in my Postgres 9.0 database:

create table test (id int not null, value int);

我在其中填充了几行：

insert into test values (1, 1);
insert into test values (2, null);
insert into test values (3, null);
insert into test values (4, 1);

现在我正在尝试使用JDBC进行阅读。当我通过 value 列中的非空值进行选择时，一切都很好：

Now I'm trying to read it with JDBC. When I select by the non-null values in the value column, everything is fine:

PreparedStatement select = c.prepareStatement("select * from test where value=?");
select.setInt(1, 1);
return select.executeQuery();

但是当我想选择值为空，结果集不包含任何行。我已经尝试了这两种方法：

But when I want to select rows where value is null, the Result Set contains no rows. I have tried both of these approaches:

select.setObject(1, null);

和

select.setNull(1, Types.INTEGER);

都不行！

发生了什么事？我知道检查NULL的正确SQL将是值为空而不是值= null ，但肯定是

What's going on? I know that the correct SQL to check for NULLs would be where value is null instead of where value=null but surely JDBC is clever enough to sort that out for me?

推荐答案

什么都不是 = NULL 。如果您在交互式查询评估器中输入 select * from test where value = NULL ，您将一无所获。 JDBC不会重写表达式，它只是替换值。

Nothing is = NULL. If you typed select * from test where value=NULL into an interactive query evaluator, you'd get nothing back. JDBC doesn't rewrite your expression, it just substitutes in the values.

您必须使用 is 运算符：

PreparedStatement select = c.prepareStatement("select * from test where value is NULL");
return select.executeQuery();

您已经说过，您希望JDBC足够聪明，可以为您做到这一点，但这这将严重违反关注点分离。您可能会想要使用 = 在您的查询中有一个参数，而您将其设置为 NULL 知道这种关系永远不会评估为真（很可能是更大条件集的一部分）。

You've said you expect JDBC to be "clever" enough to do that for you, but that would be a big violation of the separation of concerns. You may well want to have a parameter in your query using = which you set NULL knowing that that relation will never evaluate true (as part of a larger set of conditions, most likely).

这篇关于JDBC-选择列为NULL的位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！