Java - Prepared Statements And Arrays

How can I handle an array in a prepared statement? i.e, I want to do a query and one of the parameters I get is an array of strings which I want to use in the query (Don't select r

Solution 1:

Some JDBC drivers may already (before JDBC 4) contain proprietary extensions that support array-type parameters in prepared statements - you would need to consult with API for this. This would mean that you have to use and manipulate array-like type in SQL.

One work around would be using temporary tables. These are meta-steps for such solution:

1. Begin transaction (this is automatic if you are inside transactional method - EJB or Spring);
2. Using JDBC batch insert with prepared statement create and populate a temporary table with arrary elements (temporary table must have transactional scope - this is also proprietary to databases but supported by Oracle at least);
3. Construct your desired SQL that includes a join to temporary table to use array values (it could be explicit inner or outer JOIN or implicit join, e.g. using EXISTS, etc.);
4. Commit (or rollback if application exception) transaction (this should destroy temporary table; concurrent transactions should have no conflict for the same name of temporary table).

Example: IN expression gets replaced with JOIN to temporary table.

Solution 2:

This probably won't help you now, but I read that JDBC 4 will support array types as defined in the 2003 version of SQL.

Solution 3:

That pretty much depends upon RDBMS being used. Often such functionality can be accomplished using vendor's jdbc driver extensions.

2 variants I found are (for Oracle): http://blogs.itemis.de/kloss/2009/03/05/arrays-preparedstatements-jdbc-and-oracle/

http://www.angelfire.com/home/jasonvogel/java_jdbc_arrays.html

Try to look if that would help you.