java - 子句和 setArray() 中的 Postgres SQL

Question

我正在使用 Java 1.7、JDBC 4 和 Postgres。我正在尝试使用带有数组的 PreparedStatement 来填充 SQL in 子句。但是，生成的 SQL 中似乎有“{”和“}”。这是代码:

PreparedStatement ptmt = 
      connection.prepareStatement("select * from foo where id in (?)");
String[] values = new String[3];
values[0] = "a";
values[1] = "b";
values[2] = "c";
ptmt.setArray(1, connection.createArrayOf("text", values));

生成的 SQL 如下所示:

select * from foo where id in ('{"a","b","c"}')

哪个，行不通。它应该是这样的:

select * from foo where id in ("a","b","c")

或

select * from foo where id in ('a','b','c')

我在这里错过了什么？

Answer 1

使用= ANY子查询表达式。

PreparedStatement ptmt = connection.prepareStatement("select * from foo where id = ANY(?)");
String[] values = new String[]{"a","b","c"};
ptmt.setArray(1, connection.createArrayOf("text", values));

如果您需要在查询中强制执行类型，您可以这样做。

select * from foo where id = ANY(?::text[])

PostgreSQL documentation有更多细节。这个片段值得注意:

SOME is a synonym for ANY. IN is equivalent to = ANY.