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.
i understand that a PreparedStatement is precompiled but where is it stored and what is its lifetime? Is it stored by the database and the database manages its lifetime? Thus the db maintains a pool of PreparedStatement objects that it refers to? Is it a regular java object thus obeys the standard java object lifetime rules? If so, would there be any benefit to using a PreparedStatement object instead of a Statement object in a method that created a method local PreparedStatement object and used it once?
PreparedStatements (if supported) are completely owned and controlled by the database. The PreparedStatement class in JDBC is just to give you access to it. On a side note, MySQL doesn't have any concept of PreparedStatements and in this case they are managed by the Driver.
We are using PreparedStatements from a Solaris web server with the database on DB2V5/OS390. Your database analyst could probably answer your questions about how long it exists on the database. From the java side, I believe the PreparedStatement closes when the Connection Object is closed if you don't close it earlier. If it stays around on the database, I do not know how to re-use it from JDBC. That doesn't mean you can't though. We use PreparedStatement for all queries because each query is stored in it's own class and it allows us to parameterize them for re-use. A regular Statement does not allow parameters. (Is parameterize a real word? :roll: )