Pagination using a h_dataGrid and Hibernate

Problem Description:

We have some queries that can potentially return a large set of data. The first pass at this put all of the data into HttpSession and allowed a non-standard h:dataGrid like component automatically do the pagination. This works fairly well for small results sets but not so well for larger result sets as it consumes too much memory and takes a while for the first page to load. (The fancy h:dataGrid like component also automatically does sorting.)

Solution

This prototype address these issues by using Hibernate pagination instead of allowing the JSF component perform the pagination.

Instead of storing the entire results into memory, the only item that gets stored in session in a backing bean (managed bean) that has two integer fields. One of the integer fields holds the "current page", the other holds the number of pages. This is a very small foot print.

Instead of using a special dataGrid style component we used a plan old h:dataGrid. Buttons and links were added to do sorting and pagination.

The JSF related code to do pagination and sorting is very small, simple and straight forward.
The Hibernate related code to do pagination and sorting is very small, simple and straight forward.

Normally, our architecture is such that the backing bean talks to one or more managers which in turn talks to one or more DAOs. For simplicity, this prototype only has a DAO. The Spring transactions were setup directly against the DAO object.

Figure 1 shows the features that were implemented.

figure 1 Features of this prototype, pagination and sorting

Technologies Used for Solution

JSF 1.1 (MyFaces)
Standard JSF only (no special components)
Hibernate 3.0.5
Spring 1.2.5

The Hibernate DAO

The Hibernate layers consist of several hbm.xml files. The prototypes uses a modified set of persistent objects from a small application we are working on (RssFeeds->Channels->Items). The DAO sans the pagination support already existed. Here are the important methods from the DAO to implement the pagination:

HibernateRssFeedDAO.java

package com.example.jsf.hibernate.example.dao;

import java.sql.SQLException;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.dao.support.DataAccessUtils;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.example.jsf.hibernate.example.model.Channel;
import com.example.jsf.hibernate.example.model.RssFeed;

/*
 *  @author Rick Hightower, http://www.arc-mind.com, Know the Next!
 */
public class HibernateRssFeedDAO extends HibernateDaoSupport implements RssFeedDAO  {

	public int getFeedCount() {
		class ReturnValue  {
			Integer value;
		}
		final ReturnValue rv = new ReturnValue();
		getHibernateTemplate().execute(new HibernateCallback(){
			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				rv.value = 
					(Integer) session.createQuery("select count(*) from RssFeed").uniqueResult();
				return null;
			}
		});
		return rv.value.intValue();
         }

	private List getAllFeeds(final String queryName, final int startRecord, final int endRecord) {
		return getHibernateTemplate().executeFind(new HibernateCallback(){
			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				Query query = session.getNamedQuery(queryName);
				query.setFirstResult(startRecord);
				query.setMaxResults(endRecord);
				List list = query.list();
				return list;
			}
		});
	}

	public List getAllFeeds(final int startRecord, final int endRecord) {
		return getAllFeeds("getAllFeeds", startRecord, endRecord);
	}

	public List getAllFeedsAsc(int startRecord, int endRecord) {
		return getAllFeeds("getAllFeedsAsc", startRecord, endRecord);
	}

	public List getAllFeedsDec(int startRecord, int endRecord) {
		return getAllFeeds("getAllFeedsDec", startRecord, endRecord);
	}

The methods for this prototype DAO are as follows:

int getFeedCount
- Get the count of RssFeeds in the system.
List getAllFeeds (final String queryName, final int startRecord, final int endRecord)
- Helper method that executes a named query and retrives results from the startRecord to the endRecord.
List getAllFeeds(final int startRecord, final int endRecord)
- Method that executes a named query to get all RssFeeds.
List getAllFeedsAsc(int startRecord, int endRecord)
- Method that executes a query to get all RssFeeds with sort order ascending.
List getAllFeedsDec(int startRecord, int endRecord)
- Method that executes a query to get all RssFeeds with sort order descending.

