기록하고 활용하자

4 minute read

JPQL

@SpringBootTest
@Transactional
public class QuerydslBasicTest {

    @PersistenceContext
    EntityManager em;

    @BeforeEach
    public void before() {
        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);
} 
}


Querydsl

@Test
public void startJPQL() {
    String qlString =
              "select m from Member m " +
              "where m.username = :username";
      Member findMember = 
      em.createQuery(qlString, Member.class)
              .setParameter("username", "member1")
              .getSingleResult();
      assertThat(findMember.getUsername()).isEqualTo("member1");
  }

@Test
public void startQuerydsl() {
    JPAQueryFactory queryFactory = 
    new JPAQueryFactory(em); 
    QMember m = new QMember("m");

    Member findMember = queryFactory
            .select(m)
            .from(m)
            .where(m.username.eq("member1"))
            .fetchOne();
      assertThat(findMember.getUsername())
      .isEqualTo("member1");
}


JPAQueryFactory를 필드로

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

      JPAQueryFactory queryFactory;
      @BeforeEach
      public void before() {

          queryFactory = new JPAQueryFactory(em);
          //...
           }
@Test
public void startQuerydsl2() {
    QMember m = new QMember("m");

    Member findMember = queryFactory
            .select(m)
            .from(m)
            .where(m.username.eq("member1"))
            .fetchOne();
    assertThat(findMember.getUsername())
    .isEqualTo("member1");
    }
}


