• 카테고리

    질문 & 답변

  • 세부 분야

    백엔드

  • 해결 여부

    해결됨

31:04 부분 "select o from Order o" 만 했을 경우 결과값

23.10.09 19:24 작성 23.10.09 20:25 수정 조회수 535

0

2023-10-09T19:14:55.542+09:00  INFO 2455 --- [nio-8080-exec-1] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring DispatcherServlet 'dispatcherServlet'
2023-10-09T19:14:55.542+09:00  INFO 2455 --- [nio-8080-exec-1] o.s.web.servlet.DispatcherServlet        : Initializing Servlet 'dispatcherServlet'
2023-10-09T19:14:55.542+09:00  INFO 2455 --- [nio-8080-exec-1] o.s.web.servlet.DispatcherServlet        : Completed initialization in 0 ms
2023-10-09T19:14:55.660+09:00 DEBUG 2455 --- [nio-8080-exec-1] org.hibernate.SQL                        : 
    select
        o1_0.order_id,
        o1_0.delivery_id,
        o1_0.member_id,
        o1_0.order_date,
        o1_0.status 
    from
        orders o1_0 offset ? rows fetch first ? rows only
2023-10-09T19:14:55.662+09:00  INFO 2455 --- [nio-8080-exec-1] p6spy                                    : #1696846495662 | took 0ms | statement | connection 7| url jdbc:h2:tcp://localhost/~/jpanew
select o1_0.order_id,o1_0.delivery_id,o1_0.member_id,o1_0.order_date,o1_0.status from orders o1_0 offset ? rows fetch first ? rows only
select o1_0.order_id,o1_0.delivery_id,o1_0.member_id,o1_0.order_date,o1_0.status from orders o1_0 offset 0 rows fetch first 100 rows only;
2023-10-09T19:14:55.668+09:00 DEBUG 2455 --- [nio-8080-exec-1] org.hibernate.SQL                        : 
    select
        m1_0.member_id,
        m1_0.city,
        m1_0.street,
        m1_0.zipcode,
        m1_0.name 
    from
        member m1_0 
    where
        array_contains(?,m1_0.member_id)
2023-10-09T19:14:55.672+09:00  INFO 2455 --- [nio-8080-exec-1] p6spy                                    : #1696846495672 | took 0ms | statement | connection 7| url jdbc:h2:tcp://localhost/~/jpanew
select m1_0.member_id,m1_0.city,m1_0.street,m1_0.zipcode,m1_0.name from member m1_0 where array_contains(?,m1_0.member_id)
select m1_0.member_id,m1_0.city,m1_0.street,m1_0.zipcode,m1_0.name from member m1_0 where array_contains('ar0: ARRAY [CAST(1 AS BIGINT), CAST(2 AS BIGINT)]',m1_0.member_id);
2023-10-09T19:14:55.674+09:00 DEBUG 2455 --- [nio-8080-exec-1] org.hibernate.SQL                        : 
    select
        d1_0.id,
        d1_0.city,
        d1_0.street,
        d1_0.zipcode,
        d1_0.status 
    from
        delivery d1_0 
    where
        array_contains(?,d1_0.id)
2023-10-09T19:14:55.674+09:00  INFO 2455 --- [nio-8080-exec-1] p6spy                                    : #1696846495674 | took 0ms | statement | connection 7| url jdbc:h2:tcp://localhost/~/jpanew
select d1_0.id,d1_0.city,d1_0.street,d1_0.zipcode,d1_0.status from delivery d1_0 where array_contains(?,d1_0.id)
select d1_0.id,d1_0.city,d1_0.street,d1_0.zipcode,d1_0.status from delivery d1_0 where array_contains('ar1: ARRAY [CAST(1 AS BIGINT), CAST(2 AS BIGINT)]',d1_0.id);
2023-10-09T19:14:55.676+09:00 DEBUG 2455 --- [nio-8080-exec-1] org.hibernate.SQL                        : 
    select
        o1_0.order_id,
        o1_0.delivery_id,
        o1_0.member_id,
        o1_0.order_date,
        o1_0.status 
    from
        orders o1_0 
    where
        o1_0.delivery_id=?
2023-10-09T19:14:55.677+09:00  INFO 2455 --- [nio-8080-exec-1] p6spy                                    : #1696846495677 | took 0ms | statement | connection 7| url jdbc:h2:tcp://localhost/~/jpanew
select o1_0.order_id,o1_0.delivery_id,o1_0.member_id,o1_0.order_date,o1_0.status from orders o1_0 where o1_0.delivery_id=?
select o1_0.order_id,o1_0.delivery_id,o1_0.member_id,o1_0.order_date,o1_0.status from orders o1_0 where o1_0.delivery_id=1;
2023-10-09T19:14:55.677+09:00 DEBUG 2455 --- [nio-8080-exec-1] org.hibernate.SQL                        : 
    select
        o1_0.order_id,
        o1_0.delivery_id,
        o1_0.member_id,
        o1_0.order_date,
        o1_0.status 
    from
        orders o1_0 
    where
        o1_0.delivery_id=?
