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

问题描述

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

p>

我试图使用HQL实现分页。我有一个PostgreSQL数据库。

  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）; 
  

会发生什么是Hibernate提取所有消息，并在所有消息加载后返回所需消息。

因此，Hibernate获取210000个实体而不是30个返回的实体（每个消息只有2个命令）。

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

编辑：我已经尝试添加 .setFetchSize（elementsPerBlock）

/ code>。它没有帮助。

编辑2：生成的SQL查询是：

  select ... $从schemaName.messages中获得
 messages0_ 
留下外部连接schemaName.send_commands commands1_ 
 on messages0_.unique_key = commands1_.message_key 
按消息0_排序。 unique_identifier ASC 
  

绝对没有LIMIT或OFFSET

解决方案

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

将setMaxResults
或setFirstResult应用于涉及
的查询的集合的取回连接的效果是
undefined。

它从数据库到数据库各不相同，根据我的经验，结果是Hibernate通常会进行分页内存而不是在数据库查询级别。

我通常使用一个单独的查询来获取所需对象的id，并使用fetch连接将其传递到查询中。

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屋！