This is how SQL should have been used all along. They called it The Third Manifesto, ORDBMS, or other things. Regrettably, it never really took off. Because most vendors didn’t adopt it. And …| Java, SQL and jOOQ.
Consider using JSON arrays instead of objects to transfer data in order to avoid overhead on client, server, and the data itself.| Java, SQL and jOOQ.
A few forward looking SQL dialects have started introducing lambda expressions to be used with functions operating on arrays| Java, SQL and jOOQ.
A SQL MERGE statement performs actions based on a RIGHT JOIN between target and source tables| Java, SQL and jOOQ.
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→| Java, SQL and jOOQ.
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, ...| Java, SQL and jOOQ.
Oracle 23ai still doesn't support the standard SQL FILTER clause on aggregate functions, which can prove to be tricky to emulate on JSON aggregate functions| Java, SQL and jOOQ.
Getting top 1 per group values is a bit tricky in SQL in general. This post explains how to do this with Oracle when multiple values per group are requested| Java, SQL and jOOQ.
We check execution plans and benchmark results to see if using a LIMIT clause is useful when checking for existence of N values in SQL| Java, SQL and jOOQ.
Cost Based Optimisation is the de-facto standard way to optimise SQL queries in most modern databases. It is the reason why it is really really hard to implement a complex, hand-written algorithm i…| Java, SQL and jOOQ.
Starting with jOOQ 3.19, implicit path joins may skip unnecessary tables in the join tree, when no columns are referenced from those tables| Java, SQL and jOOQ.
jOOQ 3.19 supports explicit path joins, to-many path joins, and implicit join path correlation to greatly simplify your SQL queries| Java, SQL and jOOQ.
"can't specify target table for update in FROM clause" is an annoying limitation in MySQL, for which jOOQ automatically implements a workaround.| Java, SQL and jOOQ.
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:…| Java, SQL and jOOQ.
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 v…| Java, SQL and jOOQ.
Most serious data centric applications will quickly find DAOs or repositories insufficient. This is when jOOQ really shines with all its SQL support.| Java, SQL and jOOQ.
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: // BigQuery driver = “com.simba.googleb…| Java, SQL and jOOQ.
jOOQ 3.17 supports the powerful path expressions / implicit JOIN also in UPDATE and DELETE| Java, SQL and jOOQ.
Every SQL dialect and JDBC driver works a bit differently when it comes to fetching generated keys| Java, SQL and jOOQ.
The simplest way to integration test your stored procedures is with jOOQ and testcontainers| Java, SQL and jOOQ.
Using H2 as a test database vs integration testing directly on your production RDBMS| Java, SQL and jOOQ.
A lot of RDBMS support standard SQL sequences of some form. The standard SQL syntax to create a sequence is: CREATE SEQUENCE s; The following is how you could fetch a value from this sequence, usin…| Java, SQL and jOOQ.
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 colu…| Java, SQL and jOOQ.
This article illustrates various ways of turning a flat representation of hierarchical dataa into an object or JSON hierarchy, using SQL, jOOQ, or vanilla Java.| Java, SQL and 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. W…| Java, SQL and 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…| Java, SQL and jOOQ.
jOOQ's code generator generates powerful stubs to type safely call stored procedures on all supported RDBMS, as if the procedure were a local method| Java, SQL and jOOQ.
This article shows a simple example configuration of the jOOQ code generator for keeping generated output in a single package, and making it package private| Java, SQL and jOOQ.
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 cont…| Java, SQL and jOOQ.
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 ti…| Java, SQL and jOOQ.
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 rece…| Java, SQL and jOOQ.
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: Result| Java, SQL and 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 ma…| Java, SQL and jOOQ.
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,…| Java, SQL and jOOQ.
Using jOOQ's code generator, it is very easy to call a stored function accepting a table-valued parameter in SQL Server from your Java application.| Java, SQL and 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 whethe…| Java, SQL and jOOQ.
When using jOOQ Converters with UNION queries, remember that jOOQ will only consider the first UNION subquery's Converters, since jOOQ doesn't know which subquery contributed which rows.| Java, SQL and jOOQ.
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: SELECT nested.* FROM ( SELECT AUTHOR_ID,…| Java, SQL and jOOQ.
The standard SQL FILTER clause is "just" syntax sugar for an equivalent CASE expression in an aggregate function. This article shows which performs better on PostgreSQL 15| Java, SQL and jOOQ.
If you're stuck on MySQL 5.7 and cannot profit from MySQL 8.0's window function support, then this article will help you emulate (almost all) window functions using local variables and JSON| Java, SQL and jOOQ.
Many applications migrating to jOOQ tend to keep "the old stuff" around by continuing executing queries with JPA, JDBC, JdbcTemplate, etc. and using jOOQ only as a query builder. This article shows numerous benefits of executing jOOQ that users are missing this way.| Java, SQL and jOOQ.
This article describes how to use jOOQ's LoggingConnection, or write your own, to log all SQL statements executed by R2DBC.| Java, SQL and jOOQ.
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 j…| Java, SQL and jOOQ.
Using LATERAL or APPLY to create local variables in SQL is great alternative to the WITH clause| Java, SQL and jOOQ.
Using jOOQ to call stored procedures and functions from Java is very simple thanks to jOOQ's code generator| Java, SQL and jOOQ.
An interesting case of using implicit JOIN path expressions in a JOIN .. ON clause, with jOOQ| Java, SQL and jOOQ.
A quick cheat sheet describing the most important jOOQ types| Java, SQL and jOOQ.
No need for expensive Tableau subscriptions anymore. Plot your data directly with this PostgreSQL query.| Java, SQL and jOOQ.
The second best way to fetch a Spring Data JPA DTO Projection is to use jOOQ.| Java, SQL and jOOQ.
What does it mean if the jOOQ generated VERSION symbol cannot be found by the compiler?| Java, SQL and jOOQ.
Starting from jOOQ 3.17, the Condition type extends Field| Java, SQL and 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…| Java, SQL and jOOQ.
A very little known feature in jOOQ is the Formattable.formatChart() capability, which allows for formatting any jOOQ result as an ASCII chart. This can be useful for quick plotting of results in y…| Java, SQL and jOOQ.
