aspose file tools*
The moose likes JDBC 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
Bookmark "Can you use NOT IN ( ?) with PreparedStatements?" Watch "Can you use NOT IN ( ?) with PreparedStatements?" New topic
Author

Can you use NOT IN ( ?) with PreparedStatements?

SAFROLE YUTANI
Ranch Hand

Joined: Jul 06, 2001
Posts: 257
I have to execute the following SQL...
DELETE FROM EMPLOYEE_OBJECTIVE WHERE OBJECTIVE_ID NOT IN ( ?)
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,
SAF
Daniel Dunleavy
Ranch Hand

Joined: Mar 13, 2001
Posts: 276
FYI....
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.
Dan
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?

Jamie
 
Don't get me started about those stupid light bulbs.
 
subject: Can you use NOT IN ( ?) with PreparedStatements?