jOOQ 3.19 finally delivers on a set of features that will greatly simplify your queries further, after jOOQ 3.11 introduced implicit to-one joins: Explicit path joins To-many path joins Implicit join path correlation What are these features? Many ORMs (e.g. JPA, Doctrine, jOOQ 3.11 and others) support "path joins" (they may have different names for … Continue reading jOOQ 3.19’s new Explicit and Implicit to-many path joins
Category: jooq-development
This category contains posts related to current developments of jOOQ
jOOQ 3.19.0 Released with DuckDB, Trino, Oracle 23c support, join path improvements, an official gradle plugin, commercial maven repositories, policies, UDT paths, trigger meta data, hierarchies, and much more
New Dialects It's been a few releases since we've added support for new dialects, but finally some very interesting RDBMS of increasing popularity have joined the jOOQ family including: DuckDB (experimental support) Trino These dialects are available in all jOOQ editions. New dialect versions In addition to these entirely new dialects, big new CockroachDB and … Continue reading jOOQ 3.19.0 Released with DuckDB, Trino, Oracle 23c support, join path improvements, an official gradle plugin, commercial maven repositories, policies, UDT paths, trigger meta data, hierarchies, and much more
How to Turn a List of Flat Elements into a Hierarchy in Java, SQL, or jOOQ
Occasionally, you want to write a SQL query and fetch a hierarchy of data, whose flat representation may look like this: SELECT id, parent_id, label FROM t_directory; The result might be: |id |parent_id|label | |---|---------|-------------------| |1 | |C: | |2 |1 |eclipse | |3 |2 |configuration | |4 |2 |dropins | |5 |2 |features | … Continue reading How to Turn a List of Flat Elements into a Hierarchy in Java, SQL, or jOOQ
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
Create Dynamic Views with jOOQ 3.17’s new Virtual Client Side Computed Columns
One of jOOQ 3.17's coolest new features are client side computed columns. jOOQ 3.16 already added support for server side computed columns, which many of you appreciate for various reasons. What's a computed column? A computed column is a column that is derived ("computed") from an expression. It cannot be written to. It works like … Continue reading Create Dynamic Views with jOOQ 3.17’s new Virtual Client Side Computed Columns
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
The Performance of Various To-Many Nesting Algorithms
It's been a while since jOOQ 3.15 has been released with its revolutionary standard SQL MULTISET emulation feature. A thing that has been long overdue and which I promised on twitter a few times is to run a few benchmarks comparing the performance of various approaches to nesting to-many relationships with jOOQ. This article will … Continue reading The Performance of Various To-Many Nesting Algorithms
Changing SELECT .. FROM Into FROM .. SELECT Does Not “Fix” SQL
Every now and then, I see folks lament the SQL syntax's peculiar disconnect between the lexical order of operations (SELECT .. FROM) the logical order of operations (FROM .. SELECT) Most recently here in a Youtube comment reply to a recent jOOQ/kotlin talk. Let's look at why jOOQ didn't fall into this trap of trying … Continue reading Changing SELECT .. FROM Into FROM .. SELECT Does Not “Fix” SQL
Setting the JDBC Statement.setFetchSize() to 1 for Single Row Queries
An interesting hint by Vladimir Sitnikov has made me think about a new benchmark for jOOQ: https://twitter.com/lukaseder/status/1407662449331949568 The benchmark should check whether single row queries should have a JDBC Statement.setFetchSize(1) call made to them by default. The Javadoc of the method says: Gives the JDBC driver a hint as to the number of rows that … Continue reading Setting the JDBC Statement.setFetchSize() to 1 for Single Row Queries
Nested Transactions in jOOQ
Since jOOQ 3.4, we have an API that simplifies transactional logic on top of JDBC in jOOQ, and starting from jOOQ 3.17 and #13502, an equivalent API will also be made available on top of R2DBC, for reactive applications. As with everything jOOQ, transactions are implemented using explicit, API based logic. The implicit logic implemented … Continue reading Nested Transactions in jOOQ