Hibernate: Single Shot Query to get Count and Data by ScrollableResults

Pagination is one issue when you display list-style data.
Usually it requires two kinds of data: "certain range of objects"(offset, limit) and "total number of data"(count).

There is a couple of ways to implement this.

(1) Use two queries. one for total number(count) and one for ranged data(offset,limit).
(2) Load all data into java layer, then check the size of list.

(2) is not practical if you have large number of data and displaying only a few of them, it has huge overhead.
So, I have seen couple of projects using (1) style to display list of data and total number of them.

For retrieving ranged data, hibernate provides "setFetchSize(int)" and "setFirstResult(int)" to specify offset and limit.
This way, application only instantiate ranged return objects.
Also, these two methods separate pagination information from hql and absorbes vendor specific offset and limit SQL.

For example, HQL that retrieves all active users is :
"from User where active = true"
you may easily retrieve ranged users from offset 10 to 30, 30 to 50...

Now you need to retrieve total number of active users in order to display how many pages it has.
For total number of active users, you may use this hql.
"select (*) from User where active = true"

Then, how do you manage those almost similar two queries?

From my experience, some of the applications use string manipulation that dynamically replace or add select clause to HQL statement.
(Adding "select count(*)" is easy to do because hql statement can start with from-clause. )
But replacing may not easy if the hql already has select clause. "select id, name from User where active = true".
You can write some methods to replace it but the source code may not seem neat.

Some applications prepare two hql statements, one for total number and the other for data.


If your JDBC happily supports scrollable result set, here is one way to retrieve count and data by single shot query.

public ResultList scrollCount(final String hql, final int scrollOffset,
  final int scrollSize) {
    return (ResultList) getHibernateTemplate().execute(
      new HibernateCallback() {
        public Object doInHibernate(Session session)
                throws HibernateException, SQLException {

          Query q = session.createQuery(hql);
          ScrollableResults rs = q.scroll(ScrollMode.FORWARD_ONLY);

          rs.beforeFirst();
          rs.scroll(scrollOffset);

          ResultList resultList = new ResultList();
          for (int i = 0; i < scrollSize && rs.next(); i++) {
            Object[] results = rs.get();
            // flatten only if one object per row
            resultList.add(results.length == 1 ? results[0]
                  : results);
          }

          rs.last();
          final int total = rs.getRowNumber() + 1; // start with -1
          resultList.setTotalSize(total);

          return resultList;
        }
    });
}

public class ResultList extends ArrayList {
  private int totalSize;

  public int getTotalSize() {
      return totalSize;
  }

  public void setTotalSize(int totalSize) {
      this.totalSize = totalSize;
  }
}

You can put it into your dao class, probably in generic dao, base dao, abstract dao...

So, now you don't need to do any string manipulation.
I'm not sure about the cost of moving result set.
But single query can retrieve required two data.
From maintaining source code view, it also reduces duplication.