Hey 2011: Where is my easy pagination and sort?

Update (6/22/12): Even better; I've found a great new ORM library called ebean. Article forthcoming!


Update (5/23/11): I found some exciting stuff around JPA and pagination in the new Spring Data project: http://www.springsource.org/spring-data/jpa

No matter which framework I use, it always seems like I need to re-invent the wheel when it comes to supporting pagination and sort in my web applications. Now, I'll admit that my experience is still mostly limited to Java and Spring (and JPA/Hibernate, for that matter), but c'mon, at least give me something basic, like what I've written below. When you want to display a pagable, sortable table in a UI, you are going to want to do common things like determine total rows, total filtered rows, current count, as well as sort on one or more fields.

	public SearchResults<T> search(SearchRequest request) {
	String q = "from " + clazz.getSimpleName() + " o ";
	Map<String, Object> qParams = new HashMap<String, Object>();
	if( request.getQuery() != null ){
	q += "where " + request.getQuery();
	}
	else if( request.getSearchFields() != null && request.getSearchFields().size() > 0 ){
	q += "where ";
	for( Map.Entry<String, Object[]> f : request.getSearchFields().entrySet() ){
	if( f.getValue().length > 1 ){
	q += "o." + f.getKey() + " in (" + StringUtils.join(f.getValue(), ',') + ") and ";
	}
	else {
	try {
	String resolvedKey = f.getKey().replace('.', '_');
	q += "o." + f.getKey() + "=:" + resolvedKey;
	Class<?> paramType = resolveNestedPropertyStatically(clazz, f.getKey().toString());
	Object resolvedParam = f.getValue()[0];
	if( !paramType.equals(resolvedParam.getClass()) ){
	resolvedParam = BeanUtils.findMethod(paramType, "valueOf", String.class).invoke(null, resolvedParam);
	}
	qParams.put(resolvedKey, resolvedParam);
	q += " and ";
	}
	catch( Exception e ){
	LOG.error("Error resolving query parameters: " + e.getMessage(), e);
	throw new RuntimeException(e);
	}
	}
	}
	q = q.substring(0, q.length() - " and ".length());
	}
	String o = "";
	if( request.getSortColumns() != null ){
	o += "order by ";
	for( Map.Entry<String, String> sortCol : request.getSortColumns().entrySet() ){
	o += "o." + sortCol.getKey() + " " + sortCol.getValue() + ", ";
	}
	if( request.getSortColumns().size() > 0 )
	o = o.substring(0, o.length() - 2);
	}
	long totalRecords = (Long) entityManager.createQuery(
	"select count(o) from " + clazz.getSimpleName() + " o").getSingleResult();
	Query query = entityManager.createQuery("select count(o) " + q);
	for( Map.Entry<String, Object> qParam : qParams.entrySet() ){
	query.setParameter(qParam.getKey(), qParam.getValue());
	}
	long totalDisplayRecords = (Long) query.getSingleResult();
	query = entityManager.createQuery("select o " + q + " " + o);
	for( Map.Entry<String, Object> qParam : qParams.entrySet() ){
	query.setParameter(qParam.getKey(), qParam.getValue());
	}
	query.setFirstResult(request.getStart());
	query.setMaxResults(request.getCount());
	List<T> data = query.getResultList();
	SearchResults<T> results = new SearchResults<T>(data, totalRecords, totalDisplayRecords);
	return results;
	}

view raw gistfile1.java hosted with ❤ by GitHub

This snippet did require me to come up with a cool bit of code to statically determine the type of a nested property specified using bean notation:

	private Class<?> resolveNestedPropertyStatically(Class<?> c, String property) throws Exception {
	int firstDot = property.indexOf(".");
	String baseProperty = firstDot != -1 ? property.substring(0, firstDot) : property;
	Class<?> basePropertyClass = BeanUtils.getPropertyDescriptor(c, baseProperty).getPropertyType();
	if( firstDot == -1 ){
	return basePropertyClass;
	}
	else {
	return resolveNestedPropertyStatically(basePropertyClass,
	property.substring(firstDot + 1, property.length()));
	}
	}

view raw gistfile1.java hosted with ❤ by GitHub

*note that this uses org.springframework.beans.BeanUtils, and not org.apache.commons.beanutils.BeanUtils; this is mainly because Spring's version allows for static type resolution already.