22

I know the safe pattern in Java is to close your ResultSet, Statement, and Connection in order in a finally block.

If you close connection and then try to close statement(doesnt throw exception). But if you try to call any method from statement an exception is thrown.

I was wondering does closing connection automatically close all the statement objects created out of that connection?

Update:
I am using DatabaseProductVersion: Oracle Database 11g Release 11.1.0.0.0
DriverName: Oracle JDBC driver
DriverVersion: 10.2.0.4.0

Srujan Kumar Gulla
  • 5,721
  • 9
  • 48
  • 78
  • 2
    http://stackoverflow.com/questions/4507440/must-jdbc-resultsets-and-statements-be-closed-separately-although-the-connection – Aviram Segal Dec 24 '12 at 17:54

2 Answers2

19

Yes it does, Connection.close API says "Releases this Connection object's database and JDBC resources immediately instead of waiting for them to be automatically released". The problem is that applications typically use database connection pools and these may simply return Connection to pool on Connection.close.

In any case, it's a good practice to always close ResultSet and Statement explicitly and not to rely on Connection.close.

Besides, it's not the best idea to work with JDBC directly. You can use Spring JDBC instead and forget about releasing resources problem.

Evgeniy Dorofeev
  • 133,369
  • 30
  • 199
  • 275
  • 3
    A well written connection pool should close the related underlying resources (other than the connection itself) when the connection is closed. Well written client code should not depend upon that. In practice most things are badly written, and it's good practice for your code to come up smelling of roses. – Tom Hawtin - tackline Dec 24 '12 at 19:04
  • 1
    Right. I decided to test it with Apache's BasicDataSource: I opened conn, created stmt, closed conn and ran query. Got SQLException "com.mysql.jdbc.StatementImpl@1095a1" is closed." – Evgeniy Dorofeev Dec 25 '12 at 05:05
  • "it's not the best idea to work with JDBC directly". Why not? Spring may be useful in some cases, but in others just creates unnecessary overhead. – Greg Brown Mar 06 '17 at 14:58
  • Down-vote due to Spring suggestion, not really relative to the question. – Koray Tugay Jun 05 '17 at 18:28
4

The details are ultimately down to each JDBC driver implementation; however, once a connection to the database is closed, everything related to it is disposed at the DB side, so there is nothing much the client side can do but auto-close the objects representing these resources.

You never know in what ways the databeses/drivers could be broken (there may be resource leaks, for example), therefore the best practice recommendation is to close everything explicitly.

Marko Topolnik
  • 195,646
  • 29
  • 319
  • 436