Sleep mode: paging with setFirstResult and setMaxResult

Question

I am working on a Java EE project that uses Hibernate as an ORM framework. To break down query results, I use the .setFirstResult and .setMaxResult methods (criteria API).

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

By switching the logging level to debugging, I was able to catch the SQL query created by Hibernate. It:

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

The second request seems to be "wrong." I use Oracle as a DBMS. Maybe this is a Hibernate bug? Can anybody 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();

Answer 1

, SQL- ( ).

, , . 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);