Spring Specification With Sum Function

Question

I am trying a sum function in spring specification as below:

@Override
public Predicate toPredicate(Root<Stock> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
    Path expression = root.get("qty");
    query.select(builder.sum(expression));
    return null;
}

The query I want to execute is:

SELECT SUM(o.qty) FROM Stock o;

But Spring is not creating a sum function and is executing this:

SELECT o.qty FROM Stock o

I checked so many Stack Overflow question but there is no answer in Specification way, most of the people use JPQL for @Query annotation for this. But my further query and design is very complex so I have to use Specification only. Because I need fully dynamic query.

There is similar question as well, and this as well.

Answer 1

I think this is not possible out of the box. Here is the reason why. I'm referring to Spring JPA 1.7.3 (source code can be found here: http://grepcode.com/snapshot/repo1.maven.org/maven2/org.springframework.data/spring-data-jpa/1.7.3.RELEASE/)

A JPA specification is used via JPA repository, e.g.:

myRepo.findAll(mySpec);

The source code for this function is:

/*
 * (non-Javadoc)
 * @see org.springframework.data.jpa.repository.JpaSpecificationExecutor#findAll(org.springframework.data.jpa.domain.Specification)
 */
public List<T> findAll(Specification<T> spec) {
    return getQuery(spec, (Sort) null).getResultList();
}

Here is the first issue here: the return type and query type are bound to entity type T. In your case it means that return result is going to be a list of Stock entities.

Second issue is with the way select list is composed in getQuery() function:

/**
 * Creates a {@link TypedQuery} for the given {@link Specification} and {@link Sort}.
 * 
 * @param spec can be {@literal null}.
 * @param sort can be {@literal null}.
 * @return
 */
protected TypedQuery<T> getQuery(Specification<T> spec, Sort sort) {

    CriteriaBuilder builder = em.getCriteriaBuilder();
    CriteriaQuery<T> query = builder.createQuery(getDomainClass());

    Root<T> root = applySpecificationToCriteria(spec, query);
    query.select(root);

    if (sort != null) {
        query.orderBy(toOrders(sort, root, builder));
    }

    return applyRepositoryMethodMetadata(em.createQuery(query));
}

As you can see this type of call explicitly selects root (which is of type T or Stock in your case) and since it's executed after applying specification to criteria query, it overrides whatever you do in specification.

There is a workaround, though.

You can extend an existing JPA repository class:

public class MyRepositoryImpl<T>
    extends SimpleJpaRepository<T, Integer> implements MyRepository<T>
{

and add special methods with appropriate signatures to perform what you need:

public <P> P calcAggregate(EntitySpecification<T> spec, SingularAttribute<?,P> column)
{
    CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
    CriteriaQuery<P> query = criteriaBuilder.createQuery(column.getJavaType());

    if (spec != null) 
    {
        Root<T> root = query.from(getDomainClass());
        query.where(spec.toPredicate(root, query, criteriaBuilder));
        query.select(criteriaBuilder.sum(root.get(column.getName())));
    }

    TypedQuery<P> typedQuery = em.createQuery(query);

    P result = typedQuery.getSingleResult();

    return result;      
}

Then use it as:

myRepo.calcAggregate(mySpec, Stock_.qty);

where Stock_ is a metamodel class for Stock entity.

This is a very simple example with just one field and without ability to select an aggregate operation. It can be extended to suit one's needs.