hibernatejpaprimefaceslazy-loadingprimefaces-datatable

How to query data for Primefaces dataTable using lazy loading and pagination


In my JSF's datatable I have implemented lazy loading and when I paginate through records it is taking time about 4 or 5 seconds to execute next set of records, actually it should be take less than a second to execute the results.

This has happened to the way I have implemented it, not sure how could I resolve this.

DataModel class which extends LazyDataModel

@Override
public List<Request> load(int startingAt, int maxPerPage, String sortField,
                          SortOrder sortOrder, Map<String, String> filters)
{
    requestList = requestService.getRequest(startingAt, maxPerPage,
                                            sortField, sortOrder, filters);
    this.setRowCount(requestList.size());
    if (requestList.size() > maxPerPage)
    {
        System.out.println("executing");
        return requestList.subList(startingAt, startingAt + maxPerPage);
    }
    else
    {
        System.out.println("executing else ");
        return requestList;
    }

    return requestList;
}

and in dao class

@Override
public List<Request> getRequest(int startingAt, int maxPerPage,
                                String sortField, SortOrder sortOrder, Map<String, String> filters)
{
    Criteria criteria = sessionFactory.getCurrentSession().createCriteria(
                            Request.class);
    criteria.addOrder(Order.desc("requestNo"));
    for (Map.Entry<String, String> entry : filters.entrySet())
    {
        if (entry.getValue() != null)
        {
            criteria.add(Restrictions.ilike("requestNo",
                                            "%" + entry.getValue() + "%"));
        }
    }
    //criteria.setMaxResults(maxPerPage);
    //criteria.setFirstResult(startingAt);
    return criteria.list();
}

Could someone explain what caused this delay in paginating through the records?

If I remove the following

if (requestList.size() > maxPerPage)
{
    System.out.println("executing");
    return requestList.subList(startingAt, startingAt + maxPerPage);
}
else
{
    System.out.println("executing else ");
    return requestList;
}

and execute, then it is executes perfectly without delay, however the problem is this.setRowCount(requestList.size()); always 5 which is my default number of records per page.

Update 2

@Override
    public List<Request> load(int startingAt, int maxPerPage, String sortField,
            SortOrder sortOrder, Map<String, String> filters) {
        requestList = requestService.getRequest(startingAt, maxPerPage,
                sortField, sortOrder, filters);
        this.setRowCount(requestService.getRequestCount());
        if (requestService.getRequestCount() > maxPerPage) {
            try {

                return requestList.subList(startingAt, startingAt + maxPerPage);
            } catch (IndexOutOfBoundsException e) {
                //e.printStackTrace();
                return requestList.subList(startingAt, startingAt
                        + (requestService.getRequestCount() % maxPerPage));
            }
        } else {
            return requestList;
        }       
    }

Used a different query for getting count of resultset using the following

@Override
    public int count() {
        int count = ((Long) sessionFactory.getCurrentSession()
                .createQuery("select count(*) from Request").uniqueResult())
                .intValue();
        System.out.println(" count size " + count);
        return count;
    }

and my dao

@Override
        public List<Request> getRequest(int startingAt, int maxPerPage,
                String sortField, SortOrder sortOrder, Map<String, String> filters) {
            Criteria criteria = sessionFactory.getCurrentSession().createCriteria(
                    Request.class);
            criteria.addOrder(Order.desc("requestNo"));
            for (Map.Entry<String, String> entry : filters.entrySet()) {
                if (entry.getValue() != null) {
                    criteria.add(Restrictions.ilike("requestNo",
                            "%" + entry.getValue() + "%"));         }
            }
             criteria.setMaxResults(maxPerPage);
             criteria.setFirstResult(startingAt);       
                return criteria.list(); 

        }

Solution

  • In case of very large resulting lists, the Java-side counting and the sublisting operations can be dangerous for the memory usage and consequently also on the performance side.

    Instead, I usually go with the following approach: use 2 queries, one for counting the filtered resultSet (I let the db do the count), and another one for retrieving the paginated resultSet (I let the db extract the sublist). I have never experienced significant delays, even with tables containing millions of rows.

    Follows a concrete example with sorting and filtering. All the code uses JPA standard (no Hibernate or Spring custom features) The CriteriaQuery approach is particularly indicated in such situations.

    MyBean class

    @ManagedBean
    @ViewScoped
    public class MyBean {
        @EJB
        private MyObjFacade myObjFacade;
        private LazyDataModel<MyObjType> model;        // getter and setter
    
        @PostConstruct
        public void init() {
            model = new LazyDataModel<MyObjType> () {
    
                @Override
                public List<MyObjType> load(int first, int pageSize, String sortField, SortOrder sortOrder, Map<String, String> filters) {
                    model.setRowCount(myObjFacade.count(filters));
                    return myObjFacade.getResultList(first, pageSize, sortField, sortOrder, filters);
                }
            };
            model.setRowCount(myObjFacade.count(new HashMap<String, String> ()));
        }
    }
    

    MyObjFacade class

    @Stateless
    public class MyObjFacade {
        @PersistenceContext
        private EntityManager em;
        @EJB
        private MyObjFacade myObjFacade;
    
        private Predicate getFilterCondition(CriteriaBuilder cb, Root<MyObjType> myObj, Map<String, String> filters) {
            Predicate filterCondition = cb.conjunction();
            String wildCard = "%";
            for (Map.Entry<String, String> filter : filters.entrySet()) {
                String value = wildCard + filter.getValue() + wildCard;
                if (!filter.getValue().equals("")) {
                    javax.persistence.criteria.Path<String> path = myObj.get(filter.getKey());
                    filterCondition = cb.and(filterCondition, cb.like(path, value));
                }
            }
            return filterCondition;
        }
    
        public int count(Map<String, String> filters) {
            CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();
            CriteriaQuery<Long> cq = cb.createQuery(Long.class);
            Root<MyObjType> myObj = cq.from(MyObjType.class);
            cq.where(myObjFacade.getFilterCondition(cb, myObj, filters));
            cq.select(cb.count(myObj));
            return em.createQuery(cq).getSingleResult().intValue();
        }
    
        public List<MyObjType> getResultList(int first, int pageSize, String sortField, SortOrder sortOrder, Map<String, String> filters) {
            CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();
            CriteriaQuery<MyObjType> cq = cb.createQuery(MyObjType.class);
            Root<MyObjType> myObj = cq.from(MyObjType.class);
            cq.where(myObjFacade.getFilterCondition(cb, myObj, filters));
            if (sortField != null) {
                if (sortOrder == SortOrder.ASCENDING) {
                    cq.orderBy(cb.asc(myObj.get(sortField)));
                } else if (sortOrder == SortOrder.DESCENDING) {
                    cq.orderBy(cb.desc(myObj.get(sortField)));
                }
            }
            return em.createQuery(cq).setFirstResult(first).setMaxResults(pageSize).getResultList();
        }
    }