Is it necessary to close a PreparedStatement before pointing to another one?

Question

I'm aware that a very similar question was asked here: Is it necessary to close PreparedStatement before preparing another Statement But even after looking at the answer, I'm still confused.

If say, I modify the OP's code to this:

Connection conn = null;
PreparedStatement pstmt = null;
ResultSet r = null;
try{
    conn = db.getConnection();
    pstmt = conn.prepareStatement(firstsql);
    r = pstmt.executeQuery();
    // do something with the ResultSet
    r.close();       // do I need this line?
    pstmt.close();   // do I need this line?
    PreparedStatement pstmt = conn.prepareStatement(secondsql);
    ResultSet r = pstmt.executeQuery();
    // do something with the ResultSet again
}
finally{
    if(r!=null)
        r.close();
    if(pstmt!=null)
        pstmt.close();
    if(conn!=null)
        conn.close();

Do I need those 2 lines? I'm aware that both r and pstmt are inevitably going to be closed(regardless of wether or not there'll be a runtime error), since they're in the finally block but r and pstmt are both pointers.

Pointers that throughout this code point to many different PreparedStatement and ResultSet objects. Wouldn't I need to close those objects before I point to other ones?

In my mind if this code was missing those 2 lines it would only ensure that only the last 2 objects to be pointed to by r and pstmt will be closed.

Please learn about [try-with-resources](https://docs.oracle.com/javase/tutorial/essential/exceptions/tryResourceClose.html). It will 1) simplify your code, and 2) prevent resource leaks (eg you currently leak `pstmt` and `conn` if `r.close()` throws an exception), and 3) correct application of try-with-resources would have avoided this question. — Mark Rotteveel, Dec 01 '18 at 14:24
@Mark Rotteveel I'm aware of the try-with-resources. I know I should change, but my IDE doesn't support it yet. Also, if r.close() throws an exception, wouldn't the flow of the program go to the catch block then the finally block where both conn and pstmt would close? — ATC, Dec 01 '18 at 18:53
What IDE are you using that it doesn't support try-with-resources? It was introduced back in 2011 with Java 7. — Mark Rotteveel, Dec 01 '18 at 18:59
Nvm, I'm a goofball. It does work. I'm using Eclipse, and simply assumed it wouldn't work since our lecturer told us that it might not. But it does. Either way, I'll switch to try-with-resources but for the sake of understanding this, why would there be resource leak if r.close() throws an exception, wouldn't the flow of the program go to the catch block then the finally block where both conn and pstmt would close? — ATC, Dec 01 '18 at 22:05
If in the catch block `r.close()` fails, then `pstmt` and `conn` are never closed, if `pstmt.close()` fails, then `conn` is never closed. — Mark Rotteveel, Dec 02 '18 at 07:35

score 0 · Accepted Answer · edited Dec 01 '18 at 14:26

First, note that this code will not compile since you're declaring new variables pstmt and r while these variables are still available in the scope.

If you weren't, you should definitely close pstmt and r before assigning new values to them. By assigning new values to these variables, you are effectively losing the reference to the previous values, and will not be able to close them, resulting in a resource leak.

As a side note, note that such classes usually have their finalize method call close, so sooner or later when the garbage collector collects them they will be closed, but you should never depend on this behavior.

Is it necessary to close a PreparedStatement before pointing to another one?

1 Answers1