How do I bind an ArrayList to a PreparedStatement in Oracle?

Question

I was wondering if there was a way to bind an ArrayList (or any kind of List, for that matter) to a PreparedStatement which will eventually be used to access an Oracle database. I found:

http://stackoverflow.com/questions/178479/alternatives-for-java-sql-preparedstatement-in-clause-issue

And that seems similar to my issue, but this question is more specific: I'd like to bind an ArrayList to a PreparedStatement to be used in Oracle, if it is possible, how is this accomplished?

Answer 1

Well, judging by the answer to that one, especially the comments to my wrong answer in that question, you can't.

See http://java.sun.com/j2se/1.3/docs/guide/jdbc/getstart/mapping.html#996857

Answer 2

You can't bind a List to a single parameter in a prepared statement.

Generate SQL with the a parameter marker for each element in the list, for example:

SELECT NAME FROM ITEM WHERE ID IN (?, ?, ?, ?)

Even though you'll generate a new statement for each query, I'd still recommend using a PreparedStatement. If your list contains String instances, you'll get the necessary escaping to protect from SQL injection.

But even if it's a safe type, like Integer objects, some drivers or middleware can cache PreparedStatements, and return a cached instance if the same form is requested. Of course, some testing would be necessary. If your lists vary widely in size, you'll have many different statements, and a poorly-implemented cache might not be prepared to handle so many.

Answer 3

You can't bind it directly. There is a way to pass an array as a parameter. I have no idea what you want to do with it on the database side so this may not help you.

Basically, you have to create a nested table type in the database; build a Java object based on that type, containing the data from your array; and pass that as the parameter.

If you created these objects in the database:

CREATE OR REPLACE TYPE my_nested_table IS TABLE OF VARCHAR2(20);
CREATE TABLE my_table (a  my_nested_table) NESTED TABLE a STORE AS my_table_a;

Then you can write Java code like this:

String[] insertvalues = { "a", "b", "c" };
PreparedStatement p = conn.prepareStatement("INSERT INTO my_table VALUES( ? )");
ARRAY insertParameter = new ARRAY( a_desc, conn, insertvalues );
p.setArray( 1, insertParameter );
p.execute();

The results in Oracle look like this:

dev> select * from my_table;

A
--------------------------------------------------------------------------------
MY_NESTED_TABLE('a', 'b', 'c')