Can I execute @Query with aggregate function with Specifications on one Spring Data JPA repository method?

Question

I have very similar to previously asked question. Except in my case query is about to find multiple sum()

public interface IQuotaRepository extends JpaRepository<QuotaDao, Long>, JpaSpecificationExecutor<QuotaDao> {

        @Query( "select new ca.quota.kpi.model.dto.TotalDto(sum(q.value1), sum(q.value2), sum(q.value3), sum(q.value4), "
                                                                + "sum(q.value5), sum(q.value6), sum(q.value7), sum(q.value8), "
                                                                + "sum(q.value9), sum(q.value10), sum(q.value11), sum(q.value12)) from QuotaDao q")
        public TotalDto sumQuota();//Specification<QuotaDao> spec

        @Query( "select new ca.quota.kpi.model.dto.TotalDto(sum(q.value1), sum(q.value2), sum(q.value3), sum(q.value4), "
                                                                + "sum(q.value5), sum(q.value6), sum(q.value7), sum(q.value8), "
                                                                + "sum(q.value9), sum(q.value10), sum(q.value11), sum(q.value12)) from QuotaDao q")
        public TotalDto sumQuotaWithSpec(Specification<QuotaDao> spec);
}

sumQuota() works fine
sumQuotaWithSpec(Specification spec) throws an QueryParameterException: Position beyond number of declared ordinal parameters. Remember that ordinal parameters are 1-based! Position: 1

I was hoping to reuse "Specification spec" to filter result with various parameters coming from grid control.

Could you please advice any solution or alternative

did you got answer for this , i am also facing same issue. – Anchit Pancholi Aug 13 '16 at 22:17 — Anchit Pancholi, Aug 13 '16 at 22:17
@Anchit Pancholi . I've post the answer – AlexeiP Aug 14 '16 at 18:10 — AlexeiP, Aug 14 '16 at 18:10

score 0 · Answer 1 · answered Aug 14 '16 at 18:09

Well... I've found solution wich may be more a way around than strict JPA approach

First I declared method in repository interface

public interface IQuotaRepositoryCustom{

    public TotalDto sumQuotaWithFilters(FilterParameterExtJs6[] filters);
    ...
}

Then in repository implementation implement this method

@Repository
public class IQuotaRepositoryImpl implements IQuotaRepositoryCustom {
    private static Logger logger = Logger.getLogger(IQuotaRepositoryImpl.class);    
    private final String SELECT_COUNT_FOR_TOTAL = "select count(*)";
    private final String SELECT_COUNT_FOR_TOTAL_QUOTA = SELECT_COUNT_FOR_TOTAL + " from QuotaDao q";
    private final String SELECT_TOTAL = "select new org.avp.quota.kpi.model.dto.TotalDto(sum(q.value1), sum(q.value2), sum(q.value3), sum(q.value4), "
                                                                                    + "sum(q.value5), sum(q.value6), sum(q.value7), sum(q.value8), "
                                                                                    + "sum(q.value9), sum(q.value10), sum(q.value11), sum(q.value12))";

private final String SELECT_TOTAL_QUOTA = SELECT_TOTAL + " from QuotaDao q";
    ...
    public TotalDto sumQuotaWithFilters(FilterParameterExtJs6[] filters) {
        String countHql = SELECT_COUNT_FOR_TOTAL_QUOTA + buildWhereClause(filters);
        Query countQuery = em.createQuery(countHql.toString());
        Long count = (Long)countQuery.getSingleResult();
        if(count.longValue() == 0L)
            return new TotalDto();
        String hql = SELECT_TOTAL_QUOTA+ buildWhereClause(filters);
        Query query = em.createQuery(hql.toString());
        return (TotalDto) query.getSingleResult();
    }
}

TotalDto is plain datatransfer object

    public class TotalDto {

        private long value1;
        private long value2;
        private long value3;
        private long value4;
        private long value5;
        private long value6;
        private long value7;
        private long value8;
        private long value9;
        private long value10;
        private long value11;
        private long value12;
        private long total;
// constructor and getters/setters removed
}

Local helper function as name suggests just build where clause string (I don't publish the code because of very specific format coming from UI)

private String buildWhereClause(FilterParameterExtJs6[] filterParameters) {
    StringBuffer sb = new StringBuffer();
    // Build where clause string 
    return sb.toString();
}

Finally in service call this repository method

public TotalDto getFilteredSummAggregateQuota(final FilterParameterExtJs6[] filterParameters){
    TotalDto aggregateTotalQuota = quotaRepository.sumQuotaWithFilters(filterParameters);
    return aggregateTotalQuota;
}

Hope this will help you to get around.

ok, but this is not the right way with JPA, so i have used QueryDSL for this. — Anchit Pancholi, Aug 15 '16 at 12:47
@AlexeiP, Please see - http://cfmtips.blogspot.com/2016/08/spring-jpa-repository-hibernate.html. It explains about relationship too. — Shamseer, Aug 28 '16 at 17:04

Can I execute @Query with aggregate function with Specifications on one Spring Data JPA repository method?

1 Answers1