[QueryDsl] QueryDsl groupBy 여러 개 적용하기

Spring Data Jpa를 사용하는 중 Kindergarten, Recruit, Lottery의 3개의 엔티티를 통해 2개의 조건을 그룹핑하고 Count() 해야 하는 쿼리가 필요했습니다.

이를 JPA를 작성하는 경우 긴 JPQL 코드로 가독성이 떨어지고 제대로 작성되었는지 컴파일 시점에서 문법 오류가 불가능하다고 생각되어, QueryDsl을 통해 위 문제를 해결하면서 필요한 쿼리를 생성하였습니다.

---

배운 점

• ORM을 통한 2개의 그룹핑과 개수 카운트 및 총 개수 구하기
• 실제 SQL 쿼리 작성과 공식문서를 통해 지원하는 기능 적용하여 해결하기
• 불변성 객체를 유지하면서 Response DTO로 파싱 하기
• 쿼리 확인하기

 

실제 SQL 작성

select r.kindergarten_id, r.age_class, count(l.recruit_id) as "총 개수" from lottery_tb as l
join recruit_tb as r on l.recruit_id = r.recruit_id
group by
    ROLLUP(r.kindergarten_id, r.age_class);

 

 

실제 SQL에서는 어린이집별에 따라 클래스반의 지원자 수와 총 지원자 수를 위 쿼리를 통해 조회할 수 있는 것을 확인했습니다.

 하지만 JPA나 QueryDsl에서는 Rollup과 같은 함수를 제공하지 않아, lottery.count()를 구한 후 각 클래스반에 해당하는 지원자 수를 모두 더해 총 지원자 수를 구하는 방향으로 수정했습니다.

 

미사용 JPQL

String sql = "SELECT r.kindergarten_nm AS kindergartenNm, " +
                "       COUNT(l.recruit_id) AS totalCnt, " +
                "       SUM(CASE WHEN r.age_class = 'INFANT' THEN 1 ELSE 0 END) AS infantCnt, " +
                "       SUM(CASE WHEN r.age_class = 'TODDLER' THEN 1 ELSE 0 END) AS toddlerCnt, " +
                "       SUM(CASE WHEN r.age_class = 'KID' THEN 1 ELSE 0 END) AS kidCnt " +
                "FROM lottery_tb l " +
                "JOIN recruit_tb r ON l.recruit_id = r.recruit_id " +
                "GROUP BY r.kindergarten_nm, r.age_class WITH ROLLUP";

Query query = entityManager.createNativeQuery(sql);
List<Object[]> resultList = query.getResultList();
List<TotalApplication> result = new ArrayList<>();

for (Object[] row : resultList) {
    String kindergartenNm = (String) row[0];
    Integer totalCnt = ((Number) row[1]).intValue();
    Integer infantCnt = (row[2] != null) ? ((Number) row[2]).intValue() : 0;
    Integer toddlerCnt = (row[3] != null) ? ((Number) row[3]).intValue() : 0;
    Integer kidCnt = (row[4] != null) ? ((Number) row[4]).intValue() : 0;

    result.add(
            new TotalApplication(kindergartenNm, totalCnt, infantCnt, toddlerCnt, kidCnt));
}

return result;

 

위 방식으로 작성하는 경우 오타나 오류가 발생하기 쉽고, 기존 SQL 문과 비슷하지 않아 처음 작성하는 사람에게 잘 이해가 되지 않겠다는 생각이 들었습니다. 그래서 아래에 QueryDsl을 통해 컴파일 시점 오류 확인과 가독성을 높이며 쿼리를 생성하였습니다.

 

실제 QueryDSL

@Override
public List<TotalApplication> findTotalApplication(List<Recruit> recruits) {

    List<Tuple> results = jpaQueryFactory
            .select(recruit.kindergarten.kindergartenNm,
                    recruit.ageClass,
                    lottery.count().as("cnt"))
            .from(lottery)
            .join(lottery.recruit, recruit)
            .where(lottery.recruit.in(recruits))
            .groupBy(recruit.kindergarten.id, recruit.ageClass)
            .fetch();

    return extractTotalApplication(results);
}

private List<TotalApplication> extractTotalApplication(List<Tuple> results) {

    Map<String, TotalApplication> applicationMap = new HashMap<>();

    for (Tuple tuple : results) {
        String kdgNm = tuple.get(recruit.kindergarten.kindergartenNm);
        String ageClass = tuple.get(recruit.ageClass).toString();
        int count = tuple.get(lottery.count().as("cnt")).intValue();

        TotalApplication application = applicationMap.computeIfAbsent(kdgNm, nm ->
                TotalApplication.builder()
                        .kindergartenNm(nm)
                        .totalCnt(0)
                        .infantCnt(0)
                        .toddlerCnt(0)
                        .kidCnt(0)
                        .build()
        );

        TotalApplication updatedApplication = TotalApplication.builder()
                .kindergartenNm(application.getKindergartenNm())
                .totalCnt(application.getTotalCnt() + count)
                .infantCnt(ageClass.equals("INFANT") ? count : application.getInfantCnt())
                .toddlerCnt(ageClass.equals("TODDLER") ? count : application.getToddlerCnt())
                .kidCnt(ageClass.equals("KID") ? count : application.getKidCnt())
                .build();
        applicationMap.put(kdgNm, updatedApplication);
    }

    return new ArrayList<>(applicationMap.values());
}

