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.
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.
The future is here. It's just not evenly distributed yet. - William Gibson
Consultant @ Xebia. Sonny GillTweets
Joined: Jan 28, 2004
I am not sure about that. It seems that this is a specific limitation of Oracle 9i JDBC driver.
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.
Joined: Jan 28, 2004
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.
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.