Pagination in Hibernate and EJB3

Hi,
Gavin thanks for the good blog. We are doing the same thing in our swing based application but we want to show the user the total count of resultset. So kindly can you modify this code to show the best way of doing so maybe once in the queries lifecycle.

Regards,
Shoaib Akhtar

Maybe it's better to rename isNextPage() to hasNextPage()

I found Gavin's class to be a very good base for a more robust class that includes a count of the total results available.

Using this modified class, you can display something like, "Showing records 1 to 25 of 47." It requires that your underlying database (and driver) support a scrollable result set (to get the total number of results that match the query).

I am using this class with SQL Server 2000 and jTDS 0.8.1 and it works very well.

Enjoy!


/*
 * Created on Oct 27, 2004
 */
package com.ugs.it.salescentre.navigation;

import java.util.List;

import org.apache.log4j.Logger;

import com.ugs.it.salescentre.logger.SalesCentreLogger;

import net.sf.hibernate.HibernateException;
import net.sf.hibernate.Query;
import net.sf.hibernate.ScrollableResults;

/**
 * This class provides pagination for displaying results from a large result set
 * over a number of pages (i.e. with a given number of results per page).
 *
 * Taken from http://blog.hibernate.org/cgi-bin/blosxom.cgi/2004/08/14#fn.html.
 *
 * @author Gavin King
 * @author Eric Broyles
 */
public class Page
{

    private List results;
    private int pageSize;
    private int page;
    private ScrollableResults scrollableResults;
    private int totalResults = 0;

    /**
     * Construct a new Page. Page numbers are zero-based, so the
     * first page is page 0.
     *
     * @param query
     * the Hibernate Query
     * @param page
     * the page number (zero-based)
     * @param pageSize
     * the number of results to display on the page
     */
    public Page(Query query, int page, int pageSize)
    {
        this.page = page;
        this.pageSize = pageSize;
        try
        {
            scrollableResults = query.scroll();
            /*
             * We set the max results to one more than the specfied pageSize to
             * determine if any more results exist (i.e. if there is a next page
             * to display). The result set is trimmed down to just the pageSize
             * before being displayed later (in getList()).
             */
            results = query.setFirstResult(page * pageSize).setMaxResults(
                    pageSize + 1).list();
        }
        catch (HibernateException e)
        {
            getLogger().error(
                    "Failed to get paginated results: " + e.getMessage());
        }

    }

    public boolean isFirstPage()
    {
        return page == 0;
    }

    public boolean isLastPage()
    {
        return page >= getLastPageNumber();
    }

    public boolean hasNextPage()
    {
        return results.size() > pageSize;
    }

    public boolean hasPreviousPage()
    {
        return page > 0;
    }

    public int getLastPageNumber()
    {
        /*
         * We use the Math.floor() method because page numbers are zero-based
         * (i.e. the first page is page 0).
         */
        double totalResults = new Integer(getTotalResults()).doubleValue();
        return new Double(Math.floor(totalResults / pageSize)).intValue();
    }

    public List getList()
    {
        /*
         * Since we retrieved one more than the specified pageSize when the
         * class was constructed, we now trim it down to the pageSize if a next
         * page exists.
         */
        return hasNextPage() ? results.subList(0, pageSize) : results;
    }

    public Logger getLogger()
    {
        return SalesCentreLogger.getStaticLogger(this);
    }

    public int getTotalResults()
    {
        try
        {
            getScrollableResults().last();
            totalResults = getScrollableResults().getRowNumber();
        }
        catch (HibernateException e)
        {
            getLogger().error(
                    "Failed to get last row number from scollable results: "
                            + e.getMessage());
        }
        return totalResults;
    }

    public int getFirstResultNumber()
    {
        return page * pageSize + 1;
    }

    public int getLastResultNumber()
    {
        int fullPage = getFirstResultNumber() + pageSize - 1;
        return getTotalResults() < fullPage ? getTotalResults() : fullPage;
    }

    public int getNextPageNumber()
    {
        return page + 1;
    }

    public int getPreviousPageNumber()
    {
        return page - 1;
    }

    protected ScrollableResults getScrollableResults()
    {
        return scrollableResults;
    }

}

Hi,
 I'm using Hibernate in my project, i wanna implement pagination. how can i implement using Hibernate?? plz guide me. is it any options there to implement.

I forgot to take my custom logger code out of the class in the post above -- be sure to change the logger to fit your implementation.

Why is the parameter in the setMaxResults method "pageSize + 1" instead of just "pageSize"?

Using the example, there would be 41 rows per page instead of 40.

This looked like what I needed except for some reason calling "setMaxResults(pageSize+1);" causes list() to fail for "abiguous column name".

Anybody else have this problem?

I tried using an iterator instead of calling list. However, it causes many selects to be run really slowing things down.

thoughts ?

Gavin,
just wanted to say thanks. I ran across this example a little bit ago and just ran across a situation where I need it.

I've used your code to build an example on the wiki:
 http://www.hibernate.org/243.html

I was trying to create a generic way to do pagination using hibernate. My plan was to create criterias and then get the result lists.

As Shoaib Akhtar mentioned, I did not find a good way to get total out of my criterias.

Supposedly in JDBC, I could do the following tricks:

BufferString selectStatement;
BufferString fromStatement;
BufferString whereStatement;
BufferString orderStatement;

.......

then I dynamically populate all the statements basing on my requirements;

..........

then when I need solid result lists, I could put in page number /page size.
WHEN I NEED THE TOTAL COUNTS, I COULD REPLACE selectStatement with my "select count(*)" and run another query.

Criteria/Query API in Hibernate shall able to do similar things. But it is a very valid requirement to know the numbers of results as well as the list of results.

Thank you very much,

Melvin
http://www.ginkgosoft.com

What a coincidence with a "Paginator" class I recently came up with based on Hibernate.

One problem I encountered with the "subList" method (JDK1.4) is that the returned List object is not Serializable causing problem when the result is returned from the EJB tier. The implementation class is a "RandomAccessSubList" or something like that.

I needed to wrap it with new ArrayList(result.subList(...)) to get it to work.