Pass String array values to sql IN condition

Question

I have String array like

String[] val=request.getParameterValues("Names");  
for(int i=0;i<val.length;i++){  
 //printval[i];
}

I would like to assign the values of String array to a sql statement as

How can I pass all the string values to sql condition? If array have the following values JAMES,SMITH, JAKE etc, then I would pass it as

sql =  "where dept_name in('JAMES','SMITH','JAKE')";

Ideally I would want the string array values to be passed inside the in condition of sql.

Why can't you use a `StringBuilder` to build a pre-prepared statement? — Shark, May 09 '13 at 12:05
maybe [this](http://stackoverflow.com/questions/16372046/getting-records-from-database-with-variable-condition/16372094#16372094) is what you want — A4L, May 09 '13 at 12:09

score 3 · Answer 1 · answered May 09 '13 at 12:07

3

This is very simple!

just try:

import org.apache.commons.lang3.StringUtils;


StringUtils.join(new String[] {"Hello", "World", "!"}, ", ");

and you will have the string joined by commas

answered May 09 '13 at 12:07

Dima

8,586
4
28
57

Semih Yagcioglu · Accepted Answer · 2013-05-09T12:20:28.743

2

You can construct a query as a filter and append this query to your main query.

StringBuilder sb= new StringBuilder();
String filter = "";

String[] val=request.getParameterValues("Names");  
for(int i=0;i<val.length;i++){  
 //printval[i];
 sb.append( "'"+val[i]+"'," );
}

filter = sb.ToString();
filter = filter.substring(0, filter.length()-1);

sql =  "where dept_name in("+filter+")";

edited May 09 '13 at 12:20

answered May 09 '13 at 12:08

Semih Yagcioglu

4,011
1
26
43

I forgot to put the val as array, can you try it again? – Semih Yagcioglu May 09 '13 at 12:20

score 1 · Answer 3 · answered May 09 '13 at 12:16

This should do the trick:

   StringBuilder csvStr = new StringBuilder();
    for(int i=0;i<val.length;i++){
        if (val.length > 1 && i !=0) {
            csvStr.append(",");
        }
        csvStr.append ("'").append(val[i]).append("'");
    }
    System.out.println(csvStr);

Use csvStr.toString() into the sql you are using.

  sql =  "where dept_name in (".concat(csvStr.toString()).concat(")");

score 1 · Answer 4 · edited Feb 07 '17 at 12:07

1

sql = "where dept_name in(select column_value from table(?))";

Then pass the Array to from JDBC.

Object[] dept = {"Dev","QA", "Support"};
Array deptArray = connection.createArrayOf("VARCHAR", dept);
preparedStatement.setArray(idx, deptArray);

edited Feb 07 '17 at 12:07

Tom

16,842
17
45
54

answered Feb 07 '17 at 11:56

Arun Kumar

39
3

How is *"select column_value from table(?)"* supposed to fit in here? – Tom Feb 07 '17 at 12:07
Issue with for loop and appending values in sql is security issue as it can cause sql injection issues. If you still want to use, then you should use encode literals. – Arun Kumar Jan 11 '19 at 03:36
column_value will be pseudo column for the scalar nested table created by oracle. – Arun Kumar Jan 11 '19 at 03:39

Pass String array values to sql IN condition

4 Answers4

Linked