Statement pooling implementation (was Re: Under what circumstances does PreparedStatement use stored plans?)

James Robinson wrote:
>
> 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.
>
>
> Diving in, I see that Postgres's PREPARE statement needs the types
> up-front, as in:
>
> PREPARE t_prep (TEXT) as select id from users where name = $1;
>
> But PreparedStatement doesn't know the JDBC datatypes (which we map onto
> Postgres types) until execute time. Hence I guess AbstractJDBC1Statement's
> delaying of server-preparing until execute-time, when all of the params
> have
> been pushed in.

Yes.

In actual fact this is probably broken in the current driver -- it
should discard any existing server-side prepared query if the parameter
types change, but currently I don't think it does.

> A global cache of prepared PreparedStatements seems to have to assume that
> subsequent calls ought to be executed with the same param types. Is this
> too
> bold of an assumption -- I know that in my EJB code this will hold, but
> I don't
> know about others.

> Unless the key to the map of cached prepared statement also incorporates
> the type signatures of each param. Ugh.

You can probably get away with ignoring the parameter types altogether
and just key on (query,resultset type,resultset holdability). If the
actual parameter types change (i.e. you gave out a pooled statement with
the right query but the "wrong" types) then you don't get the benefit of
PREPARE, but you do still get correct behaviour (assuming the underlying
statement behaves correctly, see above).

> That would push fetching from the map
> until the actual executeQuery() call on PreparedStatement, as opposed to
> at Connection.prepareStatement(String queryPattern) time -- which I
> can semi-gracefully hook into in AbstractJdbc3Connection.java to
> keep JDBC1/2 unaffected. The executeQuery() hooking looks harder to
> implement without poisoning JDBC 1/2.

I'd leave the core driver alone and look at implementing the pooling in
the ConnectionPoolDataSource layer. There are already proxy Connection
and Statement objects created by PooledConnectionImpl; you should be
able to intercept Connection.prepareStatement() and Statement.close() to
manage the statement pool. The pool itself ends up attached to a
PooledConnectionImpl.

> Unrelated, what is the philosophy regarding putting what type of code in
> the AbstractXXX class as opposed to the JDBCNXXX class? I'm sure there's
> a good reason, but it escapes my grasp at the moment ...

The AbstractJdbcN classes have code that:

a) can be compiled under JDBC version N or above
b) isn't a concrete class

They are compiled for all JDBC versions >= N.

The JdbcN classes are thin wrappers that provide the actual
implementation of the JDBC interfaces for a specific JDBC version M.
They are compiled for JDBC version M only.

The reason for this split is that if you have a concrete class that
implements only the JDBC1 versions of the JDBC interfaces, it's not
going to compile under JDBC2 as the extra parts of the JDBC interfaces
present in JDBC2 are not implemented. But we want to share as much code
as possible between JDBC versions, and we can't just use the same code
under all versions as (for example) there are extra constants and
interfaces present only in JDBC3 that JDBC3-implementing code must
reference.

So we make the main implementation classes abstract, have a hierarchy of
classes that implements the various JDBC levels, and "tap off" the
appropriate level as a concrete implementation depending on the version
of JDBC in use when compiling.

-O