How to use arraylist as a parameter in IN clause of SQL

Question

I have a array list of integers . I want to use this list in the IN clause of SQL query. I tried to do the below, but i know it wont work. Can someone suggest a better way .

List<Integer> **plcList**  = new ArrayList<Integer>();
String finalQuery = "
select plc,direction,ROUND(AVG(CAST(speed as float)),2) as speed,COUNT(plc) as count,time_id 
from processed 
WHERE plc IN " + " " + "(**plcList**)"+ " " + "
group by plc, direction, time_id";

Answer 1

I am not a Java prof. but I would suggest you to loop throug your list and make string with comma seperated values. Like in PHP we do this with implode function. And hence your final string would be something like this

 1,2,3,4,5

and then use it in your sql query

I am not sure about my code but try this as I am not a Java programmar ( make the necessary changes if any syntax error)

String intlist = "";
for (String temp : list) 
{
   intlist += ","+temp;
}
String Str = new String(intlist);
String sqlstring = Str.substring(1);

Answer 2

I suggest to have a look on this one: PreparedStatement IN clause alternatives?

And this page: http://www.javaranch.com/journal/200510/Journal200510.jsp#a2

In your case, you could just loop to build the integer list as Abdul mentioned above.

Answer 3

Try this..

List<Integer> myList = new ArrayList<Integer>();
StringBuilder builder = new StringBuilder();
// length of the list
int locSize = myList.size();

Object[] args = new Object[locSize];               
for( int i = 0; i < locSize; i++ ) {
  builder.append(" ?, " );
  args[i]= myList.get(i);
}

You can then use this in your query, something like this.

....WHERE plc IN ("+ builder.toString() +") group by plc, direction, time_id";

Refer passing object[] to sql query for similar question.