Paging techniques for applications using google appengine for java

Paging technique is implemented in tons of websites and there are many libraries, both opensource and commercial components, that are easy to integrate into any website. Ajax based tables (excel like) with navigators for paging to and fro, are integral part of RIA applications. Why do we need a different technique to implement paging for Google appengine application when so many components exists today? To answer this, we need to understand two aspects.

1) How majority of the "paging" components are designed for usability?
2) How data is stored and retreived in applications that use Google Appengine?

I used my favourite "paging" library Jqgrid to explain the techniques. The component is based on JQuery and is well documented. Click the link for jqgrid demo




The above picture shows,
- options to navigate page by page
- total number of records available
- total number of pages
etc


The jqgrid demo website explains the techniques of implementing the paging concepts. (using php and mysql rdbms). This is applicbale to all databases with some minor changes to the queries used.
Unfortunately, they are not applicable to Google Appengine database. It doesn't support aggregate functions like count, avg, sum etc. In this sample we atleast need the count function to use. Appengine database targets high performance applications and therefore do not support standard SQL aggregate functions. We may be tempted to query the whole dataset and get the total number of records but this defeats the purpose of "paging" as it increases load on memory and time of retreival. A word of caution - you may be surprised to know that many of the "Paging" components load all the records by default causing performance overheads. Additional customization options may be provided but if we donot read the documentation and implement in a hurry - it is gaurenteed to fail in production environments.

I put together two options to implement paging and use jqgrid. The steps are applicable for any other components like jqgrid.

Option 1: Maintain a count of objects as they are getting added and removed in the Google appengine database. The limitation, of this approach is a) having to maintain the counter b) can not be used if we are searching by any field in the table. Because the search returns a subset of the data and the counter that we stored is not effective.

Option 2:
Customize jqgrid not to show "total pages", "total number of records", "move first", "move last buttons". Just make "Prev" and "Next" buttons visible. This might sound like a limitation but compared to the first option this is far better and flexible approach.

Using counter:

For ease of explanation I took a very simple domain class that uses JDO for persistence.

Note: Please excuse me for not applying the indentation correctly. I believe the classes are simple and self-explanatory. I also assume knowledge on Google AppEngine API, as the focus of this article is on GAE application developers.
For other curious readers Option 1, can be ruled out completely. However, option 2 can be used for even RDBMS based applications, using jsp and jqgrid.

package com.sample.service.domain;

import javax.jdo.annotations.IdGeneratorStrategy;
import javax.jdo.annotations.IdentityType;
import javax.jdo.annotations.PersistenceCapable;
import javax.jdo.annotations.Persistent;
import javax.jdo.annotations.PrimaryKey;

import com.google.appengine.api.datastore.Key;
import com.google.appengine.api.users.User;

@PersistenceCapable(identityType = IdentityType.APPLICATION)
public class TestUser {

 @PrimaryKey    
 @Persistent(valueStrategy = IdGeneratorStrategy.IDENTITY)
 private Key key;
 
 @Persistent    
 private String companyCode;
 
 @Persistent    
 private String name;
 
 @Persistent    
 private String email;
 
 @Persistent    
 private String comments;
 
 @Persistent    
 private String role;
 
 @Persistent    
 private User user;
 
 @Persistent    
 private String activeFlag;
 

 public Key getKey() {
  return key;
 }

 public void setKey(Key key) {
  this.key = key;
 }

 public String getCompanyCode() {
  return companyCode;
 }

 public void setCompanyCode(String companyCode) {
  this.companyCode = companyCode;
 }

 public String getName() {
  return name;
 }

 public void setName(String name) {
  this.name = name;
 }

 public String getEmail() {
  return email;
 }

 public void setEmail(String email) {
  this.email = email;
 }

 public String getComments() {
  return comments;
 }

 public void setComments(String comments) {
  this.comments = comments;
 }

 public String getRole() {
  return role;
 }

 public void setRole(String role) {
  this.role = role;
 }

 public User getUser() {
  return user;
 }

 public void setUser(User user) {
  this.user = user;
 }

 public String getActiveFlag() {
  return activeFlag;
 }

 public void setActiveFlag(String activeFlag) {
  this.activeFlag = activeFlag;
 }
 
 
 
}

Three more classe below:
1) a class to persist and retreive objects of the domain class "TestUser" in the above example. (UserMgmtService.java)
2) a generic domain class used for storing counter. (Counter.java)
3) a utility class to get the JDO persistance manager, and to increment and decrement the counter.(Util.java)

UserMgmtService.java

package com.sample.service;

import java.util.List;

import javax.jdo.JDOHelper;
import javax.jdo.PersistenceManager;
import javax.jdo.PersistenceManagerFactory;

