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.