컬렉션 조회: DTO 직접 조회

1. DTO를 직접 가져오기 위해 DTO 및 repository생성.

---

@Data
public class OrderQueryDto {

    private Long orderId;
    private String name;
    private LocalDateTime orderDate;
    private OrderStatus orderStatus;
    private Address address;
    private List<OrderItemQueryDto> orderItems;

    public OrderQueryDto(Long orderId, String name, LocalDateTime orderDate, OrderStatus orderStatus, Address address) {
        this.orderId = orderId;
        this.name = name;
        this.orderDate = orderDate;
        this.orderStatus = orderStatus;
        this.address = address;
    }
}

@Data
public class OrderItemQueryDto {
    private String itemName;
    private int orderPrice;
    private int count;
}

@Repository
@RequiredArgsConstructor
public class OrderQueryRepository {

    private final EntityManager em;

    public List<OrderQueryDto> findOrderQueryDtos(){
        return em.createQuery(
                "select new springjpa2.repository.order.query.OrderQueryDto(o.id, m.name, o.orderDate, o.status, d.address) " +
                        "from Order o " +
                        "join o.member m " +
                        "join o.delivery d", OrderQueryDto.class)
                .getResultList();
    }


}

•  JPQL new를 통해 생성자를 넣어도 컬렉션을 바로 넣을 수는 없다. 
• 따라서 OrderQueryDto의 생성자에 orderItems가 빠져있다..
• 따라서 일단 컬렉션을 제외하고 생성한 뒤
• 생성된 OrderQueryDto를 루프를 돌면서 OrderItemQueryDto를 넣어줘야 한다.
• 이 때 즉 각 Order에 해당하는 OrderItem을 DB에서 찾아와서 DTO형태로 넣어줘야 한다.
• 아래와 같이 구성하면 된다.
• 핵심은 JPQL을 통해 new를 해도 컬렉션을 바로 넣어줄 수 없기 때문에 따로 넣어줘야 한다.

@Repository
@RequiredArgsConstructor
public class OrderQueryRepository {

    private final EntityManager em;

    public List<OrderQueryDto> findOrderQueryDtos(){
        List<OrderQueryDto> result = findOrders();

        result.forEach(o -> {
            List<OrderItemQueryDto> orderItems =  findOrderItems(o.getOrderId());
            o.setOrderItems(orderItems);
        });
        return  result;
    }

    private List<OrderItemQueryDto> findOrderItems(Long orderId) {
        return em.createQuery(
                "select new springjpa2.repository.order.query.OrderItemQueryDto(oi.order.id, i.name, oi.orderPrice, oi.count)" +
                        "from OrderItem oi " +
                        " join oi.item i " +
                        "where oi.order.id = :orderId ", OrderItemQueryDto.class)
                .setParameter("orderId", orderId)
                .getResultList();
    }

    private List<OrderQueryDto> findOrders() {
        return em.createQuery(
                "select new springjpa2.repository.order.query.OrderQueryDto(o.id, m.name, o.orderDate, o.status, d.address) " +
                        "from Order o " +
                        "join o.member m " +
                        "join o.delivery d", OrderQueryDto.class)
                .getResultList();
    }


}

 

2. 정리

---

• Query : 루트 1번, 컬렉션 N번 
• ToOne 관계들을 먼저 조회하고, 루프 돌면서 컬렉션을 넣어줌
  • 왜? 
  • ToOne은 조인해도 데이터 row가 증가하지 ㅇ낳는다.
  • ToMany관계는 조인하면 row가 증가해.. ㅜ
• row수가 증가하지 않는 ToOne은 조인으로 최적화하기 쉬워! 그러니 한번에 조회하고!!!
• ToMany 관계는 최적화 하기 어려우지.. findOrdersItem()같이 별로 메서드를 활용하
• Query 확인
  • 흠 결국 N+1 이다. 최적화가 필요해.

Hibernate: 
    select
        order0_.order_id as col_0_0_,
        member1_.name as col_1_0_,
        order0_.order_date as col_2_0_,
        order0_.status as col_3_0_,
        delivery2_.city as col_4_0_,
        delivery2_.street as col_4_1_,
        delivery2_.zipcode as col_4_2_ 
    from
        orders order0_ 
    inner join
        member member1_ 
            on order0_.member_id=member1_.member_id 
    inner join
        delivery delivery2_ 
            on order0_.delivery_id=delivery2_.delivery_id

Hibernate: 
    select
        orderitem0_.order_id as col_0_0_,
        item1_.name as col_1_0_,
        orderitem0_.order_price as col_2_0_,
        orderitem0_.count as col_3_0_ 
    from
        order_item orderitem0_ 
    inner join
        item item1_ 
            on orderitem0_.item_id=item1_.item_id 
    where
        orderitem0_.order_id=?

Hibernate: 
    select
        orderitem0_.order_id as col_0_0_,
        item1_.name as col_1_0_,
        orderitem0_.order_price as col_2_0_,
        orderitem0_.count as col_3_0_ 
    from
        order_item orderitem0_ 
    inner join
        item item1_ 
            on orderitem0_.item_id=item1_.item_id 
    where
        orderitem0_.order_id=?

 

3. GitHub : 210824 collection, DTO 조회

---

GitHub - bsh6463/SpringBootJPA1