如何使用延迟加载和分页查询 Primefaces dataTable 的数据 [英] How to query data for Primefaces dataTable using lazy loading and pagination

问题描述

在我的 JSF 数据表中，我实现了延迟加载，当我对记录进行分页时，执行下一组记录需要大约 4 或 5 秒的时间，实际上执行结果应该不到一秒钟.

这发生在我实施它的方式上，不知道我该如何解决.

扩展 LazyDataModel 的 DataModel 类

@Override公共列表<请求>负载(intstartingAt，int maxPerPage，字符串sortField，SortOrder sortOrder, Map过滤器){requestList = requestService.getRequest(startingAt, maxPerPage,sortField，sortOrder，过滤器)；this.setRowCount(requestList.size());如果 (requestList.size() > maxPerPage){System.out.println("执行中");返回 requestList.subList(startingAt,startingAt + maxPerPage);}别的{System.out.println("执行else");返回请求列表；}返回请求列表；}

在 dao 类中

@Override公共列表<请求>getRequest(int 开始在，int maxPerPage，String sortField, SortOrder sortOrder, Map过滤器){标准标准 = sessionFactory.getCurrentSession().createCriteria(请求.class);标准.addOrder(Order.desc("requestNo"));for (Map.Entry entry : filters.entrySet()){如果 (entry.getValue() != null){标准.add(Restrictions.ilike("requestNo","%" + entry.getValue() + "%"));}}//criteria.setMaxResults(maxPerPage);//criteria.setFirstResult(startingAt);返回标准列表()；}

有人能解释一下是什么导致了记录分页延迟的原因吗?

如果我删除以下内容

if (requestList.size() > maxPerPage){System.out.println("执行中");返回 requestList.subList(startingAt,startingAt + maxPerPage);}别的{System.out.println("执行else");返回请求列表；}

并执行，然后完美执行，没有延迟，但是问题是this.setRowCount(requestList.size()); 总是 5，这是我每页的默认记录数.>

更新 2

@Override公共列表<请求>负载(intstartingAt，int maxPerPage，字符串sortField，SortOrder sortOrder, Map过滤器){requestList = requestService.getRequest(startingAt, maxPerPage,sortField、sortOrder、过滤器)；this.setRowCount(requestService.getRequestCount());如果 (requestService.getRequestCount() > maxPerPage) {尝试 {返回 requestList.subList(startingAt,startingAt + maxPerPage);} catch (IndexOutOfBoundsException e) {//e.printStackTrace();返回 requestList.subList(startingAt,startingAt+ (requestService.getRequestCount() % maxPerPage));}} 别的 {返回请求列表；}}

使用不同的查询获取结果集的计数，使用以下内容

@Override公共整数计数(){int count = ((Long) sessionFactory.getCurrentSession().createQuery("select count(*) from Request").uniqueResult()).intValue();System.out.println("计数大小" + count);返回计数；}

还有我的道

@Override公共列表<请求>getRequest(int 开始在，int maxPerPage，String sortField, SortOrder sortOrder, Map过滤器){标准标准 = sessionFactory.getCurrentSession().createCriteria(请求.class);标准.addOrder(Order.desc("requestNo"));for (Map.Entry entry : filters.entrySet()) {如果 (entry.getValue() != null) {标准.add(Restrictions.ilike("requestNo","%" + entry.getValue() + "%"));}}标准.setMaxResults(maxPerPage);标准.setFirstResult(startingAt);返回标准列表()；}

解决方案

如果结果列表非常大，Java 端的计数 和子列表 操作可以对内存使用造成危险，因此在性能方面也是危险的.

相反，我通常采用以下方法:使用 2 个查询，一个用于计算过滤后的结果集(我让数据库进行计数)，另一个用于检索分页的结果集(我让数据库提取子列表).我从未遇到过明显的延迟，即使是包含数百万行的表.

遵循排序和过滤的具体示例.所有代码都使用 JPA 标准(没有 Hibernate 或 Spring 自定义功能)CriteriaQuery 方法特别适用于这种情况.

MyBean 类

@ManagedBean@ViewScoped公共类 MyBean {@EJB私人 MyObjFacade myObjFacade;private LazyDataModel模型;//获取器和设置器@PostConstruct公共无效初始化(){模型 = 新的 LazyDataModel(){@覆盖公共列表加载(int first，int pageSize，String sortField，SortOrder sortOrder，Map<String，String>过滤器){model.setRowCount(myObjFacade.count(filters));return myObjFacade.getResultList(first, pageSize, sortField, sortOrder, filters);}};model.setRowCount(myObjFacade.count(new HashMap()));}}

MyObjFacade 类

@Stateless公共类 MyObjFacade {@PersistenceContext私有 EntityManager em；@EJB私人 MyObjFacade myObjFacade;私有谓词 getFilterCondition(CriteriaBuilder cb, Root myObj, Map 过滤器) {谓词 filterCondition = cb.conjunction();字符串通配符 = "%";for (Map.Entry filter : filters.entrySet()) {字符串值 = wildCard + filter.getValue() + wildCard；如果 (!filter.getValue().equals("")) {javax.persistence.criteria.Path路径 = myObj.get(filter.getKey());filterCondition = cb.and(filterCondition, cb.like(path, value));}}返回过滤条件；}公共整数计数(地图<字符串，字符串>过滤器){CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();CriteriaQuerycq = cb.createQuery(Long.class);根myObj = cq.from(MyObjType.class);cq.where(myObjFacade.getFilterCondition(cb, myObj, 过滤器));cq.select(cb.count(myObj));返回 em.createQuery(cq).getSingleResult().intValue();}公共列表getResultList(int first, int pageSize, String sortField, SortOrder sortOrder, Map 过滤器) {CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();CriteriaQuerycq = cb.createQuery(MyObjType.class);根myObj = cq.from(MyObjType.class);cq.where(myObjFacade.getFilterCondition(cb, myObj, 过滤器));如果(排序字段！= null){如果(sortOrder == SortOrder.ASCENDING){cq.orderBy(cb.asc(myObj.get(sortField)));} else if (sortOrder == SortOrder.DESCENDING) {cq.orderBy(cb.desc(myObj.get(sortField)));}}返回 em.createQuery(cq).setFirstResult(first).setMaxResults(pageSize).getResultList();}}

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

        }

解决方案

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

这篇关于如何使用延迟加载和分页查询 Primefaces dataTable 的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！