Hibernate - HQL 分页 [英] Hibernate - HQL pagination

问题描述

这个问题类似于:HQL - 分页的行标识符

我正在尝试使用 HQL 实现分页.我有一个 PostgreSQL 数据库.

int elementsPerBlock = 10;整数页 = 2;//偏移量 = 2*10String sqlQuery = "FROM Messages AS msg " +" LEFT JOIN FETCH msg.commands AS cmd " +"ORDER BY msg.identifier ASC" ;查询查询 = session.createQuery( sqlQuery ).setFirstResult( elementsPerBlock * ( (page-1) +1 ) ).setMaxResults( elementsPerBlock );

Hibernate 会获取所有消息，并在它们全部加载后返回所需的消息.

因此，Hibernate 获取了 210000 个实体，而不是返回的 30 个实体(每个消息正好有 2 个命令).

有没有办法将开销减少 7000 倍?

我尝试添加 .setFetchSize( elementsPerBlock ) .它没有帮助.

生成的SQL查询是:

选择...来自 schemaName.messages messages0_左外连接 schemaName.send_commands commands1_在messages0_.unique_key=commands1_.message_key按messages0_.unique_identifier ASC 排序

绝对没有限制或偏移

解决方案

根据 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.

这篇关于Hibernate - HQL 分页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！