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:
PreparedStatement IN clause alternatives?
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?
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.
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')
Well, judging by the answer to that one, especially the comments to my wrong answer in that question, you can't.
See https://docs.oracle.com/javase/1.5.0/docs/guide/jdbc/getstart/mapping.html#996857.
