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:

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?

Answer 1

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.