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.

References to this page

Feedback

Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo