Section4. 중급문법

프로젝션과 결과 반환 - 기본

• select절에 뭘 가져올지 지정하는 것
• 프로젝션 대상이 하나면 타입을 명확하게 지정 가능
• 둘 이상이면 튜플이나 DTO로 조회

튜플 조회

• repository계층에서만 사용해야함.

@Test
    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로 조회

• 순수 JPA에서 DTO로 조회
  • 순수 JPA에서 DTO를 조회할 때 new 명령어를 사용해야 함
  • DTO의 패키지 경로를 기입해야 해서 지저분
  • 생성자 방식만 지원

@Test
    void findDtoByJPQL() {
        // new Operation
        List<MemberDto> resultList = em.createQuery("select new study.querydsl.dto.MemberDto(m.username, m.age) from Member m", MemberDto.class).getResultList();
        for (MemberDto memberDto : resultList) {
            System.out.println("memberDto = " + memberDto);
        }
    }

Querydsl 빈 생성(Bean population)

• 프로퍼티 접근
• setter이용

@Test
    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);
        }
    }

• 필드 직접 접근
  • Getter/Setter무시하고 바로 필드를 인식해서 꽂힘

@Test
    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);
        }
    }

• 생성자

@Test
    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);
        }
    }

• 별칭과 필드 이름이 다를 때 as를 붙여 사용 (필드 접근)

// 별칭이 다를때
    @Test
    void findUserDtoByField(){
        List<UserDto> result = queryFactory
                .select(Projections.fields(UserDto.class, member.username.as("name"), member.age))
                .from(member)
                .fetch();
        for (UserDto userDto : result) {
            System.out.println("memberDto = " + userDto);
        }
    }

• 서브쿼리를 생성하여 쿼리 만들어 별칭 지정할 때 (필드 접근)

@Test
    void findUserDtoByField(){
        QMember memberSub = new QMember("memberSub");
        List<UserDto> result = queryFactory
                .select(Projections.fields(UserDto.class
                        , member.username.as("name")
                        , Expressions.as(JPAExpressions
                                .select(memberSub.age.max())
                                        .from(memberSub), "age")))
                .from(member)
                .fetch();
        for (UserDto userDto : result) {
            System.out.println("memberDto = " + userDto);
        }
    }

• 정리
  • ExpressionUtils.as(source,alias) : 필드나, 서브 쿼리에 별칭 적용
  • username.as("memberName") : 필드에 별칭 적용
• 생성자 별칭 지정

@Test
    void findUserDtoByConstructor(){
        List<UserDto> result = queryFactory
                .select(Projections.constructor(UserDto.class, member.username, member.age))
                .from(member)
                .fetch();
        for (UserDto userDto : result) {
            System.out.println("memberDto = " + userDto);
        }
    }

프로젝션 결과반환 - @QueryProjection

• 생성자 접근은 생성자에 없는 다른 필드의 데이터를 넣었을 때, 런타임 오류로 발생 시키지만 QueryProjection은 바로 컴파일 오류로 발생시켜준다.
• 단점
  • Q파일 생성시켜줘야한다.
  • queryDsl의 의존성을 가진다.

// MemberDto 생성자에 @QueryProjection을 붙여주고 compileQuerydsl을 빌드해준다.
@Test
    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);
        }
    }

동적쿼리 - BooleanBuilder 사용

동적 쿼리를 해결하는 두가지방식

• BooleanBuilder
• Where 다중 파라미터 사용

@Test
    public void dynamicQuery_BooleanBuilder() {
        String usernameParam = "member1";
        Integer ageParam = 10;

        List<Member> result = searchMember1(usernameParam, ageParam);
        assertThat(result.size()).isEqualTo(1);
    }

    private List<Member> searchMember1(String usernameParam, Integer ageParam) {
        BooleanBuilder builder = new BooleanBuilder();

        if(usernameParam !=null) {
            builder.and(member.username.eq(usernameParam));
        }

        if(ageParam != null) {
            builder.and(member.age.eq(ageParam));
        }
        List<Member> result = queryFactory.selectFrom(member)
                .where(builder)
                .fetch();
        return result;
    }

/* select
        member1 
    from
        Member member1 
    where
        member1.username = ?1 
        and member1.age = ?2 */ select
            member0_.member_id as member_i1_1_,
            member0_.age as age2_1_,
            member0_.team_id as team_id4_1_,
            member0_.username as username3_1_ 
        from
            member member0_ 
        where
            member0_.username=? 
            and member0_.age=?
