Connection behavior - DriverManager.getConnection() and DataSource.getConnection()

Question

If I get a connection object using DriverManager.getConnection() and DataSource.getConnection(), how they differ in behavior when .close() is called on those objects?

Before .close() method call, I got relevant Statement and ResultSet objects from these two different connections. Soon after getting these two objects, if I say connection1.close() (through DriverManager.getConnection()), it will nullify the connection object and I'm not supposed / allowed to access the relevant Statement and ResultSet objects. Correct me if I'm wrong?

Second scenario, now if I say connection2.close() (through DataSource.getConnection()), it simply returns it back to the pool. But the connection is still live. Will I be able to access the associated Statement and ResultSet objects?

Answer 1

If we assume a (basic) DataSource (that is: one that does not do connection pooling), then you obtain a physical connection that is the same as one obtained from DriverManager (some drivers even internally use DriverManager from the DataSource, or a DataSource from DriverManager). So those connections will behave identically.

Now if we assume a DataSource that provides connection pooling, then the DataSource itself uses a ConnectionPoolDataSource (or a similar internal mechanism) to obtain a PooledConnection. This PooledConnection manages the actual physical connection to the database.

When a user requests a connection from the DataSource, the DataSource will checkout a PooledConnection, and ask it for a Connection. The PooledConnection will then create a logical connection that uses or wraps the physical connection (eg using a Proxy). The DataSource will return that logical connection to the user.

To the user the logical connection should behave identical to a physical connection in all aspects. So when a user closes the connection, that logical connection and all dependent JDBC objects will be closed and behave identical to a physical connection close.

JDBC 4.1 section 11.1 says:

Connection pooling is completely transparent to the client: A client obtains a pooled connection and uses it just the same way it obtains and uses a non pooled connection.

And section 11.4:

If the application attempts to reuse the logical handle, the Connection implementation throws an SQLException.

and

For a given PooledConnection object, only the most recently produced logical Connection object will be valid. Any previously existing Connection object is automatically closed when the associated PooledConnection.getConnection method is called.

In the background however, when the logical connection is closed, the PooledConnection will signal the DataSource that it is available for reuse, and the DataSource will then return it to the connection pool, or close the PooledConnection (which closes the physical connection) if it no longer needs the connection.

The DataSource can also forcefully revoke a connection from a user (eg when a connection is checked out too long, etc), by asking the PooledConnection to close the logical connection.

Answer 2

connection1.close() (through DriverManager.getConnection()),

This will close the physical connection established to the database and all the resources viz. Resultset, Statement, Connection are released. So, you cannot access them after the connection is closed.

connection2.close() (through DataSource.getConnection())

This is DataSource-implementation dependent and so the behavior need not be consistent across different DataSource implementations. Also, within a given DataSource implementation, the connection's actual life cycle is dependent on various other parameters that it is strongly recommended to not differentiate this Connection from the one obtained through DriverManager.

If you really want the data held in the ResultSet to be available after the Statement and Connection are closed, you can take a look at CachedRowSet if that fits your usecase.