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?
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.