Recursive Query with JOOQ.


사용될 테이블은 ‘그룹’테이블로 구조는 간단하다.

테이블의 PK는 SEQ이고, PAR_SEQ를 통해 상위 참조하는 형태이다.

MySQL에서 Recursive Query는 아래와 같이 작성할 수 있다.

이를 JOOQ로 작성하려면 어떻게 해야할까?

구글링을 열심히 해봤지만.. 제대로된 예제가 존재하지 않는 듯 하다.

아래의 JOOQ 공식 Menual에서는 Recursive Query를 사용하려면, DSLContext.withRecursive() 혹은 DSL.withRecursive() 를 사용하라고 짤막하게 안내하고 있다.

DSLContext.withRecursive()를 확인해보면, 첫번째 파라메터는 ‘String alias’를 받고 두번째 부터는 ‘String fieldAliasN…’을 받고 있다.

아마도 SQL에서 WITH RECURSIVE CTE(SEQ, GRP_NAME, PAR_SEQ) 이 부분을 그려줄 것 같다.

DSLContext.withRecursive(“CTE”, “SEQ”, “GRP_NAME”, “PAR_SEQ”) 로 코드를 시작하면 될 것같다.

이렇게 완성한 코드는 아래와 같다.

import static org.jooq.impl.DSL.*;

위의 DSL을 static import하여 select나 filed, table, concat등의 메서드를 사용하였다.

이렇게 나름 완벽(?)한 코드를 실행하니 SQLException이 발생한다.

쿼리까지 잘 만들어졌고 쿼리가 실행되면서 오류가 난것이다.

만들어진 쿼리가 좀 수상하다.

with recursive `CTE`(`SEQ`, `PAR_SEQ`, `GRP_NAME`) as (select * from (select `T`.`SEQ` as `SEQ`, `T`.`PAR_SEQ` as `PAR_SEQ`, `T`.`GRP_NAME` as `GRP_NAME` from `jooq_pilot`.`GRP` as `T` where `T`.`PAR_SEQ` is null) x union all select * from (select `T`.`SEQ` as `SEQ`, `T`.`PAR_SEQ` as `PAR_SEQ`, concat(cast(`CTE`.`GRP_NAME` as char), '/', `T`.`GRP_NAME`) as `GRP_NAME` from `jooq_pilot`.`GRP` as `T` join `CTE` on `T`.`PAR_SEQ` = `CTE`.`SEQ` where `T`.`PAR_SEQ` is not null) x) select `CTE`.`SEQ`, `CTE`.`PAR_SEQ`, `CTE`.`GRP_NAME` from `CTE`

확인을 좀 더 해보니, unionAll을 사용하면 각각 select * from 절을 추가하는데 이 때문에 오류가 나고 있었다.

JOOQ 자체의 오류인지.. 방법을 잘모르는건지.. 한참을 고민하다 약간 둘레길을 선택했다. unionAll을 사용하지 않고 하나의 CTE를 더 만들어서 해결했다.

이번에는 필자가 의도한대로 아래와 같은 결과가 나왔다.

한발 더 나가서, 계층적인 구조의 결과를 얻기 위한 코드는 아래와 같다.

실행된 결과는 아래와 같다.

해당 소스는 GitHub에 있으니 참고 바란다.