This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JDBC and the fly likes How to create prepared statement for query with clause where in Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to create prepared statement for query with clause where in" Watch "How to create prepared statement for query with clause where in" New topic
Author

How to create prepared statement for query with clause where in

ashish kulkarni
Ranch Hand

Joined: Aug 15, 2002
Posts: 130
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');


A$HI$H
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3700
    
    5

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.


My Blog: Down Home Country Coding with Scott Selikoff
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30067
    
149

Me too. So I wrote an article on it.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3700
    
    5

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.
 
wood burning stoves
 
subject: How to create prepared statement for query with clause where in
 
Similar Threads
How to get total records
Query help
Help required for hibernate query (HQL) calling stored procedure
Help required for hibernate query (HQL) calling stored procedure
Convert this SQL to EJB QL