[JPA] 객체 지향 쿼리 언어 JPQL 4(기본 문법 - 조인, 서브쿼리, 조건식 , 기본 함수)

조인

• 내부 조인:
  select m from Member m [inner] join m.team t
• 외부 조인:
  select m from Member m left [outer] join m.team t
• 세타 조인:
  select count(m) from Member m, Team t where m.username = t.name

[]는 생략이 가능하다.

 

조인 - ON 절

• 조인 대상 필터링이 가능하다.
  • 예) 회원과 팀을 조인하면서, 팀 이름이 A인 팀만 조인
    JPQL: select m, t from Member m left join m.team t on t.name = 'A'
    
    SQL: select m.*,t.* from Member M left join Team t on m.team_id = t.id and t.name = 'A'
• 연관관계 없는 엔티티 외부 조인도 가능하다.
  
  
  • 예) 회원의 이름과 팀의 이름이 같은 대상 외부 조인
    JPQL:
    select m, t from
    Member m left join team t on m.username = t.name
    
    SQL:
    select m.*,t.*from
    member m left join team t on m.username = t.name

서브 쿼리

• 나이가 평균보다 많은 회원
  select m from Member m
  where m.age > (select avg(m2.age) from Member m2)
• 한 건이라도 주문한 고객
  select m from Member m
  where (select count(o) from Order o where m = o.member) > 0

서브 쿼리 지원 함수

• [NOT] exists (subquery): 서브쿼리에 결과가 존재하면 참
  • {all | any | some} (subquery)
    • all: 조건이 모두 만족하면 참
    • any, some: 같은 의미, 조건을 하나라도 만족하면 참
  • [NOT] in (subquery): 서브쿼리의 결과 중 하나라도 같은 것이 있으면 참

예시:

팀 A 소속인 회원

select m from Member m

where exsits (select t from m.team t where t.name = '팀A')

 

전체 상품 각각의 재고보다 주문량이 많은 주문들

select o from Order o

where o.orderAmount > ALL(select p.stockAmount from Product p)

* ALL 연산자는 서브쿼리에서 반환된 모든 조건의 만족해야 한다.

즉 orderAmount 가 모든 stockAmount 보다 커야함을 의미한다.

 

어떤 팀이든 팀에 소속된 회원

select m from Member m

where m.team = ANY(select t from Team t)

JPA 서브 쿼리 한계

• jpa는 where, having 절에서만 서브 쿼리 사용이 가능하다.
• select 절도 가능하다 (하이버네이트에서 지원)
• from 절의 서브 쿼리는 현재 jpql에서 불가능하다
  • 조인으로 풀 수 있으면 풀어서 해결하자 

JPQL 타입 표현

• 문자: 'hello'
• 숫자:  10L(long), 10D(double), 10F(float)
• boolean: TRUE,FALSE
• enum: jpabook.MemberType.Admin (패키지명 포함)
  • 보통 패키지명을 넣지 않기 위해 파라미터로 만들어서 파라미터 바인딩을 한다.
• 엔티티 타입: TYPE(m) = Member (상속 관계에서 사용, 상속 관계에서 자식 클래스의 타입을 지정하기 위함)

조건식 - CASE 식

• COALESCE: 하나씩 조회해서 null이 아니면 반환
• NULLIF: 두 값이 같으면 null 반환, 다르면 첫번째 값 반환

예시) 사용자 이름이 없으면 이름 없는 회원을 반환

select coalesce(m.username,'이름 없는 회원') from Member m

 

사용자 이름이 '관리자'면 null을 반환하고 나머지는 본인의 이름을 반환

select nullif(m.username, '관리자') from Member m

 

JPQL 기본 함수

• CONCAT (문자열 더하기)
• SUBSTRING
• TRIM
• LOWER,UPPER (소문자, 대문자 바꾸기)
• LENGTH
• LOCATE
  • 예시, locate('de', 'abcde') 를 실행하면 4가 나온다. 위치를 반환
• ABS,SQRT,MOD
• SIZE, INDEX(JPA 용도)
  • size는 컬렉션의 사이즈를 반환한다.

위의 함수들은 jpql에서 제공하는 표준 함수기에 데이터베이스에 관계없이 그냥 쓰면 된다.

 

만약 위의 함수들로 해결이 안 되면 사용자 정의 함수를 만들어 사용할 수 있다.

 

사용자 정의 함수

하이버네이트는 사용 전 방언에 추가해야 한다.

사용하는 DB 방언을 상속 받고, 사용자 정의 함수를 등록한다.

 

함수 호출 방식: select function('group_concat', i.name) from Item i

 

표준 함수를 제외한 데이터베이스의 종속적인 함수들은 이미 다 등록이 되어있기에 그냥 사용할 수 있다.

 

등록되어 있지 않은 함수들은 우리가 따로 등록해야 한다.

 

사용자 정의 함수 등록 방법

클래스를 하나 만들고 해당 데이터 베이스 방언을 상속 받아야 한다.

 

함수는 우선적으로 데이터베이스에 미리 등록해둬야 한다.

public class Myh2dialect extends H2Ddialect {
	public Myh2dialect() {
    	registerFunction("group_concat", new StandardSQLFuncrion("group_concat", StandardBasicTypes.STRING));
    }
}

 

이후 yml 파일에서 dialect를  Myh2dialect로 바꿔주어야 한다.