wood burning stoves*
The moose likes JDBC and the fly likes How to use Prepared statements when searching an unknown number of values Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to use Prepared statements when searching an unknown number of values" Watch "How to use Prepared statements when searching an unknown number of values" New topic
Author

How to use Prepared statements when searching an unknown number of values

Mark Wa
Ranch Hand

Joined: May 29, 2009
Posts: 122
Hello, I would like to use a prepared statements to do a select statement, however I do not know how many values I would need to compare against until runtime.

The statement would look like this

select * from table where column name like 'value1' or 'value2' or 'value3'

but I do not know how many values I may need to add in as the user will be selecting them. Currently I have built the statement as a string, but I need it to be resisteant against sql injection attacks.

Thanks,
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 39478
    
  28
Welcome to JavaRanch

Have you got such queries working at the command line? Can you display details of tables and their columns so you could choose columns to enter into your statements?
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

JDBC doesn't have a setArrayOfValues method (this is a driver/database restriction) so you'll have to do it yourself. You'll probably have to build your SQL dynamically (as you would a Statement) and keep track of the values passes to the query criteria then bind them at the end. So two loops should do it. To make things easier you might find that "in" clauses are easier to manage than multiple or conditions.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Mark Wa
Ranch Hand

Joined: May 29, 2009
Posts: 122
Paul Sturrock wrote:JDBC doesn't have a setArrayOfValues method (this is a driver/database restriction) so you'll have to do it yourself. You'll probably have to build your SQL dynamically (as you would a Statement) and keep track of the values passes to the query criteria then bind them at the end. So two loops should do it. To make things easier you might find that "in" clauses are easier to manage than multiple or conditions.



Would this not be inefficient? as a prepared statement is somehow stored, so that the database knows how to best access the data prior to the query being run
John Kimball
Ranch Hand

Joined: Apr 13, 2009
Posts: 96
The efficiency of the prepared statement really only comes into play if the same SQL query is reused in bulk--that is it needs to be re-used dozens, hundreds, or thousands of times in one session--in which case the same instance of the prepared statement is supposed to be re-used.

Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30789
    
157

See my article on select batching for the JavaRanch Journal. I wrote it in 2005, but it everything in it is still true.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: How to use Prepared statements when searching an unknown number of values