The public methods getAllFeeds, getAllFeedsAsc and getAllFeedsDec execute the private helper method getAllFeeds with the name of the named query that the methods needs to execute, and the startRecord and endRecord. Here are the three queries that get executed located in the hbm.xml mapping file as follows:

Hibernate mapping file

<query name="readFeedPopulated">from RssFeed rss join fetch rss.channels where rss.id=:rssId</query>
	<query name="getAllFeeds">from RssFeed</query>
	<query name="getAllFeedsAsc">from RssFeed rss order by rss.location </query>
         <query name="getAllFeedsDec">from RssFeed rss order by rss.location desc</query>

Figure 2 describes this pictorially as follows:

Figure 2: Named queries that are used for this prototype.

The endRecord means the length of the items that will be returned not the location of the end record in the database.

The key to using Hibernate pagination is in the private helper method getAllFeeds as follows:

HibernateRssFeedDAO.java

private List getAllFeeds(final String queryName, final int startRecord, final int endRecord) {
		return getHibernateTemplate().executeFind(new HibernateCallback(){
			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				Query query = session.getNamedQuery(queryName);
				query.setFirstResult(startRecord);
				query.setMaxResults(endRecord);
				List list = query.list();
				return list;
			}
		});
	}

Notice that the above method calls setFirstResult and setMaxResults (used in the anonymous HibernateCallback.doInHibernate callback object to be precise).

The Hibernate API docs say the following about these methods:

setFirstResult

public Query setFirstResult(int firstResult)Set the first row to retrieve. If not set, rows will be retrieved beginnning from row 0.

Parameters: firstResult - a row number, numbered from 0

setMaxResults

public Query setMaxResults(int maxResults)Set the maximum number of rows to retrieve. If not set, there is no limit to the number of rows retrieved.

Parameters: maxResults - the maximum number of rows

This allows us to limit the number of results to just the items that we need for the current page.

The JSF Backing Bean

There is one backing bean that uses the DAO as follows:

ChannelFeedsBackingBean.java

package com.example.jsf.hibernate.example.web;

import java.text.MessageFormat;
import java.util.List;
import java.util.Random;


import com.qualcomm.jsf.hibernate.example.dao.RssFeedDAO;
import com.qualcomm.jsf.hibernate.example.model.RssFeed;

/**
 * @author Rick Hightower, http://www.arc-mind.com Know the Next
 *
 */
public final class ChannelFeedsBackingBean  {

	private RssFeedDAO rssDAO;
	private int currentPage;
	private int numberOfPages;
	
	/* This should be parameters or properties stored somewhere */
	private final int itemsPerPage = 10;
	private final int rewindFastForwardBy = 10;
	
	private static final int SORT_LINK_ASC = 0;
	private static final int SORT_LINK_DEC = 1;
	private static final int SORT_LINK_NATURAL = 2;
	private int linkSortOrder = SORT_LINK_NATURAL;
	
	public ChannelFeedsBackingBean() {
		currentPage = 0;
	}
	
	public String sortLinkAsc () {
		currentPage = 0;
		linkSortOrder = SORT_LINK_ASC;
		return "sortLinkAsc";
	}
	public String sortLinkDec () {
		currentPage = 0;		
		linkSortOrder = SORT_LINK_DEC;
		return "sortLinkAsc";
	}
	
	public RssFeedDAO getRssDAO() {
		return rssDAO;
	}
	public void setRssDAO(RssFeedDAO rssDAO) {
		this.rssDAO = rssDAO;
	}
	
	public String getRecordStatus () {
		return MessageFormat.format("{0} of {1}", 
				new Object []{
				Integer.valueOf(currentPage),
				Integer.valueOf(numberOfPages)
				});
	}

	
	public String rewind () {
		this.rewind(this.rewindFastForwardBy);
		return "rewind";
	}

	public void rewind (int aRewindFastForwardBy) {
		int newPageNumber = currentPage - aRewindFastForwardBy;
		if (newPageNumber < 0) {
			currentPage = 0;
		} else {
			currentPage = newPageNumber;
		}
	}	

