jQuery in Action, 2nd edition*
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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Can you use NOT IN ( ?) with PreparedStatements?
 
Similar Threads
Using bean after resultset
Number Format Exception
Simple Stateless Session Bean
How to use Prepared statements when searching an unknown number of values
Edit .Properties file in JSP