File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Proper way to close Connection, Statement & ResultSet Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Proper way to close Connection, Statement & ResultSet" Watch "Proper way to close Connection, Statement & ResultSet" New topic
Author

Proper way to close Connection, Statement & ResultSet

Sajee Joseph
Ranch Hand

Joined: Jan 17, 2001
Posts: 200
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

Joined: Apr 14, 2004
Posts: 10336

Close the connection in a finally block. All other JDBC resource depend on this so are freed up implicitly.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
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

Joined: Aug 19, 2004
Posts: 29
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

Joined: Apr 20, 2000
Posts: 3252
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 ]
 
Consider Paul's rocket mass heater.
 
subject: Proper way to close Connection, Statement & ResultSet