Java – HQL – paginated row identifier

Does anyone know if HQL has keywords to identify rows such as ROWID or rownum?

I want to implement paging with HQL, but I can't use it Setmaxresult() or Setfirstresult(), because I don't directly use the session object, I don't use the query object, but just create a string for my query and use Find() method

I try to use limit and offset in my query, but HQL seems to ignore these keywords and return the whole result to me anyway

I also cannot use the hibernate standard because it does not support the "having" clause in my query

My last approach is to limit the result set using the rownum / ROWID keyword Does anyone else have any other suggestions?

Solution

This is a case where hibernate shines:

HQL query is a typical solution

int elementsPerBlock = 10;
int page = 2;

return  getSession().createQuery("from SomeItems order by id asc")
            .setFirstResult(elementsPerBlock * (page-1) + 1 )
            .setMaxResults(elementsPerBlock)
            .list();

Hibernate will translate this into a schema that the database understands according to its SQL dialect On Oracle, it will create a subselect using rownum < 10. On PostgreSQL, it will issue limit / offset, and on MSSQL server, it will issue a top

As far as I know, it is impossible to achieve this with "pure" HQL