File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes PreparedStatment for Search Queries Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "PreparedStatment for Search Queries" Watch "PreparedStatment for Search Queries" New topic

PreparedStatment for Search Queries

Poorav Chaudhari
Ranch Hand

Joined: Sep 17, 2002
Posts: 50

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.
- Poorav

Poorav Chaudhari
Wayne L Johnson
Ranch Hand

Joined: Sep 03, 2003
Posts: 399
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 ]
Stan James
(instanceof Sidekick)
Ranch Hand

Joined: Jan 29, 2003
Posts: 8791
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 ]

A good question is never answered. It is not a bolt to be tightened into place but a seed to be planted and to bear more seed toward the hope of greening the landscape of the idea. John Ciardi
David O'Meara

Joined: Mar 06, 2001
Posts: 13459

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)
Monmohan Singh
Ranch Hand

Joined: Aug 02, 2002
Posts: 82
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
I agree. Here's the link:
subject: PreparedStatment for Search Queries
It's not a secret anymore!