Should I close a Connection obtained from a DataSource manually?

Question

When I get a Connection from the DataSource, should I close it manually? I mean in case I must close it, how it will be used in future requests?

Answer 1

A connection obtained from a connection pool should be used exactly the same as a normal connection. The JDBC 4.2 specification (section 11.1) says about pooling:

When an application is finished using a connection, it closes the logical connection using the method Connection.close. This closes the logical connection but does not close the physical connection. Instead, the physical connection is returned to the pool so that it can be reused.

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.

(emphasis mine)

This means that when you are done with a connection, you always call Connection.close()! It doesn't matter if it is a physical connection, or a logical connection from the pool.

The reason is that whether a connection is a physical (direct) connection or a logical connection should be purely a matter of configuration, not a concern of the application code that merely uses the connection.

In the case of a connection pool, the close() will - details may vary, and some implementations are buggy in this respect - invalidate the logical connection and signal to the connection pool that the underlying physical connection is available for re-use. The connection pool may do some validity checks and then return the (physical) connection into the pool or close it (eg if the pool has too many idle connections, or the connection is too old, etc).

Calling close() is not only allowed, it is even vital for the correct working of a connection pool. Not calling close() usually requires some helper thread to close (reclaim) logical connections that have been in use for too long. As this timeout is usually longer than normal application needs, it might lead to exhaustion of the pool, or to configuration where the pool needs a higher maximum number of connections than is really necessary.

Answer 2

You should close Connection in order to return it to the pool, next time you'll ask for Datasource.getConnection() connection from the pool will be obtained. There is no problem here. Sometimes you don't want to close connection after each operation and use the same connection for several operations. In this case you shouldn't close it until last operation finished.