ResultSet empty when using createArrayOf in Java with PostgreSQL

Question

I'm trying to query product table using an array of ids. Here's a fragment of the method:

PreparedStatement statement = connection
    .prepareStatement("SELECT * FROM product WHERE id IN (?)");

System.out.println(ids /*ArrayList<Integer>*/); //prints [3]

Array array = connection.createArrayOf("INTEGER", ids.toArray());
// Array array = connection.createArrayOf("INTEGER", new Integer[]{1, 2, 3}); //<-----tried this too

statement.setArray(1, array);

ResultSet results = statement.executeQuery();

while (results.next()) {
    System.out.println("does not print this");
    Product product = new Product(0);
    product.setId(results.getInt("id"));
    products.add(product);
}

return products;

Table product contains 3 rows with ids 1, 2 and 3. products returns null. Any idea why?

Thanks

EDIT

According to section 9.23.1

The right-hand side is a parenthesized list of scalar expressions

example (1,2,3)

So, I think question turns into: how to get that list of scalar expressions from my ArrayList?

This is not how you populate an SQL set. You have to provide the appropriate number of ? markers and set values for them manually. An SQL Array is a completely different beast. — user207421
– user207421, Commented Sep 8, 2020 at 6:19
ok, so that's how I don't do it. now, how might I do it? any examples, snippets, resources, pointers, signals? thanks — Scaramouche
– Scaramouche, Commented Sep 8, 2020 at 6:21
Have you tried: 'SELECT * FROM product WHERE id = ANY(?)' and does your query work, using the psql client? — Simon Schiff
– Simon Schiff, Commented Sep 8, 2020 at 8:12

Simon Schiff · Accepted Answer · 2020-09-09 16:38:45Z

1

To check in the WHERE clause of a query, whether an element is in an array you can use ANY. In your case:

SELECT * FROM product WHERE id = ANY(?)

answered Sep 9, 2020 at 16:38

Simon Schiff

7116 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

ResultSet empty when using createArrayOf in Java with PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related