Finding the total result size using Hibernate Query and pagination

[David Irwin]

All,

I'm using Hibernate/HQL/named queries to retrieve data from a database. In particular I use pagination (i.e. I control the number of results that are returned on any one query) so as not to pull thousands of rows from the database.

Here's the java code I have right now:

    @SuppressWarnings("unchecked")
    public final AlarmQueryResponse findAll(AlarmQuery alarmQuery) {
        // attempt to retrieve the named query
        Query dbQuery = getSession().getNamedQuery(QUERY_STANDARD);
 
        // set the query parameters
        dbQuery.setParameterList("deviceIds", alarmQuery.getDeviceIds());
        dbQuery.setParameterList("alarmLevels", alarmQuery.getLevels(), Hibernate
                .custom(AlarmLevelUserType.class));
        dbQuery.setParameterList("alarmCategories", alarmQuery.getCategories(), Hibernate
                .custom(AlarmCategoryUserType.class));
        dbQuery.setParameter("afterDate", alarmQuery.getAfter());
        dbQuery.setParameter("beforeDate", alarmQuery.getBefore());
 
        // set the query paging parameters
        dbQuery.setFirstResult(alarmQuery.getPageStart());
        dbQuery.setMaxResults(alarmQuery.getPageEnd() - alarmQuery.getPageStart());
 
        AlarmQueryResponse queryResponse = new AlarmQueryResponse();
        queryResponse.setAlarmQuery(alarmQuery);
 
        // execute the query
        queryResponse.addAlarms((List<AlarmInfo> :wink:  dbQuery.list());
 
        return queryResponse;
}

What I'd like to be able to do, however, is find out how many total results this query will return independent of the pagination (so I can display something like "Results 31 through 40 of 123" to the end user). I've looked at the Hibernate Query API but don't see anything help. Any ideas?

Thanks,
Dave

[Angel Taveras]

Hello David,

I been there before and what i did was kinda like a double query, one for the result and the other one for the count of the unpaged results. Hibernate do have a ScrollableResult that could help you a little with this.

This it's a reality that many of us encounter time to time when working with db, because of the paging and all that stuff, but there it's a pattern to solve this. Here explains about the Pagination pattern with diagrams and stuff.