Spring Webflux & QueryDSL

Spring Webflux에 dynamic query를 사용하기 위해서 QueryDSL을 붙히려고 했다.

Reactive에 맞는 것을 붙히기 위해 r2dbc & QueryDSL을 채택해서, https://github.com/infobip/infobip-spring-data-querydsl/issues 를 참조해서 진행했다.

1. 환경 : Spring Webflux, PostgreSQL (with r2dbc), gradle

gradle 파일 내용

  implementation 'com.fasterxml.jackson.core:jackson-databind:2.13.3'
implementation group: 'org.json', name: 'json', version: '20200518'
implementation group: 'com.googlecode.json-simple', name: 'json-simple', version: '1.1.1'
implementation group: 'org.apache.poi', name: 'poi-ooxml', version: '5.0.0'
// swagger
implementation 'io.springfox:springfox-swagger2:3.0.0'
implementation 'io.springfox:springfox-swagger-ui:3.0.0'
// r2dbc with QueryDSL
implementation 'org.springframework.boot:spring-boot-starter-data-r2dbc'
implementation 'org.springframework.boot:spring-boot-starter-webflux'
implementation 'io.r2dbc:r2dbc-postgresql:0.8.12.RELEASE'
implementation 'org.postgresql:postgresql'
implementation group: 'com.infobip', name: 'infobip-spring-data-r2dbc-querydsl-boot-starter', version: '6.2.0'
runtimeOnly 'io.r2dbc:r2dbc-pool'
runtimeOnly 'com.github.jasync-sql:jasync-r2dbc-mysql:2.0.8'
//annotationProcessor 'com.infobip:infobip-spring-data-jdbc-annotation-processor-common'
annotationProcessor group: 'com.infobip', name: 'infobip-spring-data-jdbc-annotation-processor', version: '6.2.0'
compileOnly 'org.projectlombok:lombok'
annotationProcessor 'org.projectlombok:lombok'
compileOnly 'org.projectlombok:lombok'
annotationProcessor 'org.projectlombok:lombok'
implementation 'org.springframework.boot:spring-boot-starter-log4j2'
implementation group: 'org.springframework', name: 'spring-test', version: '5.3.20'
implementation 'io.springfox:springfox-boot-starter:3.0.0'
testImplementation 'org.springframework.boot:spring-boot-starter-test'
testImplementation 'io.projectreactor:reactor-test'

• 적용
  일반적인 queryDSL 적용하듯이 적용하면 되었다.

 Repository 설정 예제)
import com.infobip.spring.data.r2dbc.QuerydslR2dbcFragment;
import com.infobip.spring.data.r2dbc.QuerydslR2dbcRepository;
import {project 경로}.dto.DailyVisitMart;
import org.springframework.data.querydsl.ReactiveQuerydslPredicateExecutor;
import org.springframework.stereotype.Repository;
 
 @Repository
public interface RDailyVisitMart extends QuerydslR2dbcRepository<DailyVisitMart, Long>, ReactiveQuerydslPredicateExecutor<DailyVisitMart>,
                                                    QuerydslR2dbcFragment<DailyVisitMart> {
}

DTO 설정 예제)
import java.time.LocalDateTime;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.springframework.data.annotation.Id;
import org.springframework.data.relational.core.mapping.Table;

  @Data
@NoArgsConstructor
@AllArgsConstructor
@Table("daily_visit_mart")
public class DailyVisitMart {
    @Id
    private LocalDateTime dt_day;
    private String disp_mall_no;
    private String basics_mall_no;
    private String basics_mall_name;
    private String spec_mall_no;
    private String spec_mall_name;
    private String media_dcode;
    private String media_dcode_name;
    private Long unique_visitors;
    private Long login_visitors;
    private Long visitors;
    private Long page_views;
    private Long new_visitors;
    private String bounce_rate;
    private LocalDateTime load_ts;
}

Repository와 DTO만 조금 다르고, 나머지는 하던대로 Controller 만들어서, 얘네들을 사용하면 된다.

QueryDSL이기 때문에, gradle build를 해주면, {project_path}/build/generated/sources/annotationProcessor/java/main/... 경로에 dto 기반으로 Q 파일들이 자동 생성된다. 그 Q 파일들은 쿼리 수행에 이용된다.

  간단한 쿼리 수행 예제)
private final RTotalSaleMartOrdDt rTotalSaleMartOrdDt;

    public Flux<TotalSaleMartOrdDt> getVendName() {
        return rTotalSaleMartOrdDt.query(query -> {
            QTotalSaleMartOrdDt totalSaleMartOrdDt = QTotalSaleMartOrdDt.totalSaleMartOrdDt;

            return query.select(rTotalSaleMartOrdDt.entityProjection())
                        .from(totalSaleMartOrdDt)
                        .limit(10);
        }).all();
    }

이런식으로 하면, 전체 조회가 가능하고, 따로 조회할 컬럼들의 DTO를 따로 정의하고,

public Flux<VisitDto> dailyVisit(Map<String, Object> map) {
      return rDailyVisitMart.query(query -> {
          QDailyVisitMart dailyVisitMart = QDailyVisitMart.dailyVisitMart;

          return
              query.select(
                  new QVisitDto(
                      dailyVisitMart.dt_day.as("ord_date"),
                      dailyVisitMart.unique_visitors.sum().as("uv"),
                      dailyVisitMart.login_visitors.sum().as("lv"),
                      dailyVisitMart.page_views.sum().as("pv"),
                      dailyVisitMart.visitors.sum().as("sessions"),
                      dailyVisitMart.new_visitors.sum().as("new_visits"),
                      dailyVisitMart.spec_mall_no
                  )
              )
              .from(dailyVisitMart)
              .where(dailyVisitMart.dt_day.between(
                  LocalDateTime.parse(map.get("startDate").toString(), DateTimeFormatter.ofPattern("yyyy-MM-dd")),
                  LocalDateTime.parse(map.get("endDate").toString(), DateTimeFormatter.ofPattern("yyyy-MM-dd"))
              )).groupBy(
                  dailyVisitMart.dt_day,
                  dailyVisitMart.basics_mall_no,
                  dailyVisitMart.spec_mall_no
              );
      }).all();
  }
  
  
 -- VisitDTO
import com.querydsl.core.annotations.QueryProjection;
import java.time.LocalDateTime;
import lombok.Data;

@Data
public class VisitDto {
  private LocalDateTime ord_date;
  private Long uv;
  private Long lv;
  private Long pv;
  private Long sessions;
  private Long new_visits;
  private String spec_mall_no;

  @QueryProjection
  public VisitDto(LocalDateTime ord_date, Long uv, Long lv, Long pv, Long sessions, Long new_visits, String spec_mall_no) {
      this.ord_date = ord_date;
      this.uv = uv;
      this.lv = lv;
      this.pv = pv;
      this.sessions = sessions;
      this.new_visits = new_visits;
      this.spec_mall_no = spec_mall_no;
  }
}

이런식으로 수행도 가능하다.

• 특이사항

  어떤 DB인지는 생각 안나는데, 그 DB가 아니면 SQLTemplate을 설정해줘야 했다.
  나는 PostgreSQL이라서
  @Bean
  public SQLTemplates sqlTemplates() {
  return new PostgreSQLTemplates();
  }
  PostgreSQLTemplate Bean 추가를 따로 해줘서 해결했다.