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

        }

Micer · Accepted Answer · 2015-04-09 08:08:04Z

35

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

edited Apr 9, 2015 at 8:08

Micer

9,0193 gold badges84 silver badges78 bronze badges

answered Dec 20, 2012 at 14:05

perissf

16.3k14 gold badges85 silver badges121 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

Jacob Over a year ago

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?

perissf Over a year ago

You need to use filters also in the count query.

Jacob Over a year ago

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

Jacob Over a year ago

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.

perissf Over a year ago

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

|

beny23 · Accepted Answer · 2012-12-20 14:59:56Z

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.

answered Dec 20, 2012 at 14:59

beny23

35.1k6 gold badges84 silver badges86 bronze badges

3 Comments

Jacob Over a year ago

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

beny23 Over a year ago

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?

beny23 Over a year ago

It might be worth formulating another question for those issues.

Jasper de Vries · Accepted Answer · 2024-11-12 09:19:31Z

2

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

JPALazyDataModel<MyEntity> lazyDataModel = JPALazyDataModel.<MyEntity>builder()
        .entityClass(MyEntity.class)
        .entityManager(() -> entityManager)
        .build();

If you are using selection, you need to set the rowKeyField field in the builder.

        .rowKeyField(MyEntity_.id) // JPA MetaModel
        .rowKeyField("id") // or as String

edited Nov 12, 2024 at 9:19

answered May 16, 2023 at 6:52

Jasper de Vries

20.4k6 gold badges68 silver badges107 bronze badges

Comments

Lenny Primak · Accepted Answer · 2023-05-16 03:12:51Z

1

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

answered May 16, 2023 at 3:12

Lenny Primak

18 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

13 Comments

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

13 Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related