Java Using SQL Execute method but being able to access results

Question

Rather than having complex SQL queries, tries, catch and finals everywhere in the code I have a method execute(SQL, up to three inputs) however if trying to access the ResultsSet this produces outside of the execute you get the error:

"Operation not allowed after ResultSet closed"

Which is because when you close the PreparedStatement it closes the ResultsSetToo (and there seems to be no way around it).

Is there a way to fix this? The only thing I could think of was converting it to an array which is stored

Many thanks for your time,

Answer 1

I have encountered the same problem in the past. Now i use this method:

public ArrayList<Map<String, String>> getListOfMapsFromSQL(String sql) throws SQLException {
    con = DriverManager.getConnection(url,user,pass);
    stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
    ArrayList<Map<String, String>> list = new ArrayList<Map<String, String>>();

    rs = stmt.executeQuery(sql);
    ResultSetMetaData rsmd = rs.getMetaData();

    while(rs.next()){
        Map<String, String> fieldsMap = new HashMap<String, String>();
        for(int i=1; i<=rsmd.getColumnCount(); i++){
            fieldsMap.put(rsmd.getColumnLabel(i), rs.getObject(i).toString());
        }
        list.add(fieldsMap);
    }

    list.trimToSize();
    stmt.close();
    con.close();
    return list;
}

Instead of returning a ResultSet, it returns a list of Maps(each one representing 1 row). The first String is the column Label, and the second is the column's value. I hope it helps. :-)

Answer 2

when you close the PreparedStatement it closes the ResultsSetToo

Correct. So may not close the PreparedStatement until you have processed the result.

I would define an interface e.g. ResultConsumer or something similar that the caller of execute() can implement. Then inside your execute() method you simply pass the Resultset to the consumer.

public Interface ResultConsumer
{
   void processResult(ResultSet rs);
}

Then your execute() could look like this

public void execute(String SQL, ResultConsumer consumer, ... other parameters)
{
   PreparedStatement stmt = ...
   ResultSet rs = stmt.executeQuery();
   consumer.processResult(rs);
   rs.close();
   stmt.close();
}

(I removed all error checking and exception handling for clarity, of course you'll need to deal with that)