Grails Cookbook - A collection of tutorials and examples

Grails HQL Pagination Examples

This will show examples on how to perform pagination with Grails HQL. A domain's executeQuery method can be used to invoke HQL commands and add pagination logic.

Introduction

When a query to the database matches a large number of records (E.g. thousands or more), it is not a good idea to display all of them in a single page. Loading a large number of records from the database will result to large overhead and slow page render - which will make user experience unpleasant. A better approach is to display few records at a time. For example, display 10 pages at a time and let the user jump to desired pages when required. Loading a small number of records is much faster and efficient.

To perform Grails HQL pagination, we need two things: the maximum number of records to fetch (e.g. 10 records at a time), and the offset of the first record to retrieve. In Grails HQL, the offset is zero-based. It means the first record has the offset 0.

Assuming we have the query "from Person order by id", this is the code to retrieve the first page:

def page1 = Person.executeQuery("from Person order by id", [offset:0, max:10])

This means to retrieve records starting from the first record (index 0) and retrieve a maximum of 10 records. If we have 10 or more records in the database, this will give us the first 10 records - which is equivalent to offset 0 trough offset 9.

If we want to retrieve the second page, we need to fetch starting from the 11th record (index 10). Hence this is the code:

def page2 = Person.executeQuery("from Person order by id", [offset:10, max:10])

Similarly, here are the codes to retrieve pages 3 to 5:

def page3 = Person.executeQuery("from Person order by id", [offset:20, max:10])
def page4 = Person.executeQuery("from Person order by id", [offset:30, max:10])
def page5 = Person.executeQuery("from Person order by id", [offset:40, max:10])

Important Note

Here are some things you need to note while using Grails HQL pagination:

HQL is Database Agnostic

Since GORM is based on Hibernate, HQL code are database agnostic. It means our code will be able to run on most popular database software (E.g. MySQL, Oracle, etc).

This means that it is Hibernate's problem to convert our HQL code to it's proper SQL counterpart. This has great productivity benefit because pagination has different implementation on each RDBMS. For example, this HQL code

Person.executeQuery("from Person order by id", [offset:20, max:10])

Has this MySQL translation:

SELECT * FROM PERSON ORDER BY ID LIMIT 20,10;

And this Oracle translation:

SELECT * 
FROM ( SELECT *, rownum rn
               FROM (SELECT *
               FROM PERSON
               ORDER BY ID)
         WHERE rownum <= 30)
 WHERE rn >= 21

As you could see, the approach is very different with these two databases. Imagine how much more complex it will become if we factor in other databases. Therefore, using HQL makes coding of database agnostic application easier.

Order By

You should always use pagination in conjunction with order by. If there is no order by clause, the ordering of database results has no guarantee. For example, if these are the result of a query (E.g. select name from animal):

Cat
Elephant
Dog

Running the same query again may produce this order:

Dog
Cat
Elephant

This means that if we don't have order by clause in our Grails HQL paginations, the result displayed on screen could be very confusing. Some records on page 1 may appear on page 2, etc.

Other Examples

Pagination with parameter list

Here is a simple example of using offset and max together with parameter list
def result = Person.executeQuery(
    "from Person where firstName = ? and lastName = ?", ['John', 'Doe'], [offset:0, max:5])

Pagination with named parameters
Here is a simple example of using offset and max together with named parameters

def result = Person.executeQuery(
    "from Person where firstName = :searchFirstName and lastName = :searchLastName",
    [searchFirstName:'John', searchLastName:'Doe'], [offset:0, max:5])

or we can shorten this to:
def result = Person.executeQuery(
    "from Person where firstName = :searchFirstName and lastName = :searchLastName",
    [searchFirstName:'John', searchLastName:'Doe', offset:0, max:5])