Hibernate: Pagination with setFirstResult and setMaxResult

Question

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();

Why do you think the second query is wrong? It's specifying strictly greater than 10, so it looks right to me. — chiastic-security
Are you sure this is not a presentation issue ? Queries looks right and this would have been detected sooner if it was an Hibernate bug ! — Gaël J
Yes, seems fine to me too. Is this an active database? Could a new row have been inserted between the two queries? Also, what happens if you run the queries using sqlplus? — blm
@blm Yes, this is an active database. No, a new row was not inserted between the two queries. If I run the queries using Oracle SQL developer I get what I see on the frontend (so this is not a presentation issue): in the second page the first correct result is substituted with the last result from the first page. — Pietro M.

Tobias Liefke Tobias Liefke · Accepted Answer · 2015-11-03T15:46:39

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: Pagination with setFirstResult and setMaxResult

1 Answers