[JPA] groupping과 subquery

JPQL(Groupping, sort, paging 처리, subquery)

JPQL로 Group by, Having

SQL과 마찬가지로 JPQL에서도 group by, having 을 사용할 수 있다

1. group by

jpql 쿼리 문에 group by 조건을 넣어 준다

쿼리문

String jpql = "SELECT d.name, MAX(e.salary), MIN(e.salary), "+
                    " SUM(e.salary), COUNt(e.salary), AVG(e.salary) "+
                    " FROM  EmployeeJoin e JOIN e.dept d " +
                    " GROUP BY d.name ";

실행 결과

Hibernate: 
    select
        department1_.name as col_0_0_,
        max(employeejo0_.salary) as col_1_0_,
        min(employeejo0_.salary) as col_2_0_,
        sum(employeejo0_.salary) as col_3_0_,
        count(employeejo0_.salary) as col_4_0_,
        avg(employeejo0_.salary) as col_5_0_ 
    from
        S_EMP employeejo0_ 
    inner join
        S_DEPT department1_ 
            on employeejo0_.DEPT_ID=department1_.DEPT_ID 
    group by
        department1_.name
부서 별 급여 정보
개발부 : MAX(38100.0), MIN(12700.0), SUM(76200.0), COUNT(3), AVG(25400.0), 
영업부 : MAX(81900.0), MIN(27300.0), SUM(163800.0), COUNT(3), AVG(54600.0),

1. having 절 사용

쿼리문

String jpql = "SELECT d.name, MAX(e.salary), MIN(e.salary), "+
                    " SUM(e.salary), COUNt(e.salary), AVG(e.salary) "+
                    " FROM  EmployeeJoin e JOIN e.dept d " +
                    " GROUP BY d.name " +
                    "HAVING AVG(e.salary) >= 30000.0";

실행결과

Hibernate: 
    select
        department1_.name as col_0_0_,
        max(employeejo0_.salary) as col_1_0_,
        min(employeejo0_.salary) as col_2_0_,
        sum(employeejo0_.salary) as col_3_0_,
        count(employeejo0_.salary) as col_4_0_,
        avg(employeejo0_.salary) as col_5_0_ 
    from
        S_EMP employeejo0_ 
    inner join
        S_DEPT department1_ 
            on employeejo0_.DEPT_ID=department1_.DEPT_ID 
    group by
        department1_.name 
    having
        avg(employeejo0_.salary)>=30000.0
부서 별 급여 정보
영업부 : MAX(81900.0), MIN(27300.0), SUM(163800.0), COUNT(3), AVG(54600.0)

Sorting

SQL과 마찬가지로 결과 값을 정렬해주는 과정

쿼리문

String jpql = "SELECT e, e.dept FROM  EmployeeJoin e "
                    +"ORDER BY e.dept.name DESC, e.salary ASC"

실행 결과

Hibernate: 
    select
        employeejo0_.id as id1_1_0_,
        department1_.DEPT_ID as dept_id1_0_1_,
        employeejo0_.COMMISSION_PCT as commissi2_1_0_,
        employeejo0_.DEPT_ID as dept_id9_1_0_,
        employeejo0_.DEPT_NAME as dept_nam3_1_0_,
        employeejo0_.MAIL_ID as mail_id4_1_0_,
        employeejo0_.name as name5_1_0_,
        employeejo0_.salary as salary6_1_0_,
        employeejo0_.START_DATE as start_da7_1_0_,
        employeejo0_.title as title8_1_0_,
        department1_.name as name2_0_1_ 
    from
        S_EMP employeejo0_ 
    inner join
        S_DEPT department1_ 
            on employeejo0_.DEPT_ID=department1_.DEPT_ID 
    order by
        department1_.name DESC,
        employeejo0_.salary ASC
검색된 직원 목록
영업부에 소속된 영업직원 1의 급여 27300.0
영업부에 소속된 영업직원 2의 급여 54600.0
영업부에 소속된 영업직원 3의 급여 81900.0
개발부에 소속된 개발직원 1의 급여 12700.0
개발부에 소속된 개발직원 2의 급여 25400.0
개발부에 소속된 개발직원 3의 급여 38100.0

Paging 처리

검색 결과의 데이터를 모두 하나의 페이지에 전송하는 것이 아니라 여러 페이지에 나누어 페이지별로 전달하여 클라이언트에게 보이게 하는 것

