JPA N+1 이슈, 서브쿼리, sorting, join

아래와 같은 관계도가 있는 테이블이 있다.

 

위에 그림 두개는 DB 관계도, 아래의 이미지는 자바단 부모 @Entity 쪽 @OneToMany 설정이다.

 

원래는 LAZY를 쓰고싶지만 회사 ..사정상 EAGER를 쓰고있다.

 

 

편의상 OP_REGULAR_ROUND => OP_ROUND

OP_USER_REGULAR_ROUND -> OP_ROUND_ORDER 로 바꿔서 설명하겠다.

 

1. JPA N+1 이슈

 

내가원하는 SQL문은 아래와 같다.

SELECT RR.ROUND, OR.*
FROM OP_ROUND OR
         INNER JOIN OP_ROUND_ORDER RR ON OR.CODE = RR.CODE
WHERE OR.CODE = '1111'
ORDER BY RR.ROUND DESC;

조인을 하고 코드를 파라미터로 넘겨준다.

 

1) service와 같은 root 에 repository를 생성 - 나는 일단 impl 생성했다.

public interface RoundCustom {
    /**
     * 설명 ~~~~
     * @return
     */
    Round getDetail(String code);
}

import static saleson.model.QRound.round;
import static saleson.model.QRoundOrder.roundOrder;

@RequiredArgsConstructor
public class RoundCustomImpl implements RoundCustom{
    private final JPAQueryFactory jpaQueryFactory;

    @Override
    public Round getDetail(String roundCode) {
    }
}

querydsl에서 사용할 q클래스와 JPAQeuryFactory 를 선언해줬다.

 

그리고 리포지토리에 extends 추가해준다.

사실 커스텀안하고 별도 리포지토리를 생성하고 거기에 관리해도된다.

public interface RoundRepository extends JpaRepository<Round, Long>, QuerydslPredicateExecutor<Round>, RoundCustom {
    Optional<Round> findByCode(String code);
}

 

 

2) QUERY DSL를 사용 할 메소드 생성

다시 RoundCustomImpl에 돌아와서..

public Round getDetail(String code) {
    
    //builder 를 따로 뺀 이유는, 이후에 where 조건이 추가될수도 있기 때문입니다.
    BooleanBuilder builder = new BooleanBuilder();
    builder.and(round.code.eq(code));

    }

querydsl 생성에 앞서 먼저 builder 를 만들어준다. 

 

where 조건에 들어갈 부분을 넣으면 된다.

 

public Round getDetail(String code) {
    
    //builder 를 따로 뺀 이유는, 이후에 where 조건이 추가될수도 있기 때문입니다.
    BooleanBuilder builder = new BooleanBuilder();
    builder.and(round.code.eq(code));

    return jpaQueryFactory
            .select(round)
            .from(round)
            .innerJoin(round.list,roundOrder) //oneToMany
            .fetchJoin()
            .where(builder)
            .orderBy(roundOrder.round.desc())
            .fetchOne();
    }

이게 완성된 로직인데, innerjoin에 들어가는 파라미터는, 부모@Entity에 @OneToMany로 걸려있는 변수를, 뒤에는 조인할 q클래스명을 입력해줬다.

 

이렇게 돌리면

