자바 ORM 표준 JPA 프로그래밍 - 기본편 수업을 듣고 정리한 내용입니다.

 

📚 6. 서브 쿼리

✔️ 나이가 평균보다 많은 회원

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 

 

📖 A. 서브 쿼리 지원 함수

• [NOT] EXISTS (subquery) : 서브쿼리에 결과가 존재하면 참이다.
  • {ALL | ANY | SOME} (subquery)
  • ALL : 모두 만족하면 참이다.
  • ANY, SOME : 같은 의미, 조건을 하나라도 만족하면 참이다.
• [NOT] IN (subquery) : 서브쿼리의 결과 중 하나라도 같은 것이 있으면 참이다.

 

✔️ 서브 쿼리 - 예제

팀A 소속인 회원

select m from Member m
where exists (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)

 

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

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

 

📖 B. JPA 서브 쿼리 한계

• JPA는 WHERE, HAVING 절에서만 서브 쿼리를 사용할 수 있다.
• 하이버네이트에서는 SELECT절도 가능하다.
• FROM 절의 서브 쿼리는 현재 JPQL에서는 불가능하다!
  ➡️ 조인으로 풀 수 있으면 풀어서 해결하기!

위 내용이 안되면 query를 두 번 날려라
통계성화면과 같이 복잡할 때는 native를 사용한다. (근데 native는 왠만하면 안쓰는게 좋다.)

 

📚 7. JPQL 타입 표현과 기타식

✔️ JPQL 타입 표현

• 문자 : HELLO, She"s
• 숫자 : 10L(Long), 10D(Double), 10F(Float)
• Boolean : TRUE, FALSE
• ENUM : jpabook.MemberType.Admin (패키지명 포함)
• 엔티티 타입 : TYPE(m) = Member (상속 관계에서 사용)

문자, 숫자, boolean 예시

            Team team = new Team();
            team.setName("teamA");
            em.persist(team);

            Member member = new Member();
            member.setUsername("teamA");
            member.setAge(10);
            em.persist(member);



            // 영속성 컨텍스트 비우기
            em.flush();
            em.clear();


            String jpql = "select m.username, 'HELLO', TRUE From Member m";
            List<Object[]> resultList = em.createQuery(jpql).getResultList();

            for (Object[] member1 : resultList) {
                System.out.println("member1[0] = " + member1[0]);
                System.out.println("member1[1] = " + member1[1]);
                System.out.println("member1[2] = " + member1[2]);
            }


            tx.commit();

 

enum 사용 예시

            Team team = new Team();
            team.setName("teamA");
            em.persist(team);

            Member member = new Member();
            member.setUsername("teamA");
            member.setAge(10);
            member.setType(MemberType.ADMIN);   // type이 ADMIN
            em.persist(member);



            // 영속성 컨텍스트 비우기
            em.flush();
            em.clear();


            // jpql.MemberType.ADMIN 타입의 USER만 조회한다.
            String jpql = "select m.username, 'HELLO', TRUE From Member m "
                    + "where m.type = jpql.MemberType.ADMIN";

            List<Object[]> resultList = em.createQuery(jpql).getResultList();

            for (Object[] member1 : resultList) {
                System.out.println("member1[0] = " + member1[0]);
                System.out.println("member1[1] = " + member1[1]);
                System.out.println("member1[2] = " + member1[2]);
            }


            tx.commit();

위는 하드 코딩

조금 더 간단하게 코드 작성

            String jpql = "select m.username, 'HELLO', TRUE From Member m "
                    + "where m.type = :userType";

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

• 지금은 member.setType(MemberType.ADMIN)이므로 select문 조건에 "where m.type = jpql.MemberType.ADMIN" 주면 "select m.username, 'HELLO', TRUE From Member m "가 실행된다.
• 만약, ADMIN이 아니라 USER라면 member[0] ~ meber[2]까지 출력되지 않는다. (조회가 되지 않으니)

 

✔️ JPQL 기타 - SQL과 문법이 같은 식

• EXISTS, IN
• AND, OR, NOT
• =, >, >=, <, <=, <>
• BETWEEN, LIKE, IS NULL

 

