javaspringspecifications

Spring boot SUM(column) with specification


I have an entity, says:

class MyEntity {
    Long id;
    String attr1;
    String attr2;
    String attr3;
    String attr4;
    Double attr5;
}

I use Specification to query the result filtered by attributes like:

class MySpecification implements Specification<MyEntity> {
    private String attr1;
    private String attr2;
    private String attr3;
    private String attr4;

@Override
public Predicate toPredicate(Root<MyEntity> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
    List<Predicate> restriction = new ArrayList<>();
    if (!StringUtils.isEmpty(attr1)) {
        restriction.add(criteriaBuilder.equal(root.get("attr1"), attr1));
    }
    if (!StringUtils.isEmpty(attr2)) {
        restriction.add(criteriaBuilder.equal(root.get("attr2"), attr2));
    }
    // And so on
    Predicate predicate = criteriaBuilder.disjunction();
    predicate.getExpressions().add(criteriaBuilder.and(restriction.toArray(new Predicate[restriction.size()])));
    return predicate;
    }
}

Now I want to get sum of attr5 by the Specification, how can I do that ?

Thank you in advanced.


Solution

  • After some research, here is solution:

    Create an interface:

    interface MyRepositoryCustom {
        <S extends Number> S sum(Specification<MyEntity> spec, Class<S> resultType, String fieldName);
    }
    

    Implementation:

    @Repository
    class MyRepositoryCustomImpl implements MyRepositoryCustom {
    
        @Autowired
        private EntityManager entityManager;
    
        @Override
        public <S extends Number> S sum(Specification<MyEntity> spec, Class<S> resultType, String fieldName) {
            CriteriaBuilder builder = entityManager.getCriteriaBuilder();
            CriteriaQuery<S> query = builder.createQuery(resultType);
            Root<MyEntity> root = applySpecificationToCriteria(spec, query);
            query.select(builder.sum(root.get(fieldName).as(resultType)));
            TypedQuery<S> typedQuery = entityManager.createQuery(query);
            return typedQuery.getSingleResult();
        }
    
        protected <S> Root<MyEntity> applySpecificationToCriteria(Specification<MyEntity> spec, CriteriaQuery<S> query) {
            Root<MyEntity> root = query.from(MyEntity.class);
            if (spec == null) {
                return root;
            }
            CriteriaBuilder builder = entityManager.getCriteriaBuilder();
            Predicate predicate = spec.toPredicate(root, query, builder);
            if (predicate != null) {
                query.where(predicate);
            }
            return root;
        }
    }
    

    Main repository should extend both JpaRepository and MyRepositoryCustom:

    @Repository
    interface MyEntityRepository extends JpaRepository<MyEntity, Long>, MyRepositoryCustom {
    
    }