select regularrou0_.ID                         as id1_47_0_,
       userregula1_.ID                         as id1_60_1_,
       regularrou0_.CREATED                    as created2_47_0_,
       regularrou0_.CREATED_BY                 as created_3_47_0_,
       regularrou0_.UPDATED                    as updated4_47_0_,
       regularrou0_.UPDATED_BY                 as updated_5_47_0_,
       regularrou0_.BUYER_EMAIL                as buyer_em6_47_0_,
       regularrou0_.BUYER_MOBILE               as buyer_mo7_47_0_,
       regularrou0_.BUYER_NAME                 as buyer_na8_47_0_,
       regularrou0_.CARD_NAME                  as card_nam9_47_0_,
       regularrou0_.INDEX_ROUND                as index_r10_47_0_,
       regularrou0_.ITEM_NAME                  as item_na11_47_0_,
       regularrou0_.ITEM_USER_CODE             as item_us12_47_0_,
       regularrou0_.RECEIVE_NAME               as receive13_47_0_,
       regularrou0_.REGULAR_ROUND_END_DATE     as regular14_47_0_,
       regularrou0_.REGULAR_ROUND_START_DATE   as regular15_47_0_,
       regularrou0_.STATUS                     as status16_47_0_,
       regularrou0_.TOTAL_ROUND                as total_r17_47_0_,
       regularrou0_.USER_ID                    as user_id18_47_0_,
       regularrou0_.USER_REGULAR_CARD_ID       as user_re19_47_0_,
       regularrou0_.USER_REGULAR_ROUND_CODE    as user_re20_47_0_,
       userregula1_.CREATED                    as created2_60_1_,
       userregula1_.CREATED_BY                 as created_3_60_1_,
       userregula1_.UPDATED                    as updated4_60_1_,
       userregula1_.UPDATED_BY                 as updated_5_60_1_,
       userregula1_.DISCOUNT_RATE              as discount6_60_1_,
       userregula1_.FAIL_COUNT                 as fail_cou7_60_1_,
       userregula1_.ITEM_ID                    as item_id8_60_1_,
       userregula1_.ITEM_NAME                  as item_nam9_60_1_,
       userregula1_.ITEM_SEQUENCE              as item_se10_60_1_,
       userregula1_.ORDER_CODE                 as order_c11_60_1_,
       userregula1_.ORDER_DATE                 as order_d12_60_1_,
       userregula1_.ORDER_SEQUENCE             as order_s13_60_1_,
       userregula1_.ORDER_STATUS               as order_s14_60_1_,
       userregula1_.PAY_AMOUNT                 as pay_amo15_60_1_,
       userregula1_.PAY_DATE                   as pay_dat16_60_1_,
       userregula1_.PAYMENT_INTERVAL           as payment17_60_1_,
       userregula1_.QUANTITY                   as quantit18_60_1_,
       userregula1_.ROUND                      as round19_60_1_,
       userregula1_.SHIPPING_CANCEL_END_DATE   as shippin20_60_1_,
       userregula1_.SHIPPING_CANCEL_START_DATE as shippin21_60_1_,
       userregula1_.SHIPPING_READY_END_DATE    as shippin22_60_1_,
       userregula1_.SHIPPING_READY_START_DATE  as shippin23_60_1_,
       userregula1_.USE_FLAG                   as use_fla24_60_1_,
       userregula1_.USER_DELIVERY_ID           as user_de25_60_1_,
       userregula1_.USER_ID                    as user_id26_60_1_,
       userregula1_.USER_REGULAR_CARD_ID       as user_re27_60_1_,
       userregula1_.USER_REGULAR_ROUND_CODE    as user_re28_60_1_,
       userregula1_.USER_REGULAR_ROUND_CODE    as user_re28_60_0__,
       userregula1_.ID                         as id1_60_0__
from OP_ROUND regularrou0_
         inner join OP_ROUND_ORDER userregula1_
                    on regularrou0_.CODE = userregula1_.code
where regularrou0_.CODE = '1111'
order by userregula1_.ROUND desc

 

이런식으로 내가원하던 조인문이 나온다.

 

 

이때 fetchJoin을 안쓰면

select regularrou0_.ID                       as id1_47_,
       regularrou0_.CREATED                  as created2_47_,
       regularrou0_.CREATED_BY               as created_3_47_,
       regularrou0_.UPDATED                  as updated4_47_,
       regularrou0_.UPDATED_BY               as updated_5_47_,
       regularrou0_.BUYER_EMAIL              as buyer_em6_47_,
       regularrou0_.BUYER_MOBILE             as buyer_mo7_47_,
       regularrou0_.BUYER_NAME               as buyer_na8_47_,
       regularrou0_.CARD_NAME                as card_nam9_47_,
       regularrou0_.INDEX_ROUND              as index_r10_47_,
       regularrou0_.ITEM_NAME                as item_na11_47_,
       regularrou0_.ITEM_USER_CODE           as item_us12_47_,
       regularrou0_.RECEIVE_NAME             as receive13_47_,
       regularrou0_.REGULAR_ROUND_END_DATE   as regular14_47_,
       regularrou0_.REGULAR_ROUND_START_DATE as regular15_47_,
       regularrou0_.STATUS                   as status16_47_,
       regularrou0_.TOTAL_ROUND              as total_r17_47_,
       regularrou0_.USER_ID                  as user_id18_47_,
       regularrou0_.USER_REGULAR_CARD_ID     as user_re19_47_,
       regularrou0_.USER_REGULAR_ROUND_CODE  as user_re20_47_
from OP_ROUND regularrou0_
         inner join OP_ROUND_ORDER userregula1_
                    on (regularrou0_.CODE = userregula1_.CODE)
where regularrou0_.CODE = '1111'
order by userregula1_.ROUND desc;

