[markhorgan]

PreparedStatement makes sense if you are doing a lot of SQL calls but I was wondering is there any advantage in using it over Statement when you're just making one SQL call. The reason I'm asking is that I read somewhere that PreparedStatements are saved so I was wondering does the JVM save all PreparedStatements over an application's lifetime and if it comes across a piece of SQL it has already prepared will it re-use it even if the actual preparedStatement object is long out of scope?

[oscagne]

The idea with a prepared statement is that you can set the query but vary the parameters you pass in. With a statement you have to prepare it dynamically each time. So if for example you wanted a page to insert the content of a form into the db each time it is called then it would be far easier to declare a preparedstatement once and the fill it with the relevant data, than to instatiate a new one each time. Instantiation is an expensive (in terms of time) operation, so multiple instantiation vs a single preparedstatement would be a lot faster.

The next step along is to create callablestatments and get them to call database stored procedures which will be faster as they will be precompiled in the database, and have far more flexibility aswell

Was that any help?

Oscagne

[markhorgan]

Thanks for that So if I wanted to use a PreparedStatement across multiple users/threads I would have to declare it as a field of the servlet/JSP page rather than a local method variable.

[oscagne]

yeah, just put it as a static member variable