Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Lists in prepared statements

 
pjoisha
Ranch Hand
Posts: 124
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 567
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Byron Estes
Ranch Hand
Posts: 313
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic