Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Closing ResultSet and Statement

 
Alec Lee
Ranch Hand
Posts: 569
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1211
IntelliJ IDE Mac
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Alec Lee
Ranch Hand
Posts: 569
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1141
1
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 569
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1211
IntelliJ IDE Mac
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic