java - Hibernate pagination mechanism

Question

I am trying to use Hibernate pagination for my query (PostgreSQL )

I set setFirstResult(0), setMaxResults(20) for my SQL query. My code is like below:

Session session = getSessionFactory().getCurrentSession();
session.beginTransaction();
Query query = session.createQuery("FROM Customers");
query.setFirstResult(0);
query.setMaxResults(20);
List<T> entities = query.list();
session.getTransaction().commit();

but when viewing the SQL Hibernate log, I still see the full SQL query:

Hibernate: select customer0_.id as id9_, customer0_.customer_name as dst2_9_, customer0_.addres as dst3_9_ from tbl_customers customer0_  

Why there is no LIMIT OFFSET in query of Hibernate pagination SQL log?

Does anyone know about Hibernate pagination mechanism?

I guess that Hibernate will select all data, put data into Resultset, and then paging in Resultset, right?

Answer 1

There are quite a few ways to paginate.

HQL and setFirstResult, setMaxResults API

Session session = sessionFactory.openSession();
Query query = session.createQuery("From Foo");
query.setFirstResult(0);
query.setMaxResults(10);
List<Foo> fooList = query.list();
//Total count
String countQ = "Select count (f.id) from Foo f";
Query countQuery = session.createQuery(countQ);
Long countResults = (Long) countQuery.uniqueResult();
//Last Page
int pageSize = 10;
int lastPageNumber = (int) ((countResult / pageSize) + 1);

HQL and the ScrollableResults API

String hql = "FROM Foo f order by f.name";
Query query = session.createQuery(hql);
int pageSize = 10;

ScrollableResults resultScroll = query.scroll(ScrollMode.FORWARD_ONLY);
resultScroll.first();
resultScroll.scroll(0);
List<Foo> fooPage = Lists.newArrayList();
int i = 0;
while (pageSize > i++) {
    fooPage.add((Foo) resultScroll.get(0));
    if (!resultScroll.next())
        break;
}
//Total count
resultScroll.last();
int totalResults = resultScroll.getRowNumber() + 1;

Simply the Criteria API

Criteria criteria = session.createCriteria(Foo.class);
criteria.setFirstResult(0);
criteria.setMaxResults(pageSize);
List<Foo> firstPage = criteria.list();
//Total count
Criteria criteriaCount = session.createCriteria(Foo.class);
criteriaCount.setProjection(Projections.rowCount());
Long count = (Long) criteriaCount.uniqueResult();

baeldung lists them all with examples.