select userregula0_.USER_REGULAR_ROUND_CODE    as user_re28_60_0_,
       userregula0_.ID                         as id1_60_0_,
       userregula0_.ID                         as id1_60_1_,
       userregula0_.CREATED                    as created2_60_1_,
       userregula0_.CREATED_BY                 as created_3_60_1_,
       userregula0_.UPDATED                    as updated4_60_1_,
       userregula0_.UPDATED_BY                 as updated_5_60_1_,
       userregula0_.DISCOUNT_RATE              as discount6_60_1_,
       userregula0_.FAIL_COUNT                 as fail_cou7_60_1_,
       userregula0_.ITEM_ID                    as item_id8_60_1_,
       userregula0_.ITEM_NAME                  as item_nam9_60_1_,
       userregula0_.ITEM_SEQUENCE              as item_se10_60_1_,
       userregula0_.ORDER_CODE                 as order_c11_60_1_,
       userregula0_.ORDER_DATE                 as order_d12_60_1_,
       userregula0_.ORDER_SEQUENCE             as order_s13_60_1_,
       userregula0_.ORDER_STATUS               as order_s14_60_1_,
       userregula0_.PAY_AMOUNT                 as pay_amo15_60_1_,
       userregula0_.PAY_DATE                   as pay_dat16_60_1_,
       userregula0_.PAYMENT_INTERVAL           as payment17_60_1_,
       userregula0_.QUANTITY                   as quantit18_60_1_,
       userregula0_.ROUND                      as round19_60_1_,
       userregula0_.SHIPPING_CANCEL_END_DATE   as shippin20_60_1_,
       userregula0_.SHIPPING_CANCEL_START_DATE as shippin21_60_1_,
       userregula0_.SHIPPING_READY_END_DATE    as shippin22_60_1_,
       userregula0_.SHIPPING_READY_START_DATE  as shippin23_60_1_,
       userregula0_.USE_FLAG                   as use_fla24_60_1_,
       userregula0_.USER_DELIVERY_ID           as user_de25_60_1_,
       userregula0_.USER_ID                    as user_id26_60_1_,
       userregula0_.USER_REGULAR_CARD_ID       as user_re27_60_1_,
       userregula0_.USER_REGULAR_ROUND_CODE    as user_re28_60_1_
from OP_ROUND_ORDER userregula0_
where userregula0_.CODE = 'R20230216100309';

 

이런식으로 두번나온다.

 

하지만 fetchJoin이 만사형통은 아니다.. 페이징은 쓸수없기에....

 

참고로 해당 sql문은 콘솔에서 확인할수있는데

이런식으로 쓱- 보고 지나가기 쉽다. 하지만 꼭 작업할때 긁어서 실제로 sql문을 육안으로 확인해보는 습관을 길러야한다.

 

2. querydsl 결과를 특정 DTO에 리턴, 서브쿼리받는법

 

내가 받고싶은 sql문

select RR.USER_ID,
       RR.USER_REGULAR_ROUND_CODE,
       RR.ITEM_NAME,
       RR.REGULAR_ROUND_START_DATE,
       RR.TOTAL_ROUND,
       RR.BUYER_NAME,
       RR.CREATED,
       min(OURR.PAY_DATE),
       min(OURR.PAYMENT_INTERVAL),
       OURR.SHIPPING_READY_START_DATE
from OP_ROUND RR
inner join OP_ROUND_ORDER OURR
        on RR.CODE = OURR.CODE
where OURR.ORDER_CODE is null
  and RR.CODE = (select CODE
                  from OP_ROUND_ORDER
                  where ORDER_CODE = '2222')

 

특정 결과만 dto에 리턴받고싶을때

 

@Override
    public dtoA method2(String orderCode){

		//서브쿼리에 넣을 where 절
        BooleanBuilder subBuilder = new BooleanBuilder();
        subBuilder.and(roundOrder.orderCode.eq(orderCode));

		//메인쿼리에 넣을 where 절
        BooleanBuilder builder = new BooleanBuilder();
        builder.and(roundOrder.orderCode.isNull());
        builder.and(round.code.eq(
                JPAExpressions.select(roundOrder.code)
                        .from(roundOrder)
                        .where(subBuilder)
        ));

        return jpaQueryFactory
                .select(Projections.constructor(dtoA.class,
                        round.userId,
                        round.userRegularRoundCode,
                        round.itemName,
                        round.regularRoundStartDate,
                        round.totalRound,
                        round.buyerName,
                        round.created,
                        roundOrder.payDate.min(),
                        roundOrder.paymentInterval.min())) //그룹함수는 여기서
                .from(round)
                .join(round.list,roundOrder)
                .where(builder)
                .fetchOne();
    }

서브쿼리는 JPAExpressions 를 사용해서 조인시킨다.

역시 이때도 where 조건이 많아지면 너무 산만하기때문에 상단에 따로 선언하고 사용하였다.

 

Projections.contructor 사용하면 dto로 받을수있다.

첫 파라미터에 리턴받고싶은 dto 클래스를 추가하고 뒤에는 전부 내가받고싶은 컬럼명 나열한다.

 

이때 해당 dto에 해당 컬럼을 받는 생성자가 선언되어있어야한다.

그룹함수는 select할때 바로 쓰면된다.