dynamic number of placeholders for IN operator in SQL query

[anchal]

Hi,

I have a problem related to passing a number of parameters (the number changes dynamically) for the IN operator in the WHERE clause of a select statement. I want to have a static query e.g. "select * from emp where emp_name in (?, ?, ?, ....)". Here the number of place holders are known only at runtime which would depend on the number of employee names in the list chosen by the user. The list e.g. is:
String emp_names = "'A', 'B', 'C', 'D'";

I want to keep the query static but at the same time be able to provide N number of parameters to this query at run time. Even better if there is a workaround to having N number of placeholders. Having too many placeholders in the query doesn't look like a good idea. I am using JDBC API.

The expression in the WHERE clause of this query is only one of the many such expressions in the WHERE clause of my actual query where I'm facing this problem.

Can somebody please help?

Thanks in advance,
Anchal.

 

[sedj]

maybe something like :

public void doSQLStuff(String[] emps) {

   String placeHolders = "";
   for (int i = 0; i < emps.length; i++) {
	placeHolders += &quot;?,&quot;;
   }
   placeHolders = placeHolders.substring(0, placeHolders.length());

   String staticSQL = &quot;select * from emp where emp_names in (&quot; +placeHolders +&quot;)&quot;;
   
   // Set up your Connection and PreparedStaement stuff ...
   
   for (int i = 0; i < emps.length; i++) {
   	preparedStatement.setString(i,emps[i]);
   }
   
   // Finish up ...
}

 

[anchal]

Hi,

Thanks for your quick reply. My problem is that I have around 10 such IN operators in my SQL querry. Also I am expected to keep my query static. The solution you have provided is good but makes my query dynamic because the placeholder part is generated at runtime and appended to base query string.

Is there any other solution to this problem?

Thanks,
Anchal.

 

[sedj]

What you require then is contradictory - on the one hand you wish to have a static SQL statement, but then also want to be able to change the number of in parameters - which logically means you have to change your SQL ?!

Why can you not change the number of in parameters on the fly ?

Have you consider using a stored procedure, and handling the "dynamic" number of in parameters that way.

 

[anchal]

Hi,

Thanks. I just found an answer to my query at the following link. I am trying out this option.

http://www.standardreporting.net/survival/view.aspx?_@id=53446

Regards,
Anchal.