pass array to postgres in JDBC

user_1321514 0
Viewed 1083

I want to do the following query in Postgres using JDBC:

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

So my Java code looks like this:

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

But this is throwing the following error: 

org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"
jdbc common-table-expression postgresql
1 Answers

You can do what you need using unnest like this:

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
}
user_22319720
Related