Solved

jdbc preparedStatement "IN" ?

Posted on 2003-11-21
20
5,863 Views
Last Modified: 2012-05-04
I have a statement I'd like to prepare that includes an IN clause.  Say... I want to find all orders IN (?), where ? is an unknown number of order ids.  Is this possible with PreparedStatements, or do I need to handbuild it?  ? could range from 1 to several hundred numbers.  I'm currently concatenating a String, which rather defeats one of the purposes of having a PreparedStatement.  I tried setting the value with setArray and setString, neither of which did much good (I didn't expect setString to work, but... worth a try, eh?)

I'm using postgresql 7.2 at home and mysql 4.0.13 at work, so would appreciate answers for either if there's a difference... (driver dependant?)
0
Comment
Question by:nentwined
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
  • 3
  • +2
20 Comments
 
LVL 15

Expert Comment

by:dualsoul
ID: 9799863
JDK javadocs quote:
............................
the setter methods (setShort, setString, and so on) for setting IN parameter values must specify types that are compatible with the defined SQL type of the input parameter. For instance, if the IN parameter has SQL type INTEGER, then the method setInt should be used.
............................

or i didn't understand your problem?
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 9800102
>>or do I need to handbuild it?

If i interpret your requirement correctly (see below) the answer is yes i'm afraid:

select * from sometable where somecolumn in (x, y, z)

Straight StringBuffer appending will do the trick
0
 

Author Comment

by:nentwined
ID: 9800406
:pounds head on wall:  Thanks.

Any thoughts on how I could possibly restructure the query?

Do you know whether it would be more time efficient to create the prepared statement once (as opposed to each time the in(...) values change, as "select * from sometable where somecolumn = " and run that prepared statement through the loop of values?

Just seems like a horribly simple oversight in how things work.

[especially when it's already inside of a loop that happens some thirty thousand times, creating a prepared statement is a slow process on a really fast sql server box :/]

Maybe it's not worth pretending it's a prepared statement when only some of the fields are being populated properly?
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 92

Expert Comment

by:objects
ID: 9800416
Try:

select * from sometable where somecolumn in (?, ?, ?)

> I'm currently concatenating a String,
> which rather defeats one of the purposes of having a PreparedStatement.

You'll still need to concatenate a String, but this doesn't defaeat the purpose of a PreparedStatement which is primarily to allow the same staement to executed multiple times more efficiently.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 9800438
>>select * from sometable where somecolumn in (?, ?, ?)

That's OK if the number of parameters remains constant

>>You'll still need to concatenate a String

Why, if the above is the case?

>>
the purpose of a PreparedStatement which is primarily to allow the same staement to executed multiple times more efficiently.
>>

This is true, meaning but the number of parameters has to be constant
0
 
LVL 92

Expert Comment

by:objects
ID: 9800445
Actually it may almost defeat the purpose as I think it'll need to precompile a new statement whenever the no of params differs.
A prepared statement still handles escaping parameters correctly for u though.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 9800470
>>
Actually it may almost defeat the purpose as I think it'll need to precompile a new statement whenever the no of params differs.
>>

Exactly - that's my point. But let's not get theoretical - *is* the number of parameters constant nentwined?
0
 
LVL 92

Expert Comment

by:objects
ID: 9800488
> But let's not get theoretical

who's getting theoretical?
i think you have misunderstood what i said.
0
 
LVL 92

Expert Comment

by:objects
ID: 9800499
Even with variable number of parameters PreparedStatement's will have advantages.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 9800504
I'm talking about speculating on fixed # parameters | variable # parameters
0
 
LVL 92

Expert Comment

by:objects
ID: 9800509
I have made no assumptions on that in any of my comments.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 9800517
>>PreparedStatement's will have advantages.

They'll take a performance hit for a potentially unnecessary parameter escapement advantage
0
 
LVL 92

Expert Comment

by:objects
ID: 9800544
A fairly insignificant hit I would say.
0
 

Author Comment

by:nentwined
ID: 9800555
:blink: This got popular all of the sudden.  Thanks :)

The number of parameters is not constant, except in that it would be were it one "array".  However (I need to revisit the code in the one case that's really slowing down), it may be splittable into three or four cases, and that could save a lot of time.
0
 
LVL 92

Accepted Solution

by:
objects earned 100 total points
ID: 9800590
Reuse of PreparedStatements that use the same # params would provide performance improvements. Perhaps keep a cache map of your statements (Integer, PreparedStatment).
0
 
LVL 86

Assisted Solution

by:CEHJ
CEHJ earned 100 total points
ID: 9800596
If you're looping quite a bit, then do split it into n cases with PreparedStatements for each.
0
 

Author Comment

by:nentwined
ID: 9800634
cache map sounds like a great idea in general.  I'll definitely have to wrap a library routine around that because I do such things (INs with variable arguments) frequently.  I think I can even go so far as to say, in this specific instance, that I can get away with one prepared statement with five variables (for the IN).  and assign the variables some non-findable value if I don't want to use them.

Thanks much for the brainstorms. :)
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 9800642
:-)
0
 
LVL 92

Expert Comment

by:objects
ID: 9800674
0
 
LVL 2

Expert Comment

by:pradeepp12
ID: 24592240
Can anyone give an example for the accepted solution? Not able to figure out how the syntax should be
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Html Table Looping (part 2) 5 41
Eclipse Java import and method not resolved 4 128
DTD and JAVA versions 1 55
Bitwise and to sum elements 2 52
This was posted to the Netbeans forum a Feb, 2010 and I also sent it to Verisign. Who didn't help much in my struggles to get my application signed. ------------------------- Start The idea here is to target your cell phones with the correct…
Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:
Suggested Courses

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question