Is statement.close() explicitly required in connection pooled environment?

Question

I am using connection pooling in my application. My question is:

Is it explicitly required to close statement before closing connection in case of connection pooled environment?

In connection pooled environment connection is not getting closed, (but returns back to free connection pool). I had checked jdbc 4.0 functional specs. In point number 9.4.4, it clearly states that :

Closing Connection Objects An application calls the method Connection.close to indicate that it has finished using a connection. All Statement objects created from a given Connection object will be closed when the close method for the object is called. Once a Connection has been closed, any attempt to access any of its methods with the exception of the close, isClosed or isValid methods will result in a SQLException being thrown.

So jdbc specs mandates closing all statement at a time of closing connection. So is it applicable to only non connection pooled environment only or it applies to connection pooled environment also ?

According to me it should not matter in case of pooled environment, because we are coding for interface (java.sql.Connection & java.sql.Statement). So we are not bothering about implementation and parent class (java.sql.Connection) doesn't have any information about child/impementation class (Vendor implementation class).

Answer 1

Absolutely. It's possible that the Statement implementation will have other resources which should be released, or have some other relationship with the connection. You don't, and shouldn't, know the implementation details.

Your approach is absolutely right: code to the interface and avoid little "short-cuts" which could easily bite you later. (Even if it works now, it might not in a future version of the pool or connection classes.)

Answer 2

Any object which has close(), release(), destroy(), etc, suggests automatically(of course you should read the API documentation, there might be different names used for this purpose) that the object needs an invoke of this method to ensure that the object resources are released when the object is not in use anymore.There will be no reason to provide such a method if the object can do that by itself.

In case of pooled java.sql.Connection, the connection is not really closed, is just pushed back in the pool as available connection but this is internal stuff aka you should not care.

Answer 3

In my experience, some JDBC drivers have bugs. They seem to work best if you close all Statements (and ResultSets) manually. Otherwise I have seen resource leaks that shouldn't have persisted past the closing of the Connection.