객체지향 쿼리 언어 - 기본 문법, 조인, 서브쿼리, JPQL 타입 표현식과 기타식, 조건식(CASE 등등), JPQL 함수

출처 : 인프런 - 자바 ORM 표준 JPA 프로그래밍 - 기본편(유료) / 김영한님  
유료 강의이므로 정리에 초점을 두고 코드는 일부만 인용

---

1. 조인

• join쿼리를 실습 하기 위해 Member와 Team을 양방향 연관관계 매핑을 하고, 연관관계 편의 메서드를 작성하여 값을 생성
• 중복 쿼리 발생 방지를 위해 @ManyToOne 맵핑에 LAZY를 적용 (관련글)

1) 내부조인

• inner 생략 가능

// 이너조인 (inner 생략 가능)
String query = "select m from Member m inner join m.team t";
List<Member> result= em.createQuery(query, Member.class)
        .getResultList();

이너 조인 쿼리 로그

2) 외부 조인

• left, right 조인을 지원하고, outer는 생략가능

// 외부조인 - left, right (outer 생략 가능)
String query2 = "select m from Member m left outer join m.team t";
List<Member> result2 = em.createQuery(query2, Member.class)
        .getResultList();

tx.commit();

외부 조인 쿼리 로그

3) 세타 조인

• 강의에서는 쿼리 결과가 교차곱(cross join)으로 출력되지만 하이버네이트가 업데이트된 이후로는 내부적으로 쿼리가 최적화가 적용되어 where문으로 출력됨

// 세타조인 - 비교조건으로 조인
String query3 = "select m from Member m, Team t where m.username = t.name";
List<Member> result3 = em.createQuery(query3, Member.class)
        .getResultList();

 

where문으로 세타조인이 변경된 모습

4) ON 절

• JPA 2.1 이후부터 ON 절을 활용한 외부 조인이 가능함(과거에는 내부 조인만 되었음)

(1) 조인 대상 필터링

• 회원과 팀을 조인하면서 팀 이름이 A인 팀만 조인

// ON절 활용1
String onQuery1 = "select m from Member m left join m.team t on t.name = :name";
List<Member> result4 = em.createQuery(onQuery1, Member.class)
        .setParameter("name", "A")
        .getResultList();

 

(2) 연관관계 없는 엔터티와의 외부 조인

• 회원의 이름과 팀의 이름이 같은 대상으로 외부 조인이 가능함(내부조인도 당연히 가능함)
• 실습시 연관관계 설정으로 작성된 코드들을 전부 제거 후 테스트를 진행해보면 전혀 관련이 없는 엔터티임에도 join쿼리를 실행할 수 있음

// ON절 활용2
String onQuery2 = "select m from Member m left join Team t on m.username = t.name";
List<Member> result5 = em.createQuery(onQuery2, Member.class)
        .getResultList();

System.out.println("result5.size() = " + result5.size());

좌) 조인 대상을 필터링한 로그 / 우) 연관관계가 없는 엔터티를 join 쿼리한 로그

---

2. 서브쿼리

1) 서브쿼리 구조

• JPQL에서도 SQL에서 지원하는 서브쿼리와 동일한 구조로 사용함
• SQL에서는 서브쿼리 작성 시 서브쿼리와 메인쿼리가 관련이 없도록 정의를 해야 쿼리 성능이 더 잘나오는데 JPQL은 SQL로 번역이 되어 실행되니 이를 참고하여 쿼리를 작성하는 것을 권장함

# 나이가 평균보다 많은 회원 - 메인쿼리의 m과 서브쿼리의 m2를 별도로 정의해서 사용, 성능이 더 좋음
select m from Member m
where m.age > (select avg(m2.age) from Member m2)

# 한 건이라도 주문한 고객 - 메인쿼리의 m을 서브쿼리에서도 사용, 성능이 더 저하됨
select m from Member m
where (select count(o) from Order o where m = o.member) > 0

2) 서브 쿼리 지원 함수

(1) [NOT] EXISTS/ALL/ANY/SOME (subquery)

• 서브쿼리에 결과가 존재하면 참
• ALL은 모두 만족하면 참
• ANY, SOME은 같은 의미로 조건을 하나라도 만족하면 참

(2) [NOT] IN (subquery)

• 서브쿼리의 결과 중 하나라도 같은 것이 있으면 참

3) 서브 쿼리 예제

(1) 팀A 소속인 회원 조회

String subQuery1 = "select m from Member m where exists (select t from m.team t where t.name = 'A')";
List<Member> subResult1 = em.createQuery(subQuery1, Member.class)
        .getResultList();

 

