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,

athspk · Accepted Answer · 2011-01-12 22:49:58Z

9

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

edited Jan 12, 2011 at 22:49

answered Jan 12, 2011 at 22:35

athspk

6,7627 gold badges39 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user330315 Over a year ago

You should pass back a Map<String, Object> in order to avoid losing information about the actual datatypes returned. And rs.getObject(1).toString() will bomb out with a NullPointerException if the table contains NULL values.

athspk Over a year ago

All the mapped JDBC Types have a toString() method. And about the null, i haven't had any problems. SELECT * From one of my Tables where there is a DATE field with null values, prints out "null" on console.

user330315 Over a year ago

Of course they have a toString() method. But converting the String back to the "real" thing might be a problem. How do you know to which object they should be converted back? What about default formatting that depends on the locale (decimal separator, date formats and so on). This is just like storing everything in a varchar column in the database...

athspk Over a year ago

You have a point here! Converting them back like this would be impossible! But this method takes as granted that you know in advance what to do with each field. If you read the key "dateOfIssue" you know its a Date ("yyyy-MM-dd") for example

user330315 · Accepted Answer · 2011-01-12 22:30:56Z

4

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)

answered Jan 12, 2011 at 22:30

user330315

1 Comment

jricher Over a year ago

Passing in a closure like that is a great way to handle this. If you still want to keep the data around, you could write a generic closure to throw the results into a List<Map> or something to read it later, if you'd like.

Vijay Shanker Dubey · Accepted Answer · 2011-01-12 22:35:09Z

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.

Collectives™ on Stack Overflow

Java Using SQL Execute method but being able to access results

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related