Hibernate：使用setFirstResult和setMaxResult进行分页 [英] Hibernate: Pagination with setFirstResult and setMaxResult

问题描述

我正在研究一个使用Hibernate作为ORM框架的Java EE项目。
为了分页查询的结果，我使用了.setFirstResult和.setMaxResult方法（Criteria API）。

问题在于第一个页面显示正确，但是当我转到第2页时，我将第一个结果显示为与第一页的最后结果相同。

通过将日志记录级别切换为调试I已经设法捕捉Hibernate构建的SQL查询。它们是：

   - 首页查询（结果从1到10）
 select * from（select this_。 DT_FINE_VAL如DT1_5_0_，this_.DT_INI_VAL如DT2_5_0_，this_.CD_TIPO_PERIODO如CD3_5_0_，this_.DT_AGGIORNAMENTO如DT4_5_0_，this_.DT_INSERIMENTO如DT5_5_0_，this_.CD_USERID_AGG如CD6_5_0_，从GPER0_POVS2.T_POVS2_PERIODI_FUNZ this_.CD_USERID_INS如CD7_5_0_由this_.CD_TIPO_PERIODO降序顺序THIS_ ）其中rownum≤10; 
 $ b $  - 第二页查询（结果从11到20）
 select * from（选择row _。*，rownum rownum_ from（选择this_.DT_FINE_VAL为DT1_5_0_，this_.DT_INI_VAL为DT2_5_0_， this_.CD_TIPO_PERIODO如CD3_5_0_，this_.DT_AGGIORNAMENTO如DT4_5_0_，this_.DT_INSERIMENTO如DT5_5_0_，this_.CD_USERID_AGG如CD6_5_0_，从GPER0_POVS2.T_POVS2_PERIODI_FUNZ通过this_.CD_TIPO_PERIODO降序THIS_顺序）row_ this_.CD_USERID_INS作为CD7_5_0_其中ROWNUM< = 20），其中rownum_> 10; 
  

看起来第二个查询是错误的。
我使用Oracle作为DBMS。
这可能是一个Hibernate的bug吗？有人可以帮我吗？

谢谢。

编辑：
这是代码：

 会话currentSession = getCurrentSession（）; 
 Criteria criteria = currentSession.createCriteria（PeriodoFunz.class）; 
 criteria.setResultTransformer（Criteria.ROOT_ENTITY）; 
订单= paginationInfo.isAsc（）？ Order.asc（paginationInfo.getOrderBy（））：Order.desc（paginationInfo.getOrderBy（））; 
 criteria.addOrder（order）; 
 .... 
 criteria = criteria.setFirstResult（paginationInfo.getFromRecord（））。setMaxResults（paginationInfo.getPageSize（））; 
列表< PeriodoFunz> result = criteria.list（）; 
  

解决方案

看起来您的订单条件导致了SQL查询这不是 stable （以相同的结果行以不同顺序进行查询）。

您可以通过添加二阶条件为一个独特的属性，例如ID：

  Order order = paginationInfo.isAsc（）？ Order.asc（paginationInfo.getOrderBy（））：Order.desc（paginationInfo.getOrderBy（））; 
 criteria.addOrder（order）; 
订单orderById = paginationInfo.isAsc（）？ Order.asc（id）：Order.desc（id）; 
 criteria.addOrder（orderById）; 
  

I'm working on a Java EE project that uses Hibernate as ORM framework. In order to paginate the results of queries, I'm using the .setFirstResult and .setMaxResult methods (Criteria API).

The problem is that the first page is displayed correctly but when I go to page 2, I have the first result displayed equal as the last result of page one.

By switching the logging level to debug I've managed to catch the SQL query that Hibernate builds. They are:

-- First page query (results from 1 to 10)
select * from ( select this_.DT_FINE_VAL as DT1_5_0_, this_.DT_INI_VAL as DT2_5_0_, this_.CD_TIPO_PERIODO as CD3_5_0_, this_.DT_AGGIORNAMENTO as DT4_5_0_, this_.DT_INSERIMENTO as DT5_5_0_, this_.CD_USERID_AGG as CD6_5_0_, this_.CD_USERID_INS as CD7_5_0_ from GPER0_POVS2.T_POVS2_PERIODI_FUNZ this_ order by this_.CD_TIPO_PERIODO desc ) where rownum <= 10;

-- Second page query (results from 11 to 20)
select * from ( select row_.*, rownum rownum_ from ( select this_.DT_FINE_VAL as DT1_5_0_, this_.DT_INI_VAL as DT2_5_0_, this_.CD_TIPO_PERIODO as CD3_5_0_, this_.DT_AGGIORNAMENTO as DT4_5_0_, this_.DT_INSERIMENTO as DT5_5_0_, this_.CD_USERID_AGG as CD6_5_0_, this_.CD_USERID_INS as CD7_5_0_ from GPER0_POVS2.T_POVS2_PERIODI_FUNZ this_ order by this_.CD_TIPO_PERIODO desc ) row_ where rownum <= 20) where rownum_ > 10;

It seems that the second query is "wrong". I'm using Oracle as DBMS. Could this be an Hibernate bug? Can someone help me?

Thanks.

EDIT: This is the code:

Session currentSession = getCurrentSession();
Criteria criteria = currentSession.createCriteria(PeriodoFunz.class);
criteria.setResultTransformer(Criteria.ROOT_ENTITY);
Order order = paginationInfo.isAsc() ? Order.asc(paginationInfo.getOrderBy()) : Order.desc(paginationInfo.getOrderBy());
criteria.addOrder(order);
....
criteria = criteria.setFirstResult(paginationInfo.getFromRecord()).setMaxResults(paginationInfo.getPageSize());
List<PeriodoFunz> result = criteria.list();

解决方案

It seems that your order criteria leads to a SQL query that is not stable (returns the same result rows in different order for the queries).

You can circumvent this by adding a second order criteria for a unique attribute, e.g. the ID:

Order order = paginationInfo.isAsc() ? Order.asc(paginationInfo.getOrderBy()) : Order.desc(paginationInfo.getOrderBy());
criteria.addOrder(order);
Order orderById = paginationInfo.isAsc() ? Order.asc("id") : Order.desc("id");
criteria.addOrder(orderById);

这篇关于Hibernate：使用setFirstResult和setMaxResult进行分页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！