jdbc preparedStatement "IN" ?

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?)
nentwinedAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dualsoulCommented:
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
CEHJCommented:
>>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
nentwinedAuthor Commented:
: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
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

Mick BarryJava DeveloperCommented:
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
CEHJCommented:
>>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
Mick BarryJava DeveloperCommented:
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
CEHJCommented:
>>
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
Mick BarryJava DeveloperCommented:
> But let's not get theoretical

who's getting theoretical?
i think you have misunderstood what i said.
0
Mick BarryJava DeveloperCommented:
Even with variable number of parameters PreparedStatement's will have advantages.
0
CEHJCommented:
I'm talking about speculating on fixed # parameters | variable # parameters
0
Mick BarryJava DeveloperCommented:
I have made no assumptions on that in any of my comments.
0
CEHJCommented:
>>PreparedStatement's will have advantages.

They'll take a performance hit for a potentially unnecessary parameter escapement advantage
0
Mick BarryJava DeveloperCommented:
A fairly insignificant hit I would say.
0
nentwinedAuthor Commented:
: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
Mick BarryJava DeveloperCommented:
Reuse of PreparedStatements that use the same # params would provide performance improvements. Perhaps keep a cache map of your statements (Integer, PreparedStatment).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CEHJCommented:
If you're looping quite a bit, then do split it into n cases with PreparedStatements for each.
0
nentwinedAuthor Commented:
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
CEHJCommented:
:-)
0
Mick BarryJava DeveloperCommented:
0
pradeepp12Commented:
Can anyone give an example for the accepted solution? Not able to figure out how the syntax should be
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java

From novice to tech pro — start learning today.