[Querydsl] 샘플

 

Where 절 Dynamic 으로 구성하기

    import com.querydsl.jpa.impl.JPAQuery;

    /**
     * JPAQuery<T> fromQuery
     * select QBean from + join 까지 작성
     */
    private <T> JPAQuery<T> queryWhere(JPAQuery<T> fromQuery, ApiForumSrchReqVo reqVo) {
        return fromQuery.where(
            getConditions(reqVo) // 여기에서 Where 절을 Dynamic 으로 구성
        );
    }
}

    import com.querydsl.core.BooleanBuilder;

	/**
     * ApiForumSrchReqVo : 파라미터를 위해 만들 Vo
     */
    private BooleanBuilder getConditions(ApiForumSrchReqVo reqVo) {
        BooleanBuilder builder = new BooleanBuilder();
        if (!isEmpty(reqVo.getForumIdx())) {
            builder.and(m.forumIdx.eq(reqVo.getForumIdx()));
        }
        if (!isEmpty(reqVo.getCreateMemberIdx())) {
            builder.and(m.memberIdx.eq(reqVo.getCreateMemberIdx()));
        }
        if (!isEmpty(reqVo.getForumName())) {
            builder.and(m.forumName.contains(reqVo.getForumName()));
        }
        if (!isEmpty(reqVo.getForumType())) {
            builder.and(d1.forumType.eq(reqVo.getForumType()));
        }
        if (!isEmpty(reqVo.getForumStatus())) {
            builder.and(d1.forumStatus.eq(reqVo.getForumStatus()));
        }
        if (!isEmpty(reqVo.getMemberType())) {
            builder.and(d4.memberType.eq(reqVo.getMemberType()));
        }
        if (!isEmpty(reqVo.getMemberStatus())) {
            builder.and(d4.memberStatus.eq(reqVo.getMemberStatus()));
        }
        if (!isEmpty(reqVo.getReqDtm())) {
            builder.and(d4.reqDtm.loe(reqVo.getReqDtm()));
        }
        if (!isEmpty(reqVo.getJoinDtm())) {
            builder.and(d4.joinDtm.loe(reqVo.getJoinDtm()));
        }
        return builder;
    }

 

전체 코드

package com.cr.back.api.forum.repository;

import static com.cr.back.comm.util.ObjectUtil.isEmpty;
import static com.cr.back.comm.util.QueryUtil.getOrderByList;
import static com.cr.back.comm.util.QueryUtil.getCase;

import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Optional;

import org.springframework.stereotype.Repository;

import com.cr.back.api.forum.model.vo.ApiForumSrchReqVo;
import com.cr.back.api.forum.model.vo.ApiForumVo;
import com.cr.back.comm.model.entity.forum.QForum;
import com.cr.back.comm.model.entity.forum.QForumInfo;
import com.cr.back.comm.model.entity.forum.QForumInfoPer;
import com.cr.back.comm.model.entity.forum.member.QForumMember;
import com.cr.back.comm.model.entity.forum.member.QForumMemberInfo;
import com.cr.back.comm.model.entity.forum.member.QForumMemberInfoPer;
import com.cr.back.comm.model.entity.forum.team.QForumTeamInfoPer;
import com.cr.back.comm.model.entity.forum.team.member.QForumTeamMember;
import com.querydsl.core.BooleanBuilder;
import com.querydsl.core.types.Projections;
import com.querydsl.core.types.QBean;
import com.querydsl.core.types.dsl.EntityPathBase;
import com.querydsl.jpa.impl.JPAQuery;
import com.querydsl.jpa.impl.JPAQueryFactory;

import lombok.RequiredArgsConstructor;

@Repository
@RequiredArgsConstructor
public class ApiForumQry {
    private final JPAQueryFactory jpaQueryFactory;
    private QForum m = new QForum("m");
    private QForumInfo d1 = new QForumInfo("d1");
    private QForumInfoPer d2 = new QForumInfoPer("d2");
    private QForumMember d3 = new QForumMember("d3");
    private QForumMemberInfo d4 = new QForumMemberInfo("d4");
    private QForumMemberInfoPer d5 = new QForumMemberInfoPer("d5");
    private QForumTeamMember d6 = new QForumTeamMember("d6");
    private QForumTeamInfoPer d7 = new QForumTeamInfoPer("d7");

    private QBean<ApiForumVo> qListBean = Projections.bean(
            ApiForumVo.class,
            m.forumIdx,
            m.memberIdx.as("createMemberIdx"),
            m.forumName,
            m.forumDesc,
            m.sortBy,
            d1.forumType,
            d1.forumStatus,
            d1.publishYn,
            d1.registDate,
            d1.publishDate,
            d3.memberIdx,
            d4.memberType,
            d4.memberStatus,
            d4.reqDtm,
            d4.joinDtm,
            getCase(d2.listQueryPer.loe(d5.listQueryPer.coalesce(d7.listQueryPer.max().coalesce("0000"))),
                    "Y", "N", "listQueryPer"),
            getCase(d2.detailQueryPer.loe(d5.detailQueryPer.coalesce(d7.detailQueryPer.max().coalesce("0000"))),
                    "Y", "N", "detailQueryPer"),
            getCase(d2.invitationPer.loe(d5.invitationPer.coalesce(d7.invitationPer.max().coalesce("0000"))),
                    "Y", "N", "invitationPer"),
            getCase(d2.createPer.loe(d5.createPer.coalesce(d7.createPer.max().coalesce("0000"))),
                    "Y", "N", "createPer"),
            getCase(d2.modifyPer.loe(d5.modifyPer.coalesce(d7.modifyPer.max().coalesce("0000"))),
                    "Y", "N", "modifyPer"),
            getCase(d2.removePer.loe(d5.removePer.coalesce(d7.removePer.max().coalesce("0000"))),
                    "Y", "N", "removePer"));