# 기본 Q-Type 활용

 //Q클래스 인스턴스를 사용하는 2가지 방법
 QMember qmember = new QMember("m");
 QMember qMember = QMember.member;

 //기본 인스턴스를 static import와 함께 사용
 @Test
 public void startQuerydsl3(){
    Member findMember = queryFactory
        .select(member)
        .from(member)
        .where(member.username.eq("member1"))
        .fetchOne();

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


# 검색 조건 쿼리

 //기본 검색 쿼리
 @Test
 public void search(){
    Member findMember = queryFactory
        .selectFrom(member)
        .where(member.username.eq("member1"))
            .and(member.age.eq(10))
        .fetchOne();

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

//JPQL이 제공하는 모든 검색 조건 제공
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
member.age.goe(30) // age >= 30
member.age.gt(30) // age > 30
member.age.loe(30) // age <= 30
member.age.lt(30) // age < 30
member.username.like("member%") //like 검색 
member.username.contains("member") // like ‘%member%’ 검색 


//AND 조건 파라미터로 처리
@Test
public void searchAndParam(){
    List<Member> result1 = queryFactory
        .selectFrom(member)
        .where(member.username.eq("member1"),
            member.age.eq(10))
        .fetch();
    assertThat(result1.size()).isEqualTo(1);
}

//결과조회
  List<Member> fetch = queryFactory
          .selectFrom(member)
          .fetch();

//단 건
Member findMember1 = queryFactory
          .selectFrom(member)
          .fetchOne();

//처음 한 건 조회
Member findMember2 = queryFactory
          .selectFrom(member)
          .fetchFirst();

//페이징에서 사용
QueryResults<Member> results = queryFactory
          .selectFrom(member)
          .fetchResults();

//count 쿼리로 변경
long count = queryFactory
          .selectFrom(member)
          .fetchCount();


정렬

@Test
public void sort(){
    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(),nullLast())
        .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();
}


페이징

//조회 건수 제한
@Test
public void paging1(){
    List<Member> result = queryFactory
        .selectFrom(member)
        .orderBy(member.username.desc())
        .offset(1)
        .limit(2)
        .fetch();

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

//전체 조회 수
@Test
pulic void paging2(){
    QueryResult<Member> queryResult = queryFactory
        .selectFrom(member)
        .orderBy(member.username.desc())
        .offset(1)
        .limit(2)
        .fetchResult();

    assertThat(queryResults.getTotal()).isEqualTo(4);
    assertThat(queryResults.getLimit()).isEqualTo(2);
    assertThat(queryResults.getOffset()).isEqualTo(1);
    assertThat(queryResults.getResults().size()).isEqualTo(2);
}


집합

@Test
public void aggregation() throws Exception {
        List<Tuple> result = queryFactory
            .select(member.count(),
                member.age.sum(),
                member.age.avg(),
                member.age.max(),
                member.age.min())
            .from(member)
            .fetch();

        Tuple tuple = result.get(0);
        assertThat(tuple.get(member.count())).isEqualTo(4);
        assertThat(tuple.get(member.age.sum())).isEqualTo(100);
        assertThat(tuple.get(member.age.avg())).isEqualTo(25);
        assertThat(tuple.get(member.age.max())).isEqualTo(40);
        assertThat(tuple.get(member.age.min())).isEqualTo(10);
}

//GroupBy 사용

@Test
public void group() throws Exception{
    List<Tuple> result = queryFactory
        .select(team.name, member.age.avg())
        .from(member)
        .join(member.team, team)
        .groupBy(team.name)
        .fetch();

    Tuple teamA = result.get(0);
    Tuple teamb = result.get(1);

      assertThat(teamA.get(team.name)).isEqualTo("teamA");
      assertThat(teamA.get(member.age.avg())).isEqualTo(15);
      assertThat(teamB.get(team.name)).isEqualTo("teamB");
      assertThat(teamB.get(member.age.avg())).isEqualTo(35);
}

//groupBy(), having() 예시
    .groupBy(item.price)
    .having(item.price.gt(1000))
    ...


조인 - 기본 조인

@Test
public void join() throws Exception{
    QMember member = QMember.member;
    QTeam team =QTeam.team;

    List<Member> result = queryFactory
        .selectFrom(member)
        .join(member.team, team)
        .where(team.name.eq("teamA"))
        .fetch();

    assertThat(result)
        .extraction("username")
        .containsExactly("member1","member2");
}


세타조인


@Test
pulic void theta_join() throws Exception{
    em.persist(new Member("teamA"))
    em.persist(new Member("teamB"))

    List<Member> result = queryFactory
        .select(member)
        .from(member, team)
        .where(member.username.eq(team.name))
        .fetch();

    assertThat(result)
        .extraction("username")
        .containsExactly("teamA","teamB");
}


조인 - on절

//1. 조인 대상 필터링
@Test
public void join_on_filtering() throws Exception{
    List<Tuple> reuslt = queryFactory
        .select(member, team)
        .from(member)
        .leftJoin(member.team, team).on(team.name.eq("teamA"))
        .fetch();
    for(Tuple tuple : result){
        System.out.println("tuple = " + tuple);
    }
}

//2. 연관관계 없는 엔티티 외부 조인
@Test
public void join_on_no_relation() throws Exception{
    em.persist(new Member("teamA"))
    em.persist(new Member("teamB"))

    List<Tuple> result = queryFactory
        .select(member, team)
        .from(member)
        .leftJoin(team).on(member.username.eq(team.name))
        .fetch();

    for (Tuple tuple : reuslt){
        System.out.prinln("t =" + tuple);
    }
}


조인 - 페치조인(SQL을 한번에 조회하는 기능)


//미적용 

@PersistenceUnit
EntityMangerFactory emf;

@Test
public void fetchJoinNo() throws Exception{
    em.flush();
    em.clear();

    Member findMember = queryFactory
        .selectFrom(member)
        .where(member.username.eq("member1"))
        .fetchOne();

    boolean loaded = 
    emf.getPersistenceUnitUtil()
    .isLoaded(findMember.getTeam)();
    assertThat(loaded).as("패치 조인 미적용").isFalse();
}

//적용
@Test
public void fetchJoinUse() throws Exception{
    em.flush();
    em.clear();

    Member findMember = queryFactory
        .selectFrom(member)
        .join(member.team, team).fetchJoin()
        .where(member.username.eq("member1"))
        .fetchOne();

    boolean loaded =
    emf.getPersistenceUnitUtil()
    .isLoaded(findMember.getTeam());
assertThat(loaded).as("페치 조인 적용").isTrue();
}


서브쿼리

@Test
public void subQuery() throws Exception{
    QMember memberSub = new QMember("memberSub");

    List<Member> result = queryFactory
        .selectFrom(member)
        .where(member.age.eq(
            JPAExpressions
                .select(memberSub.age.max())
                .from(memberSub)
        ))
        .fetch();

    asserThat(result).extraction("age")
        .containsExactly(40);
}

//서브 쿼리 goe 사용
@Test
public void subQueryGoe() throws Exception{
    QMember membersub = new QMember("memberSub");

    List<Member> result = queryFactory
        .selectFrom(member)
        .where(member.age.goe(
            JPAEXpressions
                .select(memberSub.age.avg())
                .from(memberSub)
        ))
        .fetch();
    assertThat(result).extraction("age")
        .containsExactly(30,40);
}

//서브쿼리 In 사용
@Test
public void subQueryIn() throws Exception{
    QMember memberSub = new QMember("memberSub");

    List<Member> reuslt = queryFactory
        .selectFrom(member)
        .where(member.age.in(
            JPAExpressions
                .select(memberSub.age)
                .from(memberSub)
                .where(memberSub.age.gt(10))
        ))
        .fetch();
    assertThat(result).extraction("age")
        .containsExactly(20,30,40);
}

//select 절에 subquery
List<Tuple> fetch = queryFactory.select(member.username, 
    JPAExpressions
        .select(memberSub.age.avg())
        .from(memberSub)
        ).from(member)
        .fetch();
for(Tuple tuple : fetch){
    System.out.println("username = " + tuple.get(member.username));
    Systme.out.println("age =" + 
    tuple.get(JPAExpressions.select(memberSub.age.avg())
        .from(memberSub)));   
}

//static import 활용
List<Member> result = queryFactory
    .selectFrom(member)
    .where(member.age.eq{
        select(memberSub.age.max())
            .from(memberSub)
    })
    .fetch();


CASE 문

//단순한 조건
List<String> result = queryFactory
    .select(memberr.age
        .when(10).then("열살")
        .when(20).then
        ("스무살")
        .otherwise("기타"))
    .from(member)
    .fetch();

//복잡한 조건
List<String> result = queryFactory
    .select(new CaseBuilder()
        .when(member.age.between(0,20)).then("0~2살0")
        .when(member.age.between(21,30)).then("21~30살")
        .otherwise("기타"))
    .from(member)
    .fetch();


//orderBy에서 Case 문 함께 사용하기
1. 0 ~ 30살이 아닌 회원을 가장 먼저 출력
2. 0 ~ 20살 회원 출력
3. 21 ~ 30살 회원 출력

NumberExpression<Integer> rankPath = new CaseBuilder()
    .when(member.age.between(0,20).then(2))
    .when(member.age.between(21,30)).then(1)
    .otherwise(3);

List<Tuple> result = queryFactory
    .select(member.username,member.age, rankPath)
        .from(member)
        .orderBy(rankPath.desc())
        .fetch()
        
    for (Tuple tuple : result) {
        String username = tuple.get(member.username);
        Integer age = tuple.get(member.age);
        Integer rank = tuple.get(rankPath);
        System.out.println
        ("username = " + username + " age = " + age + 
        "rank = "+ rank);
    }


상수, 문자 더하기

Tuple result = queryFactory
    .select(member.username, Expressions.constant("A"))
    .from(member)
    .fetchFirst();

//문자 더하기
String result = queryFactory
    .select(member.username.concat("_").concat(member.age.stringValue()))
    .from(member)
    .where(member.username.eq("member1"))
    .fetchOne();

카테고리:

업데이트:

댓글남기기

참고