	public void forward (int aRewindFastForwardBy) {
		int newPageNumber = currentPage + aRewindFastForwardBy;
		if (newPageNumber > this.numberOfPages) {
			currentPage = this.numberOfPages;
		} else {
			currentPage = newPageNumber;
		}
	}	

	public String previous () {
		this.rewind(1);		
		return "previous";
	}

	public String next () {
		this.forward(1);
		return "next";
	}
	
	public String fastForward () {
		this.forward(this.rewindFastForwardBy);
		return "fastForward";
	}


	public List getCurrentRssFeeds() {
		initCount();
		int startRecord = this.currentPage * this.itemsPerPage;
		switch (this.linkSortOrder) {
			case SORT_LINK_ASC:
				return this.rssDAO.getAllFeedsAsc(startRecord, this.itemsPerPage);
			case SORT_LINK_DEC:
				return this.rssDAO.getAllFeedsDec(startRecord, this.itemsPerPage);
			case SORT_LINK_NATURAL:
				return this.rssDAO.getAllFeeds(startRecord, this.itemsPerPage);
			default:
				throw new IllegalStateException("SORT ORDER NOT RECOGNIZED!");
		}
	}

	private void initCount() {
		numberOfPages = rssDAO.getFeedCount() / itemsPerPage;
		if (currentPage > numberOfPages) {
			currentPage=numberOfPages;
		}
	}

	/* This method is just for testing */
	public String add100Records () {
		System.out.println("ADDING 100 RECORDS TO DATABASE");
		Random random = new Random();

		for (int index = 0; index < 100; index++){
			random.setSeed(System.currentTimeMillis() + index);
			RssFeed feed = new RssFeed("zzhttp://www.arc-mind.com" + random.nextLong() 
					                    + "-" + System.currentTimeMillis() + "-" + index);
			this.rssDAO.storeFeed(feed);
		}
		return "add100Records";
	}
	
}

Method Summary for backing bean:

sortLinkAsc
- Event Handler for "sort ascending link".
- Sets the current sort mode to ascending.
sortLinkDec
- Event Handler for "sort descending link".
- Sets the current sort mode to descending.
setRssDAO
- Used by Spring's dependency injection
getRecordStatus
- Used by JSP page to display the current results of the pagniation routine
rewind
- Rewinds the pagination by ten pages
rewind (int aRewindFastForwardBy)
- helper method used by rewind and previous methods
- Does bounds checking to ensure you do not rewind past 0
forward (int aRewindFastForwardBy)
- helper method used by forwad and fastForward methods
- Does bounds checking to ensure you do not forward past the last page
previous
- Rewinds by 1 page
next
- Forwards by 1 page
fastForward
- Fast Forwards by 10 pages
getCurrentRssFeeds
- Uses the DAO to retrieve RssFeeds in the specified order
- Call initCount to get the current count
- This gets used by the JSP page. It is the value binding for the h:dataTable
initCount
- Uses the DAO to get a count of RssFeeds
- Initializes the numberOfPages
add100Records
- Helper method to add records to the database quickly for testing.

The code to implement this backing bean is quite simple. On the JSP page you have two command links that can change the sort mode, and a h:dataGrid that is value-bound to the currentRssFeeds property (getCurrentRssFeeds). The getCurrentRssFeeds gets the data from the DAO in three modes: natural, sort ascending and sort descending. The next(), previous(), forward() and fastForward move back & forward through the pages either a page a time or ten pages at a time.

The setRssDAO method is used to inject the DAO object. The Spring integration support was added to the faces-config.xml file as follows:

Faces-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE faces-config PUBLIC "-//Sun Microsystems, Inc.//DTD JavaServer Faces Config
 1.1//EN" "http://java.sun.com/dtd/web-facesconfig_1_1.dtd">