JPA에서는 이러한 페이징 처리를 위해 개발자가 범위를 나누어서 쿼리문을 만들게 하지 않고 영속 컨테이너가 복잡한 페이징 처리를 담당하게 한다 이렇게 되면 개발자는 페이징 처리를 제외한 기본 쿼리 작성만 하면 되기에 편리하다

검색을 위해 Query, TypedQuery를 사용해 검색 결과를 받아온 후 setFirstRresult(), setMaxResults()를 사용하여 페이징 처리르 한다

setFirstRresult() : 가져올 데이터의 인덱스 첫 시작점(DB에서는 인덱스가 1부터이지만 여기서는 0부터 시작한다)

setMaxResults() : 몇 개의 데이터를 가져올 것인지 개수를 정한다

실행 코드

String jpql = "SELECT e, e.dept FROM  EmployeeJoin e "
                    +"ORDER BY e.id";
        TypedQuery<Object[]> query = em.createQuery(jpql, Object[].class);
        int pageNumber = 2;
        int pageSize = 5;
        int startNum = (pageNumber * pageSize) - pageSize;
        query.setFirstResult(startNum);
        query.setMaxResults(pageSize);

        List<Object[]> resultList = query.getResultList();

        System.out.println(pageNumber + "페이지에 해당하는 직원 목록");
        for (Object[] result : resultList) {
            EmployeeJoin employee = (EmployeeJoin) result[0];
            Department department = (Department) result[1];
            System.out.println(employee.getId() + ":" + department.getName() + "에 소속된 " +
                                employee.getName());
        }        

결과

Hibernate: 
    select
        employeejo0_.id as id1_1_0_,
        department1_.DEPT_ID as dept_id1_0_1_,
        employeejo0_.COMMISSION_PCT as commissi2_1_0_,
        employeejo0_.DEPT_ID as dept_id9_1_0_,
        employeejo0_.DEPT_NAME as dept_nam3_1_0_,
        employeejo0_.MAIL_ID as mail_id4_1_0_,
        employeejo0_.name as name5_1_0_,
        employeejo0_.salary as salary6_1_0_,
        employeejo0_.START_DATE as start_da7_1_0_,
        employeejo0_.title as title8_1_0_,
        department1_.name as name2_0_1_ 
    from
        S_EMP employeejo0_ 
    inner join
        S_DEPT department1_ 
            on employeejo0_.DEPT_ID=department1_.DEPT_ID 
    order by
        employeejo0_.id limit ? offset ?
2페이지에 해당하는 직원 목록
6:영업부에 소속된 영업직원 3

자동적으로 limit, offset을 사용하여 쿼리를 생성한 것을 볼 수 있다

이것은 H2를 사용하여 문법이 이렇게 되는것이고 만약 다른 데이터베이스 시스템을 사용한다면 다른 문법을 사용하여 자동적으로 쿼리를 생성해 줄 것이다

SubQuery

SQL과 마찬가지로 추가적인 조건을 실행할 때 사용하는 쿼리이다

실행 코드

String jpql = "SELECT e FROM  EmployeeJoin e " 
                 + "WHERE NOT EXISTS (SELECT d " 
                 + "                    FROM Department d " 
                 + "                    WHERE d = e.dept)";
        TypedQuery<EmployeeJoin> query = em.createQuery(jpql, EmployeeJoin.class);

        List<EmployeeJoin> resultList = query.getResultList();
        System.out.println("부서에 속해있지 않는 직원 명단");
        for (EmployeeJoin employee : resultList) {
            System.out.println(employee.getName());
        }        

결과

Hibernate: 
    select
        employeejo0_.id as id1_1_,
        employeejo0_.COMMISSION_PCT as commissi2_1_,
        employeejo0_.DEPT_ID as dept_id9_1_,
        employeejo0_.DEPT_NAME as dept_nam3_1_,
        employeejo0_.MAIL_ID as mail_id4_1_,
        employeejo0_.name as name5_1_,
        employeejo0_.salary as salary6_1_,
        employeejo0_.START_DATE as start_da7_1_,
        employeejo0_.title as title8_1_ 
    from
        S_EMP employeejo0_ 
    where
        not (exists (select
            department1_.DEPT_ID 
        from
            S_DEPT department1_ 
        where
            department1_.DEPT_ID=employeejo0_.DEPT_ID))
부서에 속해있지 않는 직원 명단
아르바이트
영업부