try/catch/finally with connection.close()

Question

I need to return the data from method first and then call the connection,but warning tells me: "This method should return a result of type ResultSet», adding a return to the end of the method, after the close, but if you write it in such a way that the warning still exists. Maybe i should remove this all and use incapsulation in different way? Here is a method

public ResultSet doSQLQuery(String query) throws ClassNotFoundException{

    Class.forName("com.mysql.jdbc.Driver");

    String SQLQuery = query;

    Connection connection = null;
    PreparedStatement statement = null;
    ResultSet resultSet = null;

    try{
        connection = DriverManager.getConnection(DB_URL,login,password);

        statement = connection.prepareStatement(SQLQuery);
        resultSet = statement.executeQuery();

        return resultSet; //return from this place doesnt possible(?)

    } catch (SQLException e) {
        System.err.println("SQLException caught");
        e.printStackTrace();
    }finally {
        if (resultSet != null)
            try { resultSet.close(); }
            catch (SQLException ignore) { }
        if (statement != null)
            try { statement.close(); }
            catch (SQLException ignore) { }
        if (connection != null)
            try { connection.close(); }
            catch (SQLException ignore) { }
    }
}

upd: chose changing return type to string as a solution

Well, if you end up at the first `catch` there's no `return`. — Michael, May 14 '18 at 14:23
You are returning a closed ResultSet. No one will be able to use it. (Also, `Class.forName("com.mysql.jdbc.Driver");` hasn’t been needed for many years.) — VGR, May 14 '18 at 14:29
i should add return to finally block,but where in it, in what order should be placed closing connection and return? — Олег Котенко, May 14 '18 at 14:30
As an aside to the question, for sanity purposes, I'd recommend you create static helper methods for gracefully closing. `static void closeQuietly(ResultSet rs) { try { if (rs != null) rs.close(); } catch (Exception e) { } }` This will allow your finally block to be simply: `closeQuietly(resultSet); closeQuietly(statement); closeQuietly(connection);` rather than three if/try/catch statements. The other alternative is try-with enclosures, but sometimes those don't work well, like with Prepared Statements. — Compass, May 14 '18 at 14:42
@Compass I have never seen a try-with-resources statement fail to work with a JDBC object. try-with-resources just calls the `close()` method of an Autocloseable object. — VGR, May 14 '18 at 14:46
Please see link:- https://stackoverflow.com/questions/14853508/returning-a-resultset — Vinit Mehta, May 14 '18 at 15:04
I thought about this decision, but I did not want to depend on the returned data type method (I can use an object for the return type or a static variable outside the method, but I thought maybe there is a solution better) — Олег Котенко, May 14 '18 at 15:09
@VGR it gets messy if you have a Connection, a PreparedStatement, and a ResultSet. The Connection and the PreparedStatement can be created, but the ResultSet cannot be because you cannot configure the PreparedStatement, so you now have to declare the ResultSet outside, a `try(...)` and a `finally {}` block. With a standard statement, everything fits nicely into try-with-resources. — Compass, May 14 '18 at 15:38

score 1 · Answer 1 · answered May 14 '18 at 14:54

We can fix this method so that it returns a reference to a resultset. But this method is closing the resultset. The reference isn't going to be usable by the caller.

This code pattern, opening a resultset, closing the resultset, and returning a reference to the closed resultset isn't going to work.

try/catch/finally with connection.close()

1 Answers1