Java, SQL and jOOQ.

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

https://blog.jooq.org/getting-top-1-values-per-group-in-oracle/

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 ha...

https://blog.jooq.org/an-efficient-way-to-check-for-existence-of-multiple-values-in-sql/

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 c...

https://blog.jooq.org/a-hidden-benefit-of-implicit-joins-join-elimination/

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, Do...

https://blog.jooq.org/jooq-3-19s-new-explicit-and-implicit-to-many-path-joins/

In MySQL, you cannot do this: The UPDATE statement will raise an error as follows: SQL Error : You can’t specify target table ‘t’ for update in FROM clause People have considered this to b...

https://blog.jooq.org/workaround-for-mysqls-cant-specify-target-table-for-update-in-from-clause-error/

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

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/

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 ver...

https://blog.jooq.org/maven-coordinates-of-the-most-popular-jdbc-drivers/

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

https://blog.jooq.org/to-dao-or-not-to-dao/

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:

https://blog.jooq.org/jdbc-connection-urls-of-the-most-popular-rdbms/

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 onl...

https://blog.jooq.org/how-to-generate-package-private-code-with-jooqs-code-generator/

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 wr...

https://blog.jooq.org/how-to-pass-a-table-valued-parameter-to-a-t-sql-function-with-jooq/

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

https://blog.jooq.org/how-to-turn-a-list-of-flat-elements-into-a-hierarchy-in-java-sql-or-jooq/

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 whet...

https://blog.jooq.org/3-18-0-release-with-support-for-more-diagnostics-sql-json-oracle-associative-arrays-multi-dimensional-arrays-r2dbc-1-0/

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 attac...

https://blog.jooq.org/how-to-use-jooqs-converters-with-union-operations/

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

https://blog.jooq.org/how-to-write-a-derived-table-in-jooq/

I’ve found an interesting question on Twitter, recently. Is there any performance impact of using FILTER in SQL (PostgreSQL, specifically), or is it just syntax sugar for a CASE expression in a...

https://blog.jooq.org/the-performance-impact-of-sqls-filter-clause/

One of MySQL 8’s biggest improvements is the support of window functions. As I always said in conferences, there’s SQL before window functions and SQL after window functions. Once you start u...

https://blog.jooq.org/emulating-window-functions-in-mysql-5-7/

Previously on this blog, I’ve written a post explaining why you should use jOOQ’s code generator, despite the possibility of using jOOQ without it. In a similar fashion, as I’ve answered nu...

https://blog.jooq.org/why-you-should-execute-jooq-queries-with-jooq/

