Traversing a large database result set using toplink

If you are developing a web application at some point you would have to deal with database queries that return large result sets, your web site might not give the best user experience if it takes several minutes to display your result set with thousands of rows in it. There are several patterns that address this, if you are using plain JDBC then there is the rowid pattern to traverse large result sets. Oracle toplink makes this pattern really simple using "Scrollable Cursors", combine this with some basic pagination logic will make users traversing through thousands of records in your web application very happy.

Here is a basic example that makes traversing an email database a breeze.

Using workbench generate your Email class from your sample Email table, work bench will also generate the database session file which have connection pool information.

Lets call our data access class EmailDAO

public class EmailDAO{
 private static Server dbServer;

class EmailResultSet{
   String mymail;
   Integer totalEmails;
   Integer pagesize;
   Integer pagenumber;
   Vector emails;
};

 static{
             // in 3 tier there needs to be one server instance and multiple client instances
                 // db-session.xml contains the database connection information and should be in classpath
                 XMLSessionConfigLoader loader = new XMLSessionConfigLoader("db-session.xml");
               SessionManager mgr = SessionManager.getManager();
                   dbServer =  (ServerSession)mgr.getSession(loader, "DBSession",
                         Thread.currentThread().getContextClassLoader());
 }

/*
  * mymail is the email address whose email we need to return
  * pagesize is the number of emails to be returned.
  * pagenumber
  */
 public EmailResultSet getMails(EmailResultSet emset){
         // Step 1 Establish connection using toplink 3 tier architecture
         Session clientSession = establishConnection();
     
        // Step2 construct the query
         ExpressionBuilder exbuilder = new ExpressionBuilder();
         Expression whereclause= exbuilder.get("emailaddress").equal(emset.mymail);
         ReadAllQuery rquery = new ReadAllQuery(Email.class,whereclause);
        rquery.addDescendingOrdering("time"); // sort by time column

         // Step3 tell the read query you want the result set split into chuncks of emails each chunk has pagesize number of emails in them
        rquery.useScrollableCursor(emset.pagesize);
      
        // Step4 execute the query
         ScrollableCursor cursor = (ScrollableCursor)  clientSession.execute(rquery);
         // instead of returning a ResultSet as in a conventional execute call a cursor is returned.

   // Step 5 find total emails
         cursor.last();
       // find the total number of email records by going to the last records position      
       emset.totalEmails = new Integer(cursor.getPosition());

   // Step 6 position the cursor
   // If pagenumber is greater than zero it means the caller knows which page he wants else position it to the first page
   if (emset.pagenumber>0)
       cursor.absolute(pagenumber); //point the cursor to the wanted page
   else
        cursor.first();
              
   // Step 7 get the result set
   Vector results = new Vector(emset.pagesize);
   for (int i=0; i<emset.pagesize && cursor.hasNext(); i++)
    {
               results.add(cursor.next());
 
     }
   emset.emails=results;
   return emset;

 }

 public Session establishConnection(){
                      ConnectionPolicy connectionPolicy = new ConnectionPolicy();
                 // Use an exclusive connection for the session
                 connectionPolicy.setShouldUseExclusiveConnection(true);
                     Session clientSession = dbServer.acquireClientSession(connectionPolicy);
                    return clientSession;

 }

}