import com.sample.service.domain.TestUser;

public class UserMgmtService {

 
 public List<TestUser> getAllUsers(){
 
  return null;
 }
 
  public List<TestUser> getUsers(int pg, int limit){
  
     int idx = pg * limit - limit;
     
     PersistenceManager pm = Util.getPersistenceManager();

                
        String query = "select from " 
            + com.sample.service.domain.TestUser.class.getName() 
               + " order by name asc RANGE " + idx + ","+ (pg * limit); 
                    
        List<TestUser> users = (List<TestUser>) 
                pm.newQuery(query).execute();
        users.size();
        
        
        pm.close();
        
        return users;

  
 }
 
 public void addUser(TestUser tu){
  
  PersistenceManager pm = Util.getPersistenceManager();
  
  pm.makePersistent(tu);
  
  Util.incrementCounter(tu.getClass().getName(), pm);
  
  pm.close();
  
 }
 
    
 
 
}

Counter.java

package com.sample.service.domain;

import javax.jdo.annotations.IdGeneratorStrategy;
import javax.jdo.annotations.IdentityType;
import javax.jdo.annotations.PersistenceCapable;
import javax.jdo.annotations.Persistent;
import javax.jdo.annotations.PrimaryKey;

import com.google.appengine.api.datastore.Key;

@PersistenceCapable(identityType = IdentityType.APPLICATION)
public class Counter {
 

 @PrimaryKey    
 @Persistent(valueStrategy = IdGeneratorStrategy.IDENTITY)    
 private Key key;
 

 @Persistent    
 private String counterName;

 @Persistent    
 private int count;

 

 public Key getKey() {
  return key;
 }

 public void setKey(Key key) {
  this.key = key;
 }

 public String getCounterName() {
  return counterName;
 }

 public void setCounterName(String counterName) {
  this.counterName = counterName;
 }

 public int getCount() {
  return count;
 }

 public void setCount(int count) {
  this.count = count;
 }
 
 
}

Util.java

package com.sample.service;

import javax.jdo.JDOHelper;
import javax.jdo.JDOObjectNotFoundException;
import javax.jdo.PersistenceManager;
import javax.jdo.PersistenceManagerFactory;

import com.google.appengine.api.datastore.Key;
import com.google.appengine.api.datastore.KeyFactory;

import com.sample.service.domain.Counter;

public class Util {
 

 private static final 
    PersistenceManagerFactory pmfInstance = JDOHelper.getPersistenceManagerFactory("transactions-optional");    

 public static void incrementCounter (String objectName,     
                                PersistenceManager pm)
 {
       Counter ctr = null;
       try{
          ctr = pm.getObjectById(Counter.class, objectName);
       }catch(JDOObjectNotFoundException je){
        ctr = null; 
       }
       
        if (ctr == null){
        
         Key key = KeyFactory.createKey(Counter.class.getSimpleName(),
                  objectName);
         ctr = new Counter();
         ctr.setKey(key);
         ctr.setCounterName(objectName);
         ctr.setCount(1);
         pm.makePersistent(ctr);        
         
        }
        else{
         
         ctr.setCount(ctr.getCount() + 1);
         
        }
         
 }
 
 public static int getCount(String objectName)
 {
  
  PersistenceManager pm = getPersistenceManager();
  Counter ctr = null;
      try{
         ctr = pm.getObjectById(Counter.class, objectName);
      }catch(JDOObjectNotFoundException je){
       ctr = null; 
      }
  if (ctr == null) return 0;
  else
   return ctr.getCount();
  
 }
 

 public static PersistenceManager getPersistenceManager() 
 {        
  return pmfInstance.getPersistenceManager();    
 }

}

Integration with JQgrid

I used simple jsps to integrate jqgrid with GAE application. Jqgrid is very flexible and powerful. The complete documentation on its usage is available on their website.

I used two jsps
1) To display the jggrid (SamplejqGrid.jsp)
2) To retreive the results from the backend (SampleList.jsp)

<%@ page language="java" import="java.util.*" pageEncoding="ISO-8859-1"%>
<html>
<head>


