Recursive queries with Oracle’s CONNECT BY clause

Posted on by lukaseder
Recursive or hierarchical queries are an awkward thing in SQL. Some RDBMS allow for recursiveness in Common Table Expressions (CTE’s), but those queries tend to be quite unreadable. That’s not the case for Oracle, which, in addition to recursive CTE’s also supports a dedicated CONNECT BY clause. The general syntax for this clause looks something like this: 

--   SELECT ..
--     FROM ..
--    WHERE ..
 CONNECT BY [NOCYCLE] condition [AND condition, ...]
[START WITH condition]
-- GROUP BY ..
Iterative queries are very simple to express with jOOQ as well. Just use the connectBy() method as such: 

// Some Oracle-specific features are only available
// from the OracleFactory
OracleFactory create = new OracleFactory(connection);

// Get a table with elements 1, 2, 3, 4, 5
create.select(create.rownum())
      .connectBy(create.level().lessOrEqual(5))
      .fetch();
Recursive queries are simple to express as well. Let’s say, you have a DIRECTORY table with ID, PARENT_ID, NAME columns. In order to recursively fetch all directories and calculate their absolute paths, you could issue a query like this in jOOQ. Note the usage of Oracle’s CONNECT BY, START WITH, and PRIOR keywords, as well as the SYS_CONNECT_BY_PATH function: 

 OracleFactory ora = new OracleFactory(connection);

 List<?> paths =
 ora.select(ora.sysConnectByPath(Directory.NAME, "/").substring(2))
    .from(Directory)
    .connectBy(ora.prior(Directory.ID).equal(Directory.PARENT_ID))
    .startWith(Directory.PARENT_ID.isNull())
    .orderBy(ora.literal(1))
    .fetch(0);
jOOQ’s output could then look like this: 
+------------------------------------------------+
|substring                                       |
+------------------------------------------------+
|C:                                              |
|C:/eclipse                                      |
|C:/eclipse/configuration                        |
|C:/eclipse/dropins                              |
|C:/eclipse/eclipse.exe                          |
+------------------------------------------------+
|...21 record(s) truncated...
In the near future, jOOQ is going to project the CONNECT BY syntax and API to other RDBMS’s Common Table Expression syntax. That way, you can express hierarchical queries in any database supporting CTE’s using Oracle’s CONNECT BY syntax. For more details, see also :

Published by lukaseder

I made jOOQ

Leave a Reply