📚 8. 조건식 - CASE 등등

✔️ 기본 CASE 식

select
    case  when m.age <= 10 then '학생요금'
          when m.age >= 60 then '경로요금'
          else '일반요금'
    end
from Member m

            Team team = new Team();
            team.setName("teamA");
            em.persist(team);

            Member member = new Member();
            member.setUsername("teamA");
            member.setAge(10);
            member.setType(MemberType.ADMIN);   // type이 ADMIN
            em.persist(member);



            // 영속성 컨텍스트 비우기
            em.flush();
            em.clear();

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

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

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

            tx.commit();

실행 결과

• QueryDSL을 사용시 위보다 쉽게 코드를 작성할 수 있다.

 

✔️ 단순 CASE 식

select
    case t.name
          when '팀A' then '인센티브110%'
          when '팀B' then '인센티브120%'
          else '인센티브105%'
    end
from Team t

 

✔️ COALESCE

하나씩 조회해서 null이 아니면 반환한다.

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

• 사용자 이름이 없으면 이름 없는 회원을 반환한다.

            Team team = new Team();
            team.setName("teamA");
            em.persist(team);

            Member member = new Member();
            member.setUsername(null);
            member.setAge(10);
            member.setType(MemberType.ADMIN);   // type이 ADMIN
            em.persist(member);

            // 영속성 컨텍스트 비우기
            em.flush();
            em.clear();

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

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

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

            tx.commit();

 

실행 결과

 

✔️ NULLIF

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

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

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

            Team team = new Team();
            team.setName("teamA");
            em.persist(team);

            Member member = new Member();
            member.setUsername("관리자");
            member.setAge(10);
            member.setType(MemberType.ADMIN);   // type이 ADMIN
            em.persist(member);

            // 영속성 컨텍스트 비우기
            em.flush();
            em.clear();

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

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

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

            tx.commit();

 

실행 결과

 

📚 9. JPQL 기본 함수

• CONCAT : 문자열 연결
  • Ex) CONCAT(str1, str2, ...) → 'str1str2...' 반환
• SUBSTRING : 문자열의 일부 가져오기
  • SUBSTRING(str, pos [, length]) : 지정된 문자열의 하위 문자열을 반환한다.
  • 이때, Java처럼 0부터 시작하는 것이 아닌, SQL에서처럼 1부터 시작한다는 것을 유의하기!
  • ex) SUBSTRING('Italy', 3) → aly
• TRIM : 선행 및 후행 문자 제거
  • TRIM([[LEADING|TRAILING|BOTH] [char] FROM] str) : 선행 및/또는 후행 문자 (일반적으로 공백 문자)를 제거한 후 문자열을 반환한다.
  • TRIM(' UK ') → 'UK'
• LOWER, UPPER : 문자열 대소문자 변경
  • LOWER(str) : 소문자로 변경
  • UPPER(str) : 대문자로 변경
• LENGTH : 문자열의 문자 수 계산
  • ex) LENGTH('United States') → 13
• LOCATE : 부분 문자열 찾기
  • LOCATE(str, substr [, start]) : 부분 문자열을 검색하고 해당 위치를 반환한다.
  • ex) LOCATE('India', 'a') → 5
• ABS, SQRT, MOD → 숫자 관련 함수
  • ABS : 절댓값 반환
  • SQRT : 제곱근 반환
  • MOD : 나머지 반환
• SIZE, INDEX (JPA 용도)

 

            Member member = new Member();
            member.setUsername("관리자");
            em.persist(member);

            Member member2 = new Member();
            member2.setUsername("관리자2");
            em.persist(member2);

            // 영속성 컨텍스트 비우기
            em.flush();
            em.clear();

            String query = "select concat('a' || 'b') From Member m";

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

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

            tx.commit();

 

실행 결과

 

✔️ 사용자 정의 함수 호출

• 하이버네이트는 사용 전 방언에 추가해야 한다.
  • 사용하는 DB 방언을 상속받고, 사용자 정의 함수를 등록한다.

select function('group_concat', i.name) from Item i