CachedRowSet, ResultSetDynaClass or Another Collection?

Question

I'm cleaning up a very poorly done, large, webapp. I can't do it all at once, so I am taking it in pieces. One thing I would like to do is implement a class with functions that take SQL statements and then returns a collection. My plan is once that is taken care of I can shop around for a database framework to replace that layer without disturbing the rest of the webapp.

The big problem with returning a ResultSet out of template style database access function is that I can't close the connection without disabling the ResultSet.

I was excited to find the suggestions in this old stackoverflow post.

I read the article cited on CachedRowSet, but I am concerned where the article stated its use could create a problem with sucking up a lot of JVM memory by stuffing a large amount of results into itself.

Wouldn't the alternative Apache ResultSetDynaClass or any other Collection have the same problem? The results have to get written into memory one way or the other. Right?

If I used ChaedRowSet, ResultSetDynaClass or a Java Collection to store ResultSet data, would I lose that data once I closed the ResultSet?

I've used HashMaps to store single records from generic database query functions.

How could I use java Collections to store multiple records without the code getting cumbersome? Assuming that I could, would it be more resource efficient or flexible than the other two options?

Thanks in advance for any information or ideas.

Answer 1

Because this is a web app, you know when you need to close your resultset: at end of request. You can register your resultsets/connections in a request scope and close them all at the end of the request. This is how Cold Fusion(runs on top of Java) handles it.

Or you can do like Myna does and return a "materialized" collection by default, or take an optional rowhandler that will be executed against the raw resultset. In the second case you can get direct access to things like BLOBs that almost always require special handling and are not practical to just stuff into a collection, and you know when the rowHandler is done so you can close the resultset/connection

UPDATE:

Here is a simple example of the close-when-request-end approach

public class Query {
    static public ConcurrentHashMap     openResultSets  = new java.util.concurrent.ConcurrentHashMap();
    public ResultSet runQuery(String sql) throws SQLException{
        //set up connection, run query
        ResultSet rs = statement.executeQuery();
        if (Query.openResults.get(Thread.currentThread().getId()) == null){
            Query.openResults.put(Thread.currentThread().getId(),new Vector())
        }
        Vector openRS =  (Vector) Query.openResults.get(Thread.currentThread().getId()) 

        openRs.add(rs);
        return rs;
    }
    public void onRequestEnd(String sql) {
        Vector openRS =  (Vector) Query.openResults.get(Thread.currentThread().getId());
        if (openRS != null){
            for (ResultSet rs : openRS.values()){
                try{
                    rs.close();
                } catch(Exception e){}
            }
        }
        //do any other request end cleanup of connections, etc
    }
}

//in your servlet
public class Query extends HttpServlet {

    public void doPost(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException{

        ResultSet rs = Query.runQuery("select * from AWESOME");
        //do awesome stuff, maybe load other classes that can run runQuery 

        Query.onRequestEnd();
    }
}

This is isn't ideal, but I don't know what framework you are using. It probably makes sense to keep this in your HttpServletRequest object via setAttribute()/getAttribute(), but that requires passing the request around to every method that uses it. Either way you just need to keep an array of open resultsets and close them at the end of the request. Using a framework of some kind makes this a ton easier.