(2) 전체 상품 각각의 재고보다 주문량이 많은 주문들 조회

String subQuery2 = "select o from Order o where o.orderAmount > ALL (" +
                    "select p.stockAmount from Product p)";

List<Member> subResult2 = em.createQuery(subQuery2, Member.class)
        .getResultList();

 

(3) 어떤 팀이든 팀에 소속된 회원

String subQuery3 = "select m from Member m where m.team = ANY (select t from Team t)";
List<Member> subResult3 = em.createQuery(subQuery3, Member.class)
        .getResultList();

4) JPA 서브쿼리 한계

• JPA표준은 WHERE, HAVING 절에서만 서브 쿼리가 사용 가능하지만 대부분 하이버네이트의 구현체로 하이버네이트를 사용하기 때문에 SELECT 절에서도 서브쿼리를 사용할 수 있음
• FROM절의 서브쿼리가 불가능했지만 하이버네이트 6.1부터 FROM절의 서브쿼리를 지원함(공식 문서 url)

---

3. JPQL 타입 표현과 기타식

1) 타입 표현

(1) 문자

• 'HELLO', 'She''s'(She's 를 표현)

(2) 숫자

• 10L - Long, 10D - Double, 10F - Float

(3) Boolean

• TRUE, FALSE

(4) ENUM

• jpabook.MemberType.ADMIN 처럼 자바 패키지명을 모두 입력해야함

(5) 엔터티 타입 (TYPE(i) = Book)

• 상속관계의 엔터티를 형변환 하여 쿼리할 때 사용하며 자주 사용하진 않음
• Item의 자손인 Book엔터티가 있을 때 ~ from Item i where type(i) = Book으로 Dtype이 Book으로 설정되어 쿼리가 나감

(1) 예제

• Enum을 정의한 후 Member가 Enum을 값으로 가지고 있다고 가정 후 Member를 생성하여 실습
• Enum을 필드로 정의할때는 항상 @Enumerated(EnumType.STRING)으로 적용하는 것에 주의
• 보통은 패키지명을 직접 jpql에 입력하지않고 파라미터 바인딩을 사용하기 때문에 큰 불편함은 없음

String typeQuery1 = "select m.username, 'She''s', true, m.type from Member m " +
                    "where m.type = hellojpa.jpql.MemberType.ADMIN";
List<Object[]> typeResult1 = em.createQuery(typeQuery1).getResultList();

for (Object[] o : typeResult1) {
    System.out.println("name = " + o[0]);
    System.out.println("She's = " + o[1]);
    System.out.println("True = " + o[2]);
    System.out.println("type = " + o[3]);
}

/*
출력 결과
name = 사람
She's = She's
True = true
type = ADMIN
*/

// 파라미터 바인딩 적용
String typeQuery1 = "select m.username, 'She''s', true, m.type from Member m " +
                    "where m.type = :userType";

List<Object[]> typeResult1 = em.createQuery(typeQuery1)
        .setParameter("userType", MemberType.ADMIN)
        .getResultList();

2) 기타 식

• ANSI 표준 SQL과 문법이 같은 식은 전부 지원함
• EXISTS, IN, AND, OR, NOT, =, >, >=, <, <=, <>, BETWEEN, LIKE, IS NULL, IS NOT NULL 등등

---

4.  조건식(CASE 등등)

1) 조건식 종류

(1) CASE

• when절에 조건을 입력하여 then절의 값을 반환하는 기본 CASE식과 주어진 변수의 값이 when절일 때 then절의 값을 입력하는 단순 CASE식이 있음

// 기본 CASE 식
String caseQuery1 =
        "select " +
            "case when m.age <= 10 then '학생요금' " +
            "     when m.age >= 60 then '경로요금' " +
            "     else '일반요금' " +
            "end " +
        "from Member m";

List<String> caseResult1 = em.createQuery(caseQuery1, String.class)
        .getResultList();

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

// 단순 CASE 식
String caseQuery2 =
        "select " +
            "case t.name" +
            "     when '팀A' then '인센티브200%' " +
            "     when '팀B' then '감봉20%' " +
            "     else '인센티브 없음' " +
            "end " +
        "from Team t";

List<String> caseResult2 = em.createQuery(caseQuery2, String.class)
        .getResultList();

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

 

(2) COALESCE

• 하나씩 조회하여 값이 null이면 기본값을 반환하고 null이 아니면 저장된 값을 반환
• Member에 회원이 없으면 m.username에 이름 없는 회원을 반환

String caseQuery3 = "select coalesce(m.username, '이름 없는 회원') from Member m";

List<String> caseResult3 = em.createQuery(caseQuery3, String.class)
        .getResultList();

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

 

