Using jOOQ’s Implicit Join From Within the JOIN .. ON Clause

Starting with jOOQ 3.11, type safe implicit JOIN have been made available, and they've been enhanced to be supported also in DML statements in jOOQ 3.17. Today, I'd like to focus on a somewhat weird but really powerful use-case for implicit JOIN, when joining additional tables from within an explicit JOIN's ON clause. The use … Continue reading Using jOOQ’s Implicit Join From Within the JOIN .. ON Clause

jOOQ 3.17 Supports Implicit Join also in DML

Since jOOQ 3.11, implicit joins have been supported. An implicit join is a JOIN (mostly a LEFT JOIN) that is generated implicitly because of the presence of a path expression. If SQL supported the syntax natively, it would look like this: SELECT cu.first_name, cu.last_name, cu.address.city.country.country FROM customer AS cu All that is is convenience for … Continue reading jOOQ 3.17 Supports Implicit Join also in DML

3.17.0 Release with Computed Columns, Audit Columns, Pattern Matching, Reactive Transactions and Kotlin Coroutine Support

This release contiues the work from previous releases around more sophisticated SQL transformation capabilities, including: Client side computed columns for both read and write operationsAudit columnsPattern matching SQL transformationsMore implicit JOIN capabilities Client side computed columns A ground breaking new core feature available in all commercial distributions isthe new client side computed columns feature, building … Continue reading 3.17.0 Release with Computed Columns, Audit Columns, Pattern Matching, Reactive Transactions and Kotlin Coroutine Support

jOOQ Internals: Pushing up SQL fragments

Over the past 13 years, jOOQ has accrued quite some internal features, which you, the user, are not exposed to. One very interesting feature is the capability for any arbitrary jOOQ expression tree element to push a SQL fragment up to a higher level. How does it work? The jOOQ expression tree model When you … Continue reading jOOQ Internals: Pushing up SQL fragments

Automatically Transform Oracle Style Implicit Joins to ANSI JOIN using jOOQ

While jOOQ is mostly being used as an internal SQL DSL for embedded, dynamic SQL in Java, where it offers the best solution on the market, jOOQ is increasingly also used for one of its secondary features: Its parser. Having been introduced in jOOQ 3.9 primarly for the purpose of being able to parse DDL … Continue reading Automatically Transform Oracle Style Implicit Joins to ANSI JOIN using jOOQ

jOOQ 3.11 Released With 4 New Databases, Implicit Joins, Diagnostics, and Much More

Today, jOOQ 3.11 has been released with support for 4 new databases, implicit joins, diagnostics, and much more New Databases Supported At last, 4 new SQL dialects have been added to jOOQ! These are: jOOQ Professional Edition Aurora MySQL Edition Aurora PostgreSQL Edition Azure SQL Data Warehouse jOOQ Enterprise Edition Teradata Implicit Joins One of … Continue reading jOOQ 3.11 Released With 4 New Databases, Implicit Joins, Diagnostics, and Much More

Type Safe Implicit JOIN Through Path Navigation in jOOQ 3.11

One of the biggest contributors to SQL syntax verbosity is the need to explicitly JOIN every table that somehow contributes to the query, even if that contribution is "trivial". When looking at the Sakila database, an example could be seen easily when fetching customer data: SELECT cu.first_name, cu.last_name, co.country FROM customer AS cu JOIN address … Continue reading Type Safe Implicit JOIN Through Path Navigation in jOOQ 3.11