[QueryDSL] join, paging, where, dto Qclass

사용 이유

jpa nativeQuery로 쿼리 조회를 하다가 다중 필터를 사용하게 되면서, where절을 유동적으로 사용하기위해 사용

 

// 설문조사 리스트 조회
@Query(value =
        "SELECT sc.content, s.* " +
        "FROM survey s left join survey_category sc on sc.sur_cat_id = s.category_id " +
        "WHERE 1=1 " +
        "and s.status = 'I' " +
        "and s.is_private = 'N' "
        "and s.category_id = :categoryId "
        , nativeQuery = true)
Page<Map<String,Object>> findByCategoryIdAndStatus(@Param("categoryId") int categoryId, Pageable pageable);

 

 

---

1. pom.xml에 dependency 추가

<!-- QueryDSL APT Config -->
<dependency>
    <groupId>com.querydsl</groupId>
    <artifactId>querydsl-apt</artifactId>
    <version>${querydsl.version}</version>
    <scope>provided</scope>
</dependency>

<!-- QueryDSL JPA Config -->
<dependency>
    <groupId>com.querydsl</groupId>
    <artifactId>querydsl-jpa</artifactId>
    <version>${querydsl.version}</version>
</dependency>

<plugin>
    <groupId>com.mysema.maven</groupId>
    <artifactId>apt-maven-plugin</artifactId>
    <version>1.1.3</version>
    <executions>
        <execution>
            <goals>
                <goal>process</goal>
            </goals>
            <configuration>
                <outputDirectory>target/generated-sources/java</outputDirectory>
                <processor>com.querydsl.apt.jpa.JPAAnnotationProcessor</processor>
            </configuration>
        </execution>
    </executions>
</plugin>

 

2. Qclass 생성

pom.xml → Maven →  Generate Sources and Update Folders

 

3. SurveyRepositoryCustom.java

 public Page<SurveyDTO> findByRegIdAndCategoryIdAndStatusAndTitle(
            String title, String regId, Integer[] categoryId, SurveyStatus status, Pageable pageable) {

		//  CaseBuilder Expression 추가
        StringExpression caseStatusDeudateStr = new CaseBuilder()
                .when(qSurvey.dueDt.before(LocalDateTime.now())).then("마감")
                .when(qSurvey.dueDt.after(LocalDateTime.now())).then("배포").otherwise("");

		// qSurvey -> QSurveyDTO로 select
        List<SurveyDTO> list = queryFactory
                .select(new QSurveyDTO(
                         qSurvey.surId
                        ,qSurvey.title
                        ,qSurvey.description
                        ,qSurvey.surveyCategory.surCatId
                        ,qSurveyCategory.content.as("categoryContent")
                        ,qSurvey.version
                        ,qSurvey.status
                        ,qSurvey.dueDt
                        ,qSurvey.isLoginYn
                        ,qSurvey.isPrivateYn
                        ,qSurvey.isModifyYn
                        ,qSurvey.isAnnoyYn
                        ,qSurvey.regId
                        ,qSurvey.regDt
                        ,qSurvey.views
                        , new CaseBuilder()
                        .when(qSurvey.status.eq(SurveyStatus.valueOf("P"))).then("제작")
                        .when(qSurvey.status.eq(SurveyStatus.valueOf("I"))).then(caseStatusDeudateStr)
                        .otherwise("").as("statusName")
                        )
                )
                
                 // from 절
                .from(qSurvey)
                 // 테이블 조인
                .leftJoin(qSurveyCategory)
                .on(qSurvey.surveyCategory.surCatId.eq(qSurveyCategory.surCatId))
                // 조건절
               .where(
                    eqRegId(regId),
                    inCategoryId(categoryId),
                    likeTitle(title)
                )
                // 페이징
                .offset(pageable.getOffset())
                .limit(pageable.getPageSize())
                .orderBy(qSurvey.regDt.desc())
                .fetch();


		// 페이징을 위해 전체 갯수 카운트
        Long count = queryFactory
                .select(qSurvey.count())
                .from(qSurvey)
                .leftJoin(qSurveyCategory)
                .on(qSurvey.surveyCategory.surCatId.eq(qSurveyCategory.surCatId))
                .where(
                        eqRegId(regId),
                        inCategoryId(categoryId),
                        likeTitle(title)
                )
                .fetchOne();


        return  new PageImpl<>(list, pageable, count);
    }

 

3-1. dto를 Qclass로 만드는 방법

생성자에 @QueryProjection 어노테이션 추가

@Data
//@NoArgsConstructor
//@AllArgsConstructor
@Builder
public class SurveyDTO {

    private int surId;
    private String title;
    private String description;
    private int categoryId;
    private String categoryContent;
    private int version;
    private SurveyStatus status;
    private LocalDateTime dueDt;
    private IsYn isLoginYn;
    private IsYn isPrivateYn;
    private IsYn isModifyYn;
    private IsYn isAnnoyYn;
    private String regId;
    private LocalDateTime regDt;
    private Integer views;
    private String statusName;

    @QueryProjection
    public SurveyDTO(int surId, String title, String description, int categoryId, String categoryContent, int version, SurveyStatus status, LocalDateTime dueDt, IsYn isLoginYn, IsYn isPrivateYn, IsYn isModifyYn, IsYn isAnnoyYn, String regId, LocalDateTime regDt, Integer views, String statusName) {
        this.surId = surId;
        this.title = title;
        this.description = description;
        this.categoryId = categoryId;
        this.categoryContent = categoryContent;
        this.version = version;
        this.status = status;
        this.dueDt = dueDt;
        this.isLoginYn = isLoginYn;
        this.isPrivateYn = isPrivateYn;
        this.isModifyYn = isModifyYn;
        this.isAnnoyYn = isAnnoyYn;
        this.regId = regId;
        this.regDt = regDt;
        this.views = views;
        this.statusName = statusName;
    }
}

 

3-2. where절 메서드

// where 절
// null로 리턴되는 경우 쿼리 where 절을 타지 않음

private BooleanExpression eqRegId(String regId) {
    if (StringUtils.isEmpty(regId)) {
        return null;
    }
    return qSurvey.regId.eq(regId);
}

private BooleanExpression inCategoryId(Integer[] cateId) {
    if (cateId == null || cateId.length <1) {
        return null;
    }
    return qSurvey.surveyCategory.surCatId.in(cateId);
}

private BooleanExpression eqStatus(SurveyStatus status) {
    if (StringUtils.isEmpty(String.valueOf(status))) {
        return null;
    }
    return qSurvey.status.eq(status);
}

private BooleanExpression likeTitle(String title) {
    if (title == null || StringUtils.isEmpty(title)) {
        return null;
    }
    return qSurvey.title.like("%"+title+"%");
}