Generate Case Expression from SQL Query - Criteria Builder, Criteria Query

Question

select 
    (case 
        when dob <= '2020-02-03' then 'child'
        when dob >= '2020-02-03' then 'adult' 
        else 'teenage' 
    end) as AgeGroup, 
    count(*)
    FROM student
    GROUP BY AgeGroup

Query Result:-
AgeGroup | Count
child    | 2
adult    | 5
teenage  | 4

I have the above SQL Query which gives me the count of the students which are falling under the cases defined as child, adult, teenage.

I have tried converting the above Query into JPA case expression but it is not giving me the expected result as the above query gives.

The query is giving me count along with case and JPA code is not giving me the same.

final Expression<Object> expression =  criteriaBuilder.selectCase()
        .when(criteriaBuilder.lessThanOrEqualTo(root.get("dateOfServiceFrom"),
                daysBeforeNow(lowDays)), "child")
        .when(criteriaBuilder.greaterThanOrEqualTo(root.get("dateOfServiceFrom"),
                daysBeforeNow(highDays)), "adult")
        .otherwise("teenage");
final CriteriaQuery<Object[]> selectCase = criteriaQuery.multiselect(criteriaBuilder.count(root))
        .groupBy(expression);

I tried hard but didn't find any luck. Please Help to achieve the above result using JPA.

Any help would be appreciated ...... Thank you

Answer 1

I got the solution for the above Query and posting the link for reference:- https://www.nuomiphp.com/eplan/en/5760.html

final Expression<Object> expression =  criteriaBuilder.selectCase()
        .when(criteriaBuilder.lessThanOrEqualTo(root.get("dateOfServiceFrom"),
                daysBeforeNow(lowDays)), "child")
        .when(criteriaBuilder.greaterThanOrEqualTo(root.get("dateOfServiceFrom"),
                daysBeforeNow(highDays)), "adult")
        .otherwise("teenage");
final CriteriaQuery<Object[]> selectCase = criteriaQuery.multiselect(expression, criteriaBuilder.count(root))
        .groupBy(criteriaBuilder.literal(1));

If we are using any Expression in the select Query and we have to group by using the same expression then we have to specify the position of the expression from the select statement and pass it to the criteriaBuilder.literal({position}).

for the above example, the position of expression is '1' so I have passed '1' in criteriaBuilder.literal(1).