Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Proper way to close Connection, Statement & ResultSet

 
Sajee Joseph
Ranch Hand
Posts: 200
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello all,

I am working in an Application which uses DB2 as the Back end.
I am using DAO (Data Access Objects) to do Database operations. In most cases i use Callable Staments to call my stored Procedures to do the work.
Assuming that Conn is my 'Connection' object, stmt is my 'Callable Statement' Object & resultSet is my 'ResultSet' object, i have done the following in the finally block of my DAO methods

if (resultSet != null)
{
resultSet.close()
}
if (stmt != null)
{
stmt.close()
}
if(Conn != null)
{
Conn.close()
}

I have noticed that i get certain problems at certain times with this approach. I would like to clarify the following:
1. Is my approach to close the conn, stmt & resultSet correct?
2. Someone suggested that i need to close them in the reverse order. IS it so? or does it make no difference.
3. I notice that there is a method isClose() in the Connection object. Do i have to call this before i close.

Please answer this. Any sample code is very welcome.

Regards,
Saj
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Close the connection in a finally block. All other JDBC resource depend on this so are freed up implicitly.
 
Julian Kennedy
Ranch Hand
Posts: 823
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That's the way that I would do it. I've never seen it done any other way that is robust and makes sense.

It's good programming practice to explicitly close things that you open once you've finished with them. For example, failing to explicitly close ResultSets and/or Statements in a pooled connection implementation (where connections are not actually closed) can lead to open cursors causing unexpected SQLExceptions in unrelated code. Is it worth the risk of exposing yourself to that kind of headache for the sake of a little typing/cut&paste convenience?

Jules
 
Prashant Jain
Greenhorn
Posts: 29
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hi sajee!

I would go with Paul's opinion of closing everything in the finally block in a stand alone programm. Though as as per JDBC closing the statement should close the resultset and rs.close() may not be needed.

However here is something that I thought I would share with you guys.I encountered this with Oracle 9i driver.The JDBC contract says that the statement should close the resultset, but i have observed that this is not the implementation of the driver.If your resultset is a cursor in oracle, closing the statement does nothing!So closing the result set becomes mandatory in such a case.

Happy Programming!
Prashant
 
Peter den Haan
author
Ranch Hand
Posts: 3252
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes - the Oracle driver is very sensitive to cursor leaks In a web application environment, "closing" your connection just returns it to the pool, so you shouldn't rely on your connection close to close all other resources either.This is not enough in all cases. The close methods may throw SQLException, and a failure to close the result set, for example, will prevent the connection from being returned to the pool. In a web application environment a single problematic statement might now deplete your connecton pool in a matter of seconds, depending on how clever the application server's connection pool is. To be robust without making too many assumptions about the environment, execution of all close statements should be guaranteed:We aren't there yet. Any exception thrown by the close statement will will obscure the exception thrown inside the try block, which is bad because the first exception will be the most informative about the root cause of the problem.If you want to write good JDBC code, you'll write over two dozen lines of clutter to execute one pesky SQL query. Unsurprisingly, almost no-one does this, and virtual all JDBC code suffers from potential resource leaks and/or obscured exceptions. This is why frameworks such as the Spring JDBC template or the higher-level iBatis are IMHO indispensable tools. Never write raw JDBC. It's just too hard and cumbersome to get right.

- Peter
[ August 25, 2004: Message edited by: Peter den Haan ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic