Hibernate Pagination – How To

Hibernate Pagination – How To

11 Comments

Wikipedia defines pagination as “The process of dividing a document into discrete pages, either electronic pages or printed pages”. For a programmer, pagination is the process of dividing a large amount of content to be presented on the UI in chunks. Enterprise Web-based applications, such as search engines, forums, and e-commerce stores use pagination to break up, organize, and present content for increased readability and for a rich user experience. Imagine scrolling down a single page to view 4,84,00,000 results that Google returns when you search for the word “Pagination”. Instead, Google uses pagination to limit the results that are presented to a user per page and provides navigation options to browse the results of the other pages. This is how it looks in Google.

Pagination in Google Search

Pagination is not limited only to Web applications. I have seen Swing-based GUI applications, and also network operations software, CRM, ERP, and inventory control software with pagination enabled. Pagination is a very common use case in software development.

Hibernate, which is one of the most widely adopted ORM framework for Java provides support for pagination. In this post we’ll take a look how we can use features in Hibernate to support pagination.

Pagination Using Query

The Query interface of Hibernate, which is an object-oriented representation of a Hibernate query, provides the setFirstRow() and setMaxResults() methods for pagination. The setFirstRow() method sets the first row to retrieve while the setMaxResults() method sets the maximum number of rows to retrieve from the result set that the Query generates. So if you want to list the first five records from the result set, you will call the methods as query.setFirstResult(0) and query.setMaxResults(5). Note that we passed 0 to setFirstResult() because records in a Query result set starts from the index 0, and we want to start from the first record.

Let’s now set up an application to paginate records stored in a database. For this application, we will use an in memory H2 database. As the first step, we need to add the following dependencies to the Maven POM.

. . .
<dependency>
  <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <scope>runtime</scope>
</dependency>
<dependency>
  <groupId>org.hibernate</groupId>
  <artifactId>hibernate-core</artifactId>
  <version>4.3.10.Final</version>
</dependency>
. . .

The preceding code adds the H2 database and Hibernate dependencies to the project.

For the application, we will start with a Product entity.

Product.java

   //package guru.springframework.blog.hibernatepagination.domain;

import javax.persistence.*;
@Entity
public class Product {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;
    private String productName;
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getProductName() {
        return productName;
    }
    public void setProductName(String productName) {
        this.productName = productName;
    }
}

The Product class we wrote above is a simple entity decorated with JPA annotations.

Next, we will write a Hibernate utility class that will provide other components of the application a SessionFactory instance to create Hibernate sessions.

HibernateUtil.java

   //package guru.springframework.blog.hibernatepagination.util;


import org.hibernate.SessionFactory;
import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
import org.hibernate.cfg.Configuration;
import org.hibernate.service.ServiceRegistry;

public class HibernateUtil {
    private static final SessionFactory sessionFactory;
    private static final ServiceRegistry serviceRegistry;

    static{
        Configuration configuration=new Configuration();
        configuration.configure("hibernate.cfg.xml");
         serviceRegistry = new StandardServiceRegistryBuilder()
                .applySettings(configuration.getProperties()).build();
         sessionFactory = configuration
                .buildSessionFactory(serviceRegistry);
    }
    public static SessionFactory getSessionFactory(){
        return sessionFactory;
    }
}

It’s now time to address the main concern of the application – Pagination. Let’s start by writing a ProductDao class.

ProductDao.java

package guru.springframework.blog.hibernatepagination.dao;

import guru.springframework.blog.hibernatepagination.domain.Product;
import guru.springframework.blog.hibernatepagination.util.HibernateUtil;
import org.hibernate.*;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;

import java.util.List;

public class ProductDao {
    public void saveProducts() {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction trans = null;
        try {
            trans = session.beginTransaction();
            for (int i = 0; i < 30; i++) {
                Product product = new Product();
                product.setProductName("Product_" + i);
                session.save(product);
            }
            trans.commit();
        } catch (HibernateException e) {
            trans.rollback();
            e.printStackTrace();
        } finally {
            session.close();
        }
        System.out.println("Saved 30 products");
    }

    public int listPaginatedProductsUsingQuery(int firstResult, int maxResults) {
        int paginatedCount = 0;
        Session session = HibernateUtil.getSessionFactory().openSession();
        try {
            Query query = session.createQuery("From Product");
            query.setFirstResult(firstResult);
            query.setMaxResults(maxResults);
            List products = (List) query.list();
            if (products != null) {
                paginatedCount = products.size();
                System.out.println("Total Results: " + paginatedCount);
                for (Product product : products) {
                    System.out.println("Retrieved Product using Query. Name: " + product.getProductName());
                }
            }

        } catch (HibernateException e) {
            e.printStackTrace();

        } finally {
            session.close();
        }
        return paginatedCount;
    }

