Re: Jdbc3PoolingDataSource and Statement relationship???

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