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

Question

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(); 

        }

Symonds

Answer 1

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();
    }
}

Zena

I tried with 2 different queries, I have added my code as Update 2 by editing my question. Here the problem is when I paginate to page two I am getting java.lang.IndexOutOfBoundsException even though I am catching that exception, see my code. What could be the reason for this? – Symonds

You need to use filters also in the count query. – Zena

Could you kindly provide a example on how to get 1) one for counting the filtered resultSet (I let the db do the count) 2) for retrieving the paginated resultSet (I let the db extract the sublist). Thanks – Symonds

perissf Thanks a lot. As you have edited your answer and did not add a comment I was not aware of your edited solution. Brilliant way of using Hibernate way of getting the results. model.setRowCount(myObjFacade.count(filters)); and model.setRowCount(myObjFacade.count(new HashMap<String, String> ()));, by doing this it will filter condition and actaul count? Much appreciated. – Symonds

Correct. The solution is compliant with JPA-specification's Criteria API. Hibernate is an implementation of the spec, but there exist also other implementations. – Zena

Thanks again, appreciated. – Symonds

what's "order" in javax.persistence.criteria.Path<String> path = order.get(filter.getKey());? – Befuddle

what is "private List<Site> data = new ArrayList<Site>();" for? – Befuddle

@LeonardoKenji thanks for pointing it out, there were 2 errors in the code sample – Zena

@Zena BTW, I've posted a more complete example at leonotepad.blogspot.com.br/2014/01/…. For Path<String> you can use like, but for other Path<?>, my suggestion is to use equal instead. Also, for oracle, if Path<String> is a CLOB, you'll get an Oracle error ORA-00932 – Befuddle

So, are you saying you run a query for each new page rather than paging through a cursor? I'm sorry I avoid CriteriaBuilder (too much complexity for what should be simple queries for my taste), but it seems to me you are running a new query for every page. If so that is very dangerous, especially in an active database. Your criteria can change rapidly so you will have inconsistent pages. Between some you will have gaps leaving out rows. Other times you can duplicate, other times rows will have been deleted. You introduce inconsistency. We had to fix this kind of logic in an ETL project once. – Vasques

incredible example, thank you so much for making my day that much easier! – Brana

This is not valid for recent PF versions where you need to override a count method. – Rotary

Answer 2

I'm not sure whether this is relevant in this instance, but adding to @perissf's observations, I would be concerned about the following:

if (entry.getValue() != null)
{
    criteria.add(Restrictions.ilike("requestNo",
                                    "%" + entry.getValue() + "%"));
}

For this would resolve into a query akin to

WHERE UPPER(request_no) LIKE '%VALUE%'

which would full table scan, as an index on request_no couldn't be used in this instance, which would be very slow for tables with large amount of rows for two reasons:

• UPPER(request_no) would need a functional index.
• like '%anything' would have to look through every value of request_no regardless of whether a functional index is present or not.

Talbott

Thanks for pointing this out. So what would be the best approach to avoid a full table scan? – Symonds

Depends on what you're trying to do. Does it make sense to look for a substring inside a request number? Can you live with like 'value%'? Is requestNo a number or actually a string? – Talbott

It might be worth formulating another question for those issues. – Talbott

Answer 3

There is a library that implements all that automatically: https://docs.flowlogix.com/#section-jpa-lazymodel / https://github.com/flowlogix/flowlogix

@Named
@ViewScoped
public class UserViewer implements Serializable {
    private @Getter final JPALazyDataModel<UserEntity, Long> lazyModel =
            JPALazyDataModel.create(builder -> builder
                    .entityClass(UserEntity.class)
                    // the line below is optional, default is case-sensitive (true)
                    .caseSensitiveQuery(false)
                    .build());
}

Disclaimer: I am the maintainer

Drobman

Answer 4

Since PrimeFaces 11, a JpaLazyDataModel (source) is bundled with PrimeFaces. You can use it like:

new JpaLazyDataModel<>(MyEntity.class, () -> entityManager);

If you are using selection, you can pass the rowKey field name or a converter in the constructor (see the linked documentation).

Rotary