File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Can you use NOT IN ( ?) with PreparedStatements? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Groovy Fundamentals video training course this week in the Groovy forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Can you use NOT IN ( ?) with PreparedStatements?" Watch "Can you use NOT IN ( ?) with PreparedStatements?" New topic

Can you use NOT IN ( ?) with PreparedStatements?

Ranch Hand

Joined: Jul 06, 2001
Posts: 257
I have to execute the following SQL...
The NOT IN clause may have many values, or it may have one like (2) or (2,6,9), but how do I use a PreparedStatement to set the values? I cant use multiple '?' because I dont know how many values might occure; it's dynamic.
catch ya later,
Daniel Dunleavy
Ranch Hand

Joined: Mar 13, 2001
Posts: 276
I believe if you use a "not in" your indexes will not be used. Try checking your database's manual to see what it says. If you have a very large database, it may take a long time to execute.
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

once you create a PreparedStatement you can't change it. So if you have the PreparedStatement of "SELECT * from emp where empid in (?)", you can only substitute one value for the ?. There is no way (as of jdbc 2.0) to substitute an array of values for a '?'. The only way you could work it would be to dynamically create the sql String for each query(pretty much like using a Statement).
Probably not what you want to hear
maybe you can make up the performance by batching the Statements?

I agree. Here's the link:
subject: Can you use NOT IN ( ?) with PreparedStatements?