JPA - 객체지향 쿼리 언어 - QueryDSL Join, 서브쿼리

- 참조: 자바 ORM 표준 JPA 프로그래밍

- Join

QueryDSL 에서 Join 은 innerJoin, leftJoin, rightJoin, fullJoin 을 지원한다. 또한 JPQL 의 on 과 fetch 기능도 사용가능하다. join 을 사용할때에는 1번째 파라미터에 조인 대상을, 2번째 파라미터에 alias 를 지정한다.

 

귀찮지만 Join 예제를 실행해보기 위해 Member 엔티티와 Order 엔티티가 맵핑된 데이터를 만들어주자.

 

Member member1 = new Member();

member1.setName("Name#1");
member1.setAge(10);
member1.setAddress(
		new Address("City#1", "Street#1", "Zipcode#1"));
em.persist(member1);

Order order1 = new Order();
order1.setOrderDate(new Date());
order1.setStatus(OrderStatus.ORDER);

em.persist(order1);
order1.setMember(member1);

Member member2 = new Member();

member2.setName("Name#2");
member2.setAge(13);
member2.setAddress(
		new Address("City#1", "Street#2", "Zipcode#2"));
em.persist(member2);

Member member3 = new Member();

member3.setName("Name#3");
member3.setAge(32);
member3.setAddress(
		new Address("City#2", "Street#3", "Zipcode#3"));
em.persist(member3);

Order order3 = new Order();
order3.setOrderDate(new Date());
order3.setStatus(OrderStatus.CANCEL);
order3.setMember(member3);
em.persist(order3);

Member member4 = new Member();

member4.setName("Name#4");
member4.setAge(22);
member4.setAddress(
		new Address("City#2", "Street#4", "Zipcode#4"));
em.persist(member4);

 

Member1~4 중 Member1 과 Member3 에 Order 를 맵핑하였다. Order 엔티티들 중 Member 와 맵핑이 되어 있고, Member 의 나이가 10살 초과인 건들을 구해보자.

 

JPAQueryFactory query = new JPAQueryFactory(em);

QMember m = new QMember("m");
QOrder o = new QOrder("o");

List<Order> orders = query.select(o)
	.from(o)
	.join(o.member, m)
	.on(m.age.gt(10))
	.fetch();
	
orders.forEach(order -> {
	System.out.println("Order status: " + order.getStatus());
	System.out.println("Member name: " + order.getMember().getName());
});

 

우선 Join 을 한다는것은 EntityPath 를 2개 사용한다는 말이 된다. 우리는 Order 와 Member 를 Join 할 것이므로 QMember 와 QOrder 를 얻는다. alias 는 간단하게 앞글자를 따서 m 과 o 로 정했다.

 

QueryDSL 에서 join 을 하고 싶다면 from 뒤에 join 을 사용해주면 된다. 그리고 예제에서 알 수 있듯이 join 절 뒤에 on 절도 제공한다. on 절에는 Member 의 나이가 10살 초과라는 조건을 주었다.

 

Hibernate: 
    /* select
        o 
    from
        
    Order o   inner join
        o.member as m with m.age > ?1 */ select
            order0_.order_id as order_id1_9_,
            order0_.insert_datetime as insert_d2_9_,
            order0_.update_datetime as update_d3_9_,
            order0_.delivery_id as delivery6_9_,
            order0_.member_id as member_i7_9_,
            order0_.order_date as order_da4_9_,
            order0_.status as status5_9_ 
        from
            orders order0_ 
        inner join
            member member1_ 
                on order0_.member_id=member1_.member_id 
                and (
                    member1_.age>?
                )
Order status: CANCEL
Hibernate: 
    select
        member0_.member_id as member_i1_6_0_,
        member0_.insert_datetime as insert_d2_6_0_,
        member0_.update_datetime as update_d3_6_0_,
        member0_.city as city4_6_0_,
        member0_.street as street5_6_0_,
        member0_.zipcode as zipcode6_6_0_,
        member0_.age as age7_6_0_,
        member0_.name as name8_6_0_ 
    from
        member member0_ 
    where
        member0_.member_id=?
Member name: Name#3

 

실행해보면 inner join 과 on 절로 SQL 이 잘 변환된것을 알 수 있다. member 테이블을 한번 더 조회하는 것은 Order 엔티티에서 Member 엔티티로 ManyToOne 맵핑시 fetchType을 Lazy 로 설정하였기 때문에 이름 조회시 뒤늦게 실행되기 때문이다.

 

QueryDSL 은 inner join 뿐만 아니라 outer join 도 지원한다. 회원들의 주문들 중 주문인 상태의 건 수를 조회하는 요구사항이 있다고 가정해보자. 주문이 없더라도 회원정보는 나와야 하기 때문에 회원을 기준으로 주문을 left join 해야 한다.

 

JPAQueryFactory query = new JPAQueryFactory(em);

QMember m = new QMember("m");
QOrder o = new QOrder("o");

List<Member> members = query.select(m)
		.from(m)
		.leftJoin(m.orders, o)
		.on(o.status.eq(OrderStatus.ORDER))
		.fetch();
	
members.forEach(member -> {
	System.out.println("Member name: " + member.getName());
	System.out.println("Order Size: " + member.getOrders().size());
});

 

