How to do a Sum(case when) using JPA

Question

I don't have much background in converting between SQL and JPA-like code so I was wondering how I would go about converting the following to JPA syntax? Or if it's even possible? Thanks in advance.

SELECT DLR_CD, COUNT(*),
SUM(CASE WHEN CMPLT_DT='0001-01-01' THEN 1 END),
SUM(CASE WHEN CMPLT_DT!='0001-01-01' THEN 1 END),
SUM(CASE WHEN YEAR(CMPLT_DT) = YEAR(CURRENT DATE)-1 THEN 1 END),
SUM(CASE WHEN YEAR(CMPLT_DT) = YEAR(CURRENT DATE) THEN 1 END),
SUM(CASE WHEN (CMPLT_DT IS NULL OR CMPLT_DT='0001-01-01') THEN A.RPR_LBR_HR ELSE 0 END)
FROM <db2 table 1> A
join <db2 table 2> B
on ANUN_IND='A'
AND B.PIP_PSP_NO=A.PIP_PSP_NO
--OPTIONAL PARAMS GO HERE
GROUP BY DLR_CD
FOR FETCH ONLY WITH UR;

Edit: updated with full, barely-modified query.

Answer 1

Using criteria api it would look something like this:

    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<YourPojo> cq = cb.createQuery(YourPojo.class);
    
    Root<Entity1> root = cq.from(Entity1.class);
    Join<Entity1,Entity2> join = root.join(Entity1_.entity2,JoinType.LEFT);
    /* 
     * the join by ID is implicit when using the .join method, the second
     * condition is added as follows (Since you don't indicate the alias, 
     * I assume it is a field from table 2):
     */
    join.on(cb.equal(join.get(Entity2_.anunInd),'A'));
    
    Calendar dayOne = Calendar.getInstance();
    dayOne.set(1, 0, 1);
    
    //Expression for first and second sum case
    Expression<Long> caseDate = cb.<Long>selectCase().when(cb.equal(root.get(Entity1_.cmpltDt),dayOne.getTime()), 1L).otherwise(0L);
    Expression<Long> sumDate = cb.sum(caseDate);
    
    //Expression for third sum case
    Expression<Long> caseDateWithYearM1 = cb.<Long>selectCase().when(
            cb.equal(cb.function("YEAR", Long.class, root.get(Entity1_.cmpltDt)),
                    cb.sum(cb.function("YEAR", Long.class, cb.literal(new Date())),-1))
            , 1L).otherwise(0L);
    Expression<Long> sumDateWithYearM1 = cb.sum(caseDateWithYearM1);
    
    //Expression for fourth sun case
    Expression<Long> caseDateWithYear = cb.<Long>selectCase().when(
            cb.equal(cb.function("YEAR", Long.class, root.get(Entity1_.cmpltDt)),
                    cb.function("YEAR", Long.class, cb.literal(new Date())))
            , 1L).otherwise(0L);
    Expression<Long> sumDateWithYear = cb.sum(caseDateWithYear);

    Expression<Long> caseNew = cb.<Long>selectCase().when(cb.or(
      cb.equal(root.get(Entity1_.cmpltDt),dayOne.getTime()),
      cb.equal(root.get(Entity1_.cmpltDt),dayOne.getTime())),
             root.get(Entity1_.rprLbrHr)).otherwise(0L);

    Expression<Long> sumNew = cb.sum(caseNew);

    
    cq.multiselect(
            root.get(Entity1_.dlrCd),
            cb.count(root.get(Entity1_.dlrCd)),
            sumDate,
            sumDate,
            sumDateWithYearM1,
            sumDateWithYear,
            sumNew
    );
    
    cq.groupBy(root.get(Entity1_.dlrCd));

    List<YourPojo> resultado = entityManager.createQuery(cq).getResultList();

Your Pojo has to have a constructor with the same parameters (order and type) as the multiselect method.

The result query:

select
    entity1.DLR_CD as col_0_0_,
    count(entity1.DLR_CD) as col_1_0_,
    sum(case when entity1.CMPLT_DT=? then 1 else 0 
        end) as col_2_0_,
    sum(case when entity1.CMPLT_DT=? then 1  else 0 
        end) as col_3_0_,
    sum(case when extract(year from entity1.CMPLT_DT)=extract(year from ?)+-1 then 1 else 0 
        end) as col_4_0_,
    sum(case when extract(year from entity1.CMPLT_DT)=extract(year from ?) then 1 else 0 
        end) as col_5_0_ ,
    sum(case when entity1.CMPLT_DT is null or entity1.CMPLT_DT=? then entity1.RPR_LBR_HR else 0 
        end) as col_6_0_,
from
    entity1
left join
    entity2 on entity1.PIP_PSP_NO = entity2.PIP_PSP_NO and 
    entity2.ANUN_IND='A'
group by
    entity1.DLR_CD