[querydsl, mysql] DATE_FORMAT 등 이용해 groupby 사용하기

DB의 날짜형태가 datetime 으로 되어있는데, 종종 날짜별로 그룹해서 데이터를 뽑아야할 때가 있다.

 

예를들어 다음과 같은 형태다

 

(원본) 2020-04-01 11:25:00  => (원하는 포멧) 2020-04-01

 

이럴경우 mybatis에서는 dbms함수를 사용해서 호출하면 되는데 querydsl 을 사용한다면 dbms함수를 따로 호출해야 한다. 그리고 결과로 나온 값을 DTO로 담아야 하는것도 신경써야 한다. 그래서 여기서는

 

1. groupby 를 쓸때 date_format 함수(mysql, 날짜형 변형 함수)사용법

2. 결과값을 dto에 넣기

 

두가지를 알아볼 예정이다.

 

 

1. (groupby) datetime을 date 유형으로 묶기

테이블 엔티티를 선언할때 날짜유형을 LocalDateTime 으로 지정했다. 그래서 시간까지 나오는 것을 date_format 함수를 이용해 묶을 필요가 있었다.

다음의 코드를 통해 function호출할 것을 선언한다

StringTemplate formattedDate = Expressions.stringTemplate(
        "DATE_FORMAT({0}, {1})"
        , member202004.regDt
        , ConstantImpl.create("%Y-%m-%d"));

그리고 queryFactory를 이용해 querydsl 문법을 다음과 같이 완성했다.

queryFactory
        .from(member202004)
        .groupBy(formattedDate, member202004.status)
        .fetch();

로그를 확인하면 쿼리문이 보이는데 group by 를 실행하는 것을 알 수 있다. 다만, select 를 지정하지 않았기 떄문에 user 테이블에 있는 모든 컬럼을 호출한다.

    select
        member2020x0_.id as id1_1_,
        member2020x0_.age as age2_1_,
        member2020x0_.name as name3_1_,
        member2020x0_.reg_dt as reg_dt4_1_,
        member2020x0_.status as status5_1_ 
    from
        member202004 member2020x0_ 
    group by
        date_format(member2020x0_.reg_dt,
        ?) ,
        member2020x0_.status

 

이제 select 지정과 데이터를 담을 DTO를 추가하도록 하자

 

 

 

2. select 추가 및 dto 생성

조회완료 후 데이터를 담을 DTO를 생성한다

@Getter
@ToString
public class UserStatusGroupDto {
    private LocalDate yyyymmdd;
    private String userStatus;
    private Long count;

    public UserStatusGroupDto(String yyyymmdd, String userStatus, Long count) {
        this.yyyymmdd = LocalDate.parse(yyyymmdd, DateTimeFormatter.ofPattern("yyyy-MM-dd"));
        this.userStatus = userStatus;
        this.count = count;
    }
}

 

 

 

select에 등록하는 방법은 몇가지가 있는데 여기서는 2가지를 다룰 예정이다

1) Projections (bean 또는 constructor 사용)

2) @QueryProjection

 

우선 1번인 Projections 를 이용해 선언하는 것을 보자

 

 

2-1. Projections

queryFactory
    .select(Projections.constructor(UserStatusGroupDto.class
            , formattedDate.as("yyyymmdd")
            , QUser.status
            , QUser.status.count()))
    .from(QUser)
    .where(
            QUser.regDt.goe(startDateTime)
            , QUser.regDt.loe(endDateTime)
    )
    .groupBy(formattedDate, QUser.status)
    .fetch();

 

이제부터 실행하면 모든 컬럼이 아닌 특정 컬럼만 호출하는 쿼리를 수행한다.

    select
        date_format(member2020x0_.reg_dt,
        ?) as col_0_0_,
        member2020x0_.status as col_1_0_,
        count(member2020x0_.status) as col_2_0_ 
    from
        member202004 member2020x0_ 
    group by
        date_format(member2020x0_.reg_dt,
        ?) ,
        member2020x0_.status

 

count 함수는 long 으로 리턴하기 때문에 Long으로 선언했고, Mysql에서 제공하는 DATE_FORMAT 함수는 varchar 형으로 변경하는 것이기 때문에 String으로 선언했다.

 

여기는 Projections를 사용할때 constructor 를 이용했지만 bean을 사용할 수도 있다. constructor는 말그대로 생성할때, 그리고 bean은 setter기반으로 작동한다. 장단점이 있겠지만 개인적으로 constructor 를 선호하는 편이라 사용했다(불변 객체를 지향하기 때문)

 

 

2-2. @QueryProjection

이번에는 @QueryProjection를 이용하는 방법이다.

방법은 간단하다. 위의 어노테이션을 생성자에 붙이면 된다.

@Getter
@ToString
public class Member202004GroupbyDTO {
    private LocalDate yyyymmdd;
    private Member202004.MemberStatus status;
    private Long memberCount;

    @QueryProjection // 추가
    public Member202004GroupbyDTO(String yyyymmdd, Member202004.MemberStatus status, Long memberCount) {
        this.yyyymmdd = LocalDate.parse(yyyymmdd, DateTimeFormatter.ofPattern("yyyy-MM-dd"));
        this.status = status;
        this.memberCount = memberCount;
    }
}

 

 

이 방법의 장점은 Q로 시작하는 DTO가 생성되기 때문에 컴파일단계에서 점검할 수 있다. 다만 QueryProjection 의존성이라는게 문제라면 문제지만, 컴파일 에러를 잡아줄 수 있기때문에 선호한다.

 

아래는 생성된 QMember202004GroupbyDTO 파일이다

@Generated("com.querydsl.codegen.ProjectionSerializer")
public class QMember202004GroupbyDTO extends ConstructorExpression<Member202004GroupbyDTO> {

    private static final long serialVersionUID = -1617098831L;

    public QMember202004GroupbyDTO(com.querydsl.core.types.Expression<String> yyyymmdd, com.querydsl.core.types.Expression<Member202004.MemberStatus> status, com.querydsl.core.types.Expression<Long> memberCount) {
        super(Member202004GroupbyDTO.class, new Class<?>[]{String.class, Member202004.MemberStatus.class, long.class}, yyyymmdd, status, memberCount);
    }

}

 

이제 이걸 기반으로 queryFactory 의 문구를 구성하자

queryFactory
    .select(
            new QMember202004GroupbyDTO(
                    formattedDate
                    , member202004.status
                    , member202004.status.count())
    )
    .from(member202004)
    .groupBy(formattedDate, member202004.status)
    .fetch();

 

만약 select 부분이 이상하면 다음과 같이 빨간줄이 표기된다.

 

 

 

끝.