How to reset a JDBC Connection object (that is, a java.sql.Connection object)?
I have explored the concept of connection pooling. When a connection pool is used, a Connection object can be recycled. But how can a connection pool recycle a Connection object? I think that a connection needs to be “reset” (for example, if it is in a transaction, then perhaps rollback it, but there may be more things to reset) before it can be reused. But I cannot find such a “reset” method in the Java documentation about the class java.sql.Connection.
Advertisement
Answer
If you are talking about what you as a user of a connection should do, then that is simple: call close() on the connection. Closing the logical connection will signal to the connection pool that the connection is available for reuse, and the connection pooling manager is then responsible for performing the necessary reset, invalidation of statement handles, etc.
If you are talking about what you as the implementer of a connection pooling manager should do, that is where things become complicated.
Historically, JDBC provides no way to ‘reset’ a java.sql.Connection, other than by your own code (or your third-party connection pool) remembering the initial configuration, and restoring it after use, and keeping track of objects like statements and result sets and closing them when the connection is returned to the pool.
Originally, the intended way to reset connections in JDBC was for an application server to use a driver’s javax.sql.ConnectionPoolDataSource as a factory for javax.sql.PooledConnection objects. These PooledConnection objects serve as handles for physical connections to be held in a connection pool (to be clear, ConnectionPoolDataSource is not a connection pool, it is a data source for a connection pool). The application server would then expose a javax.sql.DataSource handing out logical Connection objects, where on Connection.close(), the driver specific implementation of PooledConnection would take care of any necessary reset of a connection (though JDBC underspecifies what is ‘necessary’).
However, in practice this route is hardly ever used because support in drivers was (and often still is) spotty, inconsistent or downright incorrect, and JDBC wasn’t clear enough on exactly what needed to be done when the logical connection was closed. The world has also shifted to using third-party connection pool libraries that do not use ConnectionPoolDataSource.
JDBC 4.3 (Java 9 and higher) introduced the methods Connection.beginRequest() and Connection.endRequest() to be called by connection pooling managers, which would seem to fit such pattern, but unfortunately JDBC 4.3 doesn’t actually specify what kind of things an implementation should or should not do in response to beginRequest and endRequest.
In short, there is no real general way to reset a connection in JDBC.