<faces-config >
    <application>
        <!-- Install the Spring JSF integration -->
        <variable-resolver>org.springframework.web.jsf.DelegatingVariableResolver</variable-resolver>
        <locale-config><default-locale>en</default-locale></locale-config>
        
        <!--  Add this for I18n
        <message-bundle>com.qualcomm.jsf.hibernate.example.resources.jsf.Messages</message-bundle>    
        -->
    </application>

	<managed-bean>
		<managed-bean-name>ChannelFeedsBackingBean</managed-bean-name>
		<managed-bean-class>
			com.example.jsf.hibernate.example.web.ChannelFeedsBackingBean
		</managed-bean-class>
		<managed-bean-scope>session</managed-bean-scope>
     	<managed-property>
            <property-name>rssDAO</property-name>
            <value>#{rssDAO}</value> <!--- Inject this value from Spring -->
        </managed-property>
     </managed-bean>
</faces-config>

Be sure to read the comments above to see how to integrate Sprng and JSF. Pay attention to org.springframework.web.jsf.DelegatingVariableResolver. (A follow up will discuss the rest of the Spring setup including OpenInSessionViewFilter and transaction support for the DAO).

The JSP front-ends the backing bean. It has (as expected) 4 buttons (next, fastForward, previous, and rewind), a dataGrid. The dataGrid consists of only one column. The column header has a label for the column and two links: asc (ascending) and dec (descending). Not much time was given to make this prototype pretty. One would hope that hte buttons and links would be replaced with images in the real applications.

Here is the JSF enabled JSP page:

<%@ page language="java" pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsf/html" prefix="h" %>
<%@ taglib uri="http://java.sun.com/jsf/core" prefix="f" %>

<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
	<base href="<%=basePath%>">

	<title>My JSF 'showRssFeedsTable.jsp' starting page</title>

	<meta http-equiv="pragma" content="no-cache">
	<meta http-equiv="cache-control" content="no-cache">
	<meta http-equiv="expires" content="0">    
	<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
	<meta http-equiv="description" content="This is my page">
    <link rel="stylesheet" 
            type="text/css" 
            href="<%=basePath%>css/main.css">
	
</head>
  
<body>
	<f:view>
		<h:form>
			<h:dataTable value="#{ChannelFeedsBackingBean.currentRssFeeds}" 
			             var="feed"
					     rowClasses="oddRow, evenRow" 
					     headerClass="tableHeader" >
				<h:column>
			         <f:facet name="header">
				<h:panelGroup>
				    <h:outputText value="Location"/>
				    <f:verbatim>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[</f:verbatim>
				      <h:commandLink styleClass="smallLink" 
				                     action="#{ChannelFeedsBackingBean.sortLinkAsc}">
				          <h:outputText id="ascTitle" value="asc"/>
		                        </h:commandLink>
					
				      <h:outputText value="," />
                                           <!-- Sort decsending -->
				      <h:commandLink 	styleClass="smallLink" 
						action="#{ChannelFeedsBackingBean.sortLinkDec}">
                                               <h:outputText id="decTitle" value="dec"/>
				      </h:commandLink>
				    <f:verbatim>]</f:verbatim>
				</h:panelGroup>
				</f:facet>
						
						<h:outputText value="#{feed.location}"/>
						
				</h:column>	
			</h:dataTable>
			
			<h:panelGroup>
				<h:commandButton 
				         value="<<"
				         action="#{ChannelFeedsBackingBean.rewind}"
				         alt="rewind"/>
				<h:commandButton 
				         value="<"
				         action="#{ChannelFeedsBackingBean.previous}"
				         alt="previous"/>
	
				<h:outputText value="#{ChannelFeedsBackingBean.recordStatus}" />
	
				<h:commandButton 
				         value=">"
				         action="#{ChannelFeedsBackingBean.next}"
				         alt="next"
				         />
				<h:commandButton 
				         value=">>"
				         action="#{ChannelFeedsBackingBean.fastForward}"
				         alt="fast forward"/>
	
			</h:panelGroup>
			<br />
			<h:panelGroup>
				<h:commandButton value="Create Test Data" 
				                 action="#{ChannelFeedsBackingBean.add100Records}"/>
	
			</h:panelGroup>
		</h:form>	
	</f:view>
</body>
</html>