Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

PreparedStatement usage in a "NOT IN" clause?

 
SAFROLE YUTANI
Ranch Hand
Posts: 257
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have a query that contains a "NOT IN" clause for which I need to specify a variable number of values, such as (1,345,90,345), but I don't see a clear way to handle this using a PreparedStatement in JDBC 2. Using Statement is out of the question since there are other values required in the query as well.
Any ideas?
Thanks,
Raffi
[ June 26, 2003: Message edited by: SAFROLE YUTANI ]
 
SJ Adnams
Ranch Hand
Posts: 925
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
well you can do;
"select a from b where c = ? and d not in (1,2,3)"
you could also create the prepared statement string on the fly, and set all parameters using preparedstatement.
you could also create a stored proceedure & send an ARRAY type.
If I was making the choice, I'd go for the first one if the values where integer cuz I'm lazy.
 
SAFROLE YUTANI
Ranch Hand
Posts: 257
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm not sure what you mean by the first option. I cannot perform your example using a PS. My query is comething like the following..
select * from employee e where e.status = 'A' and e.emp_id NOT in (?)
The ? is dynamically assigned and can be any number of integers, which cannot be performed using PS.
Raffi
 
SJ Adnams
Ranch Hand
Posts: 925
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
e.g
String sql = "select a from b where c = ? and d not in(";
while(valsIt.hasNext())
sql = sql + valsIt.next() + ",";
sql = sql + "-1)"; // dummy val to ensure syntax
That is the easiest/laziest. Using a proper ARRAY (i.e. stored proceedure) is probably the better solution.
 
SAFROLE YUTANI
Ranch Hand
Posts: 257
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
HAHAH, that's so funny because I actually resorted to using that approach last night. I wasn't happy with it, but it works!
Thanks again,
Raffi
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic