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"

标签： postgresqljdbccommon-table-expression

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
}