postgresql - 在 JDBC 中将数组传递给 postgres

Question

我想使用 JDBC 在 Postgres 中执行以下查询:

with things as (values(1),(2)) select * from things;

所以我的 Java 代码如下所示:

String sql = "with things as (?) select * from things";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setArray(1, conn.createArrayOf("INTEGER", new Integer[]{1, 2});

但这会抛出以下错误:

org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"

Answer 1

你可以像这样使用 unnest 做你需要的事情:

Integer[] id = {1, 2};
Array array = connection.createArrayOf("int4", id);

try (PreparedStatement stmt = connection.prepareStatement(
         "with things as (select unnest((?)::integer[])) select * from things")) {
    stmt.setArray(1, array);
    ResultSet rs = stmt.executeQuery();
    // use the result set
}