• SQL 문처럼 select, from, where, groupby를 사용하여 쿼리 메서드를 작성할 수 있습니다.
• 또한, 외부 메서드를 불러와 사용할 수 있어, 메서드를 재사용할 수 있는 장점이 있습니다.

 

첫 번째 메서드

public List<TotalApplication> findTotalApplication(List<Recruit> recruits) {

    List<Tuple> results = jpaQueryFactory
            .select(recruit.kindergarten.kindergartenNm,
                    recruit.ageClass,
                    lottery.count().as("cnt"))
            .from(lottery)
            .join(lottery.recruit, recruit)
            .where(lottery.recruit.in(recruits))
            .groupBy(recruit.kindergarten.id, recruit.ageClass)
            .fetch();

    return extractTotalApplication(results);
}

 

위 메서드를 통해 어린이집별 & 클래스반으로 그룹핑하여 해당 어린이집 정보와 클래스반, 지원자 수를 조회합니다.

• groupBy(): SQL과 똑같이 QueryDsl에서는 해당 메서드에 그룹핑할 유니크한 속성을 작성합니다.
• select(): 조회할 속성을 지정합니다. 여기서는 kindergartenNm, ageClass, lottery.count()를 조회합니다.
• 해당 메서드를 fetch() 하는 경우 엔티티가 아니므로 List<Tuple>이 반환됩니다. 이를 원하는 목적에 맞게 DTO로 파싱 하기 위해 파싱 메서드를 만들어 처리합니다.

 

두 번째 메서드

private List<TotalApplication> extractTotalApplication(List<Tuple> results) {

    Map<String, TotalApplication> applicationMap = new HashMap<>();

    for (Tuple tuple : results) {
        String kdgNm = tuple.get(recruit.kindergarten.kindergartenNm);
        String ageClass = tuple.get(recruit.ageClass).toString();
        int count = tuple.get(lottery.count().as("cnt")).intValue();

        TotalApplication application = applicationMap.computeIfAbsent(kdgNm, nm ->
                TotalApplication.builder()
                        .kindergartenNm(nm)
                        .totalCnt(0)
                        .infantCnt(0)
                        .toddlerCnt(0)
                        .kidCnt(0)
                        .build()
        );

        TotalApplication updatedApplication = TotalApplication.builder()
                .kindergartenNm(application.getKindergartenNm())
                .totalCnt(application.getTotalCnt() + count)
                .infantCnt(ageClass.equals("INFANT") ? count : application.getInfantCnt())
                .toddlerCnt(ageClass.equals("TODDLER") ? count : application.getToddlerCnt())
                .kidCnt(ageClass.equals("KID") ? count : application.getKidCnt())
                .build();
        applicationMap.put(kdgNm, updatedApplication);
    }

    return new ArrayList<>(applicationMap.values());
}

 

전달받은 List<Tuple>을 순차적으로 돌아 어린이집별로 지원한 클래스반 지원자 수를 DTO에 파싱 합니다.

• applicationMap.computeIfAbsent(): computeIfAbsent 메서드는 kdgNm이 존재하지 않는 경우 해당 구문을 실행하고, 아닌 경우 실행하지 않습니다.
  • 즉, 처음 생성하는 어린이집 정보인 경우 클래스반 인원을 0으로 설정합니다.
• totalCnt: 각 클래스반의 지원자 수를 모두 더합니다.
• infant, toddler, kindCnt는 ageClass를 통해 확인한 후 지원자 수를 설정합니다.

 

Hibernate 발생한 쿼리 확인

**//** 현재 진행중인 모집 **//**
Hibernate: 
    select
        r1_0.recruit_id,
        r1_0.age_class,
        r1_0.created_at,
        r1_0.first_end_dt,
        r1_0.first_start_dt,
        r1_0.kindergarten_id,
        k1_0.kindergarten_id,
        k1_0.created_at,
        k1_0.kindergarten_addr,
        k1_0.kindergarten_info,
        k1_0.kindergarten_nm,
        k1_0.kindergarten_no,
        k1_0.kindergarten_scale,
        k1_0.kindergarten_time,
        k1_0.updated_at,
        r1_0.recruit_cnt,
        r1_0.recruit_end_dt,
        r1_0.recruit_start_dt,
        r1_0.second_end_dt,
        r1_0.second_start_dt,
        r1_0.updated_at 
    from
        recruit_tb r1_0 
    join
        kindergarten_tb k1_0 
            on k1_0.kindergarten_id=r1_0.kindergarten_id 
    where
        r1_0.recruit_start_dt<=? 
        and r1_0.recruit_end_dt>=?
        
//** 어린이집&클래스별 총 신청자 수 **//
Hibernate: 
    select
        k1_0.kindergarten_nm,
        r1_0.age_class,
        count(l1_0.lottery_id) 
    from
        lottery_tb l1_0 
    join
        recruit_tb r1_0 
            on r1_0.recruit_id=l1_0.recruit_id 
    join
        kindergarten_tb k1_0 
            on k1_0.kindergarten_id=r1_0.kindergarten_id 
    where
        l1_0.recruit_id in (?, ?, ?, ?, ?, ?) 
    group by
        r1_0.kindergarten_id,
        r1_0.age_class