[JPA] 2/24 공부내용 기록(조인,on 절, 서브쿼리)

조인 

1. 종류

1) 내부조인: select m from Member m JOIN m.team t (조인 값 중 하나가 Null이면 결과테이블에 포함하지 않는거)

2)외부조인: select m from Member m LEFT JOIN m.team t (조인 값 중 하나가 Null이어도 결과테이블에 포함)

3)세타조인: select count(m) from Member m, Team t where m.username=t.name

 

2. ON 절

1) 조인 대상을 필터링할 수 있다

예시: 회원과 팀을 조인하면서, 팀 이름이 A인 팀만 조인한다(팀이름이 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'

 

2) 연관관계 없는 엔티티를 외부 조인한다(하이버네이트 5.1부터)

예시: 회원의 이름과 팀의 이름이 같은 대상 외부조인한다(회원이름=팀이름)

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  

 

3. 서브 쿼리 

예시

나이가 평균보다 많은 회원 

=> 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

 

- 서브쿼리 지원함수 

1) [NOT] EXISTS(subquery): 서브 쿼리에 결과가 존재하면 참을 반환

2) {ALL} (subquery) : 모두 만족 하면 참 

3) {ANY/SOME} (subquery): 같은 의미로, 조건을 하나라도 만족하면 참을 반환한다

4) [NOT] IN (subquery): 서브쿼리의 결과 중 하나라도 같은 것이 있으면 참을 반환한다

 

-조인예제

1) 팀A 소속인 회원

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

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

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

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

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

 

-서브 쿼리의 한계

JPQ는 SELECT(하이버네이트에서 지원),WHERE,HAVING 절에서만 서브 쿼리를 사용 가능하다

FROM 절의 서브 쿼리는 현재 JQPL에서 불가능하다(조인으로 풀 수 있으면 풀어서 해결한다)

 

4. JPQL 타입 표현

-문자: 'HELLO', 'She''s'

-숫자: 10L(Long), 10D(Double), 10F(Float)

-Boolean: True,False

-ENUM: jpabook.MemberType.Admin(패키지명 포함)

-엔티티 타입: TYPE(m) = Member. 이 식은 상속 관계에서 사용

 

5. 기타

1) SQL과 문법이 같은 식들 : EXISTS/IN, AND/OR/NOT, 부등호, BETWEEN,LIKE,IS NULL

2) 조건식: CASE 식 

 

COALESCE: 하나씩 조회해서 null이 아니면 반환한다(m.username,'이름없는 회원' 중에서 null이 아닌 값을 반환한다. username이 null이면 '이름 없는 회원'은 not null 이므로 이름없는 회원이 반환된다)

 

NULLIF: 두 값이 같으면 null 반환, 다르면 첫번째 값을 반환한다 

 

6. JPQL 기본 함수

concat: 문자 두개 합하는 거 

substring:  문자열 일부 

trim :띄어쓰기 없애는거

lower,upper: 소문자,대문자로 바꾸기

length: 문자열의 길이 

locate(‘a’,’b’): a가 b에서 몇번째에 있는지 위치(Int) 알려줌

abs,sort,mod: 수학 연산

size: 콜렉션의 크기를 알려준다

 

7. 사용자 정의 함수 호출 

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

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

과정: dialect 디렉토리에 Myh2Dialect 만들고 메소드 만든 다음 

-> persistent.xml에 Myh2Dialect로 변경하고 실행하면 된다