jOOQ already has a LoggingConnection (see also the manual), which acts as a JDBC proxy Connection to log all SQL statements that are executed by any JDBC client (including Hibernate, MyBatis, Jdb...

https://blog.jooq.org/jooqs-r2dbc-loggingconnection-to-log-all-sql-statements/

A frequently encountered doubt people have when using jOOQ is to decide when a “complex” query should be written using jOOQ API vs. when it should be implemented using native SQL. The jOOQ ma...

https://blog.jooq.org/when-to-use-jooq-and-when-to-use-native-sql/

The standard SQL WITH clause has been tremendously helpful in structuring SQL queries. Instead of nesting everything in unreadable derived tables like this: People have started moving the logic u...

https://blog.jooq.org/lateral-is-your-friend-to-create-local-column-variables-in-sql/

Using jOOQ’s code generator to call stored procedures is a popular reason to use jOOQ. For example, when you have a procedure like the following Oracle PL/SQL procedure: jOOQ will generate code...

https://blog.jooq.org/calling-procedures-with-default-parameters-using-jdbc-or-jooq/

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 somewha...

https://blog.jooq.org/using-jooqs-implicit-join-from-within-the-join-on-clause/

For new users working with jOOQ for the first time, the number of types in the jOOQ API can be overwhelming. The SQL language doesn’t have many such “visible” types, although if you think a...

https://blog.jooq.org/a-brief-overview-over-the-most-common-jooq-types/

No need for expensive Tableau subscriptions. Ditch Microsoft Excel. Just use native PostgreSQL to quickly visualise your data! Here’s an idea I had for a while. As you may know, jOOQ can produc...

https://blog.jooq.org/how-to-plot-an-ascii-bar-chart-with-sql/

I’ve just stumbled upon this great post by Vlad Mihalcea, titled The Best Way to Fetch a Spring Data JPA DTO Projection. It got some nice traction on reddit, too. This is such a nice use-case a...

https://blog.jooq.org/the-second-best-way-to-fetch-a-spring-data-jpa-dto-projection/

Starting with jOOQ 3.16 and #12601, there may be a compilation error with a message like this in your jOOQ generated code: …/DefaultCatalog.java: cannot find symbol symbol: variable VERSION_3_...

https://blog.jooq.org/cannot-resolve-symbol-version_3_17-in-jooq-generated-code/

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 th...

https://blog.jooq.org/jooq-3-17-supports-implicit-join-also-in-dml/

Starting with jOOQ 3.17, the Condition type extends the Field type. Because, that’s what the SQL standard thinks it is, in sorts: The exact definition contains intermediate rules, but ...

https://blog.jooq.org/a-condition-is-a-field/

Probably the hardest thing to standardise in SQL is RETURNING data from DML statements. In this article, we’ll look at various ways of doing that with jOOQ, in many of jOOQ’s supported dialec...

https://blog.jooq.org/the-many-ways-to-return-data-from-sql-dml/

When you write stored procedures and functions in your database, you want to ensure their correctness, just like with your Java code. In Java, this is done with unit tests, typically with JUnit. ...

https://blog.jooq.org/how-to-integration-test-stored-procedures-with-jooq/

The H2 database is an immensely popular in-memory database product mostly used by Java developers for testing. If you check out the DB-Engines ranking, it ranks 50th, which is quite impressive, a...

https://blog.jooq.org/using-h2-as-a-test-database-product/

jOOQ is mainly known for its powerful type safe, embedded, dynamic SQL capabilities that are made available through code generation. However, a secondary use case of code generation is to use it ...

https://blog.jooq.org/the-best-way-to-call-stored-procedures-from-java-with-jooq/

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. Wha...

https://blog.jooq.org/create-dynamic-views-with-jooq-3-17s-new-virtual-client-side-computed-columns/

This release contiues the work from previous releases around more sophisticated SQL transformation capabilities, including: Client side computed columns for both read and write operations Audit c...

https://blog.jooq.org/3-17-0-release-with-computed-columns-audit-columns-pattern-matching-reactive-transactions-and-kotlin-coroutine-support/

I stumbled upon a very interesting question on Stack Overflow about how to use jOOQ’s MULTISET operator to nest a collection, and then filter the result by whether that nested collection contai...

https://blog.jooq.org/how-to-filter-a-sql-nested-collection-by-a-value/

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 time...

https://blog.jooq.org/the-performance-of-various-to-many-nesting-algorithms/

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 recent...

https://blog.jooq.org/changing-select-from-into-from-select-does-not-fix-sql/

The jOOQ API is all about convenience, and as such, an important operation (the most important one?) like fetch() must come with convenience, too. The default way to fetch data is this: It fetche...

https://blog.jooq.org/the-many-different-ways-to-fetch-data-in-jooq/

An interesting hint by Vladimir Sitnikov has made me think about a new benchmark for jOOQ: The benchmark should check whether single row queries should have a JDBC Statement.setFetchSize(1) call ...

https://blog.jooq.org/setting-the-jdbc-statement-setfetchsize-to-1-for-single-row-queries/

A really cool, recent question on Stack Overflow was about how to map a nested collection into a Java Map with jOOQ. In the past, I’ve blogged about the powerful MULTISET operator many times, w...

https://blog.jooq.org/how-to-typesafely-map-a-nested-sql-collection-into-a-nested-java-map-with-jooq/

Every now and then I run across a use case for the arcane NATURAL JOIN SQL operator, and I’m even more delighted when I can make that a NATURAL FULL JOIN. A few past blog posts on the subject i...

https://blog.jooq.org/a-quick-and-dirty-way-to-concatenate-two-vaguely-related-tables-in-sql/

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, f...

https://blog.jooq.org/nested-transactions-in-jooq/

A lot of RDBMS support standard SQL sequences of some form. The standard SQL syntax to create a sequence is: The following is how you could fetch a value from this sequence, using jOOQ, assuming ...

https://blog.jooq.org/how-to-fetch-sequence-values-with-jooq/

For SQL beginners, there’s a bit of an esoteric syntax named PARTITION BY, which appears all over the place in SQL. It always has a similar meaning, though in quite different contexts. The mean...

https://blog.jooq.org/various-meanings-of-sqls-partition-by-syntax/

Questions that might be a bit more difficult to solve using ordinary SQL are questions of the kind: What films have the same actors as a given film X? As always, we’re using the sakila database...

https://blog.jooq.org/use-multiset-predicates-to-compare-data-sets/

A long standing feature request has seen little love from the jOOQ community, despite a lot of people probably wanting it. It goes by the unimpressive title Let Table extend SelectField: ht...

https://blog.jooq.org/projecting-type-safe-nested-tablerecords-with-jooq-3-17/

A tidal wave is rippling through the Java ecosystem. It is the renaming of javax to jakarta package names. Now, while we’ve all been whining and complaining and shaking our heads due the clash ...

https://blog.jooq.org/jooq-3-16-and-java-ee-vs-jakarta-ee/

I’ve recently stumbled upon this interesting Stack Overflow question about Hibernate’s popular MultipleBagFetchException. The question is super popular, and the answers are plenty. The variou...

https://blog.jooq.org/no-more-multiplebagfetchexception-thanks-to-multiset-nested-collections/

If you’re running on PostgreSQL, you could try the following cool query: What does it print (after a while)? It prints e (almost). Here are some sample results: 2.7169115477960698 2.71641455226...

https://blog.jooq.org/approximating-e-with-sql/