Java Using SQL Execute method but being able to access results

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,

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. :-)

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)

Some time back, I was in same problem to deal with. After some pondering over this design we decided to do it like below.

public static Properties execute(String string, String[] columnames) throws Exception {

    Properties resulProperties = er.executeQuery(string, columnames);

    return resulProperties;

}

For some specific reason, I created a field in my class as given below

private static ExecuteRequest er = new ExecuteRequest();

In ExecuteRequest class below code is used.

public Properties executeQuery(String sqlstatement, String[] columnNames) throws Exception {
    Properties prop = new Properties();
    try {
        prop = creteProperty(sqlstatement, columnNames);
    } catch (Exception e) {
        mlogger.report("Error executing sql statement");
        throw (e);
    }

    return prop;

}

public Properties creteProperty(String sqlstatement, String[] columnNames) throws Exception {
    Properties prop = new Properties();

    try {
        PreparedStatement stmt = ConnectionManager.getInstance().prepareStatement(sqlstatement);
        ResultSet rs = stmt.executeQuery();
        if (rs.next()) {
            for (int i = 0; i < columnNames.length; i++) {
                String key = columnNames[i];
                if (rs.getObject(key) != null) {
                    String value = (rs.getObject(key).toString());
                    prop.setProperty(key, value);
                } else {
                    String value = "";
                    prop.setProperty(key, value);
                }

            }
        }
        rs.close();
    } catch (Exception e) {
        mlogger.report("Error executing sql statement");
        throw (e);
    }
    return prop;

}

You can use this approach as a solution.