QueryDSL Group By 삽질기① - Projections.constructor()

배경

앱구현 완료 후,

이용자들의 사용통계분석을 위해

작업이력데이터를 뽑아내 통계 데이터를 추출하는 기능을 구현해야 했다

 

기존에는 JPQL로 코드를 작성했지만,

JPQL로 작성할 경우, 코드가 복잡해질 것 같아서 QueryDSL로 작성하고자 했다

 

작성한 코드는 아래와 같다

@Override
public List<WorkHistoryQdslResDto> getStaticsList(WorkHistoryType.WorkType workType, WorkHistoryType.FunctionType functionType) {
    DateTemplate<String> formattedDate =
            Expressions.dateTemplate(String.class,
                    "DATE_FORMAT({0}, {1})", workHistory.workDate, "%Y-%m-%d");

    List<WorkHistoryQdslResDto> workHistoryList = jpaQueryFactory
            .select(Projections.constructor(WorkHistoryQdslResDto.class
                    ,formattedDate.as("workDate")
                    ,workHistory.workUser.loginId
                    ,workHistory.functionType
                    ,workHistory.workType
                    ,workHistory.count().as("workCount")
            ))
            .from(workHistory)
            .where(
                   workType!=null?eqWorkType(workType):null
                    ,functionType!=null?eqFunctionType(functionType):null
            )
            .groupBy(
                    formattedDate
                    ,workHistory.workUser
                    ,workHistory.functionType
                    ,workHistory.workType)
            .fetch();

    return workHistoryList;
}

 

증상

"argument type mismatch" 에러 발생

java.lang.IllegalArgumentException: argument type mismatch
	...
	at com.querydsl.core.types.ConstructorExpression.newInstance(ConstructorExpression.java:141) ~[querydsl-core-4.4.0.jar:na]
	at com.querydsl.core.support.NumberConversions.newInstance(NumberConversions.java:86) ~[querydsl-core-4.4.0.jar:na]
	at com.querydsl.jpa.FactoryExpressionTransformer.transformTuple(FactoryExpressionTransformer.java:51) ~[querydsl-jpa-4.4.0.jar:na]
	at org.hibernate.hql.internal.HolderInstantiator.instantiate(HolderInstantiator.java:85) ~[hibernate-core-5.4.28.Final.jar:5.4.28.Final]
	at org.hibernate.loader.hql.QueryLoader.getResultList(QueryLoader.java:474) ~[hibernate-core-5.4.28.Final.jar:5.4.28.Final]
	at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2663) ~[hibernate-core-5.4.28.Final.jar:5.4.28.Final]
	at org.hibernate.loader.Loader.list(Loader.java:2658) ~[hibernate-core-5.4.28.Final.jar:5.4.28.Final]
	at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:506) ~[hibernate-core-5.4.28.Final.jar:5.4.28.Final]
	at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:400) ~[hibernate-core-5.4.28.Final.jar:5.4.28.Final]
	at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:219) ~[hibernate-core-5.4.28.Final.jar:5.4.28.Final]
	at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1414) ~[hibernate-core-5.4.28.Final.jar:5.4.28.Final]
	at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1625) ~[hibernate-core-5.4.28.Final.jar:5.4.28.Final]
	at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1593) ~[hibernate-core-5.4.28.Final.jar:5.4.28.Final]
	at org.hibernate.query.Query.getResultList(Query.java:165) ~[hibernate-core-5.4.28.Final.jar:5.4.28.Final]
	at com.querydsl.jpa.impl.AbstractJPAQuery.getResultList(AbstractJPAQuery.java:160) ~[querydsl-jpa-4.4.0.jar:na]
	at com.querydsl.jpa.impl.AbstractJPAQuery.fetch(AbstractJPAQuery.java:202) ~[querydsl-jpa-4.4.0.jar:na]
	...

 

문제

Group By로 SELECT한 쿼리 결과를 DTO에 매핑하여 반환하려고 했는데

Constructor Expression에서 에러가 발생한 것을 발견했다

Projections.constructor()를 사용하면 매핑할 DTO의 생성자를 이용하여 데이터를 매핑해주는데

이 과정에서 문제가 생긴 것 같았다

