Re: Under what circumstances does PreparedStatement use stored plans?

On Apr 13, 2004, at 5:18 PM, Oliver Jowett wrote:

> This sounds like JDBC3's "statement pooling" option. There's no API
> for this, it's just allowable behaviour, so we should be fine to
> implement something like this.

OK -- I read through the JDBC3 spec, and section 11.6 covers pooled
statements. Highlights include:

    "If a pooled connection reuses statements, the reuse must be
completely transparent to an application. In other words, from the
application’s point of view, using a PreparedStatement object that
participates in statement pooling is exactly the same as using one that
does not. Statements are kept open for reuse entirely under the covers,
so there is no change in application code. If an application closes a
PreparedStatement object, it must still call
Connection.prepareStatement in order to use it again. The only visible
effect of statement pooling is a possible improvement in performance."

    "An application may find out whether a data source supports statement
pooling by calling the DatabaseMetaData method
supportsStatementPooling."

    "In FIGURE 11-2 [ diagram of PooledConnection instances managing their
own pool of PreparedStatement objects ], the connection pool and
statement pool are implemented by the application server. However, this
functionality could also be implemented by the driver or underlying
data source. This discussion of statement pooling is meant to allow for
any of these implementations."

    Do I properly infer from this last statement that the pool of
PreparedStatements could be implemented by the app-server's connection
pool OR the JDBC driver itself (where I can do it reasonably) or in the
backend SQL server itself (which would be wicked voodoo indeed)?

    Funny, it gives no hints how to efficiently recognize if an equivalent
statement is being prepared for the Nth time.

    I'm gonna go for it. Sounds like a fun project.

----
James Robinson
Socialserve.com