Jpa Criteria API count

Question

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.

Here's my code snippet;

@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;
    }

EDITED AND WORKING CODE;

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;

    }

@XtremeBaumer Sorry I thought he was missing `cq.select(qb.count(cq.from(MyEntity.class)));` — Scary Wombat, Mar 14 '18 at 07:34
I've tried that solution but it doesn't work though. That's why I post this question. Maybe there is something wrong that I did. @XtremeBaumer — Burak N., Mar 14 '18 at 07:37
Have you tried creating a method that only does the `count`? — Scary Wombat, Mar 14 '18 at 07:49
I think it would be kinda better to out-source each query and create the result from that — XtremeBaumer, Mar 14 '18 at 08:01
Sadly I can't share some code here now because of the duplicate mark. As soon as it is lifted, I will post some code, but I can't promise that it will work — XtremeBaumer, Mar 14 '18 at 08:11
Have you tried to explicitly add [CriteriaQuery#from()](https://docs.oracle.com/javaee/6/api/javax/persistence/criteria/AbstractQuery.html#from(java.lang.Class)) method? `sc.from(iRoot);` — perissf, Mar 14 '18 at 08:19
Seperated method solved my problem! But why? I mean, I moved same codes to just different method. How did it matter? @ScaryWombat — Burak N., Mar 14 '18 at 08:23
@BurakN. add your updated code and we might be able to tell you whats the difference — XtremeBaumer, Mar 14 '18 at 08:50
I would guess that if you add this line `sc.from(UserDTO.class);` in your old code, then it will work too. That line is missing in your old code and specifies from which `Root`-class the query should select — XtremeBaumer, Mar 14 '18 at 09:08
Thanks a lot! By the way, I couldn't sort the data by using `pageable.getSort()` because I don't know where to put that. Could you please help me to do the sorting too? @XtremeBaumer — Burak N., Mar 14 '18 at 10:49
i am not common with `pageable.getSort()`, but the results should be order by your `orders` which both state `ASC` — XtremeBaumer, Mar 14 '18 at 10:59
Yes but I want to make it dynamic. That's why I need to use `pageable.getSort()` @XtremeBaumer — Burak N., Mar 14 '18 at 11:01
I assume you can get a string like shown in [this question](https://stackoverflow.com/questions/41692097/jpa-criteriaquery-implements-spring-data-pageable-getsort). Then you can determine with an IF, whether the string contains ASC or DESC and use the right case of `orders.add(cb.asc(iRoot.get("lastName")));`. If you tell me which information you can get from `pageable.getSort()`, I might be able to help — XtremeBaumer, Mar 14 '18 at 11:09

score 3 · Accepted Answer · edited Jun 20 '20 at 09:12

3

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

sc.from(UserDTO.class);

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.

Jpa Criteria API count

1 Answers1

See also:

Linked