CODEWITHSUNDEEP
javamysqlarrayslistarraylist
Shashank
Shashank

Reputation: 13

How to pass values('how many' are known at runtime) to SQL query

I have following java code in which String array i.e. list which is dynamic value known at runtime. I need to pass this value to query mentioned in string variable i.e. SQL.

List[] list = new Arraylist();

String SQL = "select * from Table_name where col_1 IN ("+list[1]+")" OR
"col_1 IN("+list[2]+")" ....... OR "col_1 IN("+list[n]+")";

List <Class_Name> systemtails = jdbcTemplateObject.query(SQL, new 
Class_Name_Mapper());

One way to do is enclosing following query in a loop, that makes query to execute more than once which in-turn affect performance.

String SQL = "select * from Table_name where col_1 IN ("+list[i]+")";

where i=1,2,3,4...n. All answers are welcomed and thank you in advance :).

PS : Query is just framed for question perspective in reality believe me it is very complex and big.

Upvotes: 0

Views: 94

Answers (2)

Elie Nassif
Elie Nassif

Reputation: 462

So I understand your query is in a for loop like so:

For int I = 0; I  < list.size; I++ {
String SQL = "select * from Table_name where col_1 IN ("+list[i]+")";
List <Class_Name> systemtails = jdbcTemplateObject.query(SQL, new 
Class_Name_Mapper());
}

why not do this instead:

    String SQL = "select * from Table_name where col_1 IN (";
    For int I = 0; I  < list.size; I++ {
      SQL+=list[I];
      If(I != list.size -1){
        SQL+=","; 
      }else{
        SQL +=")";
      }
    }
    List <Class_Name> systemtails = jdbcTemplateObject.query(SQL, new 
    Class_Name_Mapper());

Upvotes: 0

Stefano Zanini
Stefano Zanini

Reputation: 5916

First of all, you should use PreparedStatement to avoid being prone to SQL Injection.

To do that, I would use a for loop to build the IN condition

boolean first = true;
String inCondition = "(";
for(int i = 0; i < list.length; i++){
    if(first){
        first = false;
    } else {
        inCondition += ", ";
    }
    inCondition += ?;
}
inCondition += ")";

PreparedStatement ps = "select * from Table_name where col_1 IN " + inCondition;

int index = 1;
for(String val : list) {
    ps.setString(index++, val);
}

Upvotes: 1

Related Questions