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 Closing ResultSet and Statement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Closing ResultSet and Statement" Watch "Closing ResultSet and Statement" New topic
Author

Closing ResultSet and Statement

Alec Lee
Ranch Hand

Joined: Jan 28, 2004
Posts: 569
In the following code fragment, assuming the variables are valid, are the rs.close() and pstmt.close() inside the for loop necessary? I am using Oracle 9i thin driver.

I have this problem because the example given for Oracle's JDBC driver does close and re-obtain the pstmt and result set. I just wondering if I can do a con.prepareStatement(sql) outside the for() once only and reuse that pstmt for every loop.

Sonny Gill
Ranch Hand

Joined: Feb 02, 2002
Posts: 1211

It makes more sense to create the PreparedStatement before the loop begins, and close it after the loop ends. Otherwise you are not really getting the full benefit of a PreparedStatement which represents a precompiled statement.

According to Javadocs, you do not need to explicitly close the ResultSet, as it is automatically closed when the statement is closed or executed again, but there is not harm in doing it inside the loop either.

To be certain that the PreparedStatement is closed even if an exception is thrown in the for loop, the close() call should be made in a finally block.

HTH

Sonny


The future is here. It's just not evenly distributed yet. - William Gibson
Consultant @ Xebia. Sonny Gill Tweets
Alec Lee
Ranch Hand

Joined: Jan 28, 2004
Posts: 569
I am not sure about that. It seems that this is a specific limitation of Oracle 9i JDBC driver.

Does anyone have this experience?
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1134

Alec,
If you are using the same SQL each time you create a "PreparedStatement", then I agree with everything Sonny said. I think he has given you good advice.

However, if you are using many, different SQL statements in your "for" loop, then you probably need to create separate "PreparedStatement"s for each SQL statement.

Good Luck,
Avi.
Alec Lee
Ranch Hand

Joined: Jan 28, 2004
Posts: 569
Thx Avi,

I understand the point. But I am just trying to verify the specific requirement of Oracle 9i jdbc driver. I have this worry because the example given by oracle's jdbc book does close the statement and result set while the specification doesnt have such limitation.

Thx
Sonny Gill
Ranch Hand

Joined: Feb 02, 2002
Posts: 1211

Alec,

Repeatedly closing PreparedStatement does not make any sense at all, that would deny you most of the performance improvement you would get when using a PreparedStatement to run a query with different parameters.

I read somewhere, quite a long time ago, that for Oracle drivers, you need to explicitly close ResultSet before closing the Statement. Otherwise it leaves some cursors open in the database. Now, that was a long time ago, and I could not confirm it. So, you can close the ResultSets in the for loop to be on the safe side, or run some diagnostic tests to see if executing PreparedStatement again closes the previous ResultSet.

Even if this bug was present in one of the Oracle drivers, I would expect it to have been fixed in the latest drivers.

Cheers.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Closing ResultSet and Statement