[JOOQ-07] CTE 구현 방법

2019/03/22 - [Programming/JOOQ] - [JOOQ-06] 올바른 질의를 작성하게 도와주는 방법

 

JOOQ 에서는 Oracle, SQL Server, MySQL 등에서 제공하는 CTE(WITH 로 알고 계시는 분들도 있더군요) 역시 작성할 수 있습니다. 다만, 이 때에는 method 단위의 기능 보다는 문자열로 합성해야 하는 경우가 있어서 추천하고 싶은 방법은 아닙니다.

 

먼저, 아래와 같이 Java 코드로 작성합니다.

CommonTableExpression<Record2<Integer, String>> t1 =
        name("t1").fields("f1", "f2").as(select(val(1), val("a")));
CommonTableExpression<Record2<Integer, String>> t2 =
        name("t2").fields("f3", "f4").as(select(val(2), val("b")));

List<Map<String, Object>> result =
        create.with(t1)
                .with(t2)
                .select(
                        t1.field("f1").add(t2.field("f3")).as("add"),
                        t1.field("f2").concat(t2.field("f4")).as("concat"))
                .from(t1, t2)
                .fetchMaps();

 

이 코드를 MySQL 8.0 dialect 로 해서 SQL 로 변환하면 아래와 같이 로그에 출력됩니다.

with `t1`(`f1`, `f2`) as (select 1, 'a' from dual), `t2`(`f3`, `f4`) as (select 2, 'b' from dual) select (`t1`.`f1` + `t2`.`f3`) as `add`, concat(`t1`.`f2`, `t2`.`f4`) as `concat` from `t1`, `t2`

 

물론 일반SQL 에서 사용할 경우에는 다음과 같은 형태도 가능합니다.

List<Map<String, Object>> result = create.with("a").as(select(
        val(1).as("x"),
        val("a").as("y")
))
        .select()
        .from(table(name("a")))
        .fetchMaps();

 

생성된 질의는 다음과 같습니다..

with `a` as (select 1 as `x`, 'a' as `y` from dual) select * from `a`

 

간혹 재귀 형태의 CTE 을 사용하려고 하는 경우가 있는데, 많은 RDBMS 가 이를 지원하지 않기 때문에 .with() 대신에 .withRecursive() 와 같은 형태로 사용하길 권하고 있습니다.