wood burning stoves
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
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: http://aspose.com/file-tools
subject: Can you use NOT IN ( ?) with PreparedStatements?
It's not a secret anymore!