best practice to close jdbc resultset

Question

here is how I do it right now:

public static getConfs(Connection conn, String confNo){
    ResultSet rs = null;
    try{
        rs = conn.createStatement().executeQuery("select col1,col2 from table1");
        ... // do something with rs
        rs.getStatement().close();
        rs = conn.createStatement().executeQuery("select col1,col2 from table2");
        ... // do somthing with rs
        rs.getStatement().close();
        rs = null;
    }catch(Exception e){
        throw e;
    }finally{
         if(rs != null){
             try{
                 rs.getStatement().close();
             }catch(SQLException se){
                 se.printStackTrace();
             }
         }
    }
}

two questions:
1. should I reuse result set variable like that ?
2. is that good to close result set like that ? any smarter way?

Consider [this answer](http://stackoverflow.com/a/321879/2711488), especially. — Holger, Oct 11 '14 at 12:10

score 1 · Accepted Answer · answered Oct 11 '14 at 03:00

Take a look at Spring JdbcUtils source, it closes ResultSet this way

public static void closeResultSet(ResultSet rs) {
    if (rs != null) {
        try {
            rs.close();
        } catch (SQLException ex) {
            logger.trace("Could not close JDBC ResultSet", ex);
        } catch (Throwable ex) {
            // We don't trust the JDBC driver: It might throw
            // RuntimeException or Error.
            logger.trace("Unexpected exception on closing JDBC ResultSet", ex);
        }
    }
}

so your code would look like this

    Statement st = conn.createStatement();
    try {
        ResultSet rs = st.executeQuery("select col1,col2 from table1");
        // do something
        closeResultSet(rs);
        rs = st.executeQuery("select col1,col2 from table2");
        // do something
        closeResultSet(rs);
    } finally {
        // close Statement
    }

Though in my opinion the best way not to work with JDBC at low level but use Spring JDBC directly. It thouroughly thought thru and will make your code simple and reliable.

Or use Java 7 try-with-resources – Mark Rotteveel Oct 11 '14 at 11:25 — Mark Rotteveel, Oct 11 '14 at 11:25

best practice to close jdbc resultset

1 Answers1