Paginate with Hibernate
Pagination of search results is a common requirement for any application. However, I do not want to solve it again every time, especially not when using Hibernate: to manually count total number of records, then to find just page elements, etc. My idea is to use search Criteria and HQL as they are: without any paging 'statements' and to pass them to some 'magic' method that will return list of items on requested page. It sounds simple, but, as I found out, the solution required some hacks at the end.
Let's summarize the tasks that this magic methods have to perform:
- calculate total number of items - but not doing any
orderto make things faster, sincecount()doesn't need it, - read just page items,
- prepare resulting list of items together with page information.
Note that these methods are part of my extended HibernateTemplate, created in custom HibernateDaoSupport instead of default one.
HQL pagination
Lets start with the simpler problem. Here is the method:
public PageData fetchPageData(final PageRequest pageRequest, final String hql, final Object[] values) {
return (PageData) execute(new HibernateCallback() {
@Override
public Object doInHibernate(Session session) throws HibernateException {
// [1] total size
String countHql = "select count(1) " + removeSelect(removeOrders(hql));
List countlist = find(countHql, values);
int totalCount = ((Long) countlist.get(0)).intValue();
if (totalCount < 1) {
return new PageData();
}
// [2] page elements
int startIndex = PageData.calcFirstItemIndexOfPage(pageRequest, totalCount);
Query query = session.createQuery(hql);
applyParametersToQuery(query, values);
List list = query.setFirstResult(startIndex).setMaxResults(pageRequest.getPageSize()).list();
return new PageData(pageRequest.getPage(), totalCount, pageRequest.getPageSize(), list);
}
});
}First thing here is to remove all orders from HQL query and perform the count (lines 7 and 8). We also need to remove select so we can replace it with select count(1). Removal is just simple string manipulation:
public class HqlUtil {
public static String removeSelect(String hql) {
int beginPos = hql.toLowerCase().indexOf("from");
return hql.substring(beginPos);
}
private static final Pattern p = Pattern.compile("order\\s*by[\\w|\\W|\\s|\\S]*", Pattern.CASE_INSENSITIVE);
public static String removeOrders(String hql) {
Matcher m = p.matcher(hql);
StringBuffer sb = new StringBuffer();
while (m.find()) {
m.appendReplacement(sb, "");
}
m.appendTail(sb);
return sb.toString();
}
}Once when we know the total count of items we can list() just page items by narrowing the query using setFirstResult() and setMaxResults() method (line 18). Nothing special, anyway.
Criteria pagination
One might expected that things should be even simpler when working with criteria. Unfortunately, it wasn't like that. Here is the method code so we can discuss it:
private static final String FIELD_ORDER_ENTRIES = "orderEntries";
public PageData fetchPageData(final PageRequest pageRequest, final DetachedCriteria detachedCriteria) {
return (PageData) execute(new HibernateCallback() {
@Override
public Object doInHibernate(Session session) throws HibernateException {
Criteria criteria = detachedCriteria.getExecutableCriteria(session);
// [1] total size
CriteriaImpl impl = (CriteriaImpl) criteria;
Projection projection = impl.getProjection();
List<CriteriaImpl.OrderEntry> orderEntries;
List<CriteriaImpl.OrderEntry> newOrderEntries = new ArrayList<CriteriaImpl.OrderEntry>();
try {
//noinspection unchecked
orderEntries = (List<CriteriaImpl.OrderEntry>) BeanUtil.getDeclaredProperty(impl, FIELD_ORDER_ENTRIES);
BeanUtil.setDeclaredProperty(impl, FIELD_ORDER_ENTRIES, newOrderEntries);
} catch (Exception ex) {
throw new CriteriaBuilderException(ex.toString());
}
int totalCount = ((Integer) criteria.setProjection(Projections.rowCount()).uniqueResult()).intValue();
// [2] page elements
criteria.setProjection(projection);
if (projection == null) {
criteria.setResultTransformer(CriteriaSpecification.ROOT_ENTITY);
}
newOrderEntries.addAll(orderEntries);
if (totalCount < 1) {
return new PageData();
}
int startIndex = PageData.calcFirstItemIndexOfPage(pageRequest, totalCount);
List list = criteria.setFirstResult(startIndex).setMaxResults(pageRequest.getPageSize()).list();
return new PageData(pageRequest.getPage(), totalCount, pageRequest.getPageSize(), list);
}
});
}
The first problem is how to read order entries from criteria and how to replace them (with no orders). The only way to achieve this was using reflection: lines 16 and 17. Of course, this is quite a hack and this code depends on Hibernates internal implementation, but... it works:) At least I am using quite fast bean util library.
Next thing is to calculate total count of items (line 21). For that we need to preserve original projections, since we need to use Projections.rowCount().
Ok, now things becomes easier. To get just page results we need to set back the original projection, but also the original orders. Orders can be set back using reflection, however, addAll() (line 28) worked fine with me.
Finally, line 38 is similar to what we have with HQL pagination, where we actually list the results.
Usage
With above methods and its overload variants I can simply pass HQL or criteria without worrying about the pagination. fetchPageData() will do everything for me and return the results. Using criteria is especially nice, since we can prepare it dynamically, from all non-null fields of some domain objects, what results in search/page code of only few lines... but that is some other story.
Paging objects
For completeness, here is the code of paging objects, PageRequest and PageData:
public class PageRequest {
protected int page;
protected int pageSize;
...
// other fields and accessors methods
}
Page data:
public class PageData {
public static int defaultPageSize = 10;
protected final int pageSize; // all these fields has getter methods
protected final int firstIndex;
protected final int lastIndex;
protected final List pageItems;
protected final int totalItems;
protected final int totalPages;
protected int currentPage;
protected final int pageItemsCount;
/**
* Main constructor.
* @param page current page
* @param size total number of items
* @param pageSize number of items per page
* @param pageItems list of fetched items
*/
public PageData(int page, int size, int pageSize, List pageItems) {
if (pageSize <= 0) {
pageSize = defaultPageSize;
}
this.pageSize = pageSize;
this.totalItems = size;
this.pageItems = pageItems;
this.totalPages = (totalItems % pageSize == 0) ? totalItems / pageSize : totalItems / pageSize + 1;
if (page < 1) {
page = 1;
}
if (page > totalPages) {
page = totalPages;
}
this.currentPage = page;
this.firstIndex = calcFirstItemIndexOfPage(page, pageSize, size);
int last = isLastPage() ? totalItems - 1: firstIndex + pageSize - 1;
int itemsPerPage = last - firstIndex + 1;
if (last < 0) {
last = 0;
itemsPerPage = 0;
}
this.lastIndex = last;
this.pageItemsCount = itemsPerPage;
}
public boolean hasNextPage() {
return currentPage < totalPages - 1;
}
public boolean isLastPage() {
return currentPage == totalPages;
}
public boolean hasPreviousPage() {
return currentPage > 1;
}
public boolean isFirstPage() {
return currentPage == 1;
}
// ---------------------------------------------------------------- utilities
/**
* Calculates page number that contains some item.
*/
public static int calcPageOfItem(int itemIndex, int pageSize) {
return itemIndex / pageSize + 1;
}
/**
* Calculates the first item index of requested page.
*/
public static int calcFirstItemIndexOfPage(int page, int pageSize, int total) {
if (total == 0) {
return 0;
}
if (page < 1) {
page = 1;
}
int first = (page - 1) * pageSize;
if (first >= total) {
first = ((total - 1) / pageSize) * pageSize; // first item on the last page
}
return first;
}
/**
* Calculates first item index of the page.
*/
public static int calcFirstItemIndexOfPage(PageRequest pageRequest, int total) {
return calcFirstItemIndexOfPage(pageRequest.getPage(), pageRequest.getPageSize(), total);
}
}
PageRequest is simple. PageData holds results, but also calculates various data regarding target page. Please note that most of JavaDoc and all getters have been removed from above code to make it shorter.
Contents
Read about...
