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

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?

Answers


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.


Need Your Help

Checking an array for descending order

java arrays algorithm order

I am writing code to check if my array is in ascending or descending order. If the boolean 'ascending' is true, then I check if it is ascending. If it is false, then I check for descending. I need ...

Suggestions for updating ClickOnce custom prereq

visual-studio-2010 deployment clickonce

I have a WinForms app I am deploying via ClickOnce. I added a custom prereq in the form of a .msi that installs X509 certificates. Question is, if I need to change certs due to expiration, how do I...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.