How to create prepared statement for query with clause where in

[ashish kulkarni]

I have an SQL query like below

select * from table1 where test in ('','123', '345');


how do i create preparedStatement for this, the issue is the number of values in in can be different for example, the query can be as below depending on what user selects on screen

select * from table1 where test in ('','123', '345');

or

select * from table1 where test in ('','123', '345', '1233');

or

select * from table1 where test in ('','123');

[Scott Selikoff]

You can do it with a fixed number of variables (such as "WHERE IN (?,?,?)" for 3 variables) but if the number of variables is not known at compile time, you have to build the string by hand. It makes for very messy code if you ask me and has been something I've been wishing JDBC would address for while.

[Jeanne Boyarsky]

Me too. So I wrote an article on it.

[Scott Selikoff]

As a side note, part of why I don't have problems with this anymore is I tend to avoid nested queries whenever possible. There are some cases where you still need "IN ()" syntax, but I avoid them so often I don't really notice this problem much anymore.