    private Map<String, EntityPathBase> orderByMap = new HashMap<String, EntityPathBase>() {
        {
            put("forumIdx", m);
            put("forumName", m);
            put("forumDesc", m);
            put("sortBy", m);
        }
    };

    public List<ApiForumVo> findList(ApiForumSrchReqVo reqVo) {
        List<ApiForumVo> result = queryFromWhere(jpaQueryFactory.select(qListBean), reqVo)
                .offset(reqVo.getOffset())
                .limit(reqVo.getLimit())
                .orderBy(getOrderByList(orderByMap, reqVo))
                .fetch();
        return result;
    }

    public Long findListTotcnt(ApiForumSrchReqVo reqVo) {
        Long result = queryFromWhere(jpaQueryFactory.select(m.count()), reqVo)
                .fetchOne();
        return result;
    }

    public Optional<ApiForumVo> findOne(ApiForumSrchReqVo reqVo) {
        return Optional.ofNullable(queryFromWhere(jpaQueryFactory.select(qListBean), reqVo).fetchOne());
    }

    private BooleanBuilder getConditions(ApiForumSrchReqVo reqVo) {
        BooleanBuilder builder = new BooleanBuilder();
        if (!isEmpty(reqVo.getForumIdx())) {
            builder.and(m.forumIdx.eq(reqVo.getForumIdx()));
        }
        if (!isEmpty(reqVo.getCreateMemberIdx())) {
            builder.and(m.memberIdx.eq(reqVo.getCreateMemberIdx()));
        }
        if (!isEmpty(reqVo.getForumName())) {
            builder.and(m.forumName.contains(reqVo.getForumName()));
        }
        if (!isEmpty(reqVo.getForumType())) {
            builder.and(d1.forumType.eq(reqVo.getForumType()));
        }
        if (!isEmpty(reqVo.getForumStatus())) {
            builder.and(d1.forumStatus.eq(reqVo.getForumStatus()));
        }
        if (!isEmpty(reqVo.getMemberType())) {
            builder.and(d4.memberType.eq(reqVo.getMemberType()));
        }
        if (!isEmpty(reqVo.getMemberStatus())) {
            builder.and(d4.memberStatus.eq(reqVo.getMemberStatus()));
        }
        if (!isEmpty(reqVo.getReqDtm())) {
            builder.and(d4.reqDtm.loe(reqVo.getReqDtm()));
        }
        if (!isEmpty(reqVo.getJoinDtm())) {
            builder.and(d4.joinDtm.loe(reqVo.getJoinDtm()));
        }
        return builder;
    }

    private <T> JPAQuery<T> queryFromWhere(JPAQuery<T> selectQuery, ApiForumSrchReqVo reqVo) {
        return queryWhere(queryFrom(selectQuery, reqVo), reqVo);
    }

    private <T> JPAQuery<T> queryFrom(JPAQuery<T> selectQuery, ApiForumSrchReqVo reqVo) {
        return selectQuery.from(m)
                .join(d1).on(m.forumIdx.eq(d1.forumIdx))
                .join(d2).on(m.forumIdx.eq(d2.forumIdx))
                .leftJoin(d3).on(m.forumIdx.eq(d3.forumIdx).and(d3.memberIdx.eq(reqVo.getLoginMemberIdx())))
                .leftJoin(d4).on(m.forumIdx.eq(d4.forumIdx).and(d3.memberIdx.eq(d4.memberIdx)))
                .leftJoin(d5).on(m.forumIdx.eq(d5.forumIdx).and(d3.memberIdx.eq(d5.memberIdx)))
                .leftJoin(d6).on(m.forumIdx.eq(d6.forumIdx).and(d6.memberIdx.eq(reqVo.getLoginMemberIdx())))
                .leftJoin(d7).on(m.forumIdx.eq(d7.forumIdx).and(d6.teamIdx.eq(d7.teamIdx)));
    }

    private <T> JPAQuery<T> queryWhere(JPAQuery<T> fromQuery, ApiForumSrchReqVo reqVo) {
        return fromQuery.where(getConditions(reqVo))
                .groupBy(m.forumIdx,
                        m.memberIdx,
                        m.forumName,
                        m.forumDesc,
                        m.sortBy,
                        d1.forumType,
                        d1.forumStatus,
                        d1.publishYn,
                        d1.registDate,
                        d1.publishDate,
                        d3.memberIdx,
                        d4.memberType,
                        d4.memberStatus,
                        d4.reqDtm,
                        d4.joinDtm,
                        d2.listQueryPer,
                        d5.listQueryPer,
                        d2.detailQueryPer,
                        d5.detailQueryPer,
                        d2.invitationPer,
                        d5.invitationPer,
                        d2.createPer,
                        d5.createPer,
                        d2.modifyPer,
                        d5.modifyPer,
                        d2.removePer,
                        d5.removePer);
    }
}