• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Can you use NOT IN ( ?) with PreparedStatements?

 
SAFROLE YUTANI
Ranch Hand
Posts: 257
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 276
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic