인프런 커뮤니티 질문&답변

현재 2년차가 되가고 있는 주니어개발자입니다 ( _ _)

아직 경험이 많이 없어 현재 쿼리를 처리하는 퍼시스턴트 프레임워크를 mybaits 정도 밖에 써보지 못 했습니다

선생님께서 말씀하신 것처럼 쿼리를 JPQL로 작성해서 처리할 수 있다고 하셨는데...

아래는 제가 mybatis로 api 처리하고 있는 쿼리입니다. 이런 다소 지저분하고 복잡한 쿼리도 실무에서는 JPQL로 처리를 하나요??? 

SELECT /* windowClosingPosData */
   b.paymentType
   ,MAX(b.gdName) as gdName
   ,b.payMethod
   ,SUM(b.personCnt) as personCnt /* 수량 */
   ,SUM(b.price) as price /* 금액 */
   ,SUM(b.discountCount) as discountCnt /* 할인매수 */
   ,SUM(b.discountAmount) as discount /* 할인금액 */
FROM (
   SELECT
      a.paymentType
      ,a.gdName
      ,a.payMethod
      ,(
      CASE
         WHEN a.paymentType = 'PAY'
            THEN a.ticketCount
         WHEN a.paymentType = 'CANCEL'
            THEN a.refundCount
         ELSE 0
      END
      ) as personCnt /* 수량 */
      ,(
      CASE
         WHEN a.paymentType = 'PAY'
            THEN a.ticketAmount
         WHEN a.paymentType = 'CANCEL'
            THEN a.refundAmount
         ELSE 0
      END
      ) as price /* 금액 */

      ,a.discountCount /* 할인 수량 */
      ,a.discountAmount /* 할인 금액 */

   FROM (
      SELECT
         'PAY' as paymentType
         ,gd.gd_name as gdName
         ,pm.pm_pay_method as payMethod
         ,rd.init_rs_ticket_cnt as ticketCount /* 발권매수 */
         ,(rd.init_rs_ticket_cnt * rd_price) as ticketAmount /* 발권금액 */
         ,0 as refundCount /* 환불매수 */
         ,0 as refundAmount /* 환불금액 */
         ,(
         CASE
            WHEN ifnull(rd.dc_unit_price,0) > 0
               THEN rd.init_rs_ticket_cnt
            ELSE 0
         END
         ) as discountCount /* 할인매수 */
         ,(rd.init_rs_ticket_cnt * rd.dc_unit_price) as discountAmount
      FROM t_reserve_detail rd
      INNER JOIN t_reserve rs
      ON rd.rs_seq = rs.rs_seq
      INNER JOIN t_goods gd
      ON rs.gd_seq = gd.gd_seq
      INNER JOIN t_payment pm
      ON rs.rs_seq = pm.rs_seq
      AND pm.pm_payment_type = 'PAY'
      WHERE
      EXISTS (
         SELECT
            trdp.rs_seq
         FROM t_reserve_detail_person trdp
         WHERE trdp.tkt_date = DATE_FORMAT(#{searchDate}, '%Y%m%d')
         AND trdp.idkey = #{idkey}
         AND trdp.tkt_win_cd = #{winCd}
         AND trdp.rs_seq = rs.rs_seq
         )
      AND (rs.gd_package_yn = 'Y' OR (rs.rs_package_yn = 'N' AND rs.gd_package_yn = 'N'))
      AND rs.insert_idkey = #{memberSeq}
      UNION ALL
      SELECT
         'CANCEL' as paymentType
         ,gd.gd_name as gdName
         ,pm.pm_pay_method as payMethod
         ,0 as ticketCount /* 발권매수 */
         ,0 as ticketAmount /* 발권금액 */
         ,rd.cancel_ticket_cnt as refundCount /* 환불매수 */
         ,(rd.cancel_ticket_cnt * rd_price) as refundAmount /* 환불금액 */
         ,0 as discountCount /* 할인매수 */
         ,0 as discountAmount
      FROM t_reserve_detail rd
      INNER JOIN t_reserve rs
      ON rd.rs_seq = rs.rs_seq
      INNER JOIN t_goods gd
      ON rs.gd_seq = gd.gd_seq
      INNER JOIN t_payment pm
      ON rs.rs_seq = pm.rs_seq
      AND pm.pm_payment_type = 'PAY'
      WHERE
         EXISTS (
         SELECT
            trdp.rs_seq
         FROM t_reserve_detail_person trdp
         WHERE trdp.tkt_date = DATE_FORMAT(#{searchDate}, '%Y%m%d')
         AND trdp.idkey = #{idkey}
         AND trdp.cancel_win_cd = #{winCd}
         AND trdp.cancel_member_seq = #{memberSeq}
         AND trdp.rs_seq = rs.rs_seq
         )
      AND (rs.gd_package_yn = 'Y' OR (rs.rs_package_yn = 'N' AND rs.gd_package_yn = 'N'))
      AND rs.rs_status = 'D'
   ) a
) b
GROUP BY b.paymentType
      ,b.payMethod
ORDER BY b.payMethod, b.paymentType DESC

JPQL 강의도 들을 계획이지만 이렇게 복잡한 부분을 java단에서 처리한다는게 상상이 되지 않아서 질문드려봅니다 ㅠㅠ