queryDSL로 통계 쿼리 작성하기/JAVA Stream 사용

한 프로젝트에서 통계 쿼리를 누군가 sql 쿼리를 직접 사용하여 작성을 했다. 아마 queryDsl에서는 unionAll을 제공하지 않아서 그런것 같다. (@Query 어노테이션을 사용하여, nativeQuery = true) 

 

이 쿼리의 문제점은

1. 가독성이 떨어져서 보기 싫다. 띄어쓰기가 만약 잘못되어있다면?😖

2. 오라클의 경우 스키마명을 명시를 해주어야하는데, 운영과 개발의 스키마명이 달라 컴파일을 각각 2번 해줘야한다는 것이다. 너무 불편하다.

3. 나중에 이 내용을 모르는 사람이 수정 없이 배포 한 경우, 해당 테이블이 존재하지 않는다는 황당한 일이 발생 할 수도..

 

따라서 아래의 쿼리를 queryDSL을 사용하여 변경하려고 한다.

@Transactional
    @Query(
        value = "SELECT " +
                "registDate " +
                ", SUM(connSucCnt) AS connSucCnt " +
                ", SUM(sendSmsCnt) AS sendSmsCnt " +
                "FROM " +
                        "( " +
                                "SELECT " +
                                    "TO_CHAR(PROCESS_START_DATE, 'YYYY-MM') AS registDate " +
                                    ", COUNT(ROOM_UUID) AS connSucCnt " +
                                    ", 0 AS sendSmsCnt " +
                                "FROM 스키마명.테이블명 " +
                                "WHERE PROCESS_START_DATE >= TO_DATE(:startDate, 'YYYY-MM-DD') " +
                                    "AND PROCESS_START_DATE < TO_DATE(:endDate, 'YYYY-MM-DD') + INTERVAL '1' DAY " +
                                    "AND PROCESS_END_DATE IS NOT NULL " +
                                    "AND OPR_MNG_CODE = :oprMngCode " +
                                "GROUP BY TO_CHAR(PROCESS_START_DATE, 'YYYY-MM') " +
                                "UNION ALL " +
                                "SELECT " +
                                    "TO_CHAR(REGIST_DATE , 'YYYY-MM') AS registDate " +
                                    ", 0 AS connSucCnt " +
                                    ", COUNT(ROOM_UUID) AS sendSmsCnt " +
                                "FROM 스키마명.테이블명2 " +
                                "WHERE REGIST_DATE >= TO_DATE(:startDate, 'YYYY-MM-DD') " +
                                    "AND REGIST_DATE < TO_DATE(:endDate, 'YYYY-MM-DD') + INTERVAL '1' DAY " +
                                    "AND MSG_SEND_RESULT = '200' " +
                                    "AND OPR_MNG_CODE = :oprMngCode " +
                                "GROUP BY TO_CHAR(REGIST_DATE, 'YYYY-MM') " +
                        ") " +
                "GROUP BY registDate " +
                "ORDER BY registDate"
        ,nativeQuery = true
)
    List<ExReportListInterface> retrieveReportMonth(@Param("startDate") String startDate, @Param("endDate") String endDate, @Param("oprMngCode") String oprMngCode);

 

아래와 같이 queryDSL 코드를 작성하고, union all 같은 작업은 자바 단에서 실행을 하려고 했다.

StringTemplate formattedDate = Expressions.stringTemplate(
                "TO_CHAR({0}, {1})"
                , qTbLogMvCounseling.processStartDate
                , ConstantImpl.create("yyyy-MM"));

        LocalDateTime start = LocalDate.parse(startDate).atStartOfDay();
        LocalDateTime end = LocalDate.parse(endDate).plusDays(1).atStartOfDay();
        queryFactory.select(
                        formattedDate.as("registDate"),
                    qTbLogMvCounseling.roomUuid.count().as("connSucCnt"),
                        Expressions.asNumber(0).as("sendSmsCnt")
                ).from(qTbLogMvCounseling)
                .where(qTbLogMvCounseling.processStartDate.between(start, end))
                .where(qTbLogMvCounseling.processEndDate.isNotNull())
                .where(qTbLogMvCounseling.oprMngCode.startsWith(custCode))
                .groupBy(formattedDate).fetch();​

근데 이상하게 #오라클 + #queryDSL 인 경우 group by를 하는 중에  다음과 같은 에러가 났다.

 

java.sql.SQLSyntaxErrorException: ORA-00979: GROUP BY 표현식이 아닙니다.

 

그런데 해당 쿼리를 직접 오라클에서 실행시켜보면 잘 실행이 된다. 🤔