   public void deleteAllProducts() {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction trans = null;
        try {
            trans = session.beginTransaction();
            String hql = String.format("delete from Product");
            Query query = session.createQuery(hql);
            query.executeUpdate();
            trans.commit();
        } catch (HibernateException e) {
            trans.rollback();
            e.printStackTrace();
        } finally {
            session.close();
        }
        System.out.println("Deleted all products");
    }
}

In the saveProducts() method of the ProductDao class above, we saved 30 Product entities to the database. In the listPaginatedProductsUsingQuery() method, we created a Query instance and called the setFirstResult() and setmaxResults() methods passing the int values that the listPaginatedProductsUsingQuery() method receives as arguments. We executed the query and used an enhanced for loop to log the names of the retrieved products. We also wrote a deleteAllProducts() method to delete all Product records from the database.

We will next write the hibernate.cfg.xml configuration file of Hibernate. Ensure that this file is in your project classpath.

hibernate.cfg.xml

   //


    
        true
        create-drop
        org.hibernate.dialect.H2Dialect
        org.h2.Driver
        jdbc:h2:mem:testdb
        sa
        
        
    

Our application is now ready for test. Let’s write this unit test.

package guru.springframework.blog.hibernatepagination.dao;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import static org.junit.Assert.*;

public class ProductDaoTest {    
    ProductDao productDao;
    @Before
    public void setUp() throws Exception {
        productDao = new ProductDao();
        productDao.saveProducts();
    }
    @After
    public void cleanUp(){
      productDao.deleteAllProducts();
    }
    @Test
    public void testListPaginatedProductsUsingQuery() throws Exception {
        int count = productDao.listPaginatedProductsUsingQuery(0,10);
        assertEquals(10, count);
    }
}

For unit testing, we used JUnit in the test class above. We marked the setUp() and cleanUp() methods with the @Before and @After JUnit annotations. If you are new to JUnit, you can go through my series of posts on JUnit here. In the test method marked with @Test, we called the listPaginatedProductsUsingQuery() method on ProductDao passing 0 to specify the first record and 10 to specify the maximum records to retrieve.

The output on running the test is this.

2016-02-23 18:48:42.113  INFO 2668 --- [main] g.s.b.h.dao.ProductDao : Total Results: 10
2016-02-23 18:48:42.115  INFO 2668 --- [main] g.s.b.h.dao.ProductDao : Retrieved Product using Query. Name: Product_0
2016-02-23 18:48:42.129  INFO 2668 --- [main] g.s.b.h.dao.ProductDao : Retrieved Product using Query. Name: Product_1
2016-02-23 18:48:42.131  INFO 2668 --- [main] g.s.b.h.dao.ProductDao : Retrieved Product using Query. Name: Product_2
2016-02-23 18:48:42.132  INFO 2668 --- [main] g.s.b.h.dao.ProductDao : Retrieved Product using Query. Name: Product_3
2016-02-23 18:48:42.145  INFO 2668 --- [main] g.s.b.h.dao.ProductDao : Retrieved Product using Query. Name: Product_4
2016-02-23 18:48:42.151  INFO 2668 --- [main] g.s.b.h.dao.ProductDao : Retrieved Product using Query. Name: Product_5
2016-02-23 18:48:42.155  INFO 2668 --- [main] g.s.b.h.dao.ProductDao : Retrieved Product using Query. Name: Product_6
2016-02-23 18:48:42.158  INFO 2668 --- [main] g.s.b.h.dao.ProductDao : Retrieved Product using Query. Name: Product_7
2016-02-23 18:48:42.165  INFO 2668 --- [main] g.s.b.h.dao.ProductDao : Retrieved Product using Query. Name: Product_8
2016-02-23 18:48:42.166  INFO 2668 --- [main] g.s.b.h.dao.ProductDao : Retrieved Product using Query. Name: Product_9

The output above shows the log messages that list the names of the first ten products stored in the database.

Pagination Using Criteria

Pagination using Criteria is the same as using Query. The Criteria interface, similar to the Query interface, provides the setFirstResult() and setMaxResults() methods to support pagination.

In the ProductDao class, lets add a listPaginatedProductsUsingCriteria() method to perform pagination using Criteria.

. . .
public int listPaginatedProductsUsingCriteria(int firstResult, int maxResults) {
        int paginatedCount = 0;
        Session session = HibernateUtil.getSessionFactory().openSession();
        try {
            Criteria criteria = session.createCriteria(Product.class);
            criteria.setFirstResult(firstResult);
            criteria.setMaxResults(maxResults);
            List products = (List) criteria.list();
            if (products != null) {
                paginatedCount = products.size();
                System.out.println("Total Results: " + paginatedCount);
                for (Product product : products) {
                    System.out.println("Retrieved Product using Criteria. Name: " + product.getProductName());
                }
            }

        } catch (HibernateException e) {
            e.printStackTrace();
        } finally {
            session.close();
        }
        return paginatedCount;
    }
