How to Close Statements and Connection in This Method

Question

How to Close Statements and Connection in This Method

public static ResultSet getData (String query){
    
    
    try {
        Connection con = ConnectionProvider.connect();
        Statement st = con.createStatement();
        ResultSet rs = st.executeQuery(query);
        
        return rs;
        
     
        
    } catch (Exception e) {
        JOptionPane.showMessageDialog(null, e);
        System.out.println(e);
        return null; 
    }

Answer 1

You need to close connections in finally block:

try {
...
}
catch {
...
}
finally {
  try { st.close(); } catch (Exception e) { /* Ignored */ }
  try { con.close(); } catch (Exception e) { /* Ignored */ }
}

In Java 7 and higher you can define all your connections and statements as a part of try block:

try(Connection con = ConnectionProvider.connect();
    Statement st = con.createStatement();
    ResultSet rs = st.executeQuery(query);
) {

    // Statements
}
catch(....){}

Answer 2

One should use try-with-resources to automatically close all. Then there is the p

public static void processData (String query, Consumer<ResultSet> processor){
    try (Connection con = ConnectionProvider.connect();
            Statement st = con.createStatement();
             ResultSet rs = st.executeQuery(query)) {
         processor.accept(rs);      
    } catch (SQLException e) {
        JOptionPane.showMessageDialog(null, e);
        System.getLogger(getClass().getName()).log(Level.Error, e);
    }
}

processData("SELECT * FROM USERS", rs -> System.out.println(rs.getString("NAME")));

Or

public static <T> List<T> getData (String query, UnaryOperator<ResultSet, T> convert){
    try (Connection con = ConnectionProvider.connect();
            Statement st = con.createStatement();
             ResultSet rs = st.executeQuery(query)) {
        List<T> result = new ArrayList<>();
        while (rs.next()) {
            result.add(convert.apply(rs));
        }
        return result;      
    } catch (SQLException e) {
        System.getLogger(getClass().getName()).log(Level.Error, e);
        throw new IllegalArgumentException("Error in " + query, e);
    }
}

Then there is the danger with this function, that users will compose query strings like:

String query = "SELECT * FROM USERS WHERE NAME = '" + name + "'";

Which does not escape the apostrophes like in d'Alembert. It opens the gates to SQL injection, a large security breach. One needs a PreparedStatement, and then can use type-safe parameters.

As with try-with-resources the code already is reduced (no explicit closes), you should drop this kind of function. But almost most programmers make this mistake.