select to_char(tblogmvcou0_.PROCESS_START_DATE, 'yyyy-MM') as col_0_0_, count(tblogmvcou0_.ROOM_UUID) 
as col_1_0_ from 스키마명.테이블명 tblogmvcou0_ where (tblogmvcou0_.PROCESS_START_DATE 
between to_timestamp('04/01/2023 00:00:00.000', 'mm/dd/yyyy hh24:mi:ss.ff3') and to_timestamp('05/19/2023 
00:00:00.000', 'mm/dd/yyyy hh24:mi:ss.ff3')) and (tblogmvcou0_.PROCESS_END_DATE is not null) 
and (tblogmvcou0_.OPR_MNG_CODE like 'SSL%' escape '!') group by to_char(tblogmvcou0_.PROCESS_START_DATE, 
'yyyy-MM') 
 {FAILED after 16 msec}

java.sql.SQLSyntaxErrorException: ORA-00979: GROUP BY 표현식이 아닙니다.

 

#mysql 의 그룹핑인 경우에는 문제가 되지 않는 것 같은데 #oracle 에서는 제대로 작동이 안되는 것 같다.

 

그래서, 데이터를 우선 다 조회한 후 java에서 그룹핑을 하기로 했다.

 

@Slf4j
@RequiredArgsConstructor
public class TbLogMvSmsSendDslImpl implements TbLogMvSmsSendDsl {

    private final JPAQueryFactory queryFactory;

    QTbTest qTbTest= QTbTest.tbTest;

@Override
    public Map<String, List<StatisticsDataVO>> counselingMonthlyStatistics(String startDate, String endDate, String oprMngCode, String custCode) {

        LocalDateTime start = LocalDate.parse(startDate).atStartOfDay();
        LocalDateTime end = LocalDate.parse(endDate).plusDays(1).atStartOfDay();
        var query = queryFactory.select(
                        Projections.constructor(
                                StatisticsDataVO.class,
                                qTbTest.roomUuid,
                                Expressions.stringTemplate(
                                        "TO_CHAR({0}, {1})"
                                        ,qTbTest.processStartDate
                                        ,ConstantImpl.create("yyyy-MM")
                                ).as("registDate")))
                .from(qTbTest)
                .where(qTbTest.processStartDate.between(start, end))
                .where(qTbTest.processEndDate.isNotNull());
                
	//동적 쿼리 생성
        if (oprMngCode.equals("")) {
            query.where(qTbTest.oprMngCode.startsWith(custCode));
        } else {
            query.where(qTbTest.oprMngCode.eq(oprMngCode));
        }

        return query
            .fetch()
            .stream()
            .collect(
            	Collectors
                    .groupingBy(StatisticsDataVO::getRegistDate)); //StatisticsDataVO의 registDate로 그룹핑한다.
    }
}


//StatisticsDataVO
import lombok.AllArgsConstructor;
import lombok.Getter;

@Getter
@AllArgsConstructor
public class StatisticsDataVO {
    private String roomUuid;
    private String registDate;
}

 

 

uion all을 사용해서 한번에 가져왔던 테이블1, 테이블2의 값들을 위 #쿼리dsl을 사용하여 각각 가져온 후 서비스 단에서 값을 합치는 로직을 작성해준다.

 

 public List<StatisticsVO> retrieveMonthlyReport(Map<String, String> data) {
        Map<String, List<StatisticsDataVO>> counseling = testRepo.counselingMonthlyStatistics(data.get("startDate"), data.get("endDate"), data.get("oprMngCode"), data.get("custCode"));
        Map<String, List<StatisticsDataVO>> sms = testRepo.smsMonthlyStatistics(data.get("startDate"), data.get("endDate"), data.get("oprMngCode"), data.get("custCode"));
        List<StatisticsVO> result = new ArrayList<>();
        
	//Stream을 사용하여 counseling과 sms 의 key 값들을 합쳐주고 중복을 제거해준다.
    //여기서 키 값들은 "2023-05" 와 같은 날짜가 된다.
        List<String> dates = Stream.concat(counseling.keySet().stream(), sms.keySet().stream())
                .distinct()
                .collect(Collectors.toList());
	//dates들을 반복문을 돌면서, counseling과 sms에서 key가 date인 값들이 value를 가져온다.
    //value 는 list임으로, 내가 필요한 값을 가져오기 위해서는 size()를 가지고 와야한다.
    //map의 getOrDefault을 사용하여 키가 없는 경우 기본 값을 빈 arrayList로 넣어준다.
        for (String date : dates) {
            result.add(new StatisticsVO(
                                date,
                                counseling.getOrDefault(date, new ArrayList<>()).size(),
                                sms.getOrDefault(date, new ArrayList<>()).size()));
        }
        return result;
    }
    
    
    
  //StatisticsVO
  
import lombok.AllArgsConstructor;
import lombok.Getter;

@AllArgsConstructor
@Getter
public class StatisticsVO {
    private String registDate;
    private int connSucCnt;
    private int sendSmsCnt;
}