ReBugs
블로그 이미지

Home

About Me
11-23 15:29
light
Java Category/JPA

[Query DSL] 중급 문법

ReBugs
|2024. 8. 19. 00:47

이 글은 인프런 김영한님의 Spring 강의를 바탕으로 개인적인 정리를 위해 작성한 글입니다.

테스트 기본 코드

@Entity
@Getter @Setter
@NoArgsConstructor(access = AccessLevel.PROTECTED)
@ToString(of = {"id", "username", "age"})
public class Member {
    @Id @GeneratedValue
    @Column(name = "member_id")
    private Long id;

    private String username;

    private int age;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "team_id")
    private Team team;

    public Member(String username) {
        this(username, 0);
    }

    public Member(String username, int age) {
        this(username, age, null);
    }

    public Member(String username, int age, Team team) {
        this.username = username;
        this.age = age;
        if (team != null) {
            changeTeam(team);
        }
    }

    public void changeTeam(Team team) {
        this.team = team;
        team.getMembers().add(this);
    }
}

 

@Data
public class MemberDto {
    private String username;
    private int age;

    public MemberDto() {
    }

    public MemberDto(String username, int age) {
        this.username = username;
        this.age = age;
    }
}

 

@Entity
@Getter @Setter
@NoArgsConstructor(access = AccessLevel.PROTECTED)
@ToString(of = {"id", "name"})
public class Team {

    @Id
    @GeneratedValue
    @Column(name = "team_id")
    private Long id;

    private String name;

    @OneToMany(mappedBy = "team")
    private List<Member> members = new ArrayList<>();

    public Team(String name) {
        this.name = name;
    }
}

 

@SpringBootTest
@Transactional
public class QuerydslBasicTest {
    @PersistenceContext
    EntityManager em;

    JPAQueryFactory queryFactory;

    @BeforeEach
    public void before() {
        queryFactory = new JPAQueryFactory(em);

        Team teamA = new Team("teamA");
        Team teamB = new Team("teamB");
        em.persist(teamA);
        em.persist(teamB);
        Member member1 = new Member("member1", 10, teamA);
        Member member2 = new Member("member2", 20, teamA);
        Member member3 = new Member("member3", 30, teamB);
        Member member4 = new Member("member4", 40, teamB);
        em.persist(member1);
        em.persist(member2);
        em.persist(member3);
        em.persist(member4);
    }
    //...
}

 

 

프로젝션과 결과 반환

프로젝션이란 SELECT 문의 대상 지정을 뜻한다.

 

프로젝션 대상이 하나

  • 프로젝션 대상이 하나면 타입을 명확하게 지정할 수 있음
  • 프로젝션 대상이 둘 이상이면 튜플이나 DTO로 조회
@Test
public void simpleProjection(){
    List<String> result = queryFactory
            .select(member.username)
            .from(member)
            .fetch();

    for (String s : result) {
        System.out.println("s = " + s);
    }

    List<Member> result2 = queryFactory
            .select(member)
            .from(member)
            .fetch();

    for (Member s : result2) {
        System.out.println("s = " + s);
    }
}

 

튜플 조회

프로젝션 대상이 둘 이상일 때 사용

@Test
public void tupleProjection(){
    List<Tuple> result = queryFactory
            .select(member.username, member.age)
            .from(member)
            .fetch();
    for (Tuple tuple : result) {
        String username = tuple.get(member.username);
        Integer age = tuple.get(member.age);
        System.out.println("username=" + username);
        System.out.println("age=" + age);
    }
}

 

 

DTO 조회

setter 접근

@Test
public void findDtoBySetter(){
    List<MemberDto> result = queryFactory
            .select(Projections.bean(MemberDto.class,
                    member.username,
                    member.age))
            .from(member)
            .fetch();

    for (MemberDto memberDto : result) {
        System.out.println("memberDto = " + memberDto);
    }
}

 

field 접근

@Test
public void findDtoByField(){
    List<MemberDto> result = queryFactory
            .select(Projections.fields(MemberDto.class,
                    member.username,
                    member.age))
            .from(member)
            .fetch();

    for (MemberDto memberDto : result) {
        System.out.println("memberDto = " + memberDto);
    }
}

DTO가 필드의 접근 제한자가 Private여도 상관 없이 필드에 주입이 된다.

 

 

constructor 접근

