[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

PreparedStatement - "in" query problem

Posted on 2006-05-08
28
Medium Priority
?
423 Views
Last Modified: 2010-05-18
Hi,

I have a prepared statement on an in query - something like this

select * from customers where id in ?

I want to pass a string like "1,2,3" to the setXXX function. The problem being that PreparedStatement will qoute the string and make the query become

select * from customers where id in "1,2,3"

instead of

select * from customers where id in 1,2,3

I would like to know is there a way to stop PreparedStatement from quoting the parameter.

-wings
 

0
Comment
Question by:wings_gaurav
  • 12
  • 8
  • 5
  • +1
26 Comments
 
LVL 86

Expert Comment

by:CEHJ
ID: 16630828
You can't use PS for sets AFAIK. You'll have to do it 'manually'
0
 
LVL 5

Author Comment

by:wings_gaurav
ID: 16630884
not using a prepared statement is not an option. Anyway that this can be accomplished without making changes to the driver code - i have done that and in my opinion is an ugly hack.

-wings
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 16630919
>>not using a prepared statement is not an option.

It doesn't mean you can use a PS - you just can't use the params
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 5

Author Comment

by:wings_gaurav
ID: 16630956
i have to pass the params as a set in a single parameter as i don't know the number in the set at the time the prepared statement query is formulated.

I can't generate this query when i have the list of parameters to pass - the query has to be made much before.

Any ideas??

-wings
0
 
LVL 30

Expert Comment

by:Mayank S
ID: 16631046
You can use a CallableStatement with a stored-procedure and CallableStatement is also a PreparedStatement.
0
 
LVL 5

Author Comment

by:wings_gaurav
ID: 16631075
have to use a PreparedStatement - nothing else is an option - no stored procedure / no CallableStatement/ no regular statements.

-wings
0
 
LVL 30

Expert Comment

by:Mayank S
ID: 16631100
That's why I said CallableStatement is a PreparedStatement (as it extends it), so technically its not wrong ;-)
0
 
LVL 5

Author Comment

by:wings_gaurav
ID: 16631124
doessn't work - i have a limitation of not using CallableStatement - can't inject code into database

-wings
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 16631135
All you need to do is append String-wise, the set, to the end of the query
0
 
LVL 5

Author Comment

by:wings_gaurav
ID: 16631158
elaborate  - i don't want to append or conditions to the query - it destroys the advantage of using the prepared statement!

-wings
0
 
LVL 30

Expert Comment

by:Mayank S
ID: 16631222
>> select * from customers where id in "1,2,3"
>> select * from customers where id in 1,2,3

I think you need to create the prepared statement after you get your parameter.

String[] params = parameter.split ( "," ) ;

Add as many ?s to the prepared-statement as params.length (separated by params.length - 1 commas)

Then loop through (0 through params.length - 1) and then set the parameters for your prepared-statement.
0
 
LVL 5

Author Comment

by:wings_gaurav
ID: 16631238
From an earlier comment -

>I have to pass the params as a set in a single parameter as i don't know the number in the set at the time the >prepared statement query is formulated.

>I can't generate this query when i have the list of parameters to pass - the query has to be made much before.

-wings
0
 
LVL 30

Expert Comment

by:Mayank S
ID: 16631398
Can't be done AFAIK because the driver will use the DB specific separator for String, be it '' or "". So you will have to generate the query most probably.
0
 
LVL 5

Author Comment

by:wings_gaurav
ID: 16633060
from what i have researched and the comments above it seems that it is a JDBC limitation!!!

I have an inkling that if a custom type is defined using SQLData interface then this might be possible - any comments/answers?

-wings  
0
 
LVL 30

Expert Comment

by:Mayank S
ID: 16636479
>> it seems that it is a JDBC limitation!!!

Not really. You are setting 1 parameter in it using ? and then trying to fool the driver by passing more than 1 value to it ;-)

Think about it, if that is the way the driver should behave, what will happen to normal queries? Like if you actually have a query where you want to pass a String parameter as abc, it will be written in the query as abc instead of "abc" or 'abc' (whatever the DB syntax actually supports) and it will result in an SQL error - do you want that to happen?

I see no harm in creating the PreparedStatement on the fly based on number of commas because once created, it will still give you better performance than a Statement.
0
 
LVL 5

Author Comment

by:wings_gaurav
ID: 16637145
Well, theoretically i am passing an array of elements as an argument to an in clause - which makes it a single parameter.
I never said that i want to pass a STRING a,b,c - my intent is that the driver should interpret it as a,b,c. I would be happier if i could pass any one of the Set/List class or an array as a SINGLE param and it would take the value. I hope this clarifies!

I will not get into advantage/disadvantage of creating the query on the fly - that is not the point of major concern. I can't explain in simple terms "why" - but i have the limitation to work with - PreparedStatements only and stored procedures.

-wings
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 16637267
>> it seems that it is a JDBC limitation!!!

No, AFAIK it's an SQL limitation. The advantage of bind variables is that the statement can be parsed and optimized by the db and then have the values passed to it. This can't be done if the number of arguments is variable and it would have to be parsed anew each time it were executed. Therefore, gluing your set onto the end of the statement is not taking you any further away from the optimal than you would otherwise be anyway.
0
 
LVL 30

Expert Comment

by:Mayank S
ID: 16637420
>> PreparedStatements only and stored procedures

You said no stored procs earlier? If they are allowed, use CallableStatement.

