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; 
        }

Thanks in advance.

CodePudding user response：

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(....){}

CodePudding user response：

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.