Paging with Hibernate

Hibernate query api has setFirstResults and setMaxResults methods that restricts the result set for ceratain amaount. Typical usage inside a generic method would be like :

public List find(String queryString, Object[] queryArgs, int first, int max) {
 Query q = getEntityManager().createQuery(queryString);
 if (queryArgs != null) {
    setParameters(q, queryArgs);
 }
 q.setFirstResult(first);
 q.setMaxResults(max);
 return q.getResultList();
}

Here If you are using Hibernate with Oracle it will wrap the original query with a select query which will filter the result based on rowid's.

One rule of thumb while using paging is that your query shouldn't have any joins on collections. Than Hibernate will just execute the original query claiming it can't exactly determine the number of results returned. The warning "firstResult/maxResults specified with collection fetch; applying in memory!" is logged and paging only occurs on the ResultSet object.

Luckily the queries I came across mostly could be refactored so that they don't use joins on collections. Like :

   EJBQuery query = new EJBQueryImpl(Foo.class, "f");
 query.join(Joins.joinFetch("f.fooBooSet", "fbs"));
 if(sorguKriteri.getBoo() != null){
    query.addRestriction(Restrictions.addEquals("fbs.boo", sorguKriteri.getBoo()));
 }

Could be rewritten as :

  EJBQuery query = new EJBQueryImpl(Foo.class, "f");
if (sorguKriteri.getBoo() != null) {
query.addIn("f", new EJBQueryImpl(FooBoo.class,"fb", (EJBQueryImpl) query)
   .addProjections(Projections.add("fb.foo"))
   .addRestriction(Restrictions.addEquals("fb.boo",sorguKriteri.getBoo())));
}

A sub select is used to filter the result instead of join.

Another common pattern used with hibernate is using a lazy list which loads results, page by page basis which looks like :

 public class PagingList<E> extends AbstractList<E> implements Serializable {
private Query query;
private int numOfResults = 0;
private int pageSize = 10;
private List<E> currentResults = new ArrayList<E>();
private int currentPageIndex = -1;
public E get(int index) {
  int pageIndex = index / pageSize;
  if (currentPageIndex != pageIndex) {
    currentPageIndex = pageIndex;
    currentResults = getService().find(query, currentPageIndex * pageSize, pageSize);
  }
  return currentResults.get(index % pageSize);
}
public int size() {
  return numOfResults;
}
public PagingList(Query query, long numOfResults, int pageSize) {
  this.query = query;
  this.numOfResults = (int) numOfResults;
  this.pageSize = pageSize;
}
public Service getService() {
  return (Service) SpringUtils.getBean("service");
}
...
}

Here the query is executed when the get method is called on the list object. Down side of this approach is that query size must be determined with a separate select.