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.

Accepted Answer

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

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

Answer 2

If you have ArrayList then convert into Array[Object]

ArrayList<String> list = new ArrayList<String>();
PreparedStatement pstmt = 
            conn.prepareStatement("select * from employee where id in (?)");
Array array = conn.createArrayOf("VARCHAR", list.toArray());
pstmt.setArray(1, array);
ResultSet rs = pstmt.executeQuery();

Answer 3

@JulienD Best way is to break above process into two steps.

Step 1 : Lets say 'rawList' as your list that you want to add as parameters in prepared statement.

Create another list :

ArrayList<String> listWithQuotes = new ArrayList<String>();

for(String element : rawList){
    listWithQuotes.add("'"+element+"'");
}

Step 2 : Make 'listWithQuotes' comma separated.

String finalString = StringUtils.join(listWithQuotes.iterator(),",");

'finalString' will be string parameters with each element as single quoted and comma separated.

Answer 4

why making life hard-

PreparedStatement pstmt = conn.prepareStatement("select * from employee where id in ("+ StringUtils.join(arraylistParameter.iterator(),",") +)");

How to Use an Arraylist as a Prepared Statement Parameter [Duplicate]

Must Read

4 Answers

Alexander Ross