정렬 & 페이징

test에 sort기능을 넣어보자.

@Test
public void sort() {
    // 이름이 올림차순 5 6 null은 마지막에
    em.persist(new Member(null, 100));
    em.persist(new Member("member5", 100));
    em.persist(new Member("member6", 100));

    List<Member> result = queryFactory
            .selectFrom(member)
            .where(member.age.eq(100))
            .orderBy(member.age.desc(), member.username.asc().nullsLast())
            .fetch();

    Member member5 = result.get(0);
    Member member6 = result.get(1);
    Member memberNull = result.get(2);
    assertThat(member5.getUsername()).isEqualTo("member5");
    assertThat(member6.getUsername()).isEqualTo("member6");
    assertThat(memberNull.getUsername()).isNull();
}

/* select
        member1 
    from
        Member member1 
    where
        member1.age = ?1 
    order by
        member1.age desc,
        member1.username asc nulls last */
        
        
        
        // 여기 부터는 sql이다.
        select
            member0_.id as id1_1_,
            member0_.age as age2_1_,
            member0_.team_id as team_id4_1_,
            member0_.username as username3_1_ 
        from
            member member0_ 
        where
            member0_.age=? 
        order by
            member0_.age desc,
            member0_.username asc nulls last

 

 

nullLast말고 nullFirst도 있다.

.orderBy(member.age.desc(), member.username.asc().nullsFirst())

 

 

 

페이징 기능을 살펴보자

 

test를 돌려서 실행된 쿼리를 보면

// QuerydslBasicTest

@Test
public void paging1() {
    List<Member> result = queryFactory
            .selectFrom(member)
            // 페이징에는 orderBy를 넣어야 한다.
            .orderBy(member.username.desc())
            // 1개를 스킵하고(0부터 시작)
            .offset(1)
            // 2개까지
            .limit(2)
            .fetch();

    assertThat(result.size()).isEqualTo(2);
}

 

limit offset이 잘 들어가있다.

/ select
            member0_.id as id1_1_,
            member0_.age as age2_1_,
            member0_.team_id as team_id4_1_,
            member0_.username as username3_1_ 
        from
            member member0_ 
        order by
            member0_.username desc limit ? offset ?

 

파라미터 바인딩 된거 또한 확인 가능하다. db 방언에 따라서 다른게 실행이 된다. oracle이면 nullable이 실행된다.

from Member member1
order by member1.username desc */ select member0_.id as id1_1_, member0_.age as age2_1_, member0_.team_id as team_id4_1_, member0_.username as username3_1_ from member member0_ order by member0_.username desc limit 2 offset 1;

 

 

paging2 실행시키면 쿼리가 2번 나가게 된다.

 

// 전체 조회 수가 필요하면
@Test
public void paging2() {
    QueryResults<Member> queryResults = queryFactory
            .selectFrom(member)
            // 페이징에는 orderBy를 넣어야 한다.
            .orderBy(member.username.desc())
            // 1개를 스킵하고(0부터 시작)
            .offset(1)
            // 2개까지
            .limit(2)
            .fetchResults();

    // total은 4개
    assertThat(queryResults.getTotal()).isEqualTo(4);
    assertThat(queryResults.getLimit()).isEqualTo(2);
    assertThat(queryResults.getOffset()).isEqualTo(1);
    // getResults =>  contents size가 나온다
    assertThat(queryResults.getResults().size()).isEqualTo(2);
}

// count 쿼리가 나가면 페이지 밑에 페이지 수가 나타나게 된다.

/* select
        count(member1) 
    from
        Member member1 */ select
            count(member0_.id) as col_0_0_ 
        from
            member member0_
            
            
            
            
/* select
        member1 
    from
        Member member1 
    order by
        member1.username desc */ select
            member0_.id as id1_1_,
            member0_.age as age2_1_,
            member0_.team_id as team_id4_1_,
            member0_.username as username3_1_ 
        from
            member member0_ 
        order by
            member0_.username desc limit ? offset ?

 

where해서 제약조건을 넣게 되면 양쪽 쿼리에 다 붙고 조인하는데 다 붙는다.

count 쿼리에도 붙고 contents 쿼리에도 붙게 된다. => 성능상 실무에서 애매할 때가 있다.

그러므로 컨텐츠 쿼리는 복잡한데 카운트 쿼리는 단순할 때가 있으면 쿼리 두개를 따로 작성한다.

@Test
public void paging2() { 
    QueryResults<Member> queryResults = queryFactory
            .selectFrom(member)
            // 페이징에는 orderBy를 넣어야 한다.
            .orderBy(member.username.desc())
            // 제약조건 붙으면
            .where(member)
            // 1개를 스킵하고(0부터 시작)
            .offset(1)
            // 2개까지
            .limit(2)
            .fetchResults();

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

<출처 김영한: 실전! Querydsl >

https://www.inflearn.com/course/Querydsl-%EC%8B%A4%EC%A0%84/lecture/30112?tab=curriculum 

실전! Querydsl - 인프런 | 학습 페이지