Closing database related resources within method

Question

I was advised by @Joeblade to move the database connection logic to a separate method, if not a separate class because user interface classes are already very cluttered with a lot of user interface code, so it would not help readability to add db code to it. This would also help reuse the db code, if I would need to. I did it. But now, my question is regarding closing resources.

static Connection getConnection() {
    return connection;
} 

public ResultSet Execute_Query(String queryIn) throws SQLException {
    ResultSet resSet = null;        
    try {               
        connection = TableWithBottomLine.getConnection();
        Statement stmt = null;
        stmt = connection.createStatement();
        resSet = stmt.executeQuery(queryIn);                    
    } catch (SQLException e) {
        e.printStackTrace();
    }               
    return resSet;
}

public String textForLabel() throws SQLException{

    List<Float> floatArrayList = new ArrayList<Float>();

        String query ="SELECT f.Flat, f.Mobile, f.Food, f.Alcohol, f.Transport, f.Outdoor, f.Pauls_stuff, f.Stuff FROM finance.fin f WHERE f.tbl_Date >= DATE_FORMAT( NOW( ) ,  '%Y-%m-10' ) + INTERVAL IF( DAY( NOW( ) ) >10, 0 , -1 ) MONTH AND f.tbl_Date <= CURDATE( ) ";

        try {               
            ResultSet rs = null;
            rs = Execute_Query(query);          

            while (rs.next()){
              floatArrayList.add(rs.getFloat("Flat"));
              floatArrayList.add(rs.getFloat("Mobile"));
              floatArrayList.add(rs.getFloat("Food"));
              floatArrayList.add(rs.getFloat("Alcohol"));
              floatArrayList.add(rs.getFloat("Transport"));
              floatArrayList.add(rs.getFloat("Outdoor"));
              floatArrayList.add(rs.getFloat("Pauls_stuff"));
              floatArrayList.add(rs.getFloat("Stuff"));
            }           
       } catch(SQLException ee){
           ee.printStackTrace();
         }
// Irrelevant stuff down below.....

1. Do I have to close Connection, Statement, ResultSet in Execute_Query() method AND close ResultSet (only) in textForLabel() method?
2. How can I do that closing using try-with-resources feature?

Answer 1

Move ALL of the database logic, not just the connection establishment code.

List<Float> listMyStuff() {
    ArrayList<Float> list = new ArrayList<Float>();
    Connection con = null;
    Statement statement = null;
    ResultSet rs = null;

    try {
        con = DriverManager.getConnection(MY_DB_URL);
        statement = con.createStatement();
        rs = statement.executeQuery(MY_SQL);
        while(rs.next()) {
            list.add(rs.getFloat("Flat"));
            list.add(rs.getFloat("Mobile"));
            list.add(rs.getFloat("Food"));
            list.add(rs.getFloat("Alcohol"));
            list.add(rs.getFloat("Transport"));
            list.add(rs.getFloat("Outdoor"));
            list.add(rs.getFloat("Pauls_stuff"));
            list.add(rs.getFloat("Stuff"));            
        }
    }
    catch(SQLException e) {
        e.printStackTrace();
        //Do something else!?!?
    }
    finally {
        if(rs != null) { try{ rs.close(); }catch(Throwable t){} }
        if(statement != null) { try{ statement.close(); }catch(Throwable t){} }
        if(con != null) { try{ con.close(); }catch(Throwable t){} }
    }

    return list;
}

Answer 2

Try something like this

} catch (SQLException e) {
   e.printStackTrace();
} finally {
   connection.close();
}

The finally statement will make sure that the 'connection.close();' is called even if the code throws an exception.

Answer 3

Using try with resources will be much better if you chose to do so.

First get your connection details and prepare the statements in the try .

      String query ="SELECT f.Flat, f.Mobile, f.Food, f.Alcohol, f.Transport, f.Outdoor, f.Pauls_stuff, f.Stuff FROM finance.fin f WHERE f.tbl_Date >= DATE_FORMAT( NOW( ) ,  '%Y-%m-10' ) + INTERVAL IF( DAY( NOW( ) ) >10, 0 , -1 ) MONTH AND f.tbl_Date <= CURDATE( ) ";
        try (Connection connection = DriverManager.getConnection(url);
    Statement stmt=connection.createStatement();) {
        try (ResultSet rs = stmt.executeQuery(query); ) {
                while(rs.next()) {
    floatArrayList.add(rs.getFloat("Flat"));
                  floatArrayList.add(rs.getFloat("Mobile"));
                  floatArrayList.add(rs.getFloat("Food"));
                  floatArrayList.add(rs.getFloat("Alcohol"));
                  floatArrayList.add(rs.getFloat("Transport"));
                  floatArrayList.add(rs.getFloat("Outdoor"));
                  floatArrayList.add(rs.getFloat("Pauls_stuff"));
                  floatArrayList.add(rs.getFloat("Stuff"));
                }           
           } 
}catch(SQLException ee){
               ee.printStackTrace();
    }