Java, SQL and jOOQ.

ANTI JOIN is a very useful operator from relational algebra.

Some SQL operators are as esoteric as they’re powerful.

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.

When implementing the awesome MULTISET operator in jOOQ, its implementation mostly relied on SQL/JSON support of various RDBMS.

ARRAY types are a part of the ISO/IEC 9075 SQL standard.

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.

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.

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 experime…

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 qu…

I’ve blogged about generic ways of getting top 1 or top n per category queries before on this blog.

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.

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.

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?

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…

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.

Do you need to add a JDBC driver to your application, and don’t know its Maven coordinates?

jOOQ’s DAO API is one of jOOQ’s most controversial features.

Need to connect to your RDBMS with JDBC and don’t have the JDBC connection URL or driver name at hand?

Java’s package private visibility is an underrated feature.

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.

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…

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…

jOOQ 3.15 introduced the concept of an ad-hoc converter, a converter that is applied “ad-hoc” to a single query.

One of the more frequent questions about jOOQ is how to write a derived table (or a CTE).

I’ve found an interesting question on Twitter, recently.

One of MySQL 8’s biggest improvements is the support of window functions.

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.

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, JdbcTemplate, native JDBC, etc.).

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 standard SQL WITH clause has been tremendously helpful in structuring SQL queries.

Using jOOQ’s code generator to call stored procedures is a popular reason to use 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.

For new users working with jOOQ for the first time, the number of types in the jOOQ API can be overwhelming.

No need for expensive Tableau subscriptions.

I’ve just stumbled upon this great post by Vlad Mihalcea, titled The 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: [ERROR] …/DefaultCatalog.java:[53,73] cannot find symbol[ERROR] symbol: variable VERSION_3_17[ERROR] location: class org.jooq.Consta…

Since jOOQ 3.11, implicit joins have been supported.

Starting with jOOQ 3.17, the Condition type extends the Field<Boolean> type.

Probably the hardest thing to standardise in SQL is RETURNING data from DML statements.

When you write stored procedures and functions in your database, you want to ensure their correctness, just like with your Java code.

The H2 database is an immensely popular in-memory database product mostly used by Java developers for testing.

jOOQ is mainly known for its powerful type safe, embedded, dynamic SQL capabilities that are made available through code generation.

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.

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 columns Pattern matching SQL transformations More implicit JO…

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 contains a value.

It’s been a while since jOOQ 3.15 has been released with its revolutionary standard SQL MULTISET emulation feature.

Every now and then, I see folks lament the SQL syntax’s peculiar disconnect between the lexical order of operations (SELECT ..

The jOOQ API is all about convenience, and as such, an important operation (the most important one?)

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 made to them by default.

A really cool, recent question on Stack Overflow was about how to map a nested collection into a Java Map with jOOQ.