@Test
public void findDtoByConstructor(){
    List<MemberDto> result = queryFactory
            .select(Projections.constructor(MemberDto.class,
                    member.username,
                    member.age))
            .from(member)
            .fetch();

    for (MemberDto memberDto : result) {
        System.out.println("memberDto = " + memberDto);
    }
}

 

별칭이 다를 때

프로퍼티나, 필드 접근 생성 방식에서 이름이 다를 때 해결 방안

@Data
public class UserDto {
    private String name;
    private int age;
}

Member 엔티티는 userName이고 UserDto는 name이다.

 

  • ExpressionUtils.as(source,alias) : 필드나, 서브 쿼리에 별칭 적용 
  • username.as("memberName") : 필드에 별칭 적용
@Test
public void findUserDto(){
    QMember memberSub = new QMember("memberSub");
    List<UserDto> fetch = queryFactory
            .select(Projections.fields(UserDto.class,
                    member.username.as("name"),
                    ExpressionUtils.as(JPAExpressions
                                    .select(memberSub.age.max())
                                    .from(memberSub), "age")))
            .from(member)
            .fetch();

    for (UserDto userDto : fetch) {
        System.out.println("userDto = " + userDto);
    }
}

 

@QueryProjection

DTO의 생성자에 @QueryProjection 애노테이션을 붙힌다.

@Data
public class MemberDto {
    private String username;
    private int age;

    public MemberDto() {
    }

    @QueryProjection
    public MemberDto(String username, int age) {
        this.username = username;
        this.age = age;
    }
}

  • Gradle의 compileJava를 실행한다.
  • QMemberDto 생성 확인

 

@Test
public void findDtoByQueryProjection(){
    List<MemberDto> result = queryFactory
            .select(new QMemberDto(member.username, member.age))
            .from(member)
            .fetch();

    for (MemberDto memberDto : result) {
        System.out.println("memberDto = " + memberDto);
    }
}

 

컴파일 시점에 오류를 잡아준다.

하지만 DTO가 com.querydsl.core.annotations.QueryProjection 에 의존하게된다.

그렇기 때문에 QueryDSL을 사용하지 않으면 이 DTO를 사용하는 모든 계층에 대해 유지보수가 힘들어진다.

 

동적 쿼리

BooleanBuilder

보통 쿼리를 작성할 때, 여러 조건이 동적으로 추가될 수 있을 때가 있다.

예를 들어, 사용자가 입력한 값에 따라 조건이 달라질 때, BooleanBuilder를 사용하여 조건을 점진적으로 추가할 수 있다.

@Test
public void DynamicQuery_BooleanBuilder() throws Exception {
    String usernameParam = "member1";
    Integer ageParam = 10;
    List<Member> result = searchMember1(usernameParam, ageParam);
    Assertions.assertThat(result.size()).isEqualTo(1);
}

private List<Member> searchMember1(String usernameCond, Integer ageCond) {
    BooleanBuilder builder = new BooleanBuilder();
    if (usernameCond != null) {
        builder.and(member.username.eq(usernameCond));
    }
    if (ageCond != null) {
        builder.and(member.age.eq(ageCond));
    }
    return queryFactory
            .selectFrom(member)
            .where(builder)
            .fetch();
}

 

Where 다중 파라미터 사용

@Test
public void DynamicQuery_WhereParam() throws Exception {
    String usernameParam = "member1";
    Integer ageParam = 10;
    List<Member> result = searchMember2(usernameParam, ageParam);
    Assertions.assertThat(result.size()).isEqualTo(1);
}

private List<Member> searchMember2(String usernameCond, Integer ageCond) {
    return queryFactory
            .selectFrom(member)
            .where(usernameEq(usernameCond), ageEq(ageCond))
            .fetch();
}

private BooleanExpression usernameEq(String usernameCond) {
    return usernameCond != null ? member.username.eq(usernameCond) : null;
}

private BooleanExpression ageEq(Integer ageCond) {
    return ageCond != null ? member.age.eq(ageCond) : null;
}
  • where 조건에 null 값은 무시된다.
  • 메서드를 다른 쿼리에서도 재활용 할 수 있다.
  • 쿼리 자체의 가독성이 높아진다.

 

조합 가능

@Test
public void DynamicQuery_AllEq() throws Exception {
    String usernameParam = "member1";
    Integer ageParam = 10;
    List<Member> result = searchMember2(usernameParam, ageParam);
    Assertions.assertThat(result.size()).isEqualTo(1);
}

private List<Member> searchMember3(String usernameCond, Integer ageCond) {
    return queryFactory
            .selectFrom(member)
            .where(allEq(usernameCond, ageCond))
            .fetch();
}