2024-02-04 06:52:57.130 TRACE 16412 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [member1]
2024-02-04 06:52:57.130 TRACE 16412 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [2] as [INTEGER] - [10]
2024-02-04 06:52:57.136 TRACE 16412 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([member_i1_1_] : [BIGINT]) - [3]
2024-02-04 06:52:57.247  INFO 16412 --- [           main] o.s.t.c.transaction.TransactionContext   : Rolled back transaction for test: [DefaultTestContext@7fcf2fc1 testClass = QuerydslBasicTest, testInstance = study.querydsl.QuerydslBasicTest@5ffdd510, testMethod = dynamicQuery_BooleanBuilder@QuerydslBasicTest, testException = [null], mergedContextConfiguration = [WebMergedContextConfiguration@2141a12 testClass = QuerydslBasicTest, locations = '{}', classes = '{class study.querydsl.QuerydslApplication}', contextInitializerClasses = '[]', activeProfiles = '{}', propertySourceLocations = '{}', propertySourceProperties = '{org.springframework.boot.test.context.SpringBootTestContextBootstrapper=true}', contextCustomizers = set[org.springframework.boot.test.autoconfigure.actuate.metrics.MetricsExportContextCustomizerFactory$DisableMetricExportContextCustomizer@671a5887, org.springframework.boot.test.autoconfigure.properties.PropertyMappingContextCustomizer@0, org.springframework.boot.test.autoconfigure.web.servlet.WebDriverContextCustomizerFactory$Customizer@2beee7ff, org.springframework.boot.test.context.filter.ExcludeFilterContextCustomizer@3541cb24, org.springframework.boot.test.json.DuplicateJsonObjectContextCustomizerFactory$DuplicateJsonObjectContextCustomizer@13b13b5d, org.springframework.boot.test.mock.mockito.MockitoContextCustomizer@0, org.springframework.boot.test.web.client.TestRestTemplateContextCustomizer@3246fb96, org.springframework.boot.test.context.SpringBootTestArgs@1, org.springframework.boot.test.context.SpringBootTestWebEnvironment@1cd072a9], resourceBasePath = 'src/main/webapp', contextLoader = 'org.springframework.boot.test.context.SpringBootContextLoader', parent = [null]], attributes = map['org.springframework.test.context.web.ServletTestExecutionListener.activateListener' -> true, 'org.springframework.test.context.web.ServletTestExecutionListener.populatedRequestContextHolder' -> true, 'org.springframework.test.context.web.ServletTestExecutionListener.resetRequestContextHolder' -> true, 'org.springframework.test.context.event.ApplicationEventsTestExecutionListener.recordApplicationEvents' -> false]]

• ageParam에 null 입력 시 조건에 username으로만 붙는다.

2024-02-04 06:53:56.051 DEBUG 31748 --- [           main] org.hibernate.SQL                        : 
    /* select
        member1 
    from
        Member member1 
    where
        member1.username = ?1 */ select
            member0_.member_id as member_i1_1_,
            member0_.age as age2_1_,
            member0_.team_id as team_id4_1_,
            member0_.username as username3_1_ 
        from
            member member0_ 
        where
            member0_.username=?

동적쿼리 - Where 다중 파라미터 사용

• 가독성이 좋고, 소스 분석 할 때 편리함
• where에 null이 들어가면 조건에 무시된다.

@Test
    public void dynamicQuery_WhereParam() {
        String usernameParam = "member1";
        Integer ageParam = 10;

        List<Member> result = searchMember2(usernameParam, ageParam);
        assertThat(result.size()).isEqualTo(1);
    }

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

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

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

• 쿼리 조건들을 조립할 수 있다.
• 광고상태 isValid, 날짜가 isServicable로 조건 메서드를 각각 추가하면 다른 조건에 필요할 때 골라서 쓸 수 있다. (재활용 가능)

@Test
    public void dynamicQuery_WhereParam() {
        String usernameParam = "member1";
        Integer ageParam = 10;

        List<Member> result = searchMember2(usernameParam, ageParam);
        assertThat(result.size()).isEqualTo(1);
    }

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

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

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

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

수정, 삭제 벌크 연산

• 쿼리 한번으로 대량 데이터 수정

// member1 = 10 -> 비회원
        // member2 = 20 -> 비회원
        // member3 = 30 -> 유지
        // member4 = 40 -> 유지

        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 member1 : result) {
            System.out.println("member = " + member1);
        }

• 기존 숫자에 1 더하기

@Test
    public void buldAdd() {
        queryFactory
                .update(member)
                .set(member.age, member.age.add(1))
                .execute();
                        
    }

• 곱하기 (multply(x))

@Test
    public void buldMultyply() {
        queryFactory
                .update(member)
                .set(member.age, member.age.multiply(2))
                .execute();

    }

• 삭제

@Test
    public void bulkDelete() {
        queryFactory
                .delete(member)
                .where(member.age.gt(10))
                .execute();

    }

SQL function 호출하기

• SQL function은 JPA와 같이 Dialect에 등록된 내용만 호출할 수 있다.
• member → M으로 변경하는 replace함수 사용

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

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

2024-02-04 07:31:16.695 TRACE 31000 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [INTEGER] - [40]
2024-02-04 07:31:16.696 TRACE 31000 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [2] as [BIGINT] - [2]
2024-02-04 07:31:16.696 TRACE 31000 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [3] as [VARCHAR] - [member4]
2024-02-04 07:31:16.696 TRACE 31000 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [4] as [BIGINT] - [6]
2024-02-04 07:31:16.704 DEBUG 31000 --- [           main] org.hibernate.SQL                        : 
    /* select
        function('replace',
        member1.username,
        ?1,
        ?2) 
    from
        Member member1 */ select
            replace(member0_.username,
            ?,
            ?) as col_0_0_ 
        from
            member member0_
2024-02-04 07:31:16.707 TRACE 31000 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [member]
2024-02-04 07:31:16.709 TRACE 31000 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [2] as [VARCHAR] - [M]
2024-02-04 07:31:16.712 TRACE 31000 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([col_0_0_] : [VARCHAR]) - [M1]
2024-02-04 07:31:16.712 TRACE 31000 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([col_0_0_] : [VARCHAR]) - [M2]
2024-02-04 07:31:16.712 TRACE 31000 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([col_0_0_] : [VARCHAR]) - [M3]
2024-02-04 07:31:16.712 TRACE 31000 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([col_0_0_] : [VARCHAR]) - [M4]

• 소문자 비교
• lower같은 ansi 표준함수들은 querydls이 상당 부분 내장하고 있기 때문에 .where(member.username.eq(member.username.lower())) 로 처리해도 된다.

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);
        }
    }

 

Source Path

https://github.com/jang314/querydsl

GitHub - jang314/querydsl