Implementing database-paging for JSF List Views

The project I am on uses JavaSever Faces (JSF) for its UI. For pages that show a list of records we make use of the datatable component from the RichFaces library. We paginate these lists within the UI layer by using the RichFaces datascroller component. A drawback of this approach is that we are fetching all the records from the database and then only displaying one page worth of items. This could be fine for lists that run a few pages, but many of our views can contain a significant number of pages. For these views I wanted to have the list pagination done at the database layer, hopefully with as minimal an impact on our current implementation as possible.

Here’s the relevant portion of one of our list views:

<rich:dataTable value="#{MessageValidationLogRecordsController.model}" id="items" var="record" rows="15">

	<--! a bunch of columns go here...  -->
	<rich:column></rich:column>

</rich:dataTable>

<rich:datascroller align="left" for="items" maxPages="20" boundaryControls="auto"
	page="#{MessageValidationLogRecordsController.currentPage}"/>

and the relevant code from our UI controller class:

private MessageValidationLogService messageValidationLogService;
	private List<MessageValidationLogRecord> model;

	@PostConstruct
	public void initialize() {
		this.model = this.messageValidationLogService.getAllRecords();
	}

	public ListSequenceDataModel getModel() {
		return this.model;
	}

and finally (skipping a service call that is just a pass-through), our DAO call to retrieve the records:

public List<MessageValidationLogRecord> getAllRecords() {
	return getHibernateTemplate().executeFind(new HibernateCallback() {
		public Object doInHibernate(Session session) throws HibernateException, SQLException {
			Criteria criteria = session.createCriteria(MessageValidationLogRecord.class);
			criteria.addOrder(Order.desc("validationTime"));
			return criteria.list();
		}
	});
}

Whatever solution was to be implemented, the call to criteria.list() had to go, or at the very least be modified to not return every record in the table on every call.

Some research led to a recommendation to use Hibernate’s .iterate() instead of .list(), and iterate over every record until getting to the section of records to send back. More research indicated that iterate() could be more efficient than list() if enough records are already cached, but that in a worst-case scenario each call to iterate() could result in a call to the database. Not the solution I was looking for.

I know that in Oracle I could make use of rownum to paginate at the database level, but my target database was PostgreSQL. It turns out PostgreSQL has a LIMIT X OFFSET Ysyntax that would do exactly what I wanted (grab at most X rows from a query, starting at row Y). Of course, I preferred a portable Hibernate solution rather than coding vendor-specific SQL. I knew Hibernate’s setMaxResults() method would restrict the number of records returned, and it was simple enough to find out about Hibernate’s setFirstResult(). With sql logging turned on, I verified that these two criteria methods did indeed result in generating LIMIT X OFFSET Y sql.

So now I could paginate at the database level quite easily. But now I needed to get my JSF page to play ball.

Sine we were currently using the RichFaces datatable component, I looked for a different flavour of this component in their component library. While ExtendedDataTable sounded promising, it was really more for visual decorations (scrollable tables, etc). However, I stumbled upon the RichFaces ExtendedTableDataModel class. It indicated it supported a pageable data model, with the only requirement being to provide it with an object that implemented their DataProvider interface:

public interface DataProvider<T> extends Serializable {

	public int getRowCount();
	public List<T> getItemsByRange(int firstRow, int endRow);

	public T getItemByKey(Object key); // uninteresting; ignored from this point on
	public Object getKey(T item); // uninteresting; ignored from this point on

}

Excellent! Looks like exactly what I wanted. Instead of giving the datatable the list of records, you give it a dataprovider (via ExtendedTableDataModel) that knows how to access pages of your data. The datatable and the datascroller work together with the model and dataprovider to allow for a pageable list. Here’s the data provider I created (minus the boring bits):

public class MessageValidationLogRecordsControllerDataProvider implements DataProvider<MessageValidationLogRecord> {

	private MessageValidationLogService messageValidationLogService;

	public MessageValidationLogRecordsControllerDataProvider(MessageValidationLogService messageValidationLogService) {

		this.messageValidationLogService = messageValidationLogService;

	}

	public List<MessageValidationLogRecord> getItemsByRange(int firstRow, int endRow) {

		return this.messageValidationLogService.getAllRecords(firstRow, endRow);

	}

	public int getRowCount() {

		return this.messageValidationLogService.countAll();

	}

}

And here’s how the data provider is used from our UI controller class:

public class MessageValidationLogRecordsController extends CurrentPageSupport {

	private MessageValidationLogService messageValidationLogService;

	private ExtendedTableDataModel<MessageValidationLogRecord> model;

	@PostConstruct

	public void initialize() {

		MessageValidationLogRecordsControllerDataProvider dataProvider

			= new MessageValidationLogRecordsControllerDataProvider(this.messageValidationLogService);

		this.model = new PageableExtendedTableDataModel<MessageValidationLogRecord>(dataProvider);

	}

	public ExtendedTableDataModel<MessageValidationLogRecord> getModel() {

		return this.model;

	}

}

And here are the changes to our DAO class:

public List<MessageValidationLogRecord> getAllRecords(final int startRow, final int endRow) {
	return getHibernateTemplate().executeFind(new HibernateCallback() {
		public Object doInHibernate(Session session) throws HibernateException, SQLException {
			Criteria criteria = session.createCriteria(MessageValidationLogRecord.class);
			criteria.addOrder(Order.desc("validationTime"));
			criteria.addOrder(Order.desc("id"));
			criteria.setFirstResult(startRow);
			criteria.setMaxResults(endRow - startRow); // rows are 0-based; endRow is *exclusive*
			return criteria.list();
		}
	});
}

public int countAll() {
	return (Integer) getHibernateTemplate().execute(new HibernateCallback() {
		public Object doInHibernate(Session session) throws HibernateException, SQLException {
			Criteria criteria = session.createCriteria(MessageValidationLogRecord.class);
		        criteria.setProjection(Projections.rowCount());
		        return criteria.list().get(0);
		}
	});
}

So here are the steps I had to follow to get everything working:

  1. Modify my original DAO call to be paginated, taking in a start and end row.
  2. Add a new DAO call to return the number of records (pretty much the same call as above, but with a rowcount projection).
  3. Create a data provider and pass it to an instance of ExtendedTableDataModel, and hand the model to the datatable (no changes required to the JSF/xhtml page!).

Awesome! Except for the fact that it, uh, DIDN’T WORK. Every call to my dataprovider was passed a requested range of (0, numRows). What???

It turns out that the RichFaces implementation of ExtendedTableDataModel has, as its last line of code in its loadData() method:

//load all from provider and get sublist
return dataProvider.getItemsByRange(0, rowCount).subList(startRow, endRow);

In other words: ignore the fact that the DataProvider knows how to fetch a range of rows and tell it to grab the universe instead. Oh, and THEN filter the records in memory. Well, at least the code is open source and the method in question is not private. So I created a PageableExtendedTableDataModel whose sole role in life is to replace the above line with:

// TM - Ugh! Grabs all the rows anyway!!!
//load all from provider and get sublist
// return dataProvider.getItemsByRange(0, rowCount).subList(startRow, endRow);

// TM - Modification: this allows the data provider to pick up a range of rows
return getDataProvider().getItemsByRange(startRow, endRow);

And everything works as it should. With very minimal changes, the view in question is now paginating at the database.

It's only fair to share...
Share on FacebookGoogle+Tweet about this on TwitterShare on LinkedIn

Leave a Reply