private BooleanExpression allEq(String usernameCond, Integer ageCond) {
    return usernameEq(usernameCond).and(ageEq(ageCond));
}

private BooleanExpression usernameEq(String usernameCond) {
    return usernameCond != null ? member.username.eq(usernameCond) : null;
}

private BooleanExpression ageEq(Integer ageCond) {
    return ageCond != null ? member.age.eq(ageCond) : null;
}

이전의 BooleanBuilder 방식과는 조금 다르게, 각 조건을 따로 메서드로 분리하여 동적 쿼리를 작성한 후, 이를 결합하는 방식으로 구성되어 있다.

searchMember3 메서드는 주어진 usernameCond와 ageCond 조건에 따라 동적 쿼리를 실행하는 메서드이다.

where 절에서 allEq() 메서드를 호출하여 usernameCond와 ageCond에 따른 조건을 추가한다.

null 체크는 주의해서 처리해야 한다.

 

수정, 삭제 벌크 연산

JPQL 배치와 마찬가지로, 영속성 컨텍스트에 있는 엔티티를 무시하고 실행되기 때문에 배치 쿼리를 실행하 고 나면 영속성 컨텍스트를 초기화 하는 것이 안전하다.

@Test
public void bulkUpdate(){
    long count = queryFactory
            .update(member)
            .set(member.username, "비회원")
            .where(member.age.lt(28))
            .execute();

    em.flush();
    em.clear();

    List<Member> result = queryFactory
            .selectFrom(member)
            .fetch();
    for (Member member : result) {
        System.out.println("member = " + member);
    }
}

 

@Test
public void bulkAdd(){
    long count = queryFactory
            .update(member)
            .set(member.age, member.age.add(-1))
            .execute();

    em.flush();
    em.clear();

    List<Member> result = queryFactory
            .selectFrom(member)
            .fetch();
    for (Member member : result) {
        System.out.println("member = " + member);
    }
}

 

@Test
public void bulkMultiply(){
    long count = queryFactory
            .update(member)
            .set(member.age, member.age.multiply(2))
            .execute();

    em.flush();
    em.clear();

    List<Member> result = queryFactory
            .selectFrom(member)
            .fetch();
    for (Member member : result) {
        System.out.println("member = " + member);
    }
}

 

@Test
public void bulkDelete(){
    long count = queryFactory
            .delete(member)
            .where(member.age.gt(20))
            .execute();

    em.flush();
    em.clear();

    List<Member> result = queryFactory
            .selectFrom(member)
            .fetch();
    for (Member member : result) {
        System.out.println("member = " + member);
    }
}

 

 

SQL function 호출하기

SQL function은 JPA와 같이 Dialect에 등록된 내용만 호출할 수 있다.

 

member -> M으로 변경하는 replace 함수 사용

@Test
public void sqlFunction(){
    String result = queryFactory
            .select(Expressions.stringTemplate("function('replace', {0}, {1}, {2})",
                    member.username, "member", "M"))
            .from(member)
            .fetchFirst();

    System.out.println("result = " + result);

    List<String> result2 = queryFactory
            .select(Expressions.stringTemplate("function('replace', {0}, {1}, {2})",
                    member.username, "member", "M"))
            .from(member)
            .fetch();
    for (String s : result2) {
        System.out.println("s = " + s);
    }
}

 

소문자로 변경해서 비교

@Test
public void sqlFunction2(){
    List<String> result = queryFactory
            .select(member.username)
            .from(member)
            .where(member.username.eq(Expressions.stringTemplate("function('lower', {0})",
                    member.username)))
            .fetch();

    for (String s : result) {
        System.out.println("s = " + s);
    }
}

 

lower 같은 ansi 표준 함수들은 querydsl이 상당부분 내장하고 있다. 따라서 다음과 같이 처리해도 결과는 같다.

.where(member.username.eq(member.username.lower()))

'Java Category > JPA' 카테고리의 다른 글

[JPA]PostgreSQL 사용시, 엔티티에 Enum 매핑 오류  (0) 2024.11.21
[Query DSL] Spring Data JPA와 Query DSL 통합(+ Query DSL 페이징 최적화)  (0) 2024.08.20
[Query DSL] 기본 문법  (0) 2024.08.18
[Query DSL] Query DSL 초기 세팅  (0) 2024.08.17
[Spring Data JPA] Projections 과 Native Query  (0) 2024.08.16

티스토리툴바