How to set a list of integers while preparing SQL queries in Java

Question

I have a query like this - select * from tbl where ... and colname in (2,3,4)

When I prepare the query (... ' colname in (?)' ) using a PreparedStatement in Java, what setter method should I call to set these integers ? The integers are available in an int array and the size varies. If it matters, the database is MySQL and the column in question is of int type.

Answer 1

you can use another syntax

WHERE colname = ANY (?)

and call PreparedStatement.setArray(). You have to provide an instance of java.sql.Array as parameter.One advantage of ANY instead of IN is, that you can pass an empty array without causing a SQL syntax error.

Answer 2

As an addendum to Learning's explanation:

Creating an Array can be tricky.

There's a creator method in Connection in JDK 1.6 and later. http://download.java.net/jdk7/docs/api/java/sql/Connection.html#createArrayOf(java.lang.String,%20java.lang.Object[])

Since JDK1.6 is the latest stable release, your JDBC drivers may not support it.

You can try and create your own Array implementation, but that may not work with all JDBC connector implementations.