HQL - row identifier for pagination

Question

Does anyone know if HQL has a keyword to identify rows such as ROWID or ROWNUM?

I would like to implement pagination with HQL but I am not able to use .setMaxResult() or .setFirstResult() because I don't work with the session object directly and therefore don't use the Query object but simply create my query as a string and use the .find() method.

I tried using LIMIT and OFFSET in my query, but HQL seems to be ignoring these keywords and is returning the whole result to me no matter what.

I'm also not able to use Hibernate criteria because it does not have support for the "HAVING" clause that appears in my query.

My last resort is to restrict the result set using the ROWNUM/ROWID keyword. Does anyone else have any other suggestions?

Answer 1

Well, you can in principle access ROWNUM/ROWID from HSQL (though I've never used it). See e.g. Ron's Weblog. That should work.

But I'd like to point out that you're really working against Hibernate and HSQL if you do it like that. The proper way is to use setMaxResult() & friends. If you cannot do that b/c of your architecture, I'd at least reconsider my architectural decisions. I know these are always tough changes to make, but it might be worth it.

Answer 2

this is one situation where hibernate shines:

typical solution with hql query.

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 to a pattern that is understood by the database according to its sql dialect. on oracle it will create a subselect with ROWNUM < X. on postgresql it will issue a LIMIT / OFFSET on msSQL server it will issue a TOP..

to my knowledge it is not possible to achieve this with "pure" hql.

Answer 3

Even scrolling the results is not a good solution. This solution by Andreas Petersson is very good. It works like a charm.

Answer 4

Yes, I'll second that. Andreas' solution works very effectively (for me anyway) and it's simple to implement.

Answer 5

package sample.dto.model;

import java.io.Serializable; import java.util.List;

/** * * @author kishore n * * @param */

public class PageModel implements Serializable{

private static final long serialVersionUID = 1L; private Long pageNumber = null; private Long pageSize = null; private Long totalPages = null; private List searchList = null;

public Long getPageNumber() {
    return pageNumber;
}
public void setPageNumber(Long pageNumber) {
    this.pageNumber = pageNumber;
}
public Long getPageSize() {
    return pageSize;
}
public void setPageSize(Long pageSize) {
    this.pageSize = pageSize;
}
public Long getTotalPages() {
    return totalPages;
}
public void setTotalPages(Long totalPages) {
    this.totalPages = totalPages;
}
public List<T> getSearchList() {
    return searchList;
}
public void setSearchList(List<T> searchList) {
    this.searchList = searchList;
}

}