2023-10-09T19:14:55.678+09:00  INFO 2455 --- [nio-8080-exec-1] p6spy                                    : #1696846495678 | took 0ms | statement | connection 7| url jdbc:h2:tcp://localhost/~/jpanew
select o1_0.order_id,o1_0.delivery_id,o1_0.member_id,o1_0.order_date,o1_0.status from orders o1_0 where o1_0.delivery_id=?
select o1_0.order_id,o1_0.delivery_id,o1_0.member_id,o1_0.order_date,o1_0.status from orders o1_0 where o1_0.delivery_id=2;
2023-10-09T19:14:55.680+09:00 DEBUG 2455 --- [nio-8080-exec-1] org.hibernate.SQL                        : 
    select
        o1_0.order_id,
        o1_0.order_item_id,
        o1_0.count,
        o1_0.item_id,
        o1_0.order_price 
    from
        order_item o1_0 
    where
        array_contains(?,o1_0.order_id)
2023-10-09T19:14:55.686+09:00  INFO 2455 --- [nio-8080-exec-1] p6spy                                    : #1696846495686 | took 5ms | statement | connection 7| url jdbc:h2:tcp://localhost/~/jpanew
select o1_0.order_id,o1_0.order_item_id,o1_0.count,o1_0.item_id,o1_0.order_price from order_item o1_0 where 
array_contains(?,o1_0.order_id)
select o1_0.order_id,o1_0.order_item_id,o1_0.count,o1_0.item_id,o1_0.order_price from order_item o1_0 where array_contains('ar2: ARRAY [CAST(1 AS BIGINT), CAST(2 AS BIGINT), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL]',o1_0.order_id);
2023-10-09T19:14:55.687+09:00 DEBUG 2455 --- [nio-8080-exec-1] org.hibernate.SQL                        : 
    select
        i1_0.item_id,
        i1_0.dtype,
        i1_0.name,
        i1_0.price,
        i1_0.stock_quantity,
        i1_0.artist,
        i1_0.etc,
        i1_0.author,
        i1_0.isbn,
        i1_0.actor,
        i1_0.director 
    from
        item i1_0 
    where
        array_contains(?,i1_0.item_id)
2023-10-09T19:14:55.688+09:00  INFO 2455 --- [nio-8080-exec-1] p6spy                                    : #1696846495688 | took 0ms | statement | connection 7| url jdbc:h2:tcp://localhost/~/jpanew
select i1_0.item_id,i1_0.dtype,i1_0.name,i1_0.price,i1_0.stock_quantity,i1_0.artist,i1_0.etc,i1_0.author,i1_0.isbn,i1_0.actor,i1_0.director from item i1_0 where array_contains(?,i1_0.item_id)
select i1_0.item_id,i1_0.dtype,i1_0.name,i1_0.price,i1_0.stock_quantity,i1_0.artist,i1_0.etc,i1_0.author,i1_0.isbn,i1_0.actor,i1_0.director from item i1_0 where array_contains('ar3: ARRAY [CAST(1 AS BIGINT), CAST(2 AS BIGINT), CAST(3 AS BIGINT), CAST(4 AS BIGINT)]',i1_0.item_id);

 

이 결과값에서 강의랑 다른 부분이 있어서 질문 드립니다
하이버네이트 6.2 라서 array_contains 인거 아는데 아래 부분이 이해가 안됩니다.

 

   select
        o1_0.order_id,
        o1_0.delivery_id,
        o1_0.member_id,
        o1_0.order_date,
        o1_0.status 
    from
        orders o1_0 
    where
        o1_0.delivery_id=?
2023-10-09T19:14:55.677+09:00  INFO 2455 --- [nio-8080-exec-1] p6spy                                    : #1696846495677 | took 0ms | statement | connection 7| url jdbc:h2:tcp://localhost/~/jpanew
select o1_0.order_id,o1_0.delivery_id,o1_0.member_id,o1_0.order_date,o1_0.status from orders o1_0 where o1_0.delivery_id=?
select o1_0.order_id,o1_0.delivery_id,o1_0.member_id,o1_0.order_date,o1_0.status from orders o1_0 where o1_0.delivery_id=1;
2023-10-09T19:14:55.677+09:00 DEBUG 2455 --- [nio-8080-exec-1] org.hibernate.SQL                        : 
    select
        o1_0.order_id,
        o1_0.delivery_id,
        o1_0.member_id,
        o1_0.order_date,
        o1_0.status 
    from
        orders o1_0 
    where
        o1_0.delivery_id=?
