根据 JPA 2.0 规范，第 3.8.6 节查询执行，

应用setMaxResults的效果或 setFirstResult 到涉及的查询获取连接集合是未定义.

它因数据库而异，根据我的经验，结果是 Hibernate 通常在内存中而不是在数据库查询级别进行分页.

我通常做的是使用单独的查询来获取所需对象的 id，然后通过 fetch join 将其传递到查询中.

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

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.