Re: Under what circumstances does PreparedStatement use stored

James Robinson wrote:

[... maintaining a cache of prepared queries ...]

> So, ultimately, when JBoss checks out a connection from the datasource, 
> that connection may well already have a server-side prepared plan for 
> the query it is about to make, and/or increase the chances that this 
> query will be prepared in the future.
> 
> This sort of system would violate the contract of Statement.close(), 
> which should free up any resources, client or server side, associated 
> with this statement. But without it, I can't see how prepared statements 
> could ever really be used effectively in a pooled datasource scenario 
> outside of the occasional method that knows it is going to fire off the 
> same query many times in a loop. What I'd like to see use prepared 
> queries would be (at least) our finder methods that do many joins -- a 
> place where the planning cost might well be non-negligable.

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.

Actually, you don't have to do this in the driver itself at all -- you 
can do it in your connection pooling layer if you proxy the 
PreparedStatement objects handed out to clients. close() on a proxy 
returns the underlying statement to a per-physical-connection pool of 
statements rather than actually closing it. Then the driver can just 
store prepared-query-plan info per PreparedStatement as it currently does.

-O