08 February 2007

Using javax.sql.rowset.CachedRowSet for Web app pagination?

I've been trying to use the javax.sql.rowset.CachedRowSet (com.sun.rowset.CachedRowSetImpl) as a way to implement simple pagination for a web application. 
 
My reasoning was that the CachedRowSet supports a page model whereby a specific number of rows can be retrieved from a query (ie pages!) and a CachedRowSet is disconnected and serializable so it can be stored in a HttpSession between user requests so the page state can be maintained easily for each user.
 
 
Because a CachedRowSet object stores data in memory, the amount of data that it can contain at any one time is determined by the amount of memory available. To get around this limitation, a CachedRowSet object can retrieve data from a ResultSet object in chunks of data, called pages. To take advantage of this mechanism, an application sets the number of rows to be included in a page using the method setPageSize. In other words, if the page size is set to five, a chunk of five rows of data will be fetched from the data source at one time. An application can also optionally set the maximum number of rows that may be fetched at one time.
 
Therefore to provide a simple approach to doing pagination, I was thinking that I could create a CachedRowSet for a user, give it a query and set the page size to specify how many rows of data I wanted to fetch/display at a time.  Since the CachedRowSet is disconnected and serialiable, it is stored in the HttpSession and retrieved on the next request, where the nextPage() method is called to fetch the next set of rows.
 
Pseudo code:
    if is a new request
      create CachedRowSet
      set query
      set page size
      execute
    else
      CachedRowSet nextPage()

    display data from CachedRowSet
    store CachedRowSet in HttpSession
 
A quick app showed that it worked and on each request, the next page of data was displayed (I haven't actually yet looked at the database traces to determine if only 5 rows are really being returned, but lets assume it is).
 
What I then wanted to add was simple [Next] and [Prev] links to allow the user to navigate backwards and forwards through the pages of data.
 
Looking at the nextPage() method, the Javadoc (http://java.sun.com/j2se/1.5.0/docs/api/javax/sql/rowset/CachedRowSet.html#nextPage()) says:
 
boolean nextPage() ...
 
Increments the current page of the CachedRowSet. This causes the CachedRowSet implementation to fetch the next page-size rows and populate the RowSet, if remaining rows remain within scope of the original SQL query used to populated the RowSet.
 
Returns:
true if more pages exist; false if this is the last page
 
So, I thought I'd be able to use this so that when the user hits the last page of data, the nextPage() method should return false, which can then be used to determine if the [Next] link should be shown:
 
    ...
    else
      hasNext = CachedRowSet nextPage()
    if hasNext addNextLink
    display data from CachedRowSet
    store CachedRowSet in HttpSession
 
What I'm finding though is that when the CachedRowSet is on the last page of data, the nextPage() method is stil returning false, and I can pass over the end of the dataset and get an exception.
 
    if(direction == null) {
        // just show the current page set
        crs.beforeFirst();   
    } else if (direction.equalsIgnoreCase("next")) {
        // should return false when on the last page
        hasNextPage = crs.nextPage();
        crs.beforeFirst();
        System.out.println("Called next : " + hasNextPage);                   
        hasPrevPage = true;
    } else if (direction.equalsIgnoreCase("prev")){
        // should return false when on the first page
        hasPrevPage = crs.previousPage();
        crs.beforeFirst();
        System.out.println("prev:" + hasPrevPage);                   
        hasNextPage = true;
    }
Has anyone else used a CachedRowSet like this?  Interested to hear if I'm being a fool or if this is a known problem.
 
I'll put the code somewhere on the blog-o-sphere in case I'm missing something or just being a fool :-)
 
 
 
 

1 comment:

Anonymous said...

> Interested to hear if I'm being a fool
> or if this is a known problem.

You aren't a fool. Great work.

But what i want to comment here is : it becomes a performance issue. Its a heavy weight operation. also maintaining hundreds of records in cache in session is not a good way.