Is it necessary to close PreparedStatement before preparing another Statement

Question

Is it necessary to close ResultSet and PreparedStatement within one db.getConnection()? For the example below:

Connection conn = db.getConnection();
PreparedStatement pstmt = conn.prepareStatement(firstsql);
ResultSet r = pstmt.executeQuery();
// do something with the ResultSet
r.close();
pstmt.close();   // do I need close the r and pstmt here?
PreparedStatement pstmt = conn.prepareStatement(secondsql);
ResultSet r = pstmt.executeQuery();
// do something with the ResultSet again
r.close();
pstmt.close();
conn.close();
return null;

Are the codes of line 5 and line 6 necessary?

Answer 1

Strictly speaking, it is not necessary because you can have multiple prepared statements opened at the same time. What is necessary is to close every opened resource if it is not going to be utilised later.

Looking at your code, it doesn't ensure that the statement are, in fact, closed.

To ensure this, every close operation must be done inside a finally block: this way, it will executed whether the operation succeded or not.

Sample code:

PreparedStatement pstmt = null;
ResultSet r = null;
try {
    pstmt = conn.prepareStatement(firstsql);
    r = pstmt.executeQuery();
    // do something with the ResultSet
} finally {
    if (r != null) {
        try {
            r.close();
        } catch (SQLException e) {
            //log error
        }
    }
    if (pstmt != null) {
        try {
            pstmt.close();
        } catch (SQLException e) {
            //log error
        }
    }
}
//and do it again

This code can be greatly simplified if you are using Java 7 with the try-with-resources statement:

try (PreparedStatement pstmt = conn.prepareStatement(firstsql);
      ResultSet r = pstmt.executeQuery();) {
    // do something with the ResultSet
}