Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10
The WITH RECURSIVE clause
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The various SQL dialects do not agree on the use of RECURSIVE when writing recursive common table expressions. When using jOOQ, always use the DSLContext.withRecursive() or DSL.withRecursive() methods, and jOOQ will render the RECURSIVE keyword, if needed.
Assuming a table like this:
CREATE TABLE directory ( id INT NOT NULL, parent_id INT, -- In PostgreSQL, use TEXT instead, to work around https://github.com/jOOQ/jOOQ/issues/12067 label VARCHAR(50), CONSTRAINT pk_directory PRIMARY KEY (id), CONSTRAINT fk_directory FOREIGN KEY (parent_id) REFERENCES directory (id) ); INSERT INTO directory VALUES ( 1, null, 'C:'); INSERT INTO directory VALUES ( 2, 1, 'eclipse'); INSERT INTO directory VALUES ( 3, 2, 'configuration'); INSERT INTO directory VALUES ( 4, 2, 'dropins'); INSERT INTO directory VALUES ( 5, 2, 'features'); INSERT INTO directory VALUES ( 7, 2, 'plugins'); INSERT INTO directory VALUES ( 8, 2, 'readme'); INSERT INTO directory VALUES ( 9, 8, 'readme_eclipse.html'); INSERT INTO directory VALUES (10, 2, 'src'); INSERT INTO directory VALUES (11, 2, 'eclipse.exe');
Using WITH RECURSIVE, you can now query the structure of this directory as follows:
WITH RECURSIVE t (
id,
name,
path
) AS (
SELECT
DIRECTORY.ID,
DIRECTORY.LABEL,
DIRECTORY.LABEL
FROM
DIRECTORY
WHERE
DIRECTORY.PARENT_ID IS NULL
UNION ALL
SELECT
DIRECTORY.ID,
DIRECTORY.LABEL,
t.path
|| '\'
|| DIRECTORY.LABEL
FROM
t
JOIN
DIRECTORY
ON t.id = DIRECTORY.PARENT_ID
)
SELECT *
FROM
t;
CommonTableExpression<?> cte = name("t").fields(
"id",
"name",
"path"
).as(
select(
DIRECTORY.ID,
DIRECTORY.LABEL,
DIRECTORY.LABEL)
.from(DIRECTORY)
.where(DIRECTORY.PARENT_ID.isNull())
.unionAll(
select(
DIRECTORY.ID,
DIRECTORY.LABEL,
field(name("t", "path"), VARCHAR)
.concat("\\")
.concat(DIRECTORY.LABEL))
.from(table(name("t")))
.join(DIRECTORY)
.on(field(name("t", "id"), INTEGER)
.eq(DIRECTORY.PARENT_ID)))
);
System.out.println(
create.withRecursive(cte)
.selectFrom(cte)
.fetch()
);
The output would look like this:
+----+---------------------+---------------------------------------+ | id | name | path | +----+---------------------+---------------------------------------+ | 1 | C: | C: | | 2 | eclipse | C:\eclipse | | 3 | configuration | C:\eclipse\configuration | | 4 | dropins | C:\eclipse\dropins | | 11 | eclipse.exe | C:\eclipse\eclipse.exe | | 5 | features | C:\eclipse\features | | 7 | plugins | C:\eclipse\plugins | | 8 | readme | C:\eclipse\readme | | 9 | readme_eclipse.html | C:\eclipse\readme\readme_eclipse.html | | 10 | src | C:\eclipse\src | +----+---------------------+---------------------------------------+
Caveats
The SQL language expresses the recursion syntactically, meaning the table t in the above example is being referenced from within the declaration of t. This isn't possible in a language like Java. Hence, we must use the identifier API to construct identifier references for tables and columns. This technique usually appears a bit more verbose than ordinary jOOQ API usage that is based on generated code for your schema.
Feedback
Do you have any feedback about this page? We'd love to hear it!