(3) NULLIF

• 두 값이 같으면 null을 반환하고 다르면 값을 반환
• m.username이 관리자이면 null을 반환하고 그외에는 username을 반환

String caseQuery4 = "select nullif(m.username, '관리자') from Member m";

List<String> caseResult4 = em.createQuery(caseQuery4, String.class)
        .getResultList();

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

---

5. JPQL 기본함수

1) 표준 기본 함수

• 모든 DB에서 공통적으로 사용할 수 있는 함수로 DB의 종류에 상관없이 사용 가능함

(1) CONCAT

• concat('a', 'b')
• 두개의 문자를 더함

(2) SUBSTRING

• substring(m.username, 2, 3)
• 문자를 잘라서 반환, username의 값을 2번째부터 3개까지 잘라서 반환

(3) TRIM

• 공백을 제거하는 기능으로 ltrim, rtrim도 사용 가능

(4) LOWER, UPPER

• LOWER -> 소문자로 변경
• UPPER -> 대문자로 변경

(5) LENGTH

• 문자의 길이를 반환

(6) LOCATE

• locate('de', 'abcdef')
• 두번째 인수의 문자열중에서 첫번째 인수의 문자열을 찾고 위치를 Integer로 반환

(7) ABS, SQRT, MOD

• ABS -> 절대값
• SQRT -> 제곱근
• MOD -> 나머지연산

(8) SIZE, INDEX(JPA 용도)

• SIZE -> 컬렉션의 길이를 반환
• INDEX -> 일반적으로 사용하진 않고 값 타입에서 @OrderColumn을 사용한 컬렉션의 위치 값을 조회함

2) 사용자 정의 함수

• 직접 함수를 정의하여 사용 할 수 있으며 사용전에 방언에 추가해야함
• 사용하는 DB방언을 상속받고 직접 정의한 함수를 등록하여 사용하면 됨

(1) MyH2Dialect

• 사용할 DB를 상속받아서 클래스를 정의하고, 하이버네이트 버전에 따라 함수를 생성
• 정의할 함수의 기능은 상속받을 DB클래스 내부에 들어가면 선택해서 작성하거나, 직접 기능을 정의할 수 있음

package hellojpa.jpql;

// 사용할 데이터베이스를 상속받아 class를 생성
public class MyH2Dialect extends H2Dialect {

//    // 하이버네이트 5.x버전에서 사용자 정의 함수 등록 방법
//    public MyH2Dialect() {
//        functionFactory("group_concat", new StandardSQLFunction("group_concat", StandardBasicTypes.STRING));
//    }

    // 하이버네이트 6.x버전에서 사용자 정의 함수 등록 방법
    @Override
    public void contributeFunctions(FunctionContributions functionContributions) {
        super.contributeFunctions(functionContributions);

        functionContributions.getFunctionRegistry().register(
                "group_concat", // 함수이름
                new StandardSQLFunction("group_concat", StandardBasicTypes.STRING) // (실제 SQL 함수이름, 반환타입)
        );
    }

}

 

(2) persistence.xml 수정

• JPA 설정의 Dialect를 사용자 정의 함수를 생성한 클래스를 패키지명을 포함하여 입력
• 스프링부트 3.x 이상 버전을 사용하여 프로젝트를 생성했다면 알아서 적용되기 때문에 설정을 바꿀 필요 없음

<!--  <property name="hibernate.dialect" value="org.hibernate.dialect.H2Dialect"/>-->
    <property name="hibernate.dialect" value="hellojpa.jpql.MyH2Dialect"/>

 

(3) 사용방법

• 대부분 JPA를 하이버네이트로 구현하기 때문에 SQL에서 함수를 사용하는 방법과 같이 직접 정의한 함수를 '정의한함수명(인수)'로 사용하면 됨

// function('group_concat', m.username)가 기본 사용법
// 아래는 하이버네이트 구현시에만 사용가능(대부분 구현체가 하이버네이트이기 때문에 상관없음)
String functionQuery = "select group_concat(m.username) from Member m";

List<String> result = em.createQuery(functionQuery, String.class)
        .getResultList();

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

// 출력결과 - 멤버의 이름을 한줄로 출력되도록 만든 사용자 정의 함수
// s = 관리자,다른사람

 

3) 각 DB에 종속적인 함수

• 이미 하이버네이트는 각 DB에 종속적인 함수들도 JPQL에서 지원하도록 제공하기 때문에 각 DB의 함수를 사용할 수 있음
• DB에 종속적이므로 DB가 변경되면 쿼리도 변경해야 한다는 점만 주의하면 됨