Thomas Grady CBIP Thomas Grady CBIP - 1 year ago 84
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

of record ids that are type
. I would like to use
this list of record ids to select rows from another table. So far so good. Now onto the

a) I am using a prepared statement to select the data from a table using the
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
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
type value and I see no other viable

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

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

Thank you.

Answer Source

You may want to use setArray method as mentioned in the javadoc below:, 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();
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download