Jpa条件API计数 [英] Jpa Criteria API count

问题描述

我一直在尝试获取总行数，为此，我使用了JPA Criteria API，但它在长计数= em.createQuery（sc）.getSingleResult（）时引发错误。 ; 行并说 java.lang.IllegalStateException：未指定任何条件查询根目录。我已经做过一些研究，但无法解决问题。

I've been trying to get total rows count and to do so, I've used JPA Criteria API but it throws an error at Long count = em.createQuery(sc).getSingleResult(); line and saying java.lang.IllegalStateException: No criteria query roots were specified . I've done some research but couldn't narrow the problem.

这是我的代码段；

@PersistenceContext
    public EntityManager em;

......

public Page<UserDTO> findByCriteria(String filters, Pageable pageable) {
        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<UserDTO> cq = cb.createQuery(UserDTO.class);
        Root<UserDTO> iRoot = cq.from(UserDTO.class);
        List<Predicate> predicates = new ArrayList<Predicate>();

        if (StringUtils.isNotEmpty(filters)) {
            predicates.add(cb.like(cb.lower(iRoot.<String>get("login")), "%" + filters.toLowerCase() + "%"));
        }

        Predicate[] predArray = new Predicate[predicates.size()];
        predicates.toArray(predArray);

        CriteriaQuery<Long> sc = cb.createQuery(Long.class);
        sc.select(cb.count(iRoot));

        sc.where(predArray);
        Long count = em.createQuery(sc).getSingleResult();

        cq.where(predArray);

        List<Order> orders = new ArrayList<Order>(2);
        orders.add(cb.asc(iRoot.get("name")));
        orders.add(cb.asc(iRoot.get("desc")));

        cq.orderBy(orders);
        TypedQuery<UserDTO> query = em.createQuery(cq);

        Page<UserDTO> result = new PageImpl<UserDTO>(query.getResultList(), pageable, count);


        return result;
    }

编辑和工作代码；

public Page<UserDTO> findByCriteria(String columnName, String filters, Pageable pageable) {
        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<UserDTO> cq = cb.createQuery(UserDTO.class);
        Root<UserDTO> iRoot = cq.from(UserDTO.class);
        List<Predicate> predicates = new ArrayList<Predicate>();

        if (StringUtils.isNotEmpty(filters)) {
            predicates.add(cb.like(cb.lower(iRoot.<String>get(columnName)), "%" + filters.toLowerCase() + "%"));
        }

        Predicate[] predArray = new Predicate[predicates.size()];
        predicates.toArray(predArray);

        Long count = calculateCount(filters);

        cq.where(predArray);

        List<Order> orders = new ArrayList<Order>(2);
        orders.add(cb.asc(iRoot.get("firstName")));
        orders.add(cb.asc(iRoot.get("lastName")));

        cq.orderBy(orders);
        TypedQuery<UserDTO> query = em.createQuery(cq);

        Page<UserDTO> result = new PageImpl<UserDTO>(query.getResultList(), pageable, count);


        return result;
    }

    public Long calculateCount(String filters) {
        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Long> sc = cb.createQuery(Long.class);
        Root<UserDTO> iRoot = sc.from(UserDTO.class);
        List<Predicate> predicates = new ArrayList<Predicate>();

        if (StringUtils.isNotEmpty(filters)) {
            predicates.add(cb.like(cb.lower(iRoot.<String>get("login")), "%" + filters.toLowerCase() + "%"));
        }

        Predicate[] predArray = new Predicate[predicates.size()];
        predicates.toArray(predArray);

        sc.select(cb.count(iRoot));

        sc.where(predArray);
        Long count = em.createQuery(sc).getSingleResult();

        return count;

    }

推荐答案

在评论中，您需要显式添加 CriteriaQuery＃from（）方法：

As anticipated in the comment, you need to explicitly add CriteriaQuery#from() method:

sc.from(UserDTO.class);

由于API提供程序默认设置，通常不使用 from 方法到 CriteriaQuery 构造函数中指定的实体类。但是不是在这种情况下，因为该类是 Long ，并且它不对应于实体类。

The from method is often not used because the API provider defaults to the entity class specified in the CriteriaQuery constructor. Not in this case however, because the class is a Long and it doesn't correspond to an entity class.

Oracle的JAVA EE教程

这篇关于Jpa条件API计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！