>>  i am passing an array of elements as an argument to an in clause - which makes it a single parameter

No, in effect - it is more than one parameter. It might be int or String or anything else. But how will the driver know what value you are setting to it? If you call setInt (), it will assume it is one int value. If you call setString (), it will assume it is one String value. If you want the driver to behave the way you want right now, it will stop working properly for the simple and correct queries in the first place (like I explained earlier).
0
 
LVL 5

Author Comment

by:wings_gaurav
ID: 16637612
CEHJ - i am not getting into a discussion on optimizations - query path selection etc. So, i would urge that the statements/comments are kept on the primary subject. As i have said earlier - there other limitations that don't allow for use of anything else except PreparedStatements.

mayankeagle- my fault - typo - read it as no stored procedures. Also, I am not getting into whether it is more than one parameter or a single pram. Not part of the question.

Keeping it simple - Question is - need to pass multiple values (array of values) to in clause using prepared statement in JDBC. Does anybody know how to do this? - please limit your comments on "how" - if you think it is possible - otherwise let it go.

I have heard it at many places - comments here or at other places that read as can't be done. Well, i know there isn't a straight forward way of doing this

one way is to modify the driver.

other which i think might be possible would be to implement SQLData interface on an ArrayList extend.

For reference from JAVA doc of SQLData Interface
-----------
 A programmer can operate on this class instance just as on any other object in the Java programming language and then store any changes made to it by calling the PreparedStatement.setObject method, which will map it back to the SQL type.
-------------

so, i would request that people only post comments if they know the way forward on this line of thought or have an alternate line of solution.

Best Regards,
wings
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 16637650
>>one way is to modify the driver.

That won't help. Try to re-read my last comment and you'll see why
0
 
LVL 30

Expert Comment

by:Mayank S
ID: 16637661
I would not modify the driver because of: a) license agreement, b) other programs using the driver might stop working.
0
 
LVL 5

Author Comment

by:wings_gaurav
ID: 16637703
Mr. Mayank. The jdbc driver can operate in a web applications context - and I am free to modify the code of Mysql or Postgres drivers - which i have already done and works for me! - hope this answers the question! Again - all of this is largely irrelevant (license etc) to the question.

CEHJ - as I said I modified the code of the driver to remove quoting on an unused function in the application - like setBinaryStream , setBytes - and the solution works for me.  This is all repetition - yes, modification of the driver works for me on MySQL, Postgres etc but I want a better solution.

From reference from JAVA doc of SQLData Interface
-----------
 A programmer can operate on this class instance just as on any other object in the Java programming language and then store any changes made to it by calling the PreparedStatement.setObject method, which will map it back to the SQL type.
-------------

what i understand is that i can overwrite/define custom implementation for PreparedStatement.setObject for an object type. Anyway, if you don't agree it's okay by me - but i am interested in solutions not in "can't be done" comments. Especially ones like "modify the driver will not work" when it already works for me.

Regards,
wings
0
 
LVL 30

Expert Comment

by:Mayank S
ID: 16637769
>> and I am free to modify the code of Mysql or Postgres drivers - which i have already done

If you have the source code and the license allows you to do it, its fine. Though it sounds like re-inventing the wheel.

>> Especially ones like "modify the driver will not work" when it already works for me

Nobody said it won't work that way for this application. What we meant was that any other applications which are using the existing driver might start behaving wierdly, or the license might not allow you to do it (in production, this is an issue). Moreover, the implementation of those methods should be as per the Java specification (if you modify it, it might not be as per the specification). Also, I'm not sure how simple queries will behave in this case.

>> but i am interested in solutions not in "can't be done" comments.

The best solution I can think of is iterating and creating the prepared-statement based on number of commas.
0
 
LVL 5

Author Comment

by:wings_gaurav
ID: 16637809
Elaborating - the solution I think will work - I will try it today

Define a custom type and add it type map and using PreparedStatement.setObject to replace '?' with value

Steps:
1) Create a class extending ArrayList and implement SQLData.
2) Define the writeSQL function in the class to convert ArrayList values to comma separated values.
2) Get the connections TypeMap using Connection.getTypeMap
3) Add the custom type to and set the typemap using Connection.setTypeMap
4) Call PreparedStatement.setObject with parameter being and object of the class defined in step one

Relevant documentation from JAVA docs that substantiate this solution:
----------------------------------
PreparedStatement.setObject documentation

 
The given Java object will be converted to the given target SqlType before being sent to the database. If the object has a custom mapping (is of a class implementing the interface SQLData), the JDBC driver should call the method SQLData.writeSQL to write it to the SQL data stream. If, on the other hand, the object is of a class implementing Ref, Blob, Clob, Struct, or Array, the driver should pass it to the database as a value of the corresponding SQL type.
----------------------------------

---------------------------------
Interface  SQLData documentation

A programmer can operate on this class instance just as on any other object in the Java programming language and then store any changes made to it by calling the PreparedStatement.setObject method, which will map it back to the SQL type.
------------------------------

-wings

0
 
LVL 5

Author Comment

by:wings_gaurav
ID: 16845539
the answer given above by me works - so the question is closed.

-wings
0
 

Accepted Solution

by:
GranMod earned 0 total points
ID: 16918140
PAQed with points refunded (500)

GranMod
Community Support Moderator
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
In this post we will learn different types of Android Layout and some basics of an Android App.
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.
Suggested Courses
Course of the Month18 days, 17 hours left to enroll

834 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