. . .

In the preceding listPaginatedProductsUsingCriteria() method we wrote above, we called the setFirstRow() and setMaxRows() method on a Criteria instance to perform pagination.

The test code for the listPaginatedProductsUsingCriteria() method is this.

. . .
@Test
public void testListPaginatedProductsUsingCriteria() throws Exception {
    int count=productDao.listPaginatedProductsUsingCriteria(10, 5);
    assertEquals(5,count);
}
. . .

The output of the test is this.

2016-02-23 18:48:37.947  INFO 2668 --- [main] g.s.b.h.dao.ProductDao : Total Results: 5
2016-02-23 18:48:37.984  INFO 2668 --- [main] g.s.b.h.dao.ProductDao : Retrieved Product using Criteria. Name: Product_10
2016-02-23 18:48:37.988  INFO 2668 --- [main] g.s.b.h.dao.ProductDao : Retrieved Product using Criteria. Name: Product_11
2016-02-23 18:48:37.993  INFO 2668 --- [main] g.s.b.h.dao.ProductDao : Retrieved Product using Criteria. Name: Product_12
2016-02-23 18:48:37.996  INFO 2668 --- [main] g.s.b.h.dao.ProductDao : Retrieved Product using Criteria. Name: Product_13
2016-02-23 18:48:37.999  INFO 2668 --- [main] g.s.b.h.dao.ProductDao : Retrieved Product using Criteria. Name: Product_14

As you can observe in the output above, the names of five products starting from the tenth record are logged.

Calculating the Total Number of Records

A common requirement of pagination is to calculate the total number of records to be displayed in the navigation options of the UI. This figure shows few navigation options that make use of the total number of records.

Navigation Options with Total Records

If you are using Criteria, one simple approach is to make two database calls for every page fetch: One for results and another for the total record count. This approach works fine for simple applications, but is not efficient for enterprise-grade applications receiving millions of requests per day. An efficient approach is to use the same Criteria to retrieve both the results and the result count with a single database call. It can be achieved using ScrollableResults – an interface for objects that allow navigating results by arbitrary increments.

To calculate the total result counts along with the results in a single database call, lets add a static getCriteria() method to ProductDao. This method takes a Hibernate Session, constructs a Criteria from it with a restriction and projections before returning the Criteria to the caller.

The code for the getCriteria() method is this.

. . .
private static Criteria getCriteria(final Session session) {
    Criteria criteria = session.createCriteria(Product.class);
    criteria.add(Restrictions.isNotNull("productName"));
    criteria.setProjection(Projections.projectionList()
            .add(Projections.property("productName"))
            .add(Projections.property("id")));
    criteria.addOrder(Order.asc("id"));
    return criteria;
}
. . .

In ProductDao, let’s add another method with the name listPaginatedProductsUsingScrollableResults(). This method, in addition to performing pagination, calculates the total record count in the result set.

. . .
 public int listPaginatedProductsUsingScrollableResults(int firstResult, int maxResults ) {
        int totalRecords=0;
        final Session session = HibernateUtil.getSessionFactory().openSession();
        try {
            ScrollableResults  scrollableResults = getCriteria(session).scroll();
            scrollableResults.last();
            totalRecords=scrollableResults.getRowNumber()+1;
            scrollableResults.close();
            Criteria criteria = getCriteria(session);
            criteria.setFirstResult(firstResult);
            criteria.setMaxResults(maxResults);
            List<Object[]> products = criteria.list();
            if (products != null) {
                for (Object[] product : products) {
                    System.out.println("Retrieved Product using ScrollableResults. Name: " + product[0] + " out of "+totalRecords + " products");
                }
            }
        } catch (HibernateException e) {
            e.printStackTrace();
        } finally {
            session.close();
        }
        return totalRecords;
    }
 . . .

In Line 6 of the listPaginatedProductsUsingScrollableResults() method above, we called the getCriteria() static method, and then called the scroll() method on the Criteria object. The scroll() method returns a ScrollableResults instance. In Line 7 we moved the ScrollableResults instance to the last record and in Line 8, we calculated the total record count. The rest of the code is all about setting the first result and maximum results for pagination and logging the product names of the paginated records. We finally returned the total record count to the caller in Line 24.

The test code for the listPaginatedProductsUsingScrollableResults() method is this.

. . .
@Test
public void testListPaginatedProductsUsingScrollableResultSet() throws Exception {
    int totalProducts=productDao.listPaginatedProductsUsingScrollableResults(0, 3);
    assertEquals(30,totalProducts);
}
. . .

