How to use an arraylist as a prepared statement parameter [duplicate]

Question

I have looked and have been unable to find an answer to the following challenge I am having. It seems pretty straightforward but I have been unable to resolve it.

I have an ArrayList of record ids that are type Long -> ArrayList<Long>. I would like to use this list of record ids to select rows from another table. So far so good. Now onto the challenge...

a) I am using a prepared statement to select the data from a table using the ArrayList as input for this.

selectPS = dbConnection.prepareStatement("select columnA from tableA where id in ?"); 

Question on the above - how should the parameter be defined? The above does not seem correct for an ArrayList type parameter.

b) I am also running into problems when setting the value of the parameter for the prepared statement. There is no method for setting an ArrayList type value and I see no other viable options.

---> selectPS.set?????(1, arraylistParameter);      ResultSet rs = selectPS.executeQuery();  

Any help or direction you can set me in is greatly appreciated.

Thank you.

Answer 1

You may want to use setArray method as mentioned in the javadoc below:

http://docs.oracle.com/javase/6/docs/api/java/sql/PreparedStatement.html#setArray(int, java.sql.Array)

Sample Code:

PreparedStatement pstmt =                  conn.prepareStatement("select * from employee where id in (?)"); Array array = conn.createArrayOf("VARCHAR", new Object[]{"1", "2","3"}); pstmt.setArray(1, array); ResultSet rs = pstmt.executeQuery();