• 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

PreparedStatment for Search Queries

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

In my application I have a search page in which the user can enter various search criteria. For example on one of the search pages there are 5 input fields. 2 of them are date fields one is numeric and the 2 are text fields. The user can enter search values in any of the fields. assuming the entered value is valid, I create a select statement. Now if I use the Statement class to search the database creating the string is easy. But I am trying to figure out an elegant way to do the same for PreparedStatement. Before I go into why I feel Statment is easier than PreparedStatement I just want you to know that the reason I want to use PreparedStatement is because from what I have understood, it will give me a better performance. If it is not so in this situation, please let me know.

Now, my case!:

Say the user enters 'Frank', 123 & 3/3/2003 as the 3 things he wants to search on, then using Statment I can easily do this using a logic as follows. (for now lets ignore the logic for anding of the various criterias)


And that's it if I Use Statment. If I were to use PreparedStatment I would have to do something like the following

I don't think this is a good solution, and I am open to comments and suggestions. Hope to hear your opinions soon.
Thanks.
- Poorav
 
Ranch Hand
Posts: 399
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
In a separate thread, Anurag Gupta provided a link to a page regarding Statement vs. PreparedStatement Performance.
Since you are creating new PreparedStatements every time someone does a search, you probably won't get any performance gains. In your case it might be easier--and just as fast--to use a Statement.
That said, one main advantage of using a PreparedStatement in this situation is letting the database drivers take care of formatting the string and text data. What if the user name is O'Leary, with a single quote. That will mess up your Statement, but would be handled fine with a PreparedStatement.
If you do chooose to use a Statement, make sure you can handle the "odd" cases.
[ December 12, 2003: Message edited by: Wayne L Johnson ]
 
(instanceof Sidekick)
Posts: 8791
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I had a similar search back in my mainframe days where prepared statements were the only thing allowed. I made a "bitmap" to represent which fields were filled in:

Then it was a simple case statement based on map value to get a prepared statement or detect invalid combinations of fields:

As mentioned in the article referenced above, it usually takes a lot of iterations for a prepared statement to pay off. In a server situation you might be able to create one and use it all day long but in the typical client you'd be hard pressed to get much advantage.
Does that sound useful?
[ December 12, 2003: Message edited by: Stan James ]
 
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
My preference would still be to use PreparedStatements. This is however based on some criteria that is different from most people. Most of our code relies on frameworks or tools where PreparedStatements are enforced.
Besides that, there was one other point that I wanted to make. If you have to make 65 (or even 100) inserts for a PreparedStaement to be more efficient than a Statement, the gain over that small a number of operations is hardly worth bothering about. PreparedStatements are better in the long run, and if you aren't going to perform enough operations, the database operations aren't going to take up enough time for you to bother about the performance impact anyway!
Your case is slightly different since your PreparedStatements are dynamincally generated, but you only output a small (finite) set of Strings to parse, so I dont think this significant. O(4n) is still O(n)
Dave
 
Ranch Hand
Posts: 82
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Some application server provide prepared statement caching , so I think if your are using the same query again then using prepared statement would probably be better
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic