Paging Large Data Sets With A LazyList

/**
 * Get all the items of posted by the given user. The query is put into a 
 * LazyList for efficient paging.
 */
public Collection getPagedItems(String user, int pageSize) {
    EntityManager em = FacesFunctions.evaluate("${em}", EntityManager.class);
    return new LazyList(
        em.createQuery("SELECT i FROM Items WHERE i.author = :author ")
                     .setParameter("author", user), 
        pageSize,
        (Long) em.createQuery("SELECT COUNT(i) FROM Items WHERE e.author = :author")
                     .setParameter("author", user)
                     .getSingleResult());
}

/**
 * This is a list backed by a JPA Query, but only loading the results of the
 * query one page at a time. It loads one page *ahead* for each fetch miss,
 * so if you are iterating through query results backwards you will get poor
 * performance.
 */
public class LazyList extends AbstractList {
  
    /** backing query */
    Query query;
    
    /** cache of loaded items */
    Map<Integer, Object> loaded;

    /** total number of results expected */
    long numResults;
    
    /** number of results to fetch on cache miss */
    int pageSize;
    
    /** default constructor */
    public LazyList() {
        loaded = new HashMap<Integer, Object>();
    }
    
    /**
     * Create a LazyList backed by the given query, using pageSize results
     * per page, and expecting numResults from the query.
     */
    public LazyList(Query query, int pageSize, long numResults) {
        this();
        this.query = query;
        this.pageSize = pageSize;
        this.numResults = numResults;
    }
    
    /**
     * Fetch an item, loading it from the query results if it hasn't already 
     * been.
     */
     public Object get(int i) {
         if (!loaded.containsKey(i)) {
             List results = query.setFirstResult(i).setMaxResults(pageSize)
                    .getResultList();
             for (int j = 0; j < results.size(); j++) {
                  loaded.put(i + j, results.get(j));
             }
         }
         return loaded.get(i);
     }

     /**
      * Return the total number of items in the list. This is done by
      * using an equivalent COUNT query for the backed query.
      */
     public int size() {
         return (int) numResults;
     }
     
     /** update the number of results expected in this list */
     public void setNumResults(long numResults) {
         this.numResults = numResults;
     }
}

You May Also Like

Android ORM / JPA	How To Check If The Software Keyboard Is Shown In Android	EL vs Dependency Injection	EntityManager Per Session Pattern
JPA 2.0 Feature Requests	The Only DAO You'll Ever Need	How To Create Email From JSF Templates	Using c:set in Facelets

Hello Roger!
After instantiating the class LazyList in my DAO and return the list to manage bean, I get the error:

javax.persistence.PersistenceException: org.hibernate.SessionException: Session is closed!

How to keep an open session in LazyList?

Thanks!

You use it like an ordinary List.

Hi Roger Keays!

Congratulations for the excellent tutorial!

Can you provide the code sample how to use LazyList in JSF?

My application uses JSF 2.0 with RichFAces 4.

I am trying to use tomahawk t:dataScroller for pagination on my jsf page. Data is rendered properly for the first page but whenever I try to click any button to go to next page, java script error is thrown as below

form is undefined
var oldTarget = form.target;

Any solution to this will be quite helpful.

I am using tomahawk12-1.1.9 lib with JSF2.0

Thank you for your response. In my application I put LazyList in a request-scoped bean only,... even I am getting this same error.

Can you provide any working sample...it would very helpful for me...

your response is appreciated.

Sounds like you have your LazyList in a session-scoped bean. The lifetime of the list should match the lifetime of the EntityManager, so for most webapps that would mean using a request-scoped bean.

 Hey!!!This example is very useful for me...

I am getting following error when I use LazyList in <h:dataTable....

javax.persistence.PersistenceException: org.hibernate.SessionException: Session is closed!

Can you send me the code sample how to use LazyList in JSF.

or can you provide any sample JSF appication code which uses the LazyList

 

I am getting following error in get mathod.

: org.hibernate.SessionException: Session is closed!

Can you send me the code snippet to use LazyList in JSF

javax.persistence.PersistenceException

http://www.ninthavenue.com.au/products/furnace/example

Hi.

is possible to attach a simple war project showing this functionality. ??

 

Thanks

I have an application with ejbs for business and jsf for the presentation logic. The lazyList shoud be shown in a dataTable. The getPagedItems method is implemented in a session bean. The method is called from a managedbean in the web-container. My problem is: when the query from the LazyList executs i get an exception: 

 

java.lang.IllegalStateException: Attempting to execute an operation on a closed EntityManager

I know that the entitymanager is only open in the ejb-container. How can i Implement your LazyList pattern with session beans

Nice solution to a problem I've been struggling with. That said, wouldn't it be better to cache the page the element is on rather than the page of which the requests element would be the first element? In other words, shouldnt

 public Object get(int i) {
         if (!loaded.containsKey(i)) {
             List results = query.setFirstResult(i).setMaxResults(pageSize)
                    .getResultList();
             for (int j = 0; j < results.size(); j++) {
                  loaded.put(i + j, results.get(j));
             }
         }
         return loaded.get(i);
     }

be

 public Object get(int i) {
         if (!loaded.containsKey(i)) {
             int pageIndex = i / pageSize;
             List results = query.setFirstResult(pageIndex * pageSize).setMaxResults(pageSize)
                    .getResultList();
             for (int j = 0; j < results.size(); j++) {
                  loaded.put(pageIndex * pageSize + j, results.get(j));
             }
         }
         return loaded.get(i);
     }

That way you'll always cache the entire page the element is on. In your current version a single page display could trigger two queries (granted, it is unlikely since it's highly probably the implementation will request the first element of the page first, but still). Anyway, thanks a lot ;)

Depends where you keep the LazyList. In the request scope would be fine, but if you needed a memory-sensitive implementation you could replace the HashMap with a ReferenceMap.

Does this still have the potential to OOM since you are keeping the loaded data in a hashmap?

Hey Mathias, the query you pass into the lazy list should already be sorted. At work, we have encapsulated sorting so that all our data access objects extend a base class which provides the properties and methods we need (currently for Tomahawk's dataTable). Works okay for us :)

I like the idea of the LazyList and there simplicity. But is there a simple way to add sorting?

 Bear in mind that there are query isolation issues with this approach. There's nothing wrong with that, but it's important to consider the impact of isolation in your queries when evaluating what approach to use.

 

Also, it's generally a good idea to make sure you use an ORDER BY clause in your queries when doing paged queries. Queries against a relational database typically do not have any inherent ordering.

For those of you who are interested, here is our current version of the LazyList which uses OpenJPA-specific features to automatically build the COUNT query for you.

/**
 * This is a list backed by a JPA Query, but only loading the results of the
 * query one page at a time. The same Query object is used for each page, but
 * starting at a different index. Results are cached and pages are loaded one 
 * page *ahead* for each fetch miss. If you are iterating through query results
 * backwards you will get poor performance and might prefer to change the
 * ORDER BY of your query instead.
 */
public class LazyList extends AbstractList {
  
    /** backing query */
    Query query;
    
    /** cache of loaded items */
    Map loaded;

    /** total number of results expected */
    long numResults;
    
    /** number of results to fetch on cache miss */
    int pageSize;
    
    /** default constructor */
    public LazyList() {
        loaded = new HashMap();
    }
    
    /**
     * Create a LazyList backed by the given query, using pageSize results
     * per page. The number of results expected is calculated by
     * reconstructing and executing an equivalent COUNT query when the list 
     * is created.
     */
    public LazyList(Query query, int pageSize) {
        this();
        this.query = query;
        this.pageSize = pageSize;
        
        /* create a query to see how many results there are */
        OpenJPAQuery jpaQuery = (OpenJPAQuery) query;
        EntityManager em = jpaQuery.getEntityManager();
        String queryStr = jpaQuery.getQueryString();
        Query count = em.createQuery(queryStr
                .replaceFirst("(?i)SELECT (.*?) FROM", "SELECT COUNT($1) FROM")
                .replaceFirst("(?i)ORDER BY .*", ""));
        
        /* reset all the parameters */
        if (jpaQuery.hasPositionalParameters()) {
            Object[] posParams = jpaQuery.getPositionalParameters();
            for (int i = 0; i < posParams.length; i++) {
                if (posParams[i] != null) {
                    count.setParameter(i + 1, posParams[i]);
                }
            }
        } else {
            ((OpenJPAQuery) count).setParameters(jpaQuery.getNamedParameters());
        }
        
        this.numResults = (Long) count.getSingleResult();
    }
    
    /**
     * Fetch an item, loading it from the query results if it hasn't already 
     * been.
     */
     public Object get(int i) {
         if (!loaded.containsKey(i)) {
             
             /* fetch the page required starting from this index */
             List results = query.setFirstResult(i).setMaxResults(pageSize)
                        .getResultList();
             for (int j = 0; j < results.size(); j++) {
                  loaded.put(i + j, results.get(j));
             }    
         }
         return loaded.get(i);
     }

     /** 
      * Return the total number of items in the list. This is calculated
      * using an equivalent COUNT query when the list is created.
      */
     public int size() {
         return (int) numResults;
     }
     
     /** update the number of results expected in this list */
     public void setNumResults(long numResults) {
         this.numResults = numResults;
     }
}

Hmmm... I'd probably have a look at what you are using for the pageSize parameter. Also make sure your JPA vendor implements setMaxResults() properly. OpenJPA had a bug with this once upon a time.

Hi, That´s very good man,

But I have a issue im my way...

First load of a table, we had all records??

In debug I saw the table get all records, no limits...

what´s hapenning?

Thanks,  use netbeans 6.1 whith table woodstockand JPA

Hey Jónsi, glad you found this idea useful. Rog.

Here's the link to the post: www.icefaces.org/JForum/posts/list/8549.page

M8, can't thank you enough. Im gonna put a link to this blog at IceFaces forums where it's somewhat a hot issue. Greetings from Iceland!

Thanks Yosh, I fixed that up.

Hi!

Just found your blog entry... as though your link to the Seamless Library is not working (anymore)... can it be found at

http://www.ninthavenue.com.au/products/furnace

probably? ...

Looks interesting. Will give it a try..