Postgres SQL in clause and setArray()

Question

I am using Java 1.7 and JDBC 4 and Postgres. I am trying to use a PreparedStatement with an array to fill a SQL in clause. But, the SQL generated seems to have "{" and "}" in it. Here is the code:

PreparedStatement ptmt = 
      connection.prepareStatement("select * from foo where id in (?)");
String[] values = new String[3];
values[0] = "a";
values[1] = "b";
values[2] = "c";
ptmt.setArray(1, connection.createArrayOf("text", values));

The resulting SQL looks like this:

select * from foo where id in ('{"a","b","c"}')

Which, can't work. This is how it should look:

select * from foo where id in ("a","b","c")

or

select * from foo where id in ('a','b','c')

What am I missing here?

Answer 1

Use = ANY subquery expression.

PreparedStatement ptmt = connection.prepareStatement("select * from foo where id = ANY(?)");
String[] values = new String[]{"a","b","c"};
ptmt.setArray(1, connection.createArrayOf("text", values));

And if you need to enforce types in the query you can do something like this.

select * from foo where id = ANY(?::text[])

The PostgreSQL documentation has more details. This snippet is worth noting:

SOME is a synonym for ANY. IN is equivalent to = ANY.

Answer 2

When your database field is of type array, then you can use the PreparedStatement.setArray() to send an array to the query. But, in your case, it's not really an array, rather is a variable no of arguments, and you can't do that. i.e.

PreparedStatement ptmt =  connection.prepareStatement("select * from foo where id in (?)");

can take only one parameter. If you want 3 parameters to be passed, you have to do

PreparedStatement ptmt =  connection.prepareStatement("select * from foo where id in (?, ?, ?)");

And do ptmt.setString(n, "String") thrice.

If your no of arguments aren't constant, then construct the query dynamically, although, you loose the efficiency.