Java Using SQL Execute Method, but Accessing Results

Question

Instead of having complex SQL queries, attempts, catch, and finals everywhere in the code, I have a method execute(SQL, up to three inputs), however, if you try to access the ResultsSet, this throws out outside of the execution, you get an error:

"Operation not allowed after ResultSet closed"

This is because when you close PreparedStatement, it closes ResultsSetToo (and it doesn't seem to be there).

Is there any way to fix this? The only thing I could think of was convert it to an array that is stored

Thanks so much for your time,

Answer 1

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

ResultSet ( ). - , - . , .: -)

Answer 2

PreparedStatement, ResultsSetToo

. , PreparedStatement, .

, . ResultConsumer - , execute(). execute() Resultset .

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

execute() :

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

( , , )

Answer 3

Some time ago I had the same problem. After some reflection on this design, we decided to do it as shown 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 follows

private static ExecuteRequest er = new ExecuteRequest();

The ExecuteRequestclass below the 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.