Solved

jdbc preparedStatement "IN" ?

Posted on 2003-11-21
20
5,838 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
  • 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
 
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

After being asked a question last year, I went into one of my moods where I did some research and code just for the fun and learning of it all.  Subsequently, from this journey, I put together this article on "Range Searching Using Visual Basic.NET …
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
Viewers learn about the “while” loop and how to utilize it correctly in Java. Additionally, viewers begin exploring how to include conditional statements within a while loop and avoid an endless loop. Define While Loop: Basic Example: Explanatio…
Viewers learn how to read error messages and identify possible mistakes that could cause hours of frustration. Coding is as much about debugging your code as it is about writing it. Define Error Message: Line Numbers: Type of Error: Break Down…

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now