The output of the test is this.

2016-02-23 18:48:43.619  INFO 2668 --- [main] g.s.b.h.dao.ProductDao : Retrieved Product using ScrollableResults. Name: Product_0 out of 30 products
2016-02-23 18:48:43.628  INFO 2668 --- main] g.s.b.h.dao.ProductDao  : Retrieved Product using ScrollableResults. Name: Product_1 out of 30 products
2016-02-23 18:48:43.629  INFO 2668 --- [main] g.s.b.h.dao.ProductDao : Retrieved Product using ScrollableResults. Name: Product_2 out of 30 products

As you can observe in the output, the names of the first three products along with the total product count are logged.

Summary

Pagination can be handled on the client side, the server side, the database, or a mix of them. Hibernate pagination is done on the server side and, as you have seen, it’s pretty easy to understand and use. Some may argue that handling pagination on the server side results in low UI responsiveness as compared to client side pagination done using JavaScript, jQuery, or some third-party pagination plugins. However, client side pagination can lead to significant lag in the initial page load time for large data sets that are common in enterprise applications.

There is no right or wrong approach for handling pagination. Select the pagination approach that best suits your application requirements. When developing applications that use Hibernate as the ORM framework, it’s logical to make use of the built-in pagination capabilities of Hibernate instead of reinventing the wheel.

You will notice even though this is a website focused on the Spring Framework, Spring was not used in this post. Spring projects such as Spring Data JPA, or other projects like Grails use Hibernate Pagination under the covers. You as the developer do not interact with Hibernate Pagination directly. This is abstracted by the tools you are using. But often, it is important to understand what is going on under the covers.

About jt

    You May Also Like

    11 comments on “Hibernate Pagination – How To

    1. February 28, 2016 at 9:49 pm

      This is really good article for hibernate and pagination that hibernate providers out of box. I assume this is leg work for your upcoming article where you will explain how Spring deals with this and makes life easy 🙂

      Reply
      • February 29, 2016 at 9:27 am

        Of course!

        Reply
        • February 29, 2016 at 10:33 am

          Thanks, that will/should take care of all these boiler plate code of hibernate session factory , xmls etc with touch of spring boot. Looking forward to it.

          Reply
        • October 17, 2019 at 3:31 am

          Really Sir this concept help us
          Thnks

          Reply
    2. July 17, 2016 at 11:51 pm

      Thanks Josh! I love how you succinctly summarize and address the points needed to implement what you are trying to demonstrate.

      Pagination is near and dear to my heart, and I wanted to see if you have any thoughts about pagination as it results to combined enterprise microservice calls to fetch data from multiple sources, returning results in an ordered, paginated fashion?

      At my company, we currently ETL data from multiple sources each week into a central combined database. We are possibly 3 or 4 stops away from the source of truth of the data, further making data redundant in our organization. The data may have been filtered or transformed before we land it in our staging environment.

      I’m challenging my developers to leverage microservices instead where it makes sense, because we can work with the data owners to expose the RESTful web services we need, rather than continuing to move data and risk upstream schema changes that break our ETL packages.

      Now to the source of my question which I have not been able to find much in terms of direction from the web. Is is possible or practical to make a RESTful web request to a composite repository that fetches RESTful requests to multiple sources, returning the results in a sorted page that could be part of millions of pages? I would assume we would have to string a set of promises and wait to return results until all responses have come back. Latency is not an issue because we deal with DB2 with a ton of mainframe horsepower. However, our data lives not only in DB2, but also in SQL Server and Teradata, making a single query hard, if not impossible, to join.

      My group at work deals with Big Data, and I don’t know if there is a true 12-factor approach to our Spring MVC cloud application requesting sorted pages as-needed from combined sources using RESTful web requests in our back-end composite repository layer.

      I’d love to know your thoughts, or if you could point me in the right direction.

      Thanks for what you do,
      Chad

      Reply
    3. July 20, 2016 at 10:44 am

      Thanks John, very good and informative post.

      Reply
    4. October 20, 2016 at 9:06 pm

      Thanks John for very informative post on pagination.

      Reply
    5. February 25, 2017 at 12:38 pm

      Can you please provide tutorial/example for spring data jpa pagination technique with bootstrap.

      Reply
    6. April 10, 2018 at 11:21 pm

      Could please provide an explanation about spring data jpa ?

      Reply
    7. July 25, 2018 at 12:58 am

      very nice article.. Any samples that display results in data table?

      Reply
    8. April 4, 2019 at 2:35 am

      Thanks JT for a great article

      Reply

    Leave a Reply

    Your email address will not be published. Required fields are marked *

    This site uses Akismet to reduce spam. Learn how your comment data is processed.