검색 조건 쿼리

실행해보면

@Test
public void search() {
    Member findMember = queryFactory
            .selectFrom(member)
            // 이름이 member1이면서 나이가 10살인 사람을 조회
            .where(member.username.eq("member1")
                    .and(member.age.eq(10)))
            .fetchOne();

    assertThat(findMember.getUsername()).isEqualTo("member1");
}

 

실제 쿼리가 and로 되어있다.

/* select
        member1 
    from
        Member member1 
    where
    
    // JPQL도 1 2 로 걸려있다.
        member1.username = ?1 
        and member1.age = ?2 */ 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_.username=? 
            and member0_.age=?

 

이거 말고도 무수하게 많은 검색 조건들을 제공한다. 

member.username.eq("member1") // username = 'member1'
member.username.ne("member1") //username != 'member1'
member.username.eq("member1").not() // username != 'member1'

member.username.isNotNull() //이름이 is not null

member.age.in(10, 20) // age in (10,20)
member.age.notIn(10, 20) // age not in (10, 20)
member.age.between(10,30) //between 10, 30

// greater or equal
member.age.goe(30) // age >= 30
// greater than
member.age.gt(30) // age > 30
// lower or equal
member.age.loe(30) // age <= 30
// lower than
member.age.lt(30) // age < 30

member.username.like("member%") //like 검색 
member.username.contains("member") // like ‘%member%’ 검색 
member.username.startsWith("member") //like ‘member%’ 검색

 

 

between 쿼리가 나가는 것을 볼 수 있다.

@Test
public void search() {
    Member findMember = queryFactory
            .selectFrom(member)
            // 이름이 member1이면서 나이가 10살~30살인 사람을 조회
            .where(member.username.eq("member1")
                    .and(member.age.between(10, 30)))
            .fetchOne();

    assertThat(findMember.getUsername()).isEqualTo("member1");
}

/* select
        member1 
    from
        Member member1 
    where
        member1.username = ?1 
        and member1.age between ?2 and ?3 */ 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_.username=? 
            and (
                member0_.age between ? and ?
            )

 

 

그리고 and를 안쓰고 나열할 수도 있다.

// and 안쓰고 풀 수도 있다.
// 야러개를 넘기면 다 and로 인식한다.
@Test
public void searchAndParam() {
    Member findMember = queryFactory
            .selectFrom(member)
            .where( 
                    member.username.eq("member1"),
                    member.age.eq(10)
            )
            .fetchOne();

    assertThat(findMember.getUsername()).isEqualTo("member1");
}

/* select
        member1 
    from
        Member member1 
    where
        member1.username = ?1 
        and member1.age = ?2 */ 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_.username=? 
            and member0_.age=?

 

그리고 and없이 쓴 절에서 중간에 null이 들어가게되면 null을 무시할 수 있게 된다.

(동적 쿼리를 만들 때 굉장히 편리하게 짤 수가 있다.)

// and 안쓰고 풀 수도 있다.
// 야러개를 넘기면 다 and로 인식한다.
// 중간에 null이 들어가면 null을 무시하게 된다.
@Test
public void searchAndParam() {
    Member findMember = queryFactory
            .selectFrom(member)
            .where(
                    member.username.eq("member1"),
                    member.age.eq(10), null
            )
            .fetchOne();

    assertThat(findMember.getUsername()).isEqualTo("member1");
}

 

 

 

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

https://www.inflearn.com/course/Querydsl-%EC%8B%A4%EC%A0%84/dashboard

실전! Querydsl - 인프런 | 강의