Thomas Grady CBIP Thomas Grady CBIP - 3 months ago 22
Java Question

How to use an arraylist as a prepared statement parameter

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

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();
Comments