JPA Criteria query sum with Spring Data Pageable

criteria, java, jpa, spring, spring-data-jpa

Solution

You can do the following:

public Page<Long> sumOfPrices(Specification<Order> spec, Pageable pageable) {
    // Your Query
    ...

    // Here you have to count the total size of the result
    int totalRows = query.getResultList().size();

    // Paging you don't want to access all entities of a given query but rather only a page of them      
    // (e.g. page 1 by a page size of 10). Right now this is addressed with two integers that limit 
    // the query appropriately. (http://spring.io/blog/2011/02/10/getting-started-with-spring-data-jpa)
    query.setFirstResult(pageable.getPageNumber() * pageable.getPageSize());
    query.setMaxResults(pageable.getPageSize());

    Page<Long> page = new PageImpl<Long>(query.getResultList(), pageable, totalRows);
    return page;
}

That is how we do it, I hope it's help.

Further information can be found on: http://spring.io/blog/2011/02/10/getting-started-with-spring-data-jpa

Problem

I have a method in repository: ``` public Long sumOfPrices(Specification<Order> spec) { CriteriaBuilder builder = em.getCriteriaBuilder(); CriteriaQuery<Long> query = builder.createQuery(Long.class); Root<Order> root = query.from(Order.class); query.select(builder.sum(root.get(Order_.price))); query.where(spec.toPredicate(root, query, builder)); return sum = em.createQuery(query).getSingleResult(); } ``` How to write a method with pageable? ``` public Long sumOfPrices(Specification<Order> spec, Pageable pageable) ``` I don't know where to call setMaxResult and setFirstResult, because sum returns a single result.

Original source