Learn to paginate the query results using HQL APIs and ScrollableResults interface in Hibernate. Pagination helps in cases when the number of rows in query output is very high and fetching all records will badly affect the performance of the application.
1. Using Query#setFirstResult() and Query#setMaxResults()
1.1. Setting LIMIT and OFFSET
The HQL methods Query#setMaxResults() and Query#setFirstResult() are used to limit the number of results and control pagination. It is the most common technique to implement pagination in Hibernate.
TypedQuery<EmployeeEntity> query
= session.createQuery("From EmployeeEntity e ORDER BY e.id", EmployeeEntity.class);
query.setFirstResult(0);
query.setMaxResults(10);
//Returns first 10 records - Page 1
List<EmployeeEntity> employeeList = query.getResultList();Check out the SQL statement executed by Hibernate. Both methods, in fact, are used to set the LIMIT and OFFSET clauses in the SQL statements, based on the underlying database.
- The LIMIT defines the maximum number of records returned by the query.
- The OFFSET clause specifies how many records shall be skipped before the first record gets returned.
Hibernate:
select
e1_0.ID,
e1_0.email,
e1_0.firstName,
e1_0.lastName
from
Employee e1_0
order by
e1_0.ID offset ? rows fetch first ? rows onlyNote that stored procedure queries cannot be paged with setFirstResult()/setMaxResults().
1.2. Stable Ordering
It is very important to understand that a database does not guarantee any ordering of results, by default. So to ensure the consistency of the records in the paged result sets, we must provide a ORDER BY clause to ensure a stable ordering of the records.
We can see that we have applied for order by id in our example.
TypedQuery<EmployeeEntity> query
= session.createQuery("From EmployeeEntity e ORDER BY e.id", EmployeeEntity.class);1.3. Count Total Records
We can use SQL aggregate function COUNT() in the HQL query and find out the total number of records in the database.
TypedQuery countQuery =
session.createQuery("SELECT COUNT(e.id) from EmployeeEntity e", Long.class);
Long totalRecords = (Long) countQuery.getSingleResult();Check out the SQL statement generated for this.
Hibernate:
select
count(e1_0.ID)
from
Employee e1_01.4. Demo
To demonstrate the pagination functionality, we have created a class to represent the paged result that can be returned to the client.
public class PaginationResult<E> {
int currentPageNumber;
int lastPageNumber;
int pageSize;
long totalRecords;
List<E> records;
//Setters and getters are hidden for brevity
}Typically, in pagination, the application gets the requested page number along with the number of records. These two act as the request parameters.
We have written a function that accepts the above two arguments and returns an instance of PaginationResult that has all the information needed to build a pagination UI on the client side.
private PaginationResult<EmployeeEntity>
paginateUsingHql(int pageNumber, int pageSize) {
int lastPageNumber;
Long totalRecords;
List<EmployeeEntity> employeeList;
try (Session session = sessionFactory.openSession()) {
session.getTransaction().begin();
TypedQuery countQuery = session.createQuery("SELECT COUNT (e.id) from " +
"EmployeeEntity e", Long.class);
totalRecords = (Long) countQuery.getSingleResult();
if (totalRecords % pageSize == 0) {
lastPageNumber = (int) (totalRecords / pageSize);
} else {
lastPageNumber = (int) (totalRecords / pageSize) + 1;
}
session.getTransaction().commit();
}
try (Session session = sessionFactory.openSession()) {
session.getTransaction().begin();
TypedQuery<EmployeeEntity> query = session.createQuery("From " +
"EmployeeEntity e ORDER BY e.id",
EmployeeEntity.class);
query.setFirstResult((pageNumber - 1) * pageSize);
query.setMaxResults(pageSize);
employeeList = query.getResultList();
session.getTransaction().commit();
}
PaginationResult<EmployeeEntity> result = new PaginationResult<>();
result.setCurrentPageNumber(pageNumber);
result.setPageSize(pageSize);
result.setLastPageNumber(lastPageNumber);
result.setTotalRecords(totalRecords);
result.setRecords(employeeList);
return result;
}To test if the pagination is working as expected, we have inserted total of 54 records in the database. The given code iterates 54 times and inserts an EmployeeEntity instance in each iteration.
LongStream.range(1, 55).forEach(id -> {
EmployeeEntity employee = new EmployeeEntity();
employee.setFirstName("FNAME_" + id);
employee.setLastName("LNAME_" + id);
employee.setEmail("NAME_" + id + "@email.com");
session.persist(employee);
});Now test the paginateUsingHql() method using JUnit Assertions.
@Test
public void testPaginationUsingHql() {
PaginationResult<EmployeeEntity> firstPage = paginateUsingHql(1, 10);
//@formatter:off
Assertions.assertEquals(54, firstPage.getTotalRecords());
Assertions.assertEquals(6, firstPage.getLastPageNumber());
Assertions.assertEquals(10, firstPage.getRecords().size());
Assertions.assertEquals(1, firstPage.getRecords().get(0).getEmployeeId());
Assertions.assertEquals(10, firstPage.getRecords().get(9).getEmployeeId());
PaginationResult<EmployeeEntity> fourthPage = paginateUsingHql(4, 10);
//@formatter:off
Assertions.assertEquals(54, fourthPage.getTotalRecords());
Assertions.assertEquals(6, fourthPage.getLastPageNumber());
Assertions.assertEquals(10, fourthPage.getRecords().size());
Assertions.assertEquals(31, fourthPage.getRecords().get(0).getEmployeeId());
Assertions.assertEquals(40, fourthPage.getRecords().get(9).getEmployeeId());
//@formatter:on
PaginationResult<EmployeeEntity> lastPage = paginateUsingHql(6, 10);
//@formatter:off
Assertions.assertEquals(54, lastPage.getTotalRecords());
Assertions.assertEquals(6, lastPage.getLastPageNumber());
Assertions.assertEquals(4, lastPage.getRecords().size());
Assertions.assertEquals(51, lastPage.getRecords().get(0).getEmployeeId());
Assertions.assertEquals(54, lastPage.getRecords().get(3).getEmployeeId());
//@formatter:on
}2. Using ScrollableResults
2.1. Scrollable result sets
The org.hibernate.Query interface offers Query#scroll() for reading query results incrementally while maintaining an open JDBC ResultSet mapped to a server-side cursor.
The scroll() method returns a ScrollableResults which wraps an underlying JDBC scrollable ResultSet.
We can use ScrollableResults#scroll() to set the starting position of the cursor and then ScrollableResults#next() to get sequentially iterate over the number of records on the page.
Query<EmployeeEntity> query
= session.createQuery("From EmployeeEntity e ORDER BY e.id", EmployeeEntity.class);
try (ScrollableResults resultScroll =
query.scroll(ScrollMode.SCROLL_INSENSITIVE)) {
do {
EmployeeEntity employee = (EmployeeEntity) resultScroll.get();
employeeList.add(employee);
} while (resultScroll.next()
&& resultScroll.getRowNumber() >= 0
&& resultScroll.getRowNumber() <= 10);
}It is worth noting that stable ordering of result set is still a requirement to get consistent records in each call. So it is recommended to provide the ORDER BY clause in the HQL query.
2.2. Getting Records Count
One good thing about this approach is that we do not need to execute an extra query to get the total records count. Just move the cursor to the last record in the result set, and get the current row number.
resultScroll.last();
totalRecords = resultScroll.getRowNumber() + 1; //Cursor starts with 02.3. Demo
Similar to the first example, let’s build a new method that uses the ScrollableResults to paginate based on given method arguments page number and page size.
In the end, it will return an instance of PaginationResult with all the information populated into it.
private PaginationResult<EmployeeEntity>
paginateUsingScrollableResults(int pageNumber, int pageSize) {
int lastPageNumber = 0;
int totalRecords = 0;
List<EmployeeEntity> employeeList = new ArrayList<>();
try (Session session = sessionFactory.openSession()) {
session.getTransaction().begin();
Query<EmployeeEntity> query = session.createQuery("From " +
"EmployeeEntity e ORDER BY e.id",
EmployeeEntity.class);
try (ScrollableResults resultScroll =
query.scroll(ScrollMode.SCROLL_INSENSITIVE)) {
boolean hasRecords = resultScroll.first();
if (hasRecords) {
int fromRecordIndex = (pageNumber - 1) * pageSize;
int maxRecordIndex = (fromRecordIndex + pageSize) - 1;
hasRecords = resultScroll.scroll(fromRecordIndex);
if (hasRecords) {
do {
EmployeeEntity employee = (EmployeeEntity) resultScroll.get();
employeeList.add(employee);
} while (resultScroll.next()
&& resultScroll.getRowNumber() >= fromRecordIndex
&& resultScroll.getRowNumber() <= maxRecordIndex);
}
// Go to Last record.
resultScroll.last();
totalRecords = resultScroll.getRowNumber() + 1;
if (totalRecords % pageSize == 0) {
lastPageNumber = (int) (totalRecords / pageSize);
} else {
lastPageNumber = (int) (totalRecords / pageSize) + 1;
}
}
}
session.getTransaction().commit();
}
PaginationResult<EmployeeEntity> result = new PaginationResult<>();
result.setCurrentPageNumber(pageNumber);
result.setPageSize(pageSize);
result.setLastPageNumber(lastPageNumber);
result.setTotalRecords(totalRecords);
result.setRecords(employeeList);
return result;
}Now we can verify the pagination functionality using similar test and assertions.
3. Conclusion
In this tutorial, we learned to implement and test the pagination functionality in hibernate using the HQL APIs. We first learned to use the query.setFirstResult() and query.setMaxResults() methods. Then we learned to use ScrollableResults.
Finally, we tested both examples using JUnit tests.
Happy Learning !!
Leave a Reply