<link rel="stylesheet" type="text/css" media="screen" href="themes/steel/grid.css" />
 <link rel="stylesheet" type="text/css" media="screen" href="themes/jqModal.css" /> 
 <script src="scripts/jquery-1.3.2.js" type="text/javascript"></script> 
 <script src="jquery.jqGrid.js" type="text/javascript"></script> 
 <script src="js/jqModal.js" type="text/javascript"></script> 
 <script src="js/jqDnR.js" type="text/javascript"></script> 
 
 <script type="text/javascript"> 
    jQuery(document).ready(function()
       { jQuery("#list").jqGrid(
          { url:'/SampleList.jsp', 
            datatype: 'xml',  
            mtype: 'GET', 
            colNames:['Name','Email', 'Role', 'Active'], 
            colModel :[  
              {name:'uName', index:'uName', width:60,sortable:false}, 
              {name:'email', index:'email', width:60,sortable:false},
              {name:'role', index:'role', width:40,sortable:false},
              {name:'activeFlag', index:'activeFlag', width:20,sortable:false}], 
            pager: jQuery('#pager'), 
            rowNum:10, 
            sortname: 'key', 
            width: '700',
            height: '300',
            sortorder: "desc", 
            viewrecords: true, 
            imgpath: 'themes/steel/images', 
            caption: 'Users'
         }).navGrid('#pager',{view:true,add:false,edit:false,del:true,search:false}); 
         
                
   }); 
          
  </script>
  
  </head> 
  <body>
  
 
  <h2> Application Users </h2>
  <table id="list" class="scroll"></table> 
  <div id="pager" class="scroll"> </div> 


  
  </body>
</html>

Except for the script in the head tag, the remaining portion of the jsp is simple. I highlighted certain key elements in red. The id attributes "list" and "pager" are referred in the script and the body tages. They need to match exactly for expected behaviour from the jqgrid. Also, the data is going to be loaded asyncronously using the "url" attribute and the format of the expected result is "xml". I highlighted both the keywords in the above sample.

Listed below is the code for "SampleList.jsp" that is used in the jqgrid script given above.

<%@page language="java" import="java.util.*" 
      contentType="text/xml;charset=utf-8" pageEncoding="utf-8"%>
     <?xml version ="1.0" encoding='utf-8'?>
<%@page import="com.google.appengine.api.datastore.KeyFactory"%>
<%@page import="com.sample.service.UserMgmtService"%>
<%@page import="com.sample.service.domain.TestUser"%>
<%@page import="com.sample.service.Util"%>

<% 
String id = request.getParameter("id"); 
UserMgmtService service = new UserMgmtService();
 int pg = Integer.parseInt
   (request.getParameter("page"));
 int limit = Integer.parseInt(request.getParameter("rows"));
 
 int totalRecords = Util.getCount(TestUser.class.getName());
 
 int totalPages = totalRecords%limit != 0? 
     (totalRecords/limit) + 1: (totalRecords/limit);
  
List<TestUser> users = null;
if ("0".equals(id)){
  users = new ArrayList<TestUser>();
  }
else{

  users = service.getUsers( pg, limit );
  }
%>
<rows> <page><%=pg%></page> <total> <%=totalPages%></total> 
        <records><%=totalRecords%></records>
<%
  for(TestUser tu: users){
%> 
<row id="<%=KeyFactory.keyToString(tu.getKey())%>">
 <cell><%=tu.getName()%></cell>
 <cell><%=tu.getEmail()==null?"":tu.getEmail()%></cell>
 <cell><%=tu.getRole()==null?"":tu.getRole()%></cell>
 <cell><%=tu.getActiveFlag()==null?"":tu.getActiveFlag()%></cell>
</row>
<%
}

%>
</rows>

I highlighted the "page" and "rows" attributes that are populated by jqgrid and we donot need to worry about managing the state of the current page or row. The attribute are automatically populated in the http request.
Also, notice the call to the service where we are passing arguments "pg" and "limit".

The attributes are injected into the "RANGE" part of the query used in UserMgmtService.

public List<TestUser> getUsers(int pg, int limit){
  
   int idx = pg * limit - limit;
     
   PersistenceManager pm = Util.getPersistenceManager();

                
   String query = "select from " 
      + com.sample.service.domain.TestUser.class.getName() 
      + " order by name asc RANGE " + idx + ","+ (pg * limit); 
                    
   List<TestUser> users = (List<TestUser>) 
                pm.newQuery(query).execute();
   users.size(); 
        
        
   pm.close();
        
   return users;

  
 }

In the above sample, there is a dummy call to users.size(). This is required to load the list of users in memory. As we are closing the persistance manager, pm, the data is not available to the jsp pages by default. A call to users.size() does the trick of loading the data with the current version of appengine API.

Below picture is the outcome of the excercise using Option 1.

Option 2: Customizing jqGrid for GAE

Second option requires few minor changes to the code that we used in Option 1. As I explained in the beginning the second option will not have all the default features provided in the jqgrid.


Step 1:

Add the following to the SamplejgGrid.jsp. We have already added the jquery libraries, so this works without any additional javascript libraries.


