File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Lists in prepared statements Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Lists in prepared statements" Watch "Lists in prepared statements" New topic
Author

Lists in prepared statements

pjoisha
Ranch Hand

Joined: Mar 06, 2001
Posts: 123
Hi all,
I was wondering if anyone has elegant solutions in(or come across) handling IN clauses in prepared statements. Specifically I have this type of query
sql = "SELECT myname,mycity FROM mytable WHERE mycity IN (?,?)"
In here my (?,?) list is determined at runtime.
Thanks in advance.
PJ
Adam Hardy
Ranch Hand

Joined: Oct 09, 2001
Posts: 566
I doubt there's any point in using a prepared Statement for this. You would have to generate the sql statement (i.e. the number of commas in (,,,...)) at runtime as well, so you might as well use a normal statement.
I don't know whether it's database specific or much about it at all, but there is a setArray() function on preparedStatement - I was thinking somehow it might be useful here?

Adam


I have seen things you people would not believe, attack ships on fire off the shoulder of Orion, c-beams sparkling in the dark near the Tennhauser Gate. All these moments will be lost in time, like tears in the rain.
Byron Estes
Ranch Hand

Joined: Feb 21, 2002
Posts: 313
Why not just put a single comma in the prepared statement that will represent the entire list of items to be includind in the IN clause.
Before you update the prepare generate a list as a using a StringBuffer and then use a toString() method on it when you pass it to the prepared statement for substitution.


Byron Estes<br />Sun Certified Enterprise Architect<br />Senior Consulant<br />Blackwell Consulting Services<br />Chicago, IL<br /><a href="http://www.bcsinc.com" target="_blank" rel="nofollow">www.bcsinc.com</a>
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879


the query executed usually translates to "select * from table where id in ( 'value1, value2, value3' )" which doesn't work the way you intended. The next logical attempt would be to try to set the ? to "value1', 'value2', 'value3" but the single quotes are usually escaped and the result is "select * from table where id in ( 'value1'', ''value2'', ''value3' )" which won't work as you intended either. The last remark about this is that PreparedStatements are used to optimize recurring SQL where only the values of variables change, not the number of values. You'd probably lose any value added performance of PreparedStatements on the database side even if you found a work around.
Use a Statement, create the SQL string dynamically, run your own ' escape sequence, then execute the statement.
I use the word 'usually' in the context that each implementation is different and you may find a way to 'fool' your driver, but probably losing any performance gains on the database side.
Jamie
[ July 12, 2002: Message edited by: Jamie Robertson ]
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

Originally posted by Adam Hardy:
... but there is a setArray() function on preparedStatement - I was thinking somehow it might be useful here?

Adam
The setArray() method is used for setting the PreparedStatement parameter to a value that maps to a column of type SQL ARRAY. Not what we want here
[ July 12, 2002: Message edited by: Jamie Robertson ]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Lists in prepared statements