Help

Just had an interesting discussion on ejb3-feedback@sun.com, started by David Cherryhomes, which saw me stupidly insistingthat something can't be done when in fact, now that I think about it, /I realize I've actually done it before/, and that even the Hibernate AdminApp example uses this pattern!

So, just so I don't forget this pattern again, I'm going to write it down, and also write a reuseable class implementing it.

The basic problem is pagination. I want to display next and previous buttons to the user, but disable them if there are no more, or no previous query results. But I don't want to retrieve all the query results in each request, or execute a separate query to count them. So, here's the correct approach:

public class Page {
   
   private List results;
   private int pageSize;
   private int page;
   
   public Page(Query query, int page, int pageSize) {
       
       this.page = page;
       this.pageSize = pageSize;
       results = query.setFirstResult(page * pageSize)
           .setMaxResults(pageSize+1)
           .list();
   
   }
   
   public boolean isNextPage() {
       return results.size() > pageSize;
   }
   
   public boolean isPreviousPage() {
       return page > 0;
   }
   
   public List getList() {
       return isNextPage() ?
           results.subList(0, pageSize-1) :
           results;
   }

}

You can return this object to your JSP, and use it in Struts, WebWork or JSTL tags. Getting a page in your persistence logic is as simple as:

public Page getPosts(int page) {
    return new Page( 
        session.createQuery("from Posts p order by p.date desc")
        page,
        40
    );
}

The Page class works in both Hibernate and EJB 3.0.

20 comments:
 
14. Aug 2004, 22:00 CET | Link
Hanson Char
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.
ReplyQuote
 
15. Aug 2004, 10:50 CET | Link
Jian Wang | kid(AT)ustc.edu
Maybe it's better to rename isNextPage() to hasNextPage()
 
17. Aug 2004, 11:36 CET | Link
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
 
09. Sep 2004, 03:24 CET | Link
chuck
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.
 
17. Sep 2004, 22:13 CET | Link
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
 
08. Oct 2004, 23:22 CET | Link
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.


 
22. Oct 2004, 21:26 CET | Link
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 ?
 
25. Oct 2004, 13:56 CET | Link
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.
 
29. Oct 2004, 21:28 CET | Link
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;
    }

}


 
29. Oct 2004, 21:29 CET | Link
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.
 
03. Jan 2005, 00:13 CET | Link
pietro polsinelli | ppolsinelli(AT)open-lab.com
I've used your code to build an example on the wiki:
 http://www.hibernate.org/243.html
 
11. Feb 2008, 22:20 CET | Link
jlpl

http://blog.hibernate.org/Bloggers/Everyone/2004/08/14

So I have a method return a list call getSearchResult

public List getSearchResult(int page, int pageSize) { SQLQuery query

 
11. Feb 2008, 22:23 CET | Link
jlpl

http://blog.hibernate.org/Bloggers/Everyone/2004/08/14

So I have a method return a list call getSearchResult

public List getSearchResult(int page, int pageSize) { SQLQuery query

 
11. Feb 2008, 22:24 CET | Link
jlpl

So I have a method return a list call getSearchResult

public List getSearchResult(int page, int pageSize) { SQLQuery query

 
03. May 2008, 14:49 CET | Link
Devashish Patyal | jimmy_davien(AT)yahoo.co.in

Click HELP for text formatting instructions. Then edit this text and check the preview.

Excellent logic.Works very well.

 
28. Jul 2008, 22:19 CET | Link
nil

Wouldn't using a scrollable result set keep the connection open all the time ? Correct me if i m missing something.

 
15. Mar 2010, 15:23 CET | Link
Amit M

The code will skip the last record on each page. The no. of records returned will be one less than the pagesize. I think getList method should use results.subList(0, pageSize) instead of results.subList(0, pageSize-1)

 
20. May 2014, 13:04 CET | Link
mary

The interesting discussion has made the mind of the readers also so much energetic.This method of pagination is an amazing one, seriously .I haven’t encountered one like this before. The blog has become great with the realistic ideas in it.

Windows Help
 
21. Jun 2014, 15:37 CET | Link

Before you can start to learn how to trade forex there are a couple of basic things that have to be in place for you to be able to start your trading career. how to trade forex If you are referring to free forex trading then most people is referring to a free forex demo account. A forex practice account or demo account can be a great learning tool in your journey to becoming a successful forex free trading

 
07. Nov 2014, 07:27 CET | Link

Learn more about this article here: 192.168.1.1

192.168.0.1 192.168.1.254 192.168.2.1
Post Comment