Retrieving a paginated HQL query with total records included

This is going to be more of quick hit post. Many of us have written paginated queries before. (Don't need to pull back the whole database when you only need the first 100 records right ^__^ ) and, as many have discovered, ormExecuteQuery() supports the 'maxrecords' and 'offset' parameters.

This was brought up at work a few days ago (I have a feeling a lot of entries are going to start this way...) where a coworker was trying to write a query that was going to be paginated, but wanted to include the total number of records in the entire result set so that they could display it on the page. (i.e. Records 1-20 of 375, etc...)

The issue is this: HQL doesn't support COUNT() on multiple columns. So how do we get the value?
It's actually fairly simple. If we write it like so:




ORM returns the total count back with the record-set:

Which is exactly what we were looking for. Here's the important bit:

SELECT COUNT( hyundai )
FROM hyundai h
WHERE EXISTS(
      SELECT  h.id AS id,
              m.id AS musicID,
              m.name AS name
      FROM hyundai h
      JOIN h.music m       
) 

That 'exists' clause and the ability to use this a sub-query were the answer.

A quick note:

• If you have a large result set and your offset is large (i.e. 10K or so) Hibernate _will_ pull the 10K records before the next batch when it runs. If you're dealing with millions of records, it's still better to grab the data with a Common Table Expression and paginate it that way.