aspose file tools*
The moose likes JDBC and the fly likes Doubt about PreparedStatement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Doubt about PreparedStatement" Watch "Doubt about PreparedStatement" New topic
Author

Doubt about PreparedStatement

Vidya Sankar
Greenhorn

Joined: Aug 17, 2005
Posts: 20
PreparedStatement object contains not just an SQL statement, but an SQL statement that has been precompiled.

My Doubt is will the database still keep that precompiled SQL even though I close my connection and return it when I ask for the same PreparedStatement later.

I am creating a PreparedStatement within methods which inserts/select etc. I obtain a new Connection at the beginning of the method and obtain the PreparedStatement and execute my query. By the end of the method I close the PreparedStatement and Connection and therefore lose their reference. I did not want to hold the Connection resource for long intervals because the frequency with which these methods are called in indeterminate.

I am using PreparedStatement because it is convenient to set my parmeters.
and also hoping it will run faster in second executions.


Well I hope some one here can clarify the doubt - if it is advisable to use Statement or PreparedStatement in this scenario and also help me clarify my JDBC concepts.

Thank You.

Sankar.


SCJP 1.4
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333
If your SQL has something that will be a database value, then you should (almost) always use PreparedStatement. There are many reasons. Security, performance, better code.

The only exception I know of is for a very very rare performance issue that only affects some databases. Don't worry about this until you a SQL statement that's too slow.

Many databases cache previously parsed SQL; if your SQL contains data values, then it very likely won't match any SQL in the cache and will have to be re-parsed. For many queries, parsing is 50% to 99% of the time to execute the query on the database. Using PreparedStatement dramatically improves your cache hit rate on the database and makes some databases go faster even when each PreparedStatement (and its parent Connection) is used once and closed on the Java side.
Vidya Sankar
Greenhorn

Joined: Aug 17, 2005
Posts: 20
Thank You Very Much "Stu Derby" for a very nice and concise explaination. It was very helpful..

Sankar
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Doubt about PreparedStatement