I used to believe that a PreparedStatement's efficiency benefit could be realized only if it was prepared once (incurring the overhead of preparing the statement) and then re-used, like this:But I recently read something (can't remember now) that made it sound like you would still get the efficiency benefits even if you create the ps every time you use it, like this:So even though I'm creating a new instance of a ps, the Connection is smart enough to realize that it already prepared that statement before and not incur the overhead of preparing the statement again.
Is that correct? Does the statement caching occur at an even lower level? i.e. if I'm using connection pooling (so a different connection may be used each time the ps is created), is the statement cached at the db level and available for any Connection to take advantage of it? Like in this example:
BTW - I realize there are additional benefits/reasons for using PreparedStatement (like proper quote escaping).
Blake Minghelli<br />SCWCD<br /> <br />"I'd put a quote here but I'm a non-conformist"
Yes, many Application Servers (such as WebLogic, WebSphere, and even Commons DBCP) support the notion of a PreparedStatement Cache for their Connection Pools so that you still get a performance enhancement even if you "create" the PreparedStatement each time. Regardless, like you mentioned, even without increased performance there are still compelling reasons to choose PreparedStatements over regular Statements.
Thanks Chris. Let me know if I understand it correctly. The connection given by a connection pool is just a connection wrapper, not a real connection. Connection conn = ds.getConnection(); When an application creates a preparedStatment, connection pool will look up the cache and return the connection which associated with the preparedStatement in the cache. This connection may or may not be the same as the connection above.