Hibernate - HQL pagination

Question

This is a problem similar to: HQL - row identifier for pagination

I'm trying to implement pagination using HQL. I have a PostgreSQL database.

int elementsPerBlock = 10;
int page = 2; //offset = 2*10

String sqlQuery = "FROM Messages AS msg " +
                  " LEFT JOIN FETCH msg.commands AS cmd " +   
                  "ORDER BY msg.identifier ASC" ;

Query query = session.createQuery( sqlQuery )
                     .setFirstResult( elementsPerBlock * ( (page-1) +1 ) )
                     .setMaxResults( elementsPerBlock );

What happens is that Hibernate fetches ALL the Messages, and returns the needed ones after they were all loaded.

Thus, Hibernate fetches 210000 entities instead of the 30 which are returned (each Messages has exactly 2 commands).

Is there a way to reduce the overhead by a factor of 7000?

edit: I've tries adding .setFetchSize( elementsPerBlock ) . It didn't help.

edit 2: the SQL query that is generated is:

select ... 
from schemaName.messages messages0_ 
left outer join schemaName.send_commands commands1_ 
on messages0_.unique_key=commands1_.message_key 
order by messages0_.unique_identifier ASC

Absolutenly no LIMIT or OFFSET

that might be a bug. can you enable debugging parameter to show SQL queries and see the actual query executed please? — AhmetB - Google, Jun 14 '11 at 18:25
setFirstResult( elementsPerBlock * ( (page-1) +1 ) ) The "-1 +1" doesn't seem to be correct here :o) — Peter Wippermann, Nov 22 '12 at 11:43

Stevi Deter · Accepted Answer · 2011-06-14T19:05:16.773

17

Per the JPA 2.0 specification, section 3.8.6 Query Execution,

The effect of applying setMaxResults or setFirstResult to a query involving fetch joins over collections is undefined.

It varies from database to database, and in my experience, the result is Hibernate usually does the paging in memory instead of at the database query level.

What I've usually done is used a separate query to get the ids of the desired objects, and pass that into the query with the fetch join.

edited Jun 14 '11 at 19:05

answered Jun 14 '11 at 18:57

Stevi Deter

1,523
9
15

How can you use pagination with a subselect? (I understand the second idea, but then my communication can get very chatty) – iliaden Jun 14 '11 at 19:00
Edited to remove reference to subselect. It indeed defeats the whole purpose if you use a subselect. – Stevi Deter Jun 14 '11 at 19:09
although it would defeat the purpose, I want all the computations to be done on the DB side rather than on the client's side. This is why I believe that if a subselect can do the pagination, I would use it. – iliaden Jun 14 '11 at 19:10
I don't think a subselect can do pagination. No harm in trying, of course. – Stevi Deter Jun 14 '11 at 19:12

score 7 · Answer 2 · edited Jan 10 '18 at 22:30

7

i'm using this solution:

/**
 * @param limitPerPage
 * @param page
 * @return
 */
public List<T> searchByPage(int limitPerPage, int page, String entity) {
    String sql = "SELECT t FROM " + entity + " t";
    Query query = em.createQuery(sql)
            .setFirstResult(calculateOffset(page, limitPerPage))
            .setMaxResults(limitPerPage);
    return query.getResultList();
}

/**
 * @param page
 * @return
 */
private int calculateOffset(int page, int limit) {
    return ((limit * page) - limit);
}

Welcome.

edited Jan 10 '18 at 22:30

Yago Arroyo

48
2
8

answered May 13 '16 at 03:05

Odravison

81
1
1

why don't you translate it into English ? – Shantaram Tupe Dec 13 '17 at 06:01
1

I have translated to Portuguese so much English code when I don't know what the word mean... why you cannot do the same? – Odravison Dec 14 '17 at 13:12

score 1 · Answer 3 · answered May 16 '14 at 14:36

We can achieve the pagination by using Query and Criteria interface:

Pagination using Query Interface:

There are two methods of the Query interface for pagination.

1. Query setFirstResult(int startPosition): This method takes an integer that represents the first row in your result set, starting with row 0.

2. Query setMaxResults(int maxResult): This method tells Hibernate to retrieve a fixed number maxResults of objects. Using above two methods together, we can construct a paging component in our web or Swing application.

Example:

Query query = session.createQuery("FROM Employee");
query.setFirstResult(5);
query.setMaxResults(10);
List<Employee> list = query.list();
for(Employee emp: list) {            
   System.out.println(emp);
}

Pagination using Criteria Interface:

There are two methods of the Criteria interface for pagination.

1. Criteria setFirstResult(int firstResult):

Set the first result to be retrieved.

2. List item Criteria setMaxResults(int maxResults):

Set a limit upon the number of objects to be retrieved.

Example:

Criteria criteria = session.createCriteria(Employee.class);
criteria.setFirstResult(5);
criteria.setMaxResults(10);            
List<Employee> list = criteria.list();
for(Employee emp: list) {            
    System.out.println(emp);
}

Have you tried this? Stevi Deter says the effect of this is undefined. — cslotty, Jan 07 '15 at 17:21

score 1 · Answer 4 · answered Jun 14 '11 at 18:46

1

Most probably, if you create your own query with HQL, query builder methods cannot parse custom hql query and alter it. Therefore you should put your LIMIT ?, ? statement at the end of your HQL query and bind offset parameters then.

answered Jun 14 '11 at 18:46

AhmetB - Google

35,086
32
117
191

I'm having some trouble understanding what you mean. 1) how do I append a SQL string at the end of an HQL query? 2) what do you mean by "bind offset parameters" ? – iliaden Jun 14 '11 at 18:51

score 1 · Answer 5 · answered Nov 07 '11 at 10:55

Since you do not filter the result set with respect to some attributes of command entity, you could also avoid the SQL join and configure lazy fetching for message's commands. Without join, Hibernate will employ the database paging cabilities.

However, you have to care about the N+1 seletcs issue, i.e. avoiding a single select for each lazily fetched commands attribute. You can avoid this by setting the batch-size property in your hibernate mapping or globally the hibernate.default_batch_fetch_size property in your hibernate settings.

For instance: If you have fetched 100 message objects within a Hibernate session and set a batch-size of 10, Hibernate will fetch 10 command associations of 10 different message objects when you first call getCommands() of a message object. The number of queries is reduced to 10 plus the original message-fetching one.

Have a look here: http://java.dzone.com/articles/hibernate-tuning-queries-using?page=0,1 The author compares the different fetch strategies for a simple example

score 0 · Answer 6 · answered Jul 19 '19 at 09:21

I think your original exception is not correct.

What happens is that Hibernate fetches ALL the Messages, and >returns the needed ones after they were all loaded.

What happens while query processing is that setFirstResult(calculateOffset(page, limitPerPage)) gets translated to OFFSET and setMaxResults(limitPerPage) gets translated to LIMIT

Hibernate - HQL pagination

6 Answers6

Linked