2023-10-09T19:14:55.678+09:00  INFO 2455 --- [nio-8080-exec-1] p6spy                                    : #1696846495678 | took 0ms | statement | connection 7| url jdbc:h2:tcp://localhost/~/jpanew
select o1_0.order_id,o1_0.delivery_id,o1_0.member_id,o1_0.order_date,o1_0.status from orders o1_0 where o1_0.delivery_id=?
select o1_0.order_id,o1_0.delivery_id,o1_0.member_id,o1_0.order_date,o1_0.status from orders o1_0 where o1_0.delivery_id=2;

orders 를 처음에 조회하고 나서 또 추가적으로

 o1_0.delivery_id=?

이걸로 조회하는 결과가 왜 생기는걸까요?..

(배치사이즈도 강의랑 똑같이 100입니다)

 

OrderApiController.java

@GetMapping("/api/v3.1/orders")
public List<OrderDto> ordersV3_page(@RequestParam(value ="offset",defaultValue = "0") int offset,
                                    @RequestParam(value ="limit",defaultValue = "100") int limit)
{
    List<Order> orders = orderRepository.findAllWithMemberDelivery(offset,limit);
    List<OrderDto> result = orders.stream()
            .map(o -> new OrderDto(o))
            .collect(Collectors.toList());
    return result;
}

 

 

OrderRepository.java

    public List<Order> findAllWithMemberDelivery(int offset, int limit) {
        return em.createQuery(
                        "select o from Order o" , Order.class
                ).setFirstResult(offset)
                .setMaxResults(limit)
                .getResultList();
    }

이렇게 바꾸고 난 이후에 일어난 결과입니당..

강의랑 In말고도 추가적으로 다른 부분이 있어서 질문드립니다
일단 제 스프링부트 버전은 3.1.3 입니다.

 

course-thumbnail

실전! 스프링 부트와 JPA 활용2 - API 개발과 성능 최적화

주문 조회 V3.1: 엔티티를 DTO로 변환 - 페이징과 한계 돌파

강의실 바로가기

답변 2

·

답변을 작성해보세요.

2

김영한님의 프로필

김영한

지식공유자

2023.10.16

안녕하세요. ksg980105님

하이버네이트5까지는 정상 작동하고 해당 쿼리도 추가로 호출되지 않는데요.

제 생각에 이 부분은 하이버네이트 최신버전에서 OneToOne 관계일 때 발생한 버그인 것 같습니다. 다른 관계에서는 문제가 발생하지 않네요.

하이버네이트 6이 나오면서 내부 엔진이 많이 개선되었는데요. 그러면서 연관관계가 복잡한 경우에 아직 해결이 안되는 것 같습니다. 이 부분은 좀 더 기다려봐야 할 것 같아요.

문제가 해결되기 전까지는 OneToOne 관계에서 최적화가 필요한 경우 fetch join을 적극적으로 사용하시길 바랍니다.

도움이 되셨길 바래요 :)

ksg980105님의 프로필

ksg980105

질문자

2023.10.18

아 이제 이해됐네요 버그군요!! 패치조인 위주로 써야겠어요! 감사합니다 항상!!

0

인프런 AI 인턴님의 프로필

인프런 AI 인턴

2023.10.11

안녕하세요, 인프런 AI 인턴이에요. 주신 내용을 보니 Spring DispatcherServlet이 초기화되고, Hibernate SQL이 실행되는 것 같네요. 실제로 수행된 SQL은 “select o1_0.order_id,o1_0.delivery_id,o1_0.member_id,o1_0.order_date,o1_0.status from orders o1_0 offset 0 rows fetch first 100 rows only;“인 것으로 보입니다. 이 SQL은 “orders” 테이블에서 데이터를 가져와서 주문아이디, 배송아이디, 회원아이디, 주문일자, 상태를 조회하는 쿼리입니다. offset과 fetch first는 페이지네이션을 위한 쿼리 부분으로, 이 경우 처음 100개의 데이터를 가져온다는 뜻이에요. 이 쿼리를 실행했을 때의 결과값은 SQL에서 나타나지 않기 때문에 정확한 결과에 대해서는 알 수 없습니다. 추가로 무엇을 알고 싶으신 건지 더 자세한 질문을 주시면 더 도움을 드릴 수 있을 것 같아요. 어떤 도움이 필요하신가요?

ksg980105님의 프로필

ksg980105

질문자

2023.10.12

실제로 수행된 SQL이 그거라면

select o1_0.order_id, o1_0.delivery_id, o1_0.member_id, o1_0.order_date, o1_0.status from orders o1_0 where o1_0.delivery_id=?

이 코드는 왜 콘솔로그에 남아서 저를 헷깔리게 하는거죠? ㅠ

이 글과 비슷한 Q&A