Available in versions: Dev (3.19) | Latest (3.18) | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10 | 3.9
CONNECT BY clause
Applies to ❌ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The Oracle database knows a very succinct syntax for creating hierarchical queries: the CONNECT BY clause, which is fully supported by jOOQ, including all related functions and pseudo-columns. A more or less formal definition of this clause is given here:
-- SELECT .. -- FROM .. -- WHERE .. CONNECT BY [ NOCYCLE ] condition [ AND condition, ... ] [ START WITH condition ] -- GROUP BY .. -- ORDER [ SIBLINGS ] BY ..
An example for an iterative query, iterating through values between 1 and 5 is this:
SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 5
// Get a table with elements 1, 2, 3, 4, 5
create.select(level())
.connectBy(level().le(5))
.fetch();
Here's a more complex example where you can recursively fetch directories in your database, and concatenate them to a path:
SELECT SUBSTR(SYS_CONNECT_BY_PATH(DIRECTORY.NAME, '/'), 2) FROM DIRECTORY CONNECT BY PRIOR DIRECTORY.ID = DIRECTORY.PARENT_ID START WITH DIRECTORY.PARENT_ID IS NULL ORDER BY 1
.select( substring(sysConnectByPath(DIRECTORY.NAME, "/"), 2)) .from(DIRECTORY) .connectBy( prior(DIRECTORY.ID).eq(DIRECTORY.PARENT_ID)) .startWith(DIRECTORY.PARENT_ID.isNull()) .orderBy(1) .fetch();
The output might then look like this
+------------------------------------------------+ |substring | +------------------------------------------------+ |C: | |C:/eclipse | |C:/eclipse/configuration | |C:/eclipse/dropins | |C:/eclipse/eclipse.exe | +------------------------------------------------+ |...21 record(s) truncated...
Some of the supported functions and pseudo-columns are these (available from the DSL):
- LEVEL
- CONNECT_BY_IS_CYCLE
- CONNECT_BY_IS_LEAF
- CONNECT_BY_ROOT
- SYS_CONNECT_BY_PATH
- PRIOR
If this syntax is unavailable in your dialect, starting from jOOQ 3.15, it is emulated in parts using WITH RECURSIVE.
ORDER SIBLINGS
The Oracle database allows for specifying a SIBLINGS keyword in the ORDER BY clause. Instead of ordering the overall result, this will only order siblings among each other, keeping the hierarchy intact. An example is given here:
SELECT DIRECTORY.NAME FROM DIRECTORY CONNECT BY PRIOR DIRECTORY.ID = DIRECTORY.PARENT_ID START WITH DIRECTORY.PARENT_ID IS NULL ORDER SIBLINGS BY 1
.select(DIRECTORY.NAME) .from(DIRECTORY) .connectBy( prior(DIRECTORY.ID).eq(DIRECTORY.PARENT_ID)) .startWith(DIRECTORY.PARENT_ID.isNull()) .orderSiblingsBy(1) .fetch();
Feedback
Do you have any feedback about this page? We'd love to hear it!