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

Answer 1

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.

Answer 2

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.

Answer 3

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);
}

Answer 4

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.

Answer 5

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

Answer 6

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