• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

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

 
Ranch Hand
Posts: 122
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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,
 
Marshal
Posts: 79177
377
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.

 
Mark Wa
Ranch Hand
Posts: 122
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
 
Ranch Hand
Posts: 96
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.

 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
See my article on select batching for the JavaRanch Journal. I wrote it in 2005, but it everything in it is still true.
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic