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 rows that have a field that's in the array)?
+1
A:
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.
Stephen C
2009-08-29 06:15:43
+1
A:
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:
- Begin transaction (this is automatic if you are inside transactional method - EJB or Spring);
- 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);
- 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.);
- 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.
grigory
2009-08-29 06:51:21
IBM DB2 can do that, too (temp table with transactional scope). I like the idea, but what is the benefit compared to simply building the IN clause dynamically?
Robert Petermeier
2009-08-29 07:00:06
I can't prove it but the difference looks like O(log(n)) vs. O(n) or even worse. The overhead of creating and populating temporary table is negligible for larger n (n>5). Originally we introduced this to overcome performance problems with IN operator with large number of elements. After adding convenience methods that made usage transparent and concise we never looked back at using IN again (in cases when number of elements varies).
grigory
2009-08-29 22:09:52
Just quick performance comment: by replacing IN with temporary table you essentially replace OR operator with inner join. Any DBA would agree that performance gain from this is enormous.
grigory
2009-08-29 22:11:23
+1
A:
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%5Fjdbc%5Farrays.html
Try to look if that would help you.
Zorkus
2009-08-29 07:02:05