반응형
아래와 같은 관계도가 있는 테이블이 있다.
위에 그림 두개는 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할때 바로 쓰면된다.
728x90