File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
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
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
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3716
    
    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
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30960
    
158

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
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3716
    
    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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: How to create prepared statement for query with clause where in