# jOOQ

Best Practices and Lessons Learned from Writing Awesome Java and SQL Code. Get some hands-on insight on what's behind developing jOOQ.

This is one page of public article previews, not the complete archive. Follow Next page to continue. Summaries are not the original full articles.

## Simplifying ANTI JOIN with jOOQ Syntax

DevFeed: [Simplifying ANTI JOIN with jOOQ Syntax](<https://devfeed.tech/articles/simplifying-anti-join-with-jooq-syntax-28960.md>)

Original publisher: [Read original article](<https://blog.jooq.org/simplifying-anti-join-with-jooq-syntax/>)

Author: lukaseder

Published: 2026-05-07T11:26:46Z

Content type: tutorial

Language: en

Sources: [jOOQ](<https://devfeed.tech/sources/jooq.md>)

Topics: [SQL](<https://devfeed.tech/topics/sql.md>), [rdbms](<https://devfeed.tech/topics/rdbms.md>), [clickhouse](<https://devfeed.tech/topics/clickhouse.md>), [databricks](<https://devfeed.tech/topics/databricks.md>)

Tags: [anti-join](<https://devfeed.tech/tags/anti-join.md>), [clickhouse](<https://devfeed.tech/tags/clickhouse.md>), [databricks](<https://devfeed.tech/tags/databricks.md>), [join-paths](<https://devfeed.tech/tags/join-paths.md>), [jooq](<https://devfeed.tech/tags/jooq.md>), [jooq-in-use](<https://devfeed.tech/tags/jooq-in-use.md>), [sql](<https://devfeed.tech/tags/sql.md>), [sql-syntax](<https://devfeed.tech/tags/sql-syntax.md>)

### AI overview

The article explains how to express an anti join more concisely with jOOQ. It shows that jOOQ can render native anti-join syntax for supported RDBMSs such as ClickHouse and Databricks, translate it for other systems, and use implicit correlated path joins introduced in jOOQ 3.19.

### Source excerpt

ANTI JOIN is a very useful operator from relational algebra. Regrettably, only few dialects support it in terms of SQL syntax, as we've written earlier. In jOOQ, you can write it as follows: If your RDBMS supports this natively (e.g. ClickHouse, Databricks), then it is rendered as such. Otherwise, jOOQ will translate this to: But ... Continue reading Simplifying ANTI JOIN with jOOQ Syntax ->

## Why JOIN USING Can Lead to Errors in SQL

DevFeed: [Why JOIN USING Can Lead to Errors in SQL](<https://devfeed.tech/articles/why-join-using-can-lead-to-errors-in-sql-28973.md>)

Original publisher: [Read original article](<https://blog.jooq.org/why-join-using-can-lead-to-errors-in-sql/>)

Author: lukaseder

Published: 2026-05-04T14:02:39Z

Content type: tutorial

Language: en

Sources: [jOOQ](<https://devfeed.tech/sources/jooq.md>)

Topics: [SQL](<https://devfeed.tech/topics/sql.md>), [Databases](<https://devfeed.tech/topics/databases.md>), [PostgreSQL](<https://devfeed.tech/topics/postgresql.md>)

Tags: [audit-columns](<https://devfeed.tech/tags/audit-columns.md>), [errors](<https://devfeed.tech/tags/errors.md>), [join](<https://devfeed.tech/tags/join.md>), [postgresql](<https://devfeed.tech/tags/postgresql.md>), [production](<https://devfeed.tech/tags/production.md>), [schema](<https://devfeed.tech/tags/schema.md>), [sql](<https://devfeed.tech/tags/sql.md>), [statement](<https://devfeed.tech/tags/statement.md>), [syntax](<https://devfeed.tech/tags/syntax.md>), [table](<https://devfeed.tech/tags/table.md>)

### AI overview

The article explains why JOIN USING and NATURAL JOIN can make SQL queries fragile as schemas evolve. Adding a same-named column can introduce ambiguity or change join behavior, causing existing queries to fail, so explicit join conditions are recommended for production queries.

### Source excerpt

Some SQL operators are as esoteric as they're powerful. One of the oldest operator that you've likely hardly ever used in real world applications is NATURAL JOIN which is the default in relational algebra. We've covered a funky use-case for NATURAL JOIN earlier on this blog. The main reason why it's not very useful is ... Continue reading Why JOIN USING Can Lead to Errors in SQL ->

## Managing Sensitive Data in jOOQ 3.21+ Logs

DevFeed: [Managing Sensitive Data in jOOQ 3.21+ Logs](<https://devfeed.tech/articles/managing-sensitive-data-in-jooq-3-21-logs-28956.md>)

Original publisher: [Read original article](<https://blog.jooq.org/managing-sensitive-data-in-jooq-3-21-logs/>)

Author: lukaseder

Published: 2026-03-27T12:45:19Z

Content type: tutorial

Language: en

Sources: [jOOQ](<https://devfeed.tech/sources/jooq.md>)

Topics: [Logging](<https://devfeed.tech/topics/logging.md>), [sensitive data](<https://devfeed.tech/topics/sensitive-data.md>), [Security](<https://devfeed.tech/topics/security.md>), [configuration](<https://devfeed.tech/topics/configuration.md>), [Code](<https://devfeed.tech/topics/code.md>)

Tags: [configuration](<https://devfeed.tech/tags/configuration.md>), [csv](<https://devfeed.tech/tags/csv.md>), [debug-logging](<https://devfeed.tech/tags/debug-logging.md>), [jooq](<https://devfeed.tech/tags/jooq.md>), [jooq-in-use](<https://devfeed.tech/tags/jooq-in-use.md>), [json](<https://devfeed.tech/tags/json.md>), [logging](<https://devfeed.tech/tags/logging.md>), [logs](<https://devfeed.tech/tags/logs.md>), [maven](<https://devfeed.tech/tags/maven.md>), [redacted-columns](<https://devfeed.tech/tags/redacted-columns.md>), [security](<https://devfeed.tech/tags/security.md>), [sensitive-data](<https://devfeed.tech/tags/sensitive-data.md>), [xml](<https://devfeed.tech/tags/xml.md>)

### AI overview

This article explains how jOOQ debug logging can expose query bind values and fetched records in application logs, creating security concerns in production. It describes a code-generation configuration available in commercial jOOQ distributions to mask exported data, including text, HTML, and optionally CSV, JSON, or XML output.

### Source excerpt

One of jOOQ's most popular feature is the out-of-the-box debug logging experience. jOOQ developers find this feature very useful when developing their applications. Assuming you run a jOOQ query and configure your logger to print DEBUG log output: When this query is executed, your log output might contain something like this: Executing query : select ... Continue reading Managing Sensitive Data in jOOQ 3.21+ Logs ->

## Consider using JSON arrays instead of JSON objects for serialisation

DevFeed: [Consider using JSON arrays instead of JSON objects for serialisation](<https://devfeed.tech/articles/consider-using-json-arrays-instead-of-json-objects-for-serialisation-28935.md>)

Original publisher: [Read original article](<https://blog.jooq.org/consider-using-json-arrays-instead-of-json-objects-for-serialisation/>)

Author: lukaseder

Published: 2025-08-11T12:43:10Z

Content type: opinion

Language: en

Sources: [jOOQ](<https://devfeed.tech/sources/jooq.md>)

Topics: [JSON](<https://devfeed.tech/topics/json.md>), [SQL](<https://devfeed.tech/topics/sql.md>), [Databases](<https://devfeed.tech/topics/databases.md>), [Structured-data](<https://devfeed.tech/topics/structured-data.md>)

Tags: [api](<https://devfeed.tech/tags/api.md>), [compression](<https://devfeed.tech/tags/compression.md>), [java](<https://devfeed.tech/tags/java.md>), [jooq](<https://devfeed.tech/tags/jooq.md>), [json](<https://devfeed.tech/tags/json.md>), [json-array](<https://devfeed.tech/tags/json-array.md>), [json-object](<https://devfeed.tech/tags/json-object.md>), [overhead](<https://devfeed.tech/tags/overhead.md>), [performance](<https://devfeed.tech/tags/performance.md>), [sql](<https://devfeed.tech/tags/sql.md>), [sql-json](<https://devfeed.tech/tags/sql-json.md>)

### AI overview

The article explains why jOOQ serialises some internal result data as JSON arrays of arrays instead of JSON objects. Positional access can be faster and avoids problems caused by duplicate column names, but the choice is a tradeoff and depends on whether people need to inspect the JSON or consume the API.

### Source excerpt

When implementing the awesome MULTISET operator in jOOQ, its implementation mostly relied on SQL/JSON support of various RDBMS. In short, while standard SQL supports nested collections via ARRAY or MULTISET operators like this: This is poorly supported in most RDBMS, so jOOQ emulates it using SQL/JSON as follows (or similar): Wait a second. A JSON ... Continue reading Consider using JSON arrays instead of JSON objects for serialisation ->

## When SQL Meets Lambda Expressions

DevFeed: [When SQL Meets Lambda Expressions](<https://devfeed.tech/articles/when-sql-meets-lambda-expressions-28971.md>)

Original publisher: [Read original article](<https://blog.jooq.org/when-sql-meets-lambda-expressions/>)

Author: lukaseder

Published: 2025-03-27T13:04:44Z

Content type: tutorial

Language: en

Sources: [jOOQ](<https://devfeed.tech/sources/jooq.md>)

Topics: [SQL](<https://devfeed.tech/topics/sql.md>), [Structured-data](<https://devfeed.tech/topics/structured-data.md>), [DuckDB](<https://devfeed.tech/topics/duckdb.md>), [clickhouse](<https://devfeed.tech/topics/clickhouse.md>), [databricks](<https://devfeed.tech/topics/databricks.md>), [PostgreSQL](<https://devfeed.tech/topics/postgresql.md>)

Tags: [apply](<https://devfeed.tech/tags/apply.md>), [array](<https://devfeed.tech/tags/array.md>), [array-types](<https://devfeed.tech/tags/array-types.md>), [arrays](<https://devfeed.tech/tags/arrays.md>), [clickhouse](<https://devfeed.tech/tags/clickhouse.md>), [concatenation](<https://devfeed.tech/tags/concatenation.md>), [databricks](<https://devfeed.tech/tags/databricks.md>), [duckdb](<https://devfeed.tech/tags/duckdb.md>), [filter](<https://devfeed.tech/tags/filter.md>), [function](<https://devfeed.tech/tags/function.md>), [java](<https://devfeed.tech/tags/java.md>), [jooq](<https://devfeed.tech/tags/jooq.md>), [kotlin](<https://devfeed.tech/tags/kotlin.md>), [lambda](<https://devfeed.tech/tags/lambda.md>), [postgresql](<https://devfeed.tech/tags/postgresql.md>), [scala](<https://devfeed.tech/tags/scala.md>), [snowflake](<https://devfeed.tech/tags/snowflake.md>), [sql](<https://devfeed.tech/tags/sql.md>), [trino](<https://devfeed.tech/tags/trino.md>)

### AI overview

This article explains how SQL ARRAY types and lambda expressions are supported across several modern SQL dialects. It shows how jOOQ maps Java, Kotlin, and Scala lambda expressions to SQL expressions, including filtering arrays, and describes emulation with subqueries for dialects without lambda syntax.

### Source excerpt

ARRAY types are a part of the ISO/IEC 9075 SQL standard. The standard specifies how to: But it is very unopinionated when it comes to function support. The ISO/IEC 9075-2:2023(E) 6.47 <array value expression> specifies concatenation of arrays, whereas the 6.48 <array value function> section lists a not extremely useful TRIM_ARRAY function, exclusively (using which ... Continue reading When SQL Meets Lambda Expressions ->

## Think About SQL MERGE in Terms of a RIGHT JOIN

DevFeed: [Think About SQL MERGE in Terms of a RIGHT JOIN](<https://devfeed.tech/articles/think-about-sql-merge-in-terms-of-a-right-join-28967.md>)

Original publisher: [Read original article](<https://blog.jooq.org/think-about-sql-merge-in-terms-of-a-right-join/>)

Author: lukaseder

Published: 2025-03-13T14:45:53Z

Content type: tutorial

Language: en

Sources: [jOOQ](<https://devfeed.tech/sources/jooq.md>)

Topics: [SQL](<https://devfeed.tech/topics/sql.md>), [etl](<https://devfeed.tech/topics/etl.md>), [rdbms](<https://devfeed.tech/topics/rdbms.md>)

Tags: [databricks](<https://devfeed.tech/tags/databricks.md>), [etl](<https://devfeed.tech/tags/etl.md>), [firebird](<https://devfeed.tech/tags/firebird.md>), [merge](<https://devfeed.tech/tags/merge.md>), [outer-join](<https://devfeed.tech/tags/outer-join.md>), [postgresql](<https://devfeed.tech/tags/postgresql.md>), [right-join](<https://devfeed.tech/tags/right-join.md>), [schema](<https://devfeed.tech/tags/schema.md>), [semantics](<https://devfeed.tech/tags/semantics.md>), [source](<https://devfeed.tech/tags/source.md>), [sql](<https://devfeed.tech/tags/sql.md>), [sql-server](<https://devfeed.tech/tags/sql-server.md>), [statement](<https://devfeed.tech/tags/statement.md>), [syntax](<https://devfeed.tech/tags/syntax.md>), [table](<https://devfeed.tech/tags/table.md>), [target-table](<https://devfeed.tech/tags/target-table.md>), [when-matched](<https://devfeed.tech/tags/when-matched.md>), [when-not-matched](<https://devfeed.tech/tags/when-not-matched.md>), [when-not-matched-by-source](<https://devfeed.tech/tags/when-not-matched-by-source.md>)

### AI overview

The article explains SQL MERGE by relating its target-and-source behavior to a RIGHT JOIN. It covers updating matching rows, inserting nonmatching rows, deletion behavior, and the restriction against duplicate matches.

### Source excerpt

RIGHT JOIN is an esoteric feature in the SQL language, and hardly ever seen in the real world, because almost every RIGHT JOIN can just be expressed as an equivalent LEFT JOIN. The following two statements are equivalent: It's not unreasonable to expect these two statements to produce the same execution plan on most RDBMS, ... Continue reading Think About SQL MERGE in Terms of a RIGHT JOIN ->

## Resisting the Urge to Document Everything Everywhere

DevFeed: [Resisting the Urge to Document Everything Everywhere](<https://devfeed.tech/articles/resisting-the-urge-to-document-everything-everywhere-28958.md>)

Original publisher: [Read original article](<https://blog.jooq.org/resisting-the-urge-to-document-everything-everywhere/>)

Author: lukaseder

Published: 2025-02-28T10:39:07Z

Content type: opinion

Language: en

Sources: [jOOQ](<https://devfeed.tech/sources/jooq.md>)

Topics: [jooq](<https://devfeed.tech/topics/jooq.md>), [SQL](<https://devfeed.tech/topics/sql.md>), [generics](<https://devfeed.tech/topics/generics.md>)

Tags: [caveat](<https://devfeed.tech/tags/caveat.md>), [documentation](<https://devfeed.tech/tags/documentation.md>), [generics](<https://devfeed.tech/tags/generics.md>), [jooq](<https://devfeed.tech/tags/jooq.md>), [jooq-development](<https://devfeed.tech/tags/jooq-development.md>), [quirk](<https://devfeed.tech/tags/quirk.md>), [sql](<https://devfeed.tech/tags/sql.md>), [stack-overflow](<https://devfeed.tech/tags/stack-overflow.md>), [xyproblem](<https://devfeed.tech/tags/xyproblem.md>)

### AI overview

The article argues that product teams should resist documenting every workaround alongside the feature that exposed a user's problem. Using jOOQ as an example, it explains that unrelated features can be difficult to discover even when one helps address a quirk or limitation in another.

### Source excerpt

Every product manager knows this situation: This is such a common pattern, and while it's perfectly understandable for such a user to request this, it is so terribly wrong to give in to this user's request. Why is it wrong? The features are unrelated Most of the time, the two features X1 and X2 are ... Continue reading Resisting the Urge to Document Everything Everywhere ->

## jOOQ 3.20 released with ClickHouse, Databricks, and much more DuckDB support, new modules, Oracle type hierarchies, more spatial support, decfloat and synonym support, hidden columns, Scala 3, Kotlin 2, and much more

DevFeed: [jOOQ 3.20 released with ClickHouse, Databricks, and much more DuckDB support, new modules, Oracle type hierarchies, more spatial support, decfloat and synonym support, hidden columns, Scala 3, Kotlin 2, and much more](<https://devfeed.tech/articles/jooq-3-20-released-with-clickhouse-databricks-and-much-more-duckdb-support-new-modules-oracle-type-hierarchies-more-spatial-support-decfloat-and-synonym-support-hidden-co-28953.md>)

Original publisher: [Read original article](<https://blog.jooq.org/jooq-3-20-released-with-clickhouse-databricks-and-much-more-duckdb-support-new-modules-oracle-type-hierarchies-more-spatial-support-decfloat-and-synonym-support-hidden-columns-scala-3-kotlin/>)

Author: lukaseder

Published: 2025-02-20T10:27:54Z

Content type: release

Language: en

Sources: [jOOQ](<https://devfeed.tech/sources/jooq.md>)

Topics: [clickhouse](<https://devfeed.tech/topics/clickhouse.md>), [databricks](<https://devfeed.tech/topics/databricks.md>), [SQL](<https://devfeed.tech/topics/sql.md>), [DuckDB](<https://devfeed.tech/topics/duckdb.md>), [modules](<https://devfeed.tech/topics/modules.md>), [Oracle Database](<https://devfeed.tech/topics/oracle-database.md>), [Kotlin](<https://devfeed.tech/topics/kotlin.md>), [Scala](<https://devfeed.tech/topics/scala.md>), [reactive](<https://devfeed.tech/topics/reactive.md>), [jpa](<https://devfeed.tech/topics/jpa.md>)

Tags: [array](<https://devfeed.tech/tags/array.md>), [clickhouse](<https://devfeed.tech/tags/clickhouse.md>), [databricks](<https://devfeed.tech/tags/databricks.md>), [decfloat](<https://devfeed.tech/tags/decfloat.md>), [dirty-tracking](<https://devfeed.tech/tags/dirty-tracking.md>), [dml-join](<https://devfeed.tech/tags/dml-join.md>), [duckdb](<https://devfeed.tech/tags/duckdb.md>), [hidden-columns](<https://devfeed.tech/tags/hidden-columns.md>), [jdk-21](<https://devfeed.tech/tags/jdk-21.md>), [jooq](<https://devfeed.tech/tags/jooq.md>), [jooq-3-20](<https://devfeed.tech/tags/jooq-3-20.md>), [jooq-development](<https://devfeed.tech/tags/jooq-development.md>), [jpa](<https://devfeed.tech/tags/jpa.md>), [json](<https://devfeed.tech/tags/json.md>), [kotlin](<https://devfeed.tech/tags/kotlin.md>), [kotlin-2](<https://devfeed.tech/tags/kotlin-2.md>), [merge](<https://devfeed.tech/tags/merge.md>), [modules](<https://devfeed.tech/tags/modules.md>), [oracle](<https://devfeed.tech/tags/oracle.md>), [oracle-plsql-types](<https://devfeed.tech/tags/oracle-plsql-types.md>), [r2dbc](<https://devfeed.tech/tags/r2dbc.md>), [reactive](<https://devfeed.tech/tags/reactive.md>), [scala](<https://devfeed.tech/tags/scala.md>), [scala-3](<https://devfeed.tech/tags/scala-3.md>), [sequences](<https://devfeed.tech/tags/sequences.md>), [spatial](<https://devfeed.tech/tags/spatial.md>), [sql](<https://devfeed.tech/tags/sql.md>)

### AI overview

jOOQ 3.20 introduces experimental ClickHouse and Databricks SQL dialects, improves DuckDB support, adds new integration modules, and expands support for Oracle type hierarchies, spatial features, decfloat, synonyms, hidden columns, Scala 3, and Kotlin 2.

### Source excerpt

New dialects: jOOQ 3.20 ships with 2 new experimental dialects: ClickHouse is a fast-moving SQL dialect with a historic vendor-specific syntax that is gradually migrated to a more standards compliant alternative, which is why our support is still experimental. A lot of behaviours differ from what one would expect elsewhere, including NULL handling, which is ... Continue reading jOOQ 3.20 released with ClickHouse, Databricks, and much more DuckDB support, new modules, Oracle type hierarchies, more spatial support, decfloat and synonym support, hidden columns, Scala 3, Kotlin 2, and much more ->

## Emulating SQL FILTER with Oracle JSON Aggregate Functions

DevFeed: [Emulating SQL FILTER with Oracle JSON Aggregate Functions](<https://devfeed.tech/articles/emulating-sql-filter-with-oracle-json-aggregate-functions-28937.md>)

Original publisher: [Read original article](<https://blog.jooq.org/emulating-sql-filter-with-oracle-json-aggregate-functions/>)

Author: lukaseder

Published: 2024-06-03T12:17:41Z

Content type: article

Language: en

Sources: [jOOQ](<https://devfeed.tech/sources/jooq.md>)

Topics: [Oracle Database](<https://devfeed.tech/topics/oracle-database.md>), [SQL](<https://devfeed.tech/topics/sql.md>), [JSON](<https://devfeed.tech/topics/json.md>)

Tags: [aggregate-functions](<https://devfeed.tech/tags/aggregate-functions.md>), [filter](<https://devfeed.tech/tags/filter.md>), [filter-clause](<https://devfeed.tech/tags/filter-clause.md>), [jooq-3-20](<https://devfeed.tech/tags/jooq-3-20.md>), [json](<https://devfeed.tech/tags/json.md>), [json-aggregate-functions](<https://devfeed.tech/tags/json-aggregate-functions.md>), [json-arrayagg](<https://devfeed.tech/tags/json-arrayagg.md>), [json-objectagg](<https://devfeed.tech/tags/json-objectagg.md>), [oracle](<https://devfeed.tech/tags/oracle.md>), [sql](<https://devfeed.tech/tags/sql.md>), [sql-json](<https://devfeed.tech/tags/sql-json.md>)

### AI overview

This article explains how to emulate the SQL FILTER clause for JSON aggregate functions, particularly in Oracle. It presents a workaround that wraps legitimate data in arrays, filters out emulation-generated rows, and then unwraps the arrays. jOOQ 3.20 will implement these emulations.

### Source excerpt

A cool standard SQL:2003 feature is the aggregate FILTER clause, which is supported natively by at least these RDBMS: The following aggregate function computes the number of rows per group which satifsy the FILTER clause: This is useful for pivot style queries, where multiple aggregate values are computed in one go. For most basic types ... Continue reading Emulating SQL FILTER with Oracle JSON Aggregate Functions ->

## Getting Top 1 Values Per Group in Oracle

DevFeed: [Getting Top 1 Values Per Group in Oracle](<https://devfeed.tech/articles/getting-top-1-values-per-group-in-oracle-28939.md>)

Original publisher: [Read original article](<https://blog.jooq.org/getting-top-1-values-per-group-in-oracle/>)

Author: lukaseder

Published: 2024-03-01T09:55:41Z

Content type: tutorial

Language: en

Sources: [jOOQ](<https://devfeed.tech/sources/jooq.md>)

Topics: [ordering](<https://devfeed.tech/topics/ordering.md>), [JSON](<https://devfeed.tech/topics/json.md>)

Tags: [aggregate-functions](<https://devfeed.tech/tags/aggregate-functions.md>), [any-value](<https://devfeed.tech/tags/any-value.md>), [function](<https://devfeed.tech/tags/function.md>), [json](<https://devfeed.tech/tags/json.md>), [keep](<https://devfeed.tech/tags/keep.md>), [oracle](<https://devfeed.tech/tags/oracle.md>), [ordering](<https://devfeed.tech/tags/ordering.md>), [sql](<https://devfeed.tech/tags/sql.md>), [syntax](<https://devfeed.tech/tags/syntax.md>), [top-1-per-category](<https://devfeed.tech/tags/top-1-per-category.md>), [type-safety](<https://devfeed.tech/tags/type-safety.md>), [types](<https://devfeed.tech/tags/types.md>)

### AI overview

A tutorial on selecting the top one or top n values per group in Oracle. It discusses ordering and ties, repetition when projecting multiple columns, Oracle object types and ANY_VALUE in Oracle 21c, and JSON as an alternative workaround.

### Source excerpt

I've blogged about generic ways of getting top 1 or top n per category queries before on this blog. An Oracle specific version in that post used the arcane KEEP syntax: This is a bit difficult to read when you see it for the first time. Think of it as a complicated way to say ... Continue reading Getting Top 1 Values Per Group in Oracle ->

## An Efficient Way to Check for Existence of Multiple Values in SQL

DevFeed: [An Efficient Way to Check for Existence of Multiple Values in SQL](<https://devfeed.tech/articles/an-efficient-way-to-check-for-existence-of-multiple-values-in-sql-28931.md>)

Original publisher: [Read original article](<https://blog.jooq.org/an-efficient-way-to-check-for-existence-of-multiple-values-in-sql/>)

Author: lukaseder

Published: 2024-02-16T14:16:45Z

Content type: tutorial

Language: en

Sources: [jOOQ](<https://devfeed.tech/sources/jooq.md>)

Topics: [SQL](<https://devfeed.tech/topics/sql.md>), [Benchmark](<https://devfeed.tech/topics/benchmark.md>), [Oracle Database](<https://devfeed.tech/topics/oracle-database.md>), [PostgreSQL](<https://devfeed.tech/topics/postgresql.md>)

Tags: [benchmark](<https://devfeed.tech/tags/benchmark.md>), [benchmarking](<https://devfeed.tech/tags/benchmarking.md>), [compare](<https://devfeed.tech/tags/compare.md>), [count](<https://devfeed.tech/tags/count.md>), [database](<https://devfeed.tech/tags/database.md>), [derived-table](<https://devfeed.tech/tags/derived-table.md>), [execution](<https://devfeed.tech/tags/execution.md>), [exists](<https://devfeed.tech/tags/exists.md>), [join](<https://devfeed.tech/tags/join.md>), [limit](<https://devfeed.tech/tags/limit.md>), [mysql](<https://devfeed.tech/tags/mysql.md>), [oracle](<https://devfeed.tech/tags/oracle.md>), [oracle-23c](<https://devfeed.tech/tags/oracle-23c.md>), [performance](<https://devfeed.tech/tags/performance.md>), [postgresql](<https://devfeed.tech/tags/postgresql.md>), [rdbms](<https://devfeed.tech/tags/rdbms.md>), [sql](<https://devfeed.tech/tags/sql.md>), [sql-performance](<https://devfeed.tech/tags/sql-performance.md>), [sql-server](<https://devfeed.tech/tags/sql-server.md>), [sql-syntax](<https://devfeed.tech/tags/sql-syntax.md>), [top](<https://devfeed.tech/tags/top.md>)

### AI overview

This article explains how to check whether multiple matching rows exist in SQL. It recommends limiting rows in a derived table before counting them, then compares execution plans and benchmark results for PostgreSQL 15 and Oracle 23c.

### Source excerpt

In a previous blog post, we've advertised the use of SQL EXISTS rather than COUNT(*) to check for existence of a value in SQL. I.e. to check if in the Sakila database, actors called WAHLBERG have played in any films, instead of: Do this: (Depending on your dialect you may require a FROM DUAL clause, ... Continue reading An Efficient Way to Check for Existence of Multiple Values in SQL ->

## A Hidden Benefit of Implicit Joins: Join Elimination

DevFeed: [A Hidden Benefit of Implicit Joins: Join Elimination](<https://devfeed.tech/articles/a-hidden-benefit-of-implicit-joins-join-elimination-28930.md>)

Original publisher: [Read original article](<https://blog.jooq.org/a-hidden-benefit-of-implicit-joins-join-elimination/>)

Author: lukaseder

Published: 2024-01-10T14:19:00Z

Content type: article

Language: en

Sources: [jOOQ](<https://devfeed.tech/sources/jooq.md>)

Topics: [SQL](<https://devfeed.tech/topics/sql.md>), [Databases](<https://devfeed.tech/topics/databases.md>), [rdbms](<https://devfeed.tech/topics/rdbms.md>)

Tags: [implicit-joins](<https://devfeed.tech/tags/implicit-joins.md>), [implicit-path-join](<https://devfeed.tech/tags/implicit-path-join.md>), [java](<https://devfeed.tech/tags/java.md>), [join-elimination](<https://devfeed.tech/tags/join-elimination.md>), [jooq](<https://devfeed.tech/tags/jooq.md>), [jooq-3-19](<https://devfeed.tech/tags/jooq-3-19.md>), [jooq-in-use](<https://devfeed.tech/tags/jooq-in-use.md>), [many-to-many-joins](<https://devfeed.tech/tags/many-to-many-joins.md>), [optimisation](<https://devfeed.tech/tags/optimisation.md>), [path-joins](<https://devfeed.tech/tags/path-joins.md>), [sql](<https://devfeed.tech/tags/sql.md>), [to-many-joins](<https://devfeed.tech/tags/to-many-joins.md>)

### AI overview

This article explains how jOOQ 3.19 can eliminate unnecessary tables from implicit path joins when no columns from those tables are referenced. Primary and foreign keys preserve equivalence, while referenced columns cause the table to remain in the join tree. The behavior applies to implicit path joins and depends on a flag that is disabled by default.

### Source excerpt

One of jOOQ's key features so far has always been to render pretty much exactly the SQL that users expect, without any surprises - unless some emulation is required to make a query work, of course. This means that while join elimination is a powerful feature of many RDBMS, it isn't part of jOOQ's feature ... Continue reading A Hidden Benefit of Implicit Joins: Join Elimination ->

## jOOQ 3.19's new Explicit and Implicit to-many path joins

DevFeed: [jOOQ 3.19's new Explicit and Implicit to-many path joins](<https://devfeed.tech/articles/jooq-3-19-s-new-explicit-and-implicit-to-many-path-joins-28952.md>)

Original publisher: [Read original article](<https://blog.jooq.org/jooq-3-19s-new-explicit-and-implicit-to-many-path-joins/>)

Author: lukaseder

Published: 2023-12-28T14:35:14Z

Content type: release

Language: en

Sources: [jOOQ](<https://devfeed.tech/sources/jooq.md>)

Topics: [jooq](<https://devfeed.tech/topics/jooq.md>), [jpa](<https://devfeed.tech/topics/jpa.md>), [SQL](<https://devfeed.tech/topics/sql.md>)

Tags: [correlated-subqueries](<https://devfeed.tech/tags/correlated-subqueries.md>), [explicit-path-joins](<https://devfeed.tech/tags/explicit-path-joins.md>), [implicit-join](<https://devfeed.tech/tags/implicit-join.md>), [implicit-join-path-correlation](<https://devfeed.tech/tags/implicit-join-path-correlation.md>), [implicit-joins](<https://devfeed.tech/tags/implicit-joins.md>), [java](<https://devfeed.tech/tags/java.md>), [jooq](<https://devfeed.tech/tags/jooq.md>), [jooq-3-19](<https://devfeed.tech/tags/jooq-3-19.md>), [jooq-development](<https://devfeed.tech/tags/jooq-development.md>), [path-joins](<https://devfeed.tech/tags/path-joins.md>), [sql](<https://devfeed.tech/tags/sql.md>), [to-many-path-joins](<https://devfeed.tech/tags/to-many-path-joins.md>)

### AI overview

This article describes jOOQ 3.19's support for explicit path joins and new to-many path joins. It explains how path-based joins can simplify SQL queries, derive join predicates from foreign-key metadata, and allow per-query control over join types.

### Source excerpt

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: What are these features? Many ORMs (e.g. JPA, Doctrine, jOOQ 3.11 and others) support "path joins" (they may have different names for this concept). A path join is a join derived from ... Continue reading jOOQ 3.19's new Explicit and Implicit to-many path joins ->

## Workaround for MySQL's "can't specify target table for update in FROM clause" Error

DevFeed: [Workaround for MySQL's "can't specify target table for update in FROM clause" Error](<https://devfeed.tech/articles/workaround-for-mysql-s-can-t-specify-target-table-for-update-in-from-clause-error-28975.md>)

Original publisher: [Read original article](<https://blog.jooq.org/workaround-for-mysqls-cant-specify-target-table-for-update-in-from-clause-error/>)

Author: lukaseder

Published: 2023-12-20T14:20:29Z

Content type: tutorial

Language: en

Sources: [jOOQ](<https://devfeed.tech/sources/jooq.md>)

Topics: [MySQL](<https://devfeed.tech/topics/mysql.md>), [SQL](<https://devfeed.tech/topics/sql.md>), [bug](<https://devfeed.tech/topics/bug.md>), [rdbms](<https://devfeed.tech/topics/rdbms.md>)

Tags: [bug](<https://devfeed.tech/tags/bug.md>), [for-update](<https://devfeed.tech/tags/for-update.md>), [from-clause](<https://devfeed.tech/tags/from-clause.md>), [jooq-in-use](<https://devfeed.tech/tags/jooq-in-use.md>), [mariadb](<https://devfeed.tech/tags/mariadb.md>), [mysql](<https://devfeed.tech/tags/mysql.md>), [rdbms](<https://devfeed.tech/tags/rdbms.md>), [sql](<https://devfeed.tech/tags/sql.md>), [table](<https://devfeed.tech/tags/table.md>), [target-table](<https://devfeed.tech/tags/target-table.md>), [update](<https://devfeed.tech/tags/update.md>)

### AI overview

The article explains MySQL error 1093, which occurs when an UPDATE statement references its target table in a FROM clause. It presents a jOOQ transformation as a workaround and notes that similar workarounds appear in the MySQL documentation.

### Source excerpt

In MySQL, you cannot do this: The UPDATE statement will raise an error as follows: SQL Error [1093] [HY000]: You can't specify target table 't' for update in FROM clause People have considered this to be a bug in MySQL for ages, as most other RDBMS can do this without any issues, including MySQL clones: ... Continue reading Workaround for MySQL's "can't specify target table for update in FROM clause" Error ->

## 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

DevFeed: [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](<https://devfeed.tech/articles/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-d-28951.md>)

Original publisher: [Read original article](<https://blog.jooq.org/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/>)

Author: lukaseder

Published: 2023-12-15T16:30:41Z

Content type: release

Language: en

Sources: [jOOQ](<https://devfeed.tech/sources/jooq.md>)

Topics: [releases](<https://devfeed.tech/topics/releases.md>), [DuckDB](<https://devfeed.tech/topics/duckdb.md>), [CockroachDB](<https://devfeed.tech/topics/cockroachdb.md>), [Maven](<https://devfeed.tech/topics/maven.md>), [Gradle](<https://devfeed.tech/topics/gradle.md>), [rdbms](<https://devfeed.tech/topics/rdbms.md>), [Multi-tenancy](<https://devfeed.tech/topics/multi-tenancy.md>)

Tags: [cockroachdb](<https://devfeed.tech/tags/cockroachdb.md>), [cockroachdb-23](<https://devfeed.tech/tags/cockroachdb-23.md>), [duckdb](<https://devfeed.tech/tags/duckdb.md>), [gradle](<https://devfeed.tech/tags/gradle.md>), [gradle-plugin](<https://devfeed.tech/tags/gradle-plugin.md>), [implicit-joins](<https://devfeed.tech/tags/implicit-joins.md>), [java-8](<https://devfeed.tech/tags/java-8.md>), [join-paths](<https://devfeed.tech/tags/join-paths.md>), [jooq](<https://devfeed.tech/tags/jooq.md>), [jooq-3-19](<https://devfeed.tech/tags/jooq-3-19.md>), [jooq-development](<https://devfeed.tech/tags/jooq-development.md>), [maven](<https://devfeed.tech/tags/maven.md>), [maven-repository](<https://devfeed.tech/tags/maven-repository.md>), [multi-tenancy](<https://devfeed.tech/tags/multi-tenancy.md>), [oracle-23c](<https://devfeed.tech/tags/oracle-23c.md>), [policies](<https://devfeed.tech/tags/policies.md>), [release-notes](<https://devfeed.tech/tags/release-notes.md>), [releases](<https://devfeed.tech/tags/releases.md>), [row-level-security](<https://devfeed.tech/tags/row-level-security.md>), [security](<https://devfeed.tech/tags/security.md>), [trigger-meta-data](<https://devfeed.tech/tags/trigger-meta-data.md>), [triggers](<https://devfeed.tech/tags/triggers.md>), [trino](<https://devfeed.tech/tags/trino.md>), [trinodb](<https://devfeed.tech/tags/trinodb.md>), [udt-paths](<https://devfeed.tech/tags/udt-paths.md>), [udts](<https://devfeed.tech/tags/udts.md>)

### AI overview

The jOOQ 3.19.0 release adds experimental DuckDB support, Trino support, and support for CockroachDB 23 and Oracle 23c. It also improves join paths, introduces an official Gradle plugin and a commercial Maven repository, and adds policies for row-level security.

### Source excerpt

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: These dialects are available in all jOOQ editions. New dialect versions In addition to these entirely new dialects, big new CockroachDB and Oracle versions have shipped: ... 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 ->

## Maven Coordinates of the most popular JDBC Drivers

DevFeed: [Maven Coordinates of the most popular JDBC Drivers](<https://devfeed.tech/articles/maven-coordinates-of-the-most-popular-jdbc-drivers-28957.md>)

Original publisher: [Read original article](<https://blog.jooq.org/maven-coordinates-of-the-most-popular-jdbc-drivers/>)

Author: lukaseder

Published: 2023-12-13T11:25:44Z

Content type: tutorial

Language: en

Sources: [jOOQ](<https://devfeed.tech/sources/jooq.md>)

Topics: [Maven](<https://devfeed.tech/topics/maven.md>), [Maven Central](<https://devfeed.tech/topics/maven-central.md>), [Database](<https://devfeed.tech/topics/database.md>)

Tags: [database](<https://devfeed.tech/tags/database.md>), [drivers](<https://devfeed.tech/tags/drivers.md>), [integration](<https://devfeed.tech/tags/integration.md>), [java](<https://devfeed.tech/tags/java.md>), [jdbc](<https://devfeed.tech/tags/jdbc.md>), [jdbc-drivers](<https://devfeed.tech/tags/jdbc-drivers.md>), [jooq](<https://devfeed.tech/tags/jooq.md>), [maven](<https://devfeed.tech/tags/maven.md>), [maven-central](<https://devfeed.tech/tags/maven-central.md>), [sql](<https://devfeed.tech/tags/sql.md>)

### AI overview

A list of Maven coordinates for popular JDBC drivers used in jOOQ integration tests, with guidance to verify current versions on Maven Central and check driver licenses before downloading.

### Source excerpt

Do you need to add a JDBC driver to your application, and don't know its Maven coordinates? This blog post lists the most popular drivers from the jOOQ integration tests. Look up the latest versions directly on https://central.sonatype.com/ with parameters g:groupId a:artifactId, for example, the H2 database and driver: https://central.sonatype.com/search?q=g%3Acom.h2database+a%3Ah2 The list only includes drivers ... Continue reading Maven Coordinates of the most popular JDBC Drivers ->

## jOOQ DAOs, CRUD Access, and the N+1 Query Problem

DevFeed: [jOOQ DAOs, CRUD Access, and the N+1 Query Problem](<https://devfeed.tech/articles/to-dao-or-not-to-dao-28968.md>)

Original publisher: [Read original article](<https://blog.jooq.org/to-dao-or-not-to-dao/>)

Author: lukaseder

Published: 2023-12-06T13:41:52Z

Content type: opinion

Language: en

Sources: [jOOQ](<https://devfeed.tech/sources/jooq.md>)

Topics: [CRUD](<https://devfeed.tech/topics/crud.md>), [SQL](<https://devfeed.tech/topics/sql.md>), [Domain-driven design (DDD)](<https://devfeed.tech/topics/domain-driven-design.md>), [Transactions](<https://devfeed.tech/topics/transactions.md>), [account](<https://devfeed.tech/topics/account.md>)

Tags: [account](<https://devfeed.tech/tags/account.md>), [api](<https://devfeed.tech/tags/api.md>), [crud](<https://devfeed.tech/tags/crud.md>), [dao](<https://devfeed.tech/tags/dao.md>), [data](<https://devfeed.tech/tags/data.md>), [ddd](<https://devfeed.tech/tags/ddd.md>), [developers](<https://devfeed.tech/tags/developers.md>), [join](<https://devfeed.tech/tags/join.md>), [jooq](<https://devfeed.tech/tags/jooq.md>), [jooq-in-use](<https://devfeed.tech/tags/jooq-in-use.md>), [optimisation](<https://devfeed.tech/tags/optimisation.md>), [repository](<https://devfeed.tech/tags/repository.md>), [sql](<https://devfeed.tech/tags/sql.md>), [transactions](<https://devfeed.tech/tags/transactions.md>)

### AI overview

This opinion article examines jOOQ's DAO API and the tendency to use DAOs or repositories for quick CRUD access. It argues that this approach can obscure query design and lead to inefficient patterns such as N+1 queries, while showing how explicit SQL and bulk operations can improve query structure and performance.

### Source excerpt

jOOQ's DAO API is one of jOOQ's most controversial features. When it was first implemented, it was implemented merely: There's a strong hint about the third bullet given how popular Spring Data's repository "pattern" is. A lot of developers just want to quickly fetch and store data, without giving individual queries much thought. A fun ... Continue reading To DAO or not to DAO ->

## JDBC Connection URLs of the Most Popular RDBMS

DevFeed: [JDBC Connection URLs of the Most Popular RDBMS](<https://devfeed.tech/articles/jdbc-connection-urls-of-the-most-popular-rdbms-28949.md>)

Original publisher: [Read original article](<https://blog.jooq.org/jdbc-connection-urls-of-the-most-popular-rdbms/>)

Author: lukaseder

Published: 2023-12-01T11:12:59Z

Content type: tutorial

Language: en

Sources: [jOOQ](<https://devfeed.tech/sources/jooq.md>)

Topics: [rdbms](<https://devfeed.tech/topics/rdbms.md>)

Tags: [connection-url](<https://devfeed.tech/tags/connection-url.md>), [driver](<https://devfeed.tech/tags/driver.md>), [jdbc](<https://devfeed.tech/tags/jdbc.md>), [popular](<https://devfeed.tech/tags/popular.md>), [rdbms](<https://devfeed.tech/tags/rdbms.md>), [sql](<https://devfeed.tech/tags/sql.md>)

### AI overview

A reference for finding JDBC connection URLs and driver names for popular relational database management systems.

### Source excerpt

Need to connect to your RDBMS with JDBC and don't have the JDBC connection URL or driver name at hand? No problem, just look up your RDBMS below:

## How to Generate Package Private Code with jOOQ's Code Generator

DevFeed: [How to Generate Package Private Code with jOOQ's Code Generator](<https://devfeed.tech/articles/how-to-generate-package-private-code-with-jooq-s-code-generator-28941.md>)

Original publisher: [Read original article](<https://blog.jooq.org/how-to-generate-package-private-code-with-jooqs-code-generator/>)

Author: lukaseder

Published: 2023-06-28T15:54:18Z

Content type: tutorial

Language: en

Sources: [jOOQ](<https://devfeed.tech/sources/jooq.md>)

Topics: [Code](<https://devfeed.tech/topics/code.md>), [Java](<https://devfeed.tech/topics/java.md>), [Code generation](<https://devfeed.tech/topics/code-generation.md>), [configuration](<https://devfeed.tech/topics/configuration.md>), [Java 9](<https://devfeed.tech/topics/java-9.md>)

Tags: [code](<https://devfeed.tech/tags/code.md>), [code-generation](<https://devfeed.tech/tags/code-generation.md>), [code-generator](<https://devfeed.tech/tags/code-generator.md>), [compilation](<https://devfeed.tech/tags/compilation.md>), [configuration](<https://devfeed.tech/tags/configuration.md>), [how-to](<https://devfeed.tech/tags/how-to.md>), [java](<https://devfeed.tech/tags/java.md>), [java-9](<https://devfeed.tech/tags/java-9.md>), [jooq](<https://devfeed.tech/tags/jooq.md>), [jooq-code-generator](<https://devfeed.tech/tags/jooq-code-generator.md>), [jooq-in-use](<https://devfeed.tech/tags/jooq-in-use.md>), [modularity](<https://devfeed.tech/tags/modularity.md>), [package-private](<https://devfeed.tech/tags/package-private.md>), [visibility](<https://devfeed.tech/tags/visibility.md>), [visibility-modifier](<https://devfeed.tech/tags/visibility-modifier.md>)

### AI overview

This tutorial explains Java package-private visibility and shows how to configure jOOQ's code generator to generate package-private types. The approach can help keep generated jOOQ implementation details from leaking into client code.

### Source excerpt

Java's package private visibility is an underrated feature. When you omit any visibility modifier in Java, then the default (for most objects) is package private, i.e. the object is visible only to types in the same package: In fact, a compilation unit (the .java file) can contain multiple such classes. You don't have to create ... Continue reading How to Generate Package Private Code with jOOQ's Code Generator ->

## How to Pass a Table Valued Parameter to a T-SQL Function with jOOQ

DevFeed: [How to Pass a Table Valued Parameter to a T-SQL Function with jOOQ](<https://devfeed.tech/articles/how-to-pass-a-table-valued-parameter-to-a-t-sql-function-with-jooq-28943.md>)

Original publisher: [Read original article](<https://blog.jooq.org/how-to-pass-a-table-valued-parameter-to-a-t-sql-function-with-jooq/>)

Author: lukaseder

Published: 2023-04-25T12:16:30Z

Content type: tutorial

Language: en

Sources: [jOOQ](<https://devfeed.tech/sources/jooq.md>)

Topics: [sql-server](<https://devfeed.tech/topics/sql-server.md>), [SQL](<https://devfeed.tech/topics/sql.md>), [Java](<https://devfeed.tech/topics/java.md>), [Code](<https://devfeed.tech/topics/code.md>)

Tags: [code](<https://devfeed.tech/tags/code.md>), [code-generation](<https://devfeed.tech/tags/code-generation.md>), [code-generator](<https://devfeed.tech/tags/code-generator.md>), [how-to](<https://devfeed.tech/tags/how-to.md>), [java](<https://devfeed.tech/tags/java.md>), [jdbc](<https://devfeed.tech/tags/jdbc.md>), [jooq](<https://devfeed.tech/tags/jooq.md>), [jooq-in-use](<https://devfeed.tech/tags/jooq-in-use.md>), [sql-server](<https://devfeed.tech/tags/sql-server.md>), [t-sql](<https://devfeed.tech/tags/t-sql.md>), [table-valued-function](<https://devfeed.tech/tags/table-valued-function.md>), [table-valued-parameter](<https://devfeed.tech/tags/table-valued-parameter.md>)

### AI overview

This tutorial explains how to pass a Microsoft T-SQL table-valued parameter to a function using jOOQ. It covers native T-SQL and JDBC usage, then shows how jOOQ's code generator produces Java types and methods for calling the function and accessing its result rows.

### Source excerpt

Microsoft T-SQL supports a language feature called table-valued parameter (TVP), which is a parameter of a table type that can be passed to a stored procedure or function. For example, you may write: This function takes a table-valued parameter (TVP), and produces a result set containing the cross product of the parameter table with itself. ... Continue reading How to Pass a Table Valued Parameter to a T-SQL Function with jOOQ ->

## How to Turn a List of Flat Elements into a Hierarchy in Java, SQL, or jOOQ

DevFeed: [How to Turn a List of Flat Elements into a Hierarchy in Java, SQL, or jOOQ](<https://devfeed.tech/articles/how-to-turn-a-list-of-flat-elements-into-a-hierarchy-in-java-sql-or-jooq-28945.md>)

Original publisher: [Read original article](<https://blog.jooq.org/how-to-turn-a-list-of-flat-elements-into-a-hierarchy-in-java-sql-or-jooq/>)

Author: lukaseder

Published: 2023-03-24T15:45:28Z

Content type: tutorial

Language: en

Sources: [jOOQ](<https://devfeed.tech/sources/jooq.md>)

Topics: [Java](<https://devfeed.tech/topics/java.md>), [SQL](<https://devfeed.tech/topics/sql.md>), [Jackson](<https://devfeed.tech/topics/jackson.md>), [JSON](<https://devfeed.tech/topics/json.md>), [Library](<https://devfeed.tech/topics/library.md>)

Tags: [code](<https://devfeed.tech/tags/code.md>), [collections](<https://devfeed.tech/tags/collections.md>), [collector](<https://devfeed.tech/tags/collector.md>), [collectors](<https://devfeed.tech/tags/collectors.md>), [hierarchical-sql](<https://devfeed.tech/tags/hierarchical-sql.md>), [how-to](<https://devfeed.tech/tags/how-to.md>), [jackson](<https://devfeed.tech/tags/jackson.md>), [java](<https://devfeed.tech/tags/java.md>), [jooq-development](<https://devfeed.tech/tags/jooq-development.md>), [json](<https://devfeed.tech/tags/json.md>), [nested-collections](<https://devfeed.tech/tags/nested-collections.md>), [postgresql](<https://devfeed.tech/tags/postgresql.md>), [recursive-sql](<https://devfeed.tech/tags/recursive-sql.md>), [sql](<https://devfeed.tech/tags/sql.md>)

### AI overview

A tutorial on converting flat parent-child data into hierarchical structures using SQL, Java, or jOOQ. It compares a recursive PostgreSQL query with jOOQ 3.19 and a reusable pure-Java Collector, and discusses JSON serialization with Jackson and type inference considerations.

### Source excerpt

Occasionally, you want to write a SQL query and fetch a hierarchy of data, whose flat representation may look like this: The result might be: |id |parent_id|label | |---|---------|-------------------| |1 | |C: | |2 |1 |eclipse | |3 |2 |configuration | |4 |2 |dropins | |5 |2 |features | |7 |2 |plugins | |8 |2 ... Continue reading How to Turn a List of Flat Elements into a Hierarchy in Java, SQL, or jOOQ ->

## 3.18.0 Release with Support for more Diagnostics, SQL/JSON, Oracle Associative Arrays, Multi dimensional Arrays, R2DBC 1.0

DevFeed: [3.18.0 Release with Support for more Diagnostics, SQL/JSON, Oracle Associative Arrays, Multi dimensional Arrays, R2DBC 1.0](<https://devfeed.tech/articles/3-18-0-release-with-support-for-more-diagnostics-sql-json-oracle-associative-arrays-multi-dimensional-arrays-r2dbc-1-0-28927.md>)

Original publisher: [Read original article](<https://blog.jooq.org/3-18-0-release-with-support-for-more-diagnostics-sql-json-oracle-associative-arrays-multi-dimensional-arrays-r2dbc-1-0/>)

Author: lukaseder

Published: 2023-03-08T16:34:56Z

Content type: release

Language: en

Sources: [jOOQ](<https://devfeed.tech/sources/jooq.md>)

Topics: [releases](<https://devfeed.tech/topics/releases.md>), [SQL](<https://devfeed.tech/topics/sql.md>), [JSON](<https://devfeed.tech/topics/json.md>), [Oracle Database](<https://devfeed.tech/topics/oracle-database.md>), [PostgreSQL](<https://devfeed.tech/topics/postgresql.md>), [Kotlin](<https://devfeed.tech/topics/kotlin.md>)

Tags: [jdbc](<https://devfeed.tech/tags/jdbc.md>), [jooq](<https://devfeed.tech/tags/jooq.md>), [kotlin](<https://devfeed.tech/tags/kotlin.md>), [oracle](<https://devfeed.tech/tags/oracle.md>), [r2dbc](<https://devfeed.tech/tags/r2dbc.md>), [release](<https://devfeed.tech/tags/release.md>), [sql](<https://devfeed.tech/tags/sql.md>), [sql-json](<https://devfeed.tech/tags/sql-json.md>), [uncategorized](<https://devfeed.tech/tags/uncategorized.md>)

### AI overview

jOOQ 3.18.0 is a release focused on expanded SQL diagnostics and SQL/JSON support, including vendor-specific MySQL and PostgreSQL features. It also adds improvements for the Query Object Model, Oracle associative arrays, PostgreSQL multidimensional arrays, Kotlin, and R2DBC 1.0.

### Source excerpt

DiagnosticsListener improvements A lot of additional diagnostics have been added, including the automated detection of pattern replacements, helping you lint your SQL queries irrespective of whether you're using jOOQ to write your SQL, or if you're using it as a JDBC / R2DBC proxy for an existing application. A lot of these diagnostics are available ... Continue reading 3.18.0 Release with Support for more Diagnostics, SQL/JSON, Oracle Associative Arrays, Multi dimensional Arrays, R2DBC 1.0 ->

## How to use jOOQ's Converters with UNION Operations

DevFeed: [How to use jOOQ's Converters with UNION Operations](<https://devfeed.tech/articles/how-to-use-jooq-s-converters-with-union-operations-28947.md>)

Original publisher: [Read original article](<https://blog.jooq.org/how-to-use-jooqs-converters-with-union-operations/>)

Author: lukaseder

Published: 2023-03-02T11:18:17Z

Content type: tutorial

Language: en

Sources: [jOOQ](<https://devfeed.tech/sources/jooq.md>)

Topics: [SQL](<https://devfeed.tech/topics/sql.md>), [Databases](<https://devfeed.tech/topics/databases.md>)

Tags: [ad-hoc-converter](<https://devfeed.tech/tags/ad-hoc-converter.md>), [converter](<https://devfeed.tech/tags/converter.md>), [how-to](<https://devfeed.tech/tags/how-to.md>), [jdbc](<https://devfeed.tech/tags/jdbc.md>), [jooq](<https://devfeed.tech/tags/jooq.md>), [jooq-in-use](<https://devfeed.tech/tags/jooq-in-use.md>), [lambda](<https://devfeed.tech/tags/lambda.md>), [multiset](<https://devfeed.tech/tags/multiset.md>), [r2dbc](<https://devfeed.tech/tags/r2dbc.md>), [rdbms](<https://devfeed.tech/tags/rdbms.md>), [sql](<https://devfeed.tech/tags/sql.md>), [union](<https://devfeed.tech/tags/union.md>)

### AI overview

This tutorial explains how jOOQ ad-hoc converters behave with UNION operations. Because conversion occurs on the client after JDBC retrieval, it does not affect server-side UNION processing, and jOOQ applies the result-fetching row type from the first subquery. The article recommends applying client-side conversions consistently across subqueries or moving the conversion to the server when appropriate.

### Source excerpt

jOOQ 3.15 introduced the concept of an ad-hoc converter, a converter that is applied "ad-hoc" to a single query. It uses the same underlying mechanisms as any ordinary Converter that is attached to generated code for use in every query. An example of such an ad-hoc converter is this: While there are other ways to ... Continue reading How to use jOOQ's Converters with UNION Operations ->

## How to Write a Derived Table in jOOQ

DevFeed: [How to Write a Derived Table in jOOQ](<https://devfeed.tech/articles/how-to-write-a-derived-table-in-jooq-28948.md>)

Original publisher: [Read original article](<https://blog.jooq.org/how-to-write-a-derived-table-in-jooq/>)

Author: lukaseder

Published: 2023-02-24T09:18:48Z

Content type: tutorial

Language: en

Sources: [jOOQ](<https://devfeed.tech/sources/jooq.md>)

Topics: [SQL](<https://devfeed.tech/topics/sql.md>), [Java](<https://devfeed.tech/topics/java.md>), [Databases](<https://devfeed.tech/topics/databases.md>)

Tags: [cte](<https://devfeed.tech/tags/cte.md>), [derived-table](<https://devfeed.tech/tags/derived-table.md>), [how-to](<https://devfeed.tech/tags/how-to.md>), [jooq](<https://devfeed.tech/tags/jooq.md>), [jooq-in-use](<https://devfeed.tech/tags/jooq-in-use.md>), [sql](<https://devfeed.tech/tags/sql.md>), [type-safety](<https://devfeed.tech/tags/type-safety.md>)

### AI overview

This tutorial explains how to write derived tables and common table expressions in jOOQ. It notes that derived-table columns are not fully type-safe in Java, and shows that simplifying an SQL query can sometimes eliminate the derived table and allow greater use of generated code.

### Source excerpt

One of the more frequent questions about jOOQ is how to write a derived table (or a CTE). The jOOQ manual shows a simple example of a derived table: In SQL: In jOOQ: And that's pretty much it. The question usually arises from the fact that there's a surprising lack of type safety when working ... Continue reading How to Write a Derived Table in jOOQ ->

[Next page](<https://devfeed.tech/sources/jooq.md?cursor=WyIyMDIzLTAyLTI0VDA5OjE4OjQ4KzAwOjAwIiwgImE0ZjhmYzVhLTZjMmYtNGIwOC04ZDNhLWIxZWVmNGIyYTBmNiJd>)