Java - NULL ResultSet

Question

I have a function to fetch data from MySQL table

public ResultSet getAddressID(String city) throws SQLException{
        String q = "SELECT PK_ADDRESS_ID FROM tbl_addresses WHERE city =" + "\""+ city+ "\";";
        ResultSet rs = executeSearch(q);
        return rs;
    }

When I try System.out.println(n.getAddressID("Sheffield")); it returns null. Why this happened even though there are data in my table (see picture).

public ResultSet executeSearch(String q){
    openConnection();
    try{
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(q);
        closeConnection();
        return resultSet;
    }
    catch (Exception e){
        JOptionPane.showMessageDialog(null, e.getMessage());
    }
    finally {
        closeConnection();
        return null;
    }
}

Don't `finally` blocks get executed no matter what? Not sure, with Java, but it seems wrong to return from a finally block. — David T. Macknet, Nov 16 '16 at 17:33

Chris Forrence · Accepted Answer · 2016-11-16T19:01:40.957

The problem appears to be in your executeSearch method; the finally block will always execute, so by returning null in the finally block, you essentially override what you returned in the try block!

This could be an alternative solution; note that I'm returning at the end of the method instead of within any parts of the try-catch-finally block.

/**
 * Converts a provided ResultSet into a generic List so that the
 * ResultSet can be closed while the data persists.
 * Source: http://stackoverflow.com/a/7507225/899126
 */
public List convertResultSetToList(ResultSet rs) throws SQLException
{
    ResultSetMetaData md = rs.getMetaData();
    int columns = md.getColumnCount();
    List list = new ArrayList(50);

    while (rs.next())
    {
        HashMap row = new HashMap(columns);
        for(int i = 1; i <= columns; ++i)
        {
            row.put(md.getColumnName(i), rs.getObject(i));
        }
        list.add(row);
    }

    return list;
}

public List executeSearch(String q)
{
    List toReturn;
    openConnection();
    try {
        Statement statement = connection.createStatement();
        toReturn = this.convertResultSetToList(statement.executeQuery(q));
    }
    catch (Exception e) {
        JOptionPane.showMessageDialog(null, e.getMessage());
        toReturn = new ArrayList();
    }
    finally {
        closeConnection();
    }

    return toReturn;
}

I try to fetch the first element of `ResultSet` and print it out. Here's the code `if (rs.next()){ i = rs.getInt(1); };`. Somehow I getting this error `Exception in thread "main" java.sql.SQLException: Operation not allowed after ResultSet closed` — Tri Nguyen, Nov 16 '16 at 18:38
Your version works btw. But it just returns a single line `com.mysql.jdbc.JDBC42ResultSet@21b8d17c` — Tri Nguyen, Nov 16 '16 at 18:39
That's because closing the database connection also closes related ResultSets. I've updated my answer to include a method borrowed heavily from http://stackoverflow.com/a/7507225/899126; essentially, you'd convert the ResultSet to a list of HashMaps. — Chris Forrence, Nov 16 '16 at 19:01

Java - NULL ResultSet

1 Answers1