Thread: Jdbc3PoolingDataSource and Statement relationship???

Derek Dilts wrote:
> To whom it may concern:
>
> I am getting a Connection from Jdbc3PoolingDataSource.getConnection(),
> creating and executing a statement, then making a call to
> Connection.close().
>
> My question is this ... are those statements being closed by Postgres's
> pooling implementation like they should be, or do I have to keep track
> and close each Statement b/4 closing the Connection.

You should close your Statements when you no longer need them as a
matter of course -- they may be holding DB resources your application is
not avare of.

> I have read the documentation about pooling and Postgres's
> implementation of the DataSource interface,
> http://www.postgresql.org/docs/7.3/static/jdbc-datasource.html.  I am
> aware that by using the pool, Connections actually never close and
> remain open for the life of the pool. However, I am assuming that when
> my application calls Connection.close(), any associated Statements will
> be cleaned-up/closed. Is this assumption correct? If so, please point me
> to any documentation that might explain this.  If not, please advise!

Connection.close() seems to have different semantics when the
implementation is one returned by a PoolingConnection (although the spec
is, as always, annoyingly vague about it). Namely, closing such a
connection obviously *doesn't* release all the DB/JDBC resources
associated with it (as Connection.close() claims), since the underlying
connection is not actually physically closed. The driver may also be
doing statement pooling, in which case it doesn't make sense to close
down Statements whenever a connection pool client returns the connection
to the pool.

So -- I wouldn't rely on it. Close your Statements by hand when you no
longer need them.

-O

On Mon, 23 Feb 2004, Derek Dilts wrote:

> To whom it may concern:
>
> I am getting a Connection from Jdbc3PoolingDataSource.getConnection(),
> creating and executing a statement, then making a call to
> Connection.close().
>
> My question is this ... are those statements being closed by Postgres's
> pooling implementation like they should be, or do I have to keep track
> and close each Statement b/4 closing the Connection.
>
> I have read the documentation about pooling and Postgres's
> implementation of the DataSource interface,
> http://www.postgresql.org/docs/7.3/static/jdbc-datasource.html.  I am
> aware that by using the pool, Connections actually never close and
> remain open for the life of the pool. However, I am assuming that when
> my application calls Connection.close(), any associated Statements will
> be cleaned-up/closed. Is this assumption correct? If so, please point me
> to any documentation that might explain this.  If not, please advise!
>

There is no documentation available on this subject, but a simple test
tells us it can't be used any longer:

Connection con = getDataSourceConnection();
Statement stmt = con.createStatement();
con.close();
stmt.getFetchSize();

java.sql.SQLException: Statement has been closed
    at org.postgresql.jdbc2.optional.PooledConnectionImpl$StatementHandler.invoke(PooledConnectionImpl.java:382)
    at $Proxy5.getFetchSize(Unknown Source)

This doesn't mean that the Statement implementation has actually been
closed, but just that it is no longer accessable.  The actual Statement
will get cleaned up by garbage collection as long as you don't keep any
references to that Statement object around.

In general this is fine because only "broken" applications keep unusable
Statements in scope.  Dave Cramer has talked about explicitly closing
Statements on a Connection close, but has not done anything about it
because of the significant "why bother" response.

Kris Jurka