Doubt about PreparedStatement

[Vidya Sankar]

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.

[stu derby]

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]

Thank You Very Much "Stu Derby" for a very nice and concise explaination. It was very helpful..

Sankar