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?

Answer 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(?)