$('#first').remove();
$('#selbox').remove();
$('#sp_1').remove();
$('#last').remove();
$('#sp_2').remove();

Step 2:

Modify the SampleList.jsp rows tag.
<rows><page><%=pg%></page><total><%=users.size()<limit?pg:pg+1%></total> <records><%=users.size()+1 %></records>
...
...
</rows>

Notice the change to "total" and "records" compared to the previous version of the SampleList.jsp. The results from the UserMgmtService class are going to be the same in both occassions. Assuming we get results size that is equal to the page limit (say 10), we let the pager know that there may be "next" page by putting
1) total page size = current page number + 1
2) total records = page limit + 1 (say 10 + 1)

Given these two attributes , jqgrid assumes that are is going to be a next page and enables the "next" button on the grid. As we are not showing the "total pages" and "total number of records" by removing certain html elements as shown in Step 1, the incorrect calculation "total pages" and "total records" is not an issue from the display perspective.

Picture attached below is the output after applying option2. Some the elements in this image that are found in option-1 image are missing. Also complete source for SampleList.jsp and SamplejqGrid.jsp can be found below the image.

Second option, can be used for more generic search queries on google appengine database. I will explain later in my posts.

Samplejqgrid.jsp (Option 2)

<%@ page language="java" import="java.util.*" pageEncoding="ISO-8859-1"%>
<html>
<head>


<link rel="stylesheet" type="text/css" media="screen" href="themes/steel/grid.css" />
 <link rel="stylesheet" type="text/css" media="screen" href="themes/jqModal.css" /> 
 <script src="scripts/jquery-1.3.2.js" type="text/javascript"></script> 
 <script src="jquery.jqGrid.js" type="text/javascript"></script> 
 <script src="js/jqModal.js" type="text/javascript"></script> 
 <script src="js/jqDnR.js" type="text/javascript"></script> 
 
 <script type="text/javascript"> 
    jQuery(document).ready(function()
       { jQuery("#list").jqGrid(
          { url:'/SampleList.jsp', 
            datatype: 'xml', 
            mtype: 'GET', 
            colNames:['Name','Email', 'Role', 'Active'], 
            colModel :[  
             {name:'uName', index:'uName', width:60,sortable:false}, 
             {name:'email', index:'email', width:60,sortable:false},
             {name:'role', index:'role', width:40,sortable:false},
             {name:'activeFlag', index:'activeFlag', 
               width:20,sortable:false}], 
            pager: jQuery('#pager'), 
            rowNum:10, 
            sortname: 'key', 
            width: '700',
            height: '300',
            sortorder: "desc", 
            viewrecords: true, 
            imgpath: 'themes/steel/images', 
            caption: 'Users'
         }).navGrid('#pager',
               {view:true,add:false,edit:false,del:true,search:false});
         
         
         $('#first').remove();
         $('#selbox').remove();
         $('#sp_1').remove();
         $('#last').remove();
         $('#sp_2').remove();
         //$('.selbox').remove(); 
         
                
   }); 
          
  </script>
  
  </head> 
  <body>
  
 
  <h2> Application Users </h2>
  <table id="list" class="scroll"></table> 
  <div id="pager" class="scroll"> </div> 


  
  </body>
</html>

SampleList.jsp (Option 2)

<%@page language="java" import="java.util.*" 
     contentType="text/xml;charset=utf-8" pageEncoding="utf-8"%>
    <?xml version ="1.0" encoding='utf-8'?>
<%@page import="com.google.appengine.api.datastore.KeyFactory"%>
<%@page import="com.sample.service.UserMgmtService"%>
<%@page import="com.sample.service.domain.TestUser"%>
<%@page import="com.sample.service.Util"%>

<% 
String id = request.getParameter("id"); 
UserMgmtService service = new UserMgmtService();
 int pg = Integer.parseInt(request.getParameter("page"));
 int limit = Integer.parseInt(request.getParameter("rows"));
 
  
List<TestUser> users = null;
if ("0".equals(id)){
  users = new ArrayList<TestUser>();
  }
else{

  users = service.getUsers(pg, limit);
  }
%>
<rows><page><%=pg%></page><total><%=users.size()<limit?pg:pg+1%></total> 
<records><%=users.size()+1 %></records>
<%
  for(TestUser tu: users){
%> 
<row id="<%=KeyFactory.keyToString(tu.getKey())%>">
 <cell><%=tu.getName()%></cell>
 <cell><%=tu.getEmail()==null?"":tu.getEmail()%></cell>
 <cell><%=tu.getRole()==null?"":tu.getRole()%></cell>
 <cell><%=tu.getActiveFlag()==null?"":tu.getActiveFlag()%></cell>
</row>
<%
}

%>
</rows>