실행되는 SQL 은 아래와 같다.

 

Hibernate: 
    /* select
        m 
    from
        Member m   
    left join
        m.orders as o with o.status = ?1 */ select
            member0_.member_id as member_i1_6_,
            member0_.insert_datetime as insert_d2_6_,
            member0_.update_datetime as update_d3_6_,
            member0_.city as city4_6_,
            member0_.street as street5_6_,
            member0_.zipcode as zipcode6_6_,
            member0_.age as age7_6_,
            member0_.name as name8_6_ 
        from
            member member0_ 
        left outer join
            orders orders1_ 
                on member0_.member_id=orders1_.member_id 
                and (
                    orders1_.status=?
                )

 

실행해보면 4명 회원의 주문건 수를 구할 때 order 테이블을 지연 조회한다. 한 가지 주의할점이 있는데 member.getOrders() 를 하면 on 절에 기술한 조건인 Order 상태인 주문건들의 size() 가 반영되어 나오지 않고 회원이 주문한 전체 건수가 모두 나온다. 이는 객체 그래프 탐색과 DB 의 일관성을 보장하기 위함인데 이는 다음글에서 설명하도록 한다.

 

fetch join 를 사용해서 주문건을 lazy 조회하지 않고 즉시 로딩할 수 있다. 이번엔 단순히 회원이 주문한 건수를 구해본다.

 

JPAQueryFactory query = new JPAQueryFactory(em);

QMember m = new QMember("m");
QOrder o = new QOrder("o");

List<Member> members = query.select(m)
		.from(m)
		.leftJoin(m.orders, o)
		.fetchJoin()
		.fetch();
	
members.forEach(member -> {
	System.out.println("Member name: " + member.getName());
	System.out.println("Order Size: " + member.getOrders().size());
});

 

위에서 fetchJoin 메소드를 사용하면 대상을 즉시 조회한다. 그래서 생성된 SQL 도 member 만 조회하던 이전 SQL 과 달리 Order 까지 한번에 조회한다. 그래서 getOrders() 에서도 지연 조회가 발생하지 않는다.

 

Hibernate: 
    /* select
        m 
    from
        Member m   
    left join
        fetch m.orders as o */ select
            member0_.member_id as member_i1_6_0_,
            orders1_.order_id as order_id1_9_1_,
            member0_.insert_datetime as insert_d2_6_0_,
            member0_.update_datetime as update_d3_6_0_,
            member0_.city as city4_6_0_,
            member0_.street as street5_6_0_,
            member0_.zipcode as zipcode6_6_0_,
            member0_.age as age7_6_0_,
            member0_.name as name8_6_0_,
            orders1_.insert_datetime as insert_d2_9_1_,
            orders1_.update_datetime as update_d3_9_1_,
            orders1_.delivery_id as delivery6_9_1_,
            orders1_.member_id as member_i7_9_1_,
            orders1_.order_date as order_da4_9_1_,
            orders1_.status as status5_9_1_,
            orders1_.member_id as member_i7_9_0__,
            orders1_.order_id as order_id1_9_0__ 
        from
            member member0_ 
        left outer join
            orders orders1_ 
                on member0_.member_id=orders1_.member_id
Member name: Name#1
Order Size: 1
Member name: Name#2
Order Size: 0
Member name: Name#3
Order Size: 1
Member name: Name#4
Order Size: 0

---

- 서브쿼리

서브쿼리를 사용할때에는 JPASubQuery 를 사용한다. 서브쿼리의 결과가 하나면 unique(), 여러 건이면 list() 를 사용한다. 라고 책에 나오지만 QueryDSL 4.0 부터는 JPAExpressions 를 사용해야 한다.

 

JPAQueryFactory query = new JPAQueryFactory(em);

QMember m = new QMember("m");

List<Member> members = query.select(m)
		.from(m)
		.where(m.age.eq(
			JPAExpressions.select(m.age.max()).from(m)))
		.fetch();
	
members.forEach(member -> {
	System.out.println("Member name: " + member.getName());
	System.out.println("Member age: " + member.getAge());
});

 

where 절 서브쿼리를 이용하여 나이가 제일 많은 member 만 조회하는 예제를 작성해보았다.

 

Hibernate: 
    /* select
        m 
    from
        Member m 
    where
        m.age = (
            select
                max(m.age) 
            from
                Member m
        ) */ select
            member0_.member_id as member_i1_6_,
            member0_.insert_datetime as insert_d2_6_,
            member0_.update_datetime as update_d3_6_,
            member0_.city as city4_6_,
            member0_.street as street5_6_,
            member0_.zipcode as zipcode6_6_,
            member0_.age as age7_6_,
            member0_.name as name8_6_ 
        from
            member member0_ 
        where
            member0_.age=(
                select
                    max(member1_.age) 
                from
                    member member1_
            )
Member name: Name#3
Member age: 32

 

만약 서브 쿼리가 여러 건이더라도 JPAExpressions 의 select 를 이용하기 때문에 변화는 없고 Q 모델의 표현만 변경해주면 된다.

 

List<Member> members = query.select(m)
	.from(m)
	.where(m.id.in(
		JPAExpressions.select(m.id).from(m).where(m.age.gt(10))))
	.fetch();