This week's book giveaway is in the OO, Patterns, UML and Refactoring forum. We're giving away four copies of Refactoring for Software Design Smells: Managing Technical Debt and have Girish Suryanarayana, Ganesh Samarthyam & Tushar Sharma on-line! See this thread for details.
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
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 ]
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
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