[JPA] Oracle 을 사용해서 Native Query 로 출력 개수까지 다 가져와보기

예를 들어, 아래와 같은 복잡한 질의가 있다.

SELECT
    so.seqno
    , ...
    , CASE
        WHEN SYSDATE BETWEEN MIN(ev.startdate) AND MIN(ev.enddate)
            THEN 'Y'
        ELSE
            'N'
        END AS event_flag
    , TO_CHAR(MIN(so.display_start), 'yyyy-mm-dd hh24:mi') AS display_start
    , TO_CHAR(MAX(so.display_end), 'yyyy-mm-dd hh24:mi') AS display_end
    , TO_CHAR(MIN(ev.startdate), 'yyyy-mm-dd hh24:mi') AS event_start_date
    , TO_CHAR(MAX(ev.enddate), 'yyyy-mm-dd hh24:mi') AS event_end_date
    , substr(regexp_replace(CASE
        WHEN ga.type IN ('3', '4', '34') AND SOME_SCHEMA.RULE_CATEGORY_PACKAGE.getCategoryParentName(MAX(ct.category_seqno)) = '대한민국'
            THEN listagg(',' || SOME_SCHEMA.RULE_CATEGORY_PACKAGE.getCategoryPath(GDOMAIN.RULE_CATEGORY_PACKAGE.getCategoryParentSeq(ct.category_seqno))) WITHIN GROUP(ORDER BY tl.tl)
        ELSE
            listagg(',' || SOME_SCHEMA.RULE_CATEGORY_PACKAGE.getCategoryPath(ct.category_seqno)) WITHIN GROUP(ORDER BY tl.tl)
        END, '([^,]+)(,
)+', '
'), 2)
        AS category_path
    , MAX(CASE
        WHEN so.tabname = 'tab'
            THEN SOME_SCHEMA.RULE_CATEGORY_PACKAGE.getCategoryParentSeq(ct.category_seqno)
        ELSE ct.category_seqno
        END)
        AS parent_category
    , dbms_lob.substr(MAX(ga.extra_info), dbms_lob.getLength(MAX(ga.extra_info))) AS extra_info
...

위 질의를 JPA 에서 사용하려면 어떻게 해야 할까?

위 질의문은 Oracle 을 기준으로 실제 실무에서 쓰이는 것을 약간 수정해서 올린 것이다. 그리고 위 질의문은 JPA 가 아닌 다른 스택에서 사용된 질의문이다.

 

JPA 에서 기본적으로 지원해주는 JpaRepository 를 가지고 이용하기에는 위 질의에서 사용되는 연산들이 너무나 다양하다. 방언을 직접 추가해주어야 할 수도 있다. 거기에 동적으로 불러오는 로직까지 같이 구현을 해야 한다면 어떨까?

 

위의 질의문은 JPA 의 createNativeQuery 메서드를 이용해서 해결할 수 있다. 하는 김에 Paging 까지 같이 다루어야 한다고 생각을 해보자.

사실, 페이징은 createQuery 또는 createNativeQuery 의 setFirstList() 와 setMaxResults() 를 이용해서 페이징을 설정할 수 있다. 이렇게 하면 데이터베이스 방언별로 JPA 가 알아서 Paging 을 이용할 수 있도록 질의문을 생성해준다. 근데 총 레코드 개수가 몇 개인지는 알 수가 없다.

 

보통 결과의 전체 레코드의 개수를 파악할 때는 기존의 Paging 질의문 따로, Paging 이 없는 질의문 따로, 총 2개의 질의문 을 실행해서 개수를 파악한다. 만약, Paging 질의를 할 때 한 번에 총 결과 개수까지 같이 가져오면 어떨까?

 

장점으로는 하나의 질의문으로 총 결과 개수까지 같이 가져올 수 있지만, 단점으로는 각 레코드에 총 결과 개수가 같이 들어가게 되고 무엇보다 데이터 결과 수가 굉장히 많을 경우 결과 전체 레코드를 불러올 때 느린 점이 있다. 따라서 총 결과 개수를 포함한 질의문을 사용하는 것은 상황에 따라 이용하는 것이 좋다.

 

어쨌든 Oracle 기준으로 Native 한 Paging 질의를 직접 만듦으로서 결과 개수까지 같이 가져오도록 만드는 유틸 클래스를 만들어보았다.

@NoArgsConstructor(access = AccessLevel.PRIVATE)
public class SearchQueryUtil {
    /**
     * 조회 시 페이징을 지원하는 Native Query 인스턴스를 반환합니다.
     * <p>List 반환 시 모든 숫자는 {@link java.math.BigDecimal} 로 변환됩니다.</p>
     * <p><b>첫 번째와 두 번째 열은 각각 행 번호, 총 개수 입니다.</b></p>
     * @param em {@link EntityManager} 인스턴스
     * @param query 실행할 Query
     * @param params 바인드 파라미터
     * @param pageable 페이지 파라미터
     * @return 페이징 파라미터가 담긴 {@link Query} 인스턴스
     */
    public static Query makePagingQuery(EntityManager em, String query, Map<String, Object> params, Pageable pageable) {
        String pagingQuery = "SELECT * FROM " +
                             "( SELECT ROWNUM AS RNUM, COUNT(1) OVER() record_count, tbl.* " +
                                "FROM ( " +
                                "%originalQuery " +
                                ") tbl" +
                             ") WHERE rnum BETWEEN (:page * :viewCnt) AND ((:page + 1) * :viewCnt) ";
        pagingQuery = pagingQuery.replace("%originalQuery", query);
        Query queryResult = em.createNativeQuery(pagingQuery);
        queryResult.setParameter("page", pageable.getOffset());
        queryResult.setParameter("viewCnt", pageable.getPageSize());

        if (params != null && !params.isEmpty()) {
            for (String key: params.keySet()) {
                queryResult.setParameter(key, params.get(key));
            }
        }

        return queryResult;
    }

    /**
     * Native Query 를 List 로 반환합니다.
     * @param em {@link EntityManager} 인스턴스
     * @param query 실행할 Query
     * @param params 바인드 파라미터
     * @param pageable 페이지 파라미터
     * @param clazz 반환받을 특정 클래스
     * @return 반환받을 클래스로 담긴 List 인스턴스
     */
    public static <T extends PagingQueryRecord> List<T> makePagingQueryAndExecute(EntityManager em, String query, Map<String, Object> params, Pageable pageable, Class<T> clazz) {
        Query pagingQuery = makePagingQuery(em, query, params, pageable);

        JpaResultMapper jpaResultMapper = new JpaResultMapper();
        return jpaResultMapper.list(pagingQuery, clazz);
    }
}

makePagingQuery 에서는 실행하길 원하는 질의문을 감싸서 템플릿처럼 이용할 수 있도록 만든 메서드이다. 페이징을 이용할 때 Pageable 클래스를 이용해서 사용할 수 있도록 만들었다.

 

참고로 Oracle 의 ROWNUM 은 쿼리에서 사용되는 가상의 열 번호로, 쉽게 말하면 출력되는 데이터에 순차적으로 번호를 달아주는 기능이다. 하지만, 이 ROWNUM 은 ORDER BY 연산이 이루어지기 전에 저장되므로 ORDER BY 와 같이 있는 질의문이라면 데이터들의 순서가 다르게 나타난다. 따라서 서브쿼리로 감싸서 실행해야 한다.

 

SELECT 를 할 때에는 ROWNUM 과 총 전체 데이터 개수를 전달받게 되는데 createNativeQuery 를 이용할 때 특정 Entity 를 지정해주지 않는 이상, List 를 가져올 때는 Mybatis 와는 다르게 어떤 컬럼에서 가져왔는지 키에 대한 정보는 없고 오직 값을 전달한다. 따라서, 개발자가 직접 매핑을 해주어야 하는데 이를 쉽게 도와주는 QLRM 이라는 라이브러리가 있다.

물론, 결과를 받을 때 100개가 넘어가는 컬럼들을 불러와서 매핑해주어야 한다면 차라리 QueryDSL 을 쓰거나 MyBatis 를 함께 쓰는 것이 좋다.

// QLRM
api group: 'org.qlrm', name: 'qlrm', version: '3.0.4'

이 라이브러리를 이용하면 아래와 같이 사용할 수 있다.

// # JPA
Query query = em.createNativeQuery("SELECT ... FROM ...");
// Object 로 전달받은 후 직접 맵핑을 해주어야 한다.
List<Object[]> list = query.getResultList();

// # QLRM
Query query = em.createNativeQuery("SELECT ... FROM ...");
JpaResultMapper jpaResultMapper = new JpaResultMapper();
// 특정 클래스 객체로 전달받아 맵핑을 할 수 있다.
List<SomeDto> list = jpaResultMapper.list(query, SomeDto.class);

대신 QLRM 을 이용해서 특정 클래스로 맵핑을 해줄 때 반드시 해당 클래스에는 생성자가 있어야 한다.

그냥 일반 생성자가 아닌 createNativeQuery 의 getResultList 응답 결과 파라미터들을 전달받아 맵핑할 수 있는 생성자가 있어야 한다. 어떤 생성자인지 예시는 아래에서 설명한다.

 

위의 유틸 클래스를 이용할 때 Paging 관련 데이터를 받을 수 있도록 항상 결과를 받을 때는 rnum, record_count 컬럼이 포함되어서 전달받게 된다. 아무런 인터페이스 없이 그냥 클래스를 만들어서 위의 메서드를 사용하기에는 페이징 관련 컬럼이 포함되었는지 다른 개발자들은 알 수 없기 때문에 아래와 같은 추상 클래스를 만들었다.

@SuperBuilder
@Getter
@AllArgsConstructor(access = AccessLevel.PROTECTED)
@NoArgsConstructor(access = AccessLevel.PROTECTED)
public abstract class PagingQueryRecord {
    protected Integer rowNum;
    protected Integer recordCount;
}

List 를 가져올 때는 해당 클래스를 상속받은 DTO 클래스만 사용할 수 있도록 제네릭에 아래와 같이 제약을 걸어두었다.

public static <T extends PagingQueryRecord> List<T> makePagingQueryAndExecute(...)

그래서 사용할 때는 아래와 같이 상속받은 후 생성자를 만들어서 이용할 수 있도록 하였다.

숫자의 경우에는 BigDecimal 로 넘어오므로 이를 참고해서 작성하면 된다.

@SuperBuilder
@Getter
@AllArgsConstructor @NoArgsConstructor
public class Response extends PagingQueryRecord {
    private Long searchOrderSeqNo;
    private Integer orderIdx;
    private String extraInfo;
    private char someFlag;
    
    public Response(BigDecimal rowNum,
                    BigDecimal recordCount,
                    BigDecimal searchOrderSeqNo,
                    BigDecimal orderIdx,
                    String extraInfo,
                    String someFlag) {
        this.rowNum = rowNum.intValue();
        this.recordCount = recordCount.intValue();
        this.searchOrderSeqNo = searchOrderSeqNo.longValue();
        this.orderIdx = orderIdx.intValue();
        this.extraInfo = extraInfo;
        this.someFlag = String.valueOf(someFlag);
    }
}

 

이렇게 하면 아래와 같이 쉽게 질의문을 작성함으로써 결과를 가져올 수 있다.

@Repository
@RequiredArgsConstructor
public class SomeRepositoryImpl {
    @PersistenceContext
    private final EntityManager entityManager;

    public List<SomeDto> get(Pageable pageable) {
        String query = """
                    SELECT
                        '' AS some_columns
                    FROM
                        some_table
                    WHERE
                        some_where = :some_where
                    """;
        Map<String, Object> params = new HashMap<>();
        if (true)
            params.put("some_where", "yay");

        return SearchQueryUtil.makePagingQueryAndExecute(entityManager, query, params, pageable, SomeDto.class);
    }
}