순수 JPA Repo + Querydsl, 동적 쿼리

JPAQueryFactory 사용

 

Repository에서 JPAQueryFactory를 사용하는 방법은 두가지 있다

 

Repository에서 직접 EntityManager를 주입

@Repository
public class MemberJpaRepository {

    private final EntityManager em;
    private final JPAQueryFactory queryFactory;

    public MemberJpaRepository(EntityManager em) {
        this.em = em;
        this.queryFactory = new JPAQueryFactory(em);
    }
}

 

JPAQueryFactory 스프링 빈 등록

@Bean
JPAQueryFactory jpaQueryFactory(EntityManager em) {
   return new JPAQueryFactory(em);
}

BooleanBuilder를 이용한 동적 쿼리

 

MemberTeamDto.class

@Data
public class MemberTeamDto {

    private Long memberId;
    private String username;
    private int age;
    private Long teamId;
    private String teamName;

    @QueryProjection
    public MemberTeamDto(Long memberId, String username, int age, Long teamId, String teamName) {
        this.memberId = memberId;
        this.username = username;
        this.age = age;
        this.teamId = teamId;
        this.teamName = teamName;
    }
}

MemberSearchCondition.class(조회 조건)

@Data
public class MemberSearchCondition {

    private String username;
    private String teamName;
    private Integer ageGoe;
    private Integer ageLoe;
}

 

public List<MemberTeamDto> searchByBuilder(MemberSearchCondition condition) {

    BooleanBuilder builder = new BooleanBuilder();

    if (hasText(condition.getUsername())) {
        builder.and(member.username.eq(condition.getUsername()));
    }

    if (hasText(condition.getTeamName())) {
        builder.and(team.name.eq(condition.getTeamName()));
    }

    if (condition.getAgeGoe() != null) {
        builder.and(member.age.goe(condition.getAgeGoe()));
    }

    if (condition.getAgeLoe() != null) {
        builder.and(member.age.goe(condition.getAgeLoe()));
    }

    return queryFactory
           .select(new QMemberTeamDto(
                        member.id,
                        member.username,
                        member.age,
                        team.id,
                        team.name))
            .from(member)
            .leftJoin(member.team, team)
            .where(builder)
            .fetch();
}

• BooleanBuilder를 사용하면 null에 대한 처리를 주의해야한다
  • build.and(member.username.eq(condition.getUsername())) 부분에 만약 getUsername이 null이면 런타임 에러가 발생하기 떄문이다~

MemberSearchCondition condition = new MemberSearchCondition();
condition.setAgeGoe(35);
condition.setAgeLoe(40);
condition.setTeamName("teamB");

위 조건들을 파라미터로 보내게 되면 아래와 같은 쿼리를 보내게 된다~

 

BooleanExpression 이용한 동적 쿼리

public List<MemberTeamDto> search(MemberSearchCondition condition) {
    return queryFactory
            .select(new QMemberTeamDto(
                    member.id,
                    member.username,
                    member.age,
                    team.id,
                    team.name))
            .from(member)
            .leftJoin(member.team, team)
            .where(
                    usernameEq(condition.getUsername()),
                    teamNameEq(condition.getTeamName()),
                    ageGoe(condition.getAgeGoe()),
                    ageLoe(condition.getAgeLoe())
            )
            .fetch();
}

private BooleanExpression usernameEq(String username) {
    return hasText(username) ? member.username.eq(username) : null;
}
private BooleanExpression teamNameEq(String teamName) {
    return hasText(teamName) ? team.name.eq(teamName) : null;
}

private BooleanExpression ageGoe(Integer ageGoe) {
    return ageGoe != null ? member.age.goe(ageGoe) : null;
}

private BooleanExpression ageLoe(Integer ageLoe) {
    return ageLoe != null ? member.age.loe(ageLoe) : null;
}

• 조건 재사용 가능
• null 무시
• 가독성 좋음

 

만약 QMemberTeamDto가 아닌 Member를 조회하고 싶으면 어떡할까?

아래와 같이 조건은 그대로 놔두고 select절만 바꾸면 해결되는 것을 알 수 있다

public List<MemberTeamDto> search(MemberSearchCondition condition) {
    return queryFactory
            .selectFrom(member)
            .leftJoin(member.team, team)
            .where(
                    usernameEq(condition.getUsername()),
                    teamNameEq(condition.getTeamName()),
                    ageGoe(condition.getAgeGoe()),
                    ageLoe(condition.getAgeLoe())
            )
            .fetch();
}

 

조회 조건이 하나도 없다면 어떻게 될까?

실제 애플리케이션을 실행하고 아무 조건 없이 URL를 입력한다면 "모든 데이터"가 조회된다

 

초반엔 데이터가 몇 개 없어서 상관없지만 데이터의 양이 늘어날 수록 페이징 쿼리를 따로 작성해야한다

 

---

REFERENCES

영한님 querydsl 강의