List<WorkHistoryResDto> workHistoryList = jpaQueryFactory
                //SELECT 쿼리한 결과를 WorkHistoryQdslResDto에 매핑
                //Projections.constructor를 사용하면 Target DTO의 생성자를 이용하여 매핑해줌
                .select(Projections.constructor(WorkHistoryResDto.class
                        ,formattedDate.as("workDate")
                        ,workHistory.workUser.loginId
                        ,workHistory.functionType
                        ,workHistory.workType
                        ,workHistory.count().as("workCount")
                ))
                .from(workHistory)
                .where(
                       workType!=null?eqWorkType(workType):null
                        ,functionType!=null?eqFunctionType(functionType):null
                )
                .groupBy(
                        formattedDate
                        ,workHistory.workUser
                        ,workHistory.functionType
                        ,workHistory.workType)
                .fetch();

 

원인

힌트는 쿼리 로그에 있었다

2023-04-09 21:50:36.007 TRACE 54087 --- [nio-8080-exec-6] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [%Y-%m-%d]
2023-04-09 21:50:36.007 TRACE 54087 --- [nio-8080-exec-6] o.h.type.descriptor.sql.BasicBinder      : binding parameter [2] as [VARCHAR] - [%Y-%m-%d]
2023-04-09 21:50:36.017 TRACE 54087 --- [nio-8080-exec-6] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([col_0_0_] : [VARCHAR]) - [2023-03-30]
2023-04-09 21:50:36.018 TRACE 54087 --- [nio-8080-exec-6] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([col_1_0_] : [VARCHAR]) - [user1]
2023-04-09 21:50:36.018 TRACE 54087 --- [nio-8080-exec-6] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([col_2_0_] : [VARCHAR]) - [FEED]
2023-04-09 21:50:36.018 TRACE 54087 --- [nio-8080-exec-6] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([col_3_0_] : [VARCHAR]) - [WRITE]
2023-04-09 21:50:36.019 TRACE 54087 --- [nio-8080-exec-6] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([col_4_0_] : [BIGINT]) - [1]

 

문제와 관련된 로그는 아래와 같다

"extracted value ([col_0_0_] : [VARCHAR]) - [2023-03-30]"

 

위 로그를 자세히 보면 데이터 타입이 날짜타입이 아닌 "VARCHAR"타입으로 되어있다

VARCHAR 타입은 문자열 타입이기 때문에 날짜 데이터 타입으로 받을 수 없다

나는 DTO에 날짜 값이 날짜 데이터 타입으로 매핑될 것을 기대하고 코드를 작성했기 때문에 에러가 발생했다

package com.litCitrus.zamongcampusServer.dto.history;

import com.litCitrus.zamongcampusServer.domain.history.WorkHistoryType;
import lombok.*;

@Builder
@NoArgsConstructor(access = AccessLevel.PROTECTED)
@AllArgsConstructor
@Getter
public class WorkHistoryResDto {
    /* 날짜 데이터 필드의 타입을 String -> LocalDateTime으로 매핑하려고 하니 에러발생 */
    private LocalDateTime workDate;
    /* ----------------------------------------------------------------- */
    private String workUserId;
    private WorkHistoryType.FunctionType functionType;
    private WorkHistoryType.WorkType workType;
    private Long workCount;

    public WorkHistoryResDto(LocalDateTime workDate, WorkHistoryType.FunctionType functionType, WorkHistoryType.WorkType workType, Long workCount){
        this.workDate = workDate;
        this.functionType = functionType;
        this.workType = workType;
        this.workCount = workCount;
    }
}

 

해결

DTO에서 날짜 값을 저장하는 필드의 데이터 타입을 String으로 변경

package com.litCitrus.zamongcampusServer.dto.history;

import com.litCitrus.zamongcampusServer.domain.history.WorkHistoryType;
import lombok.*;

@Builder
@NoArgsConstructor(access = AccessLevel.PROTECTED)
@AllArgsConstructor
@Getter
public class WorkHistoryResDto {
    /* 날짜 데이터 필드의 타입을 LocalDateTime -> String으로 변경 */
    private String workDate;
    /* -------------------------------------------------- */
    private String workUserId;
    private WorkHistoryType.FunctionType functionType;
    private WorkHistoryType.WorkType workType;
    private Long workCount;

    public WorkHistoryResDto(String workDate, WorkHistoryType.FunctionType functionType, WorkHistoryType.WorkType workType, Long workCount){
        this.workDate = workDate;
        this.functionType = functionType;
        this.workType = workType;
        this.workCount = workCount;
    }
}

 

느낀 점

QueryDSL을 적용해서 코드를 작성한 게 처음이었는데

앞으로는 위와 같은 에러가 발생하면 로그를 한번 더 확인해봐야겠다