10. 객체지향 쿼리 언어1 - 기본 문법

@Autowired
EntityManager em;

@Autowired
MemberRepository repository;

@BeforeEach
void setUp() {
    repository.save(new Member("득윤", 26));
}

• 나이가 18살 이상인 회원을 모두 검색

@Test
void 나이가_18살이넘는_회원_쿼리() {
    String jpql = "select m from Member m where m.age > 18";
    
		List<Member> resultList = em.createQuery(jpql, Member.class)
            .getResultList();

		assertThat(resultList).hasSize(1);
		assertThat(resultList.get(0).getUsername()).isEqualTo("득윤");
		assertThat(resultList.get(0).getAge()).isGreaterThan(18);
}

select
    m.id
    m.age
    m.USERNAME
    m.TEAM_ID
from Member m
where m.age>18

JPQL 문법

• select m from Member as m where m.age > 18

• 엔티티와 속성은 대소문자를 구분 O (Member, age)

• JPQL 키워드는 대소문자 구분 X (SELECT, FROM, where)

• 테이블 이름이 아닌 엔티티 이름을 from 절에 넣음

• 별칭은 필수 (as 는 생략가능)

select 절에 사용할 수 있는 키워드

• count

@Test
void 회원_카운트_조회() {
    String jpql = "select count(m) from Member m";

    long size = em.createQuery(jpql, Long.class).getSingleResult();

    assertThat(size).isEqualTo(1);
}

• sum, avg, max, min

@Test
void 회원_나이의_합() {
    String jpql = "select sum(m.age) from Member m";

    Long ageSum = em.createQuery(jpql, Long.class).getSingleResult();

    assertThat(ageSum).isEqualTo(26L);
}

집합과 정렬

@BeforeEach
void setUp() {
    repository.save(new Member("득윤4", 44));
    repository.save(new Member("득윤1", 11));
    repository.save(new Member("득윤2", 33));
    repository.save(new Member("득윤3", 22));
}

• ORDER BY

@Test
void 회원_이름순_정렬() {
    String jpql = "select m from Member m order by m.username";

    List<Member> resultList = em.createQuery(jpql, Member.class)
																.getResultList();

		assertThat(resultList).extracting("username").isSorted();
}

Hibernate: select member_id age username from member order by username

• GROUP BY, HAVING

TypeQuery, Query

• TypeQuery - 반환 타입이 명확 한 경우 사용

TypedQuery<Member> query =
em.createQuery("SELECT m FROM Member m", Member.class);

• Query - 반환 타입이 명확하지 않을 때 사용

Query query =
em.createQuery("SELECT m.username, m.age from Member m");

결과 조회

• query.getResultList( ) : 결과가 하나 이상일때, 리스트 반환
• 결과가 없으면 빈 리스트

• query.getSingleResult( ) : 결과가 정확히 하나일때, 단일 객체 반환
• 결과가 없으면 NoResultException
• 결과가 둘 이상이면 NonUniqueResultException
 

파라미터 바인딩 - 이름 기준을 사용하자

@Test
void 회원_나이가_n_이상으로_조회하는_jpql_바인딩(){
    String jpql = "select m from Member m where m.age>= :age";

    List<Member> resultList = em.createQuery(jpql, Member.class)
            .setParameter("age", 33)
            .getResultList();

		assertThat(resultList).hasSize(2);
		assertThat(resultList).extracting(Member::getAge)
            .allSatisfy(a ->assertThat(a).isGreaterThanOrEqualTo(33));
}

프로젝션

• Select 절에 조회할 대상을 지정하는 것

• 엔티티 프로젝션

select m from Member m

• 엔티티 프로젝션

select m.team from Member m

select t from Member m join m.team t;

 

• 임베디드 타입 프로젝션

select m.address from Member m

• 스칼라 타입 프로젝션

select m.username, m.age from Member m

스칼라 타입을 전달 받는 방법
1. 쿼리

@Test
void SELECT_프로젝션_아이디와_나이만_1_쿼리(){
    String jpql = "select m.id, m.age from Member m";

    List resultList = em.createQuery(jpql).getResultList();

    for (Object o : resultList) {
        Object[] result = (Object[]) o;
        System.out.println("id = " + result[0] + " age = " + result[1]);
    }
}

Hibernate: select member_id, age from member 
id = 1 age = 44
id = 2 age = 11
id = 3 age = 33
id = 4 age = 22

1. DTO

package com.study.jpql.domain;


import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.Setter;

@AllArgsConstructor
@Getter
@Setter
public class MemberDto{
    private Long id;
    private int age;
}

@Test
void SELECT_프로젝션_아이디와_나이만_2_dto(){
    String jpql = "select new com.study.jpql.domain.MemberDto(m.id, m.age) from Member m";

    List<MemberDto> resultList = em.createQuery(jpql, MemberDto.class).getResultList();

    for (MemberDto memberDto : resultList) {
        System.out.println("id = " + memberDto.getId() + " age = " + memberDto.getAge());
    }
}

Hibernate: select member_id, age from member 
id = 1 age = 44
id = 2 age = 11
id = 3 age = 33
id = 4 age = 22

• 패키지 명을 포함한 전체 클래스명 입력해야함
• 순서가 일치하는 생성자 필요

페이징 API

• setFirstResult/ setMaxResults

@Test
void 나이로_정렬해서_첫_n개만_조회(){
    String jpql = "select m from Member m order by m.age";

    List<Member> resultList = em.createQuery(jpql, Member.class)
            .setMaxResults(3)
            .getResultList();

assertThat(resultList.get(0).getAge()).isEqualTo(11);
assertThat(resultList.get(1).getAge()).isEqualTo(22);
assertThat(resultList.get(2).getAge()).isEqualTo(33);
}

Hibernate: 
select member_id, age, username
from member  
order by age 
limit ?