0

For a code similar to this,

try{
Connection con =....
Statement stmt = ....
ResultSet set = ...
}
catch(Exception e){
...
}
finally {
con.close();
}

Given that the connection goes to a connection pool:

When con.close() is called, it goes to the connection pool and remains active. At one point of time, these connections get closed right? In that case, do we need to close result set and stmt objects as the connection will eventually get closed?

Also, can there be a situation where the stmt/result set objects might still be used and causing the connection in connection pool not getting closed?

Mercenary
  • 2,106
  • 6
  • 34
  • 43

2 Answers2

1

It's recommended to release resources as soon as they are not needed anymore.

The easiest way is to use the new try-with-resources feature (Java SE 7).

Puce
  • 37,247
  • 13
  • 80
  • 152
  • wont try-with-resources work the same way with pooling? Will existing elements get closed automatically? – Mercenary Mar 06 '14 at 13:02
  • To be more precise, will try-with-resources close `Statement` and `ResultSet` objects and then send the connection back to the pool? – Mercenary Mar 06 '14 at 13:10
  • try-with-resource calls the close method, so it works in the same way. The difference is less boilerplate and error-prone code. – Puce Mar 06 '14 at 13:44
1

When you are using connection pooling, Connection.close() invocation would put the connection back to the pool. The Statement objects will be removed when you call Connection.close(). But, AFAIK, you have to close the ResultSet objects to clear them from server explicitly. it's a good way to always close ResultSet,Statement explicitly and not to rely on Connection.close().Adding to that, if you may opt for Spring JDBC, it will take care of managing the JDBC resources. You don't have to worry about closing connections, statements and resultsets. Adding to that, from Statement API - When a Statement object is closed, its current ResultSet object, if one exists, is also closed.

Keerthivasan
  • 12,760
  • 2
  • 32
  • 53
  • Okay. Can there be a possibility where `ResultSet` objects will prevent the connections from getting closed from the pool after its use? Probably if `ResultSet` is still pointing to any db cursor or being used by some other resource? – Mercenary Mar 06 '14 at 13:14
  • No,there is no way.As API claims that when you have called `Connection.close()`,then, it releases this Connection object's database and JDBC resources immediately instead of waiting for them to be automatically released – Keerthivasan Mar 06 '14 at 13:16
  • Okay I was talking about connections that are in a pool. I just wanted to double-confirm it. A similar thing to what I said is in this case(Aaron's anwser) http://stackoverflow.com/questions/103938/resultset-not-closed-when-connection-closed – Mercenary Mar 06 '14 at 13:19
  • Yeah, i just went through it. It looks like it depends on the implementation of the connection pool mechanism in the server / library. we can't simply rely on it. so, what we have to do is just close everything by calling an utility method which is one of the answers in the provided link – Keerthivasan Mar 06 '14 at 13:26
  • Yes thats the confusion. Why is this safer approach of closing first the `ResultSet`, `Statement` and then the `Connection` when the API itself says that closing `Statement` object will close the `ResultSet` object and closing the `Connection` object will close both `Statement` and `ResultSet` objects? – Mercenary Mar 06 '14 at 13:37
  • It is us who need to make sure that our program's behaviour is good as expected. All the API specification notes entirely depends on just JDBC package. When you are involving connection pooling and other third party mechanisms to handle DB resources, then we couldn't be sure about. so, we can very well close everything ourselves since it is not a big task – Keerthivasan Mar 06 '14 at 13:40
  • Hmm! Got it! And one more thing...just wanted to know what would happen to a `Connection` in a pool if it is not closed? Will it still go back to the pool or does it remain active always? I think it goes back to the pool only when `close()` is called right? – Mercenary Mar 07 '14 at 06:42