jpa - 如何在 DAO 中为 Primefaces 延迟加载的 DataTable 过滤器实现过滤（包括多选枚举）

Question

我不知道如何准备一个 JPA (EclipseLink) 查询（使用 CtriteriaBuilder），它将包括来自 Primefaces 数据表（延迟加载）的所有过滤器值。

找到了一个论坛条目https://forum.primefaces.org/viewtopic.php?p=83497&sid=1459f3a15c3c068aa77912f1bf495370#p83497但我真的不明白如何将它应用于我的案例。

xhtml:

<p:dataTable id="datalist" 
             value="#{zadaniaController.lazyModel}" 
             var="item"
             lazy="true"
....

控制器实现延迟加载所需的 load() mentod：

@PostConstruct
    public void postConstruct() {
        lazyModel = new LazyDataModel<Zadanie>() {
            private static final long serialVersionUID = 1L;
            @Override
            public List<Zadanie> load(int first, int pageSize, String sortField, SortOrder sortOrder, Map<String, Object> filters) {
                if (null == sortField) {
                    //I want by default items to be sorted from newest to oldest
                    sortField = "terminWykonania";
                    sortOrder = SortOrder.DESCENDING;
                }
                List<Zadanie> result = ejbFacade.getResultList(first, pageSize, sortField, sortOrder, filters);
                lazyModel.setRowCount(ejbFacade.count(filters));
                return result;
            }
        };
    }

和 DAO：

@Stateless
public class ZadaniaFacade extends AbstractFacade<Zadanie> {
...
    public List<Zadanie> getResultList(int first, int pageSize, String sortField, SortOrder sortOrder, Map<String, Object> filters) {
        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Zadanie> cq = cb.createQuery(Zadanie.class);
        Root<Zadanie> zadanie = cq.from(Zadanie.class);

        if (null != filters) {         
            cq.where(zadaniaFacade.getFilterCondition(cb, zadanie, filters));
            if (sortField != null) {
                if (sortOrder == SortOrder.ASCENDING) {
                    cq.orderBy(cb.asc(zadanie.get(sortField)));
                } else if (sortOrder == SortOrder.DESCENDING) {
                    cq.orderBy(cb.desc(zadanie.get(sortField)));
                }
            }
        }
        TypedQuery<Zadanie> query = em.createQuery(cq);
        query.setFirstResult(first);
        query.setMaxResults(pageSize);
        List<Zadanie> list = query.getResultList();
        return list;
    }

private Predicate getFilterCondition(CriteriaBuilder cb, Root<Zadanie> zadanie, Map<String, Object> filters) {
        Predicate filterCondition = cb.conjunction();

        for (Map.Entry<String, Object> filter : filters.entrySet()) {
            switch (filter.getKey()) {
                case "status":
                   // I have multiple columns on the view, some are plain text that I should do LIKE, some are multiple select dropdowns, some are dates (from-to)
                    break;
                default:
                    throw new AssertionError();
            }
        }
        return filterCondition;
    }

我应该如何在 DAO 中准备查询？

PF 6.1

标签： jpaprimefaces

Answer 1

我正在做类似的事情：

我认为您需要在 DAO 中有两种方法，如下所示：

    public List<Account> loadAccountsForPrject(Project project, int first, int pageSize, Map<String, SortMeta> sortBy, Map<String, FilterMeta> filterBy) {
        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Account> cq = cb.createQuery(Account.class);
        Root<Account> root = cq.from(Account.class);
        cq.where(filterMetaToPredicate(cb, root, project, filterBy));
        cq.orderBy(sortMetaToSortOrders(sortBy, cb, root));
        TypedQuery<Account> q = em.createQuery(cq);
        return q.setMaxResults(pageSize).setFirstResult(first).getResultList();
    }

    public Long countAccountsForPrject(Project project, int first, int pageSize, Map<String, SortMeta> sortBy, Map<String, FilterMeta> filterBy) {
        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Long> cq = cb.createQuery(Long.class);
        Root<Account> root = cq.from(Account.class);
        cq.where(filterMetaToPredicate(cb, root, project, filterBy));
        cq.orderBy(sortMetaToSortOrders(sortBy, cb, root));
        cq.select(cb.count(root));
        TypedQuery<Long> q = em.createQuery(cq);
        return q.getSingleResult();
    }

那么您需要提供实现，如果您在 jsf 页面中为filterMetaToPredicate(..)和属性sortMetaToSortOrders(..)使用标准命名，那么这将更容易，filterBy因此sortBy它们与您需要的路径表达式相匹配，那么您可以使用这个通用方法：

private <T, X> Path<T> stringToPath(Root<X> r, String str) {
        String[] split = str.split("\\.");
        Path p = r;
        for (String s : split) {
            p = p.get(s);
        }
        return p;
    }

filterMetaToPredicate(..)最后，这是一个sortMetaToSortOrders(..)适合我需要的实现示例：

 private Predicate filterMetaToPredicate(CriteriaBuilder cb, Root<Account> root, Project project, Map<String, FilterMeta> filterBy) throws AssertionError {
        Predicate filter = cb.equal(root.get("project"), project);
        for (FilterMeta fm : filterBy.values()) {
            switch (fm.getFilterMatchMode()) {
                case STARTS_WITH:
                    filter = cb.and(filter, cb.like(stringToPath(root, fm.getFilterField()), fm.getFilterValue().toString() + "%"));
                    break;
                case ENDS_WITH:
                    filter = cb.and(filter, cb.like(stringToPath(root, fm.getFilterField()), "%" + fm.getFilterValue().toString()));
                    break;
                case CONTAINS:
                    filter = cb.and(filter, cb.like(stringToPath(root, fm.getFilterField()), "%" + fm.getFilterValue().toString() + "%"));
                    break;
                case EXACT:
                    break;
                case LESS_THAN:
                    break;
                case LESS_THAN_EQUALS:
                    break;
                case GREATER_THAN:
                    break;
                case GREATER_THAN_EQUALS:
                    break;
                case EQUALS:
                    filter = cb.and(filter, cb.equal(stringToPath(root, fm.getFilterField()), fm.getFilterValue()));
                    break;
                case IN:
                    break;
                case GLOBAL:
                    break;
                default:
                    throw new AssertionError(fm.getFilterMatchMode().name());
            }
        }
        return filter;
    }

    private List<Order> sortMetaToSortOrders(Map<String, SortMeta> sortBy, CriteriaBuilder cb, Root<Account> root) throws AssertionError {
        List<Order> sortOrders = new ArrayList<>();
        for (SortMeta sm : sortBy.values()) {
            switch (sm.getSortOrder()) {
                case ASCENDING:
                    sortOrders.add(cb.asc(stringToPath(root, sm.getSortField())));
                    break;
                case DESCENDING:
                    sortOrders.add(cb.desc(stringToPath(root, sm.getSortField())));
                    break;
                case UNSORTED:
                    break;
                default:
                    throw new AssertionError(sm.getSortOrder().name());
            }
            break;
        }
        return sortOrders;
    }