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 between corporate legal and engineering interests, eventually it’s time to move on and … | Continue reading
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 various limitations are discussed throughout the question, it all boils down to a simple fact: … | Continue reading
If you’re running on PostgreSQL, you could try the following cool query: WITH RECURSIVE r (r, i) AS ( SELECT random(), i FROM generate_series(1, 1000000) AS t (i) ), s (ri, s, i) AS ( SELECT … | Continue reading
Starting from jOOQ 3.16, we’re investing a lot into opening up our internal query object model (QOM) as a public API. This is mainly useful for people who use jOOQ’s parser and wish to access the parsed expression tree, or to transform SQL, e.g. to implement row level security in … | Continue reading
A while ago, jOOQ has added the org.jetbrains:annotations dependency to the jOOQ API, in order to annotate return types with nullability information. For example, the entire DSL is non-nullable: It makes sense to give this guarantee especially to kotlin users, as they can get rid … | Continue reading
I recently stumbled upon a standard SQL feature that was implemented, to my surprise, in HSQLDB. The keyword is CORRESPONDING, and it can be used with all set operations, including UNION, INTERSECT, and EXCEPT. Let’s look at the sakila database. It has 3 tables with people in it: … | Continue reading
N+1 queries are a popular problem in many applications that run SQL queries. The problem can be described easily as follows: 1 query fetching a parent value is run N queries fetching each individual child values are run This problem isn’t limited to SQL, it can happen with any po … | Continue reading
One of the coolest things about using and making jOOQ is that we get to discover the best extensions to the standard SQL language by vendors, and add support for those clauses in jOOQ via emulations. One of these syntaxes is BigQuery’s * EXCEPT syntax. Everyone who ever wrote ad- … | Continue reading
This release tackles two long standing and complex feature requests that usershave asked us to offer for a long time: a public API for manipulating jOOQ’squery object model (QOM), and spatial support. New Query Object Model (QOM) Every jOOQ query is modeled as an expression tree … | Continue reading
Starting from Spring Boot 2.5, there’s a handy new callback that you can implement, called DefaultConfigurationCustomizer, where the word DefaultConfiguration corresponds to jOOQ’s DefaultConfiguration. You can simply create a class like this in your project: The above callback r … | Continue reading
In classic SQL (i.e. before jOOQ’s awesome MULTISET operator), nested collections were fetched using ordinary (outer) joins. An example of such a query would be a query running against the sakila database to fetch actors and their films. Using jOOQ: The result from the jOOQ debug … | Continue reading
I’m answering many jOOQ questions on Stack Overflow, and a lot of times. The problem has the same cause: People not using jOOQ’s code generator. The main reason people seem not to be using it, is because it takes some extra time to set up, but as with anything well designed, the … | Continue reading
The SQL language is very intuitive. Until it isn’t. Over the years, a lot of people have criticised the SQL language for a variety of reasons. For instance: IDEs cannot easily guess what auto… | Continue reading
The upcoming jOOQ 3.16 will finally offer support for the various RDBMS GIS extensions via issue #982. This is great news per se, and will be covered in a future blog post, when the integration is … | Continue reading
I’ve recently discovered a pleasant new addition to PostgreSQL 14, the new enable_memoize flag that improves the performance of some nested loop joins where statistics hint at this being appr… | Continue reading
This fun fact has been on my mind for a while, and a recent reddit thread about “Smuggling Checked Exceptions with Sealed Interfaces” made me write this post here. Namely, Java had union types before it was cool! (If you squint hard). What are union types? Ceylon is an underrated … | Continue reading
The SQL standard knows an interesting feature where you can project any functional dependencies of a primary (or unique) key that is listed in the GROUP BY clause without having to add that functional dependency to the GROUP BY clause explicitly. What does this mean? Consider thi … | Continue reading
Java 16 includes an improvement that makes the language a bit more regular via JEP 395. The JEP says: Static members of inner classes It is currently specified to be a compile-time error if an inner class declares a member that is explicitly or implicitly static, unless the membe … | Continue reading
jOOQ’s parser can’t parse every possible SQL syntax. Try this random PostgreSQL syntax: And the jOOQ parser will complain: DOMAIN, INDEX, SCHEMA, SEQUENCE, SESSION, TABLE, TYPE, or VIEW expected: [1:7] ALTER [*]SYSTEM RESET ALL That’s perfectly fine. The goal of the jOOQ parser i … | Continue reading
Don’t you hate how you have to wrap checked exception throwing code in static initialisers? E.g. you cannot write this in Java: There’s an unhandled ClassNotFoundException, and you can’t catch / rethrow it simply. A static initialiser is needed: Yuck. Luckily, one of jOOλ’s lesse … | Continue reading
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… | Continue reading
Database first is at the core of jOOQ’s design. jOOQ has been made primarily for classic systems the database is always there and always has been and will never leave. This is because we think “data have mass” This not only translates to moving logic closer to the data (see our p … | Continue reading
If your legacy JPA application is using occasional native queries or Hibernate @Formula or Spring Data @Query annotation with vendor specific native SQL embedded in it, you can use jOOQ’s parsing connection and parsing data source to translate between dialects, without having to … | Continue reading
One of the strengths of modern RDBMS is the capability to mix the powerful SQL language with procedural code. SQL is a 4th generation programming language (4GL), and as such, extremely well suited for querying and bulk data manipulation. Its functional-declarative nature allows f … | Continue reading
MySQL’s JDBC connector has a security feature called allowMultiQueries, which defaults to false. When turned off, it prevents using a useful, but potentially dangerous feature in MySQL via JDBC: By default, the above produces a syntax error: Exception in thread "main" java.sql.SQ … | Continue reading
It’s been almost 1 year now since jOOQ 3.14 was released in October 19, 2020 with SQL/JSON (and SQL/XML) support. Half a year later, we’ve released jOOQ 3.15 with MULTISET support, whic… | Continue reading
One of the biggest new features of the recently released jOOQ 3.15 is its new support for reactive querying via R2DBC. This has been a highly popular feature request, and we finally delivered on it… | Continue reading
A tutorial on how to paginate without second round trips, including the calculation of total rows, current page number, etc. using SQL and jOOQ | Continue reading
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 … | Continue reading
Pagination is one of those things that almost everyone gets wrong for two reasons: User experience Database performance Here’s why. What’s wrong with pagination? Most applications blind… | Continue reading
Perhaps the most powerful SQL feature is the JOIN operation. It is the envy of all non-relational databases, because the concept is so simple, yet so universally applicable, when you want to “… | Continue reading
One of the biggest and undead myths in SQL is that COUNT(*) is faster than COUNT(1). Or was it that COUNT(1) is faster than COUNT(*)? Impossible to remember, because there’s really no reason … | Continue reading
Oracle 12c has introduced the useful SQL standard IDENTITY feature, which is essentially just syntax sugar for binding a sequence to a column default. We can use it like this: Which produces COL1 &… | Continue reading
How to write a simple API is already an art on its own. I didn’t have time to write a short letter, so I wrote a long one instead. ― Mark Twain But keeping an API simple for beginners and mos… | Continue reading
A question that is frequently occurring among my SQL training’s participants is: What’s the difference between putting a predicate in the JOIN .. ON clause and the WHERE clause? I can d… | Continue reading
After coding for a while (eek, almost 20 years or so in my case, time flies when you’re having fun), one starts to embrace those habits. Because, you know… Anything that Can Possibly Go… | Continue reading
B-Tree indexes are perfect when your data is uniformly distributed. They are not really useful, when you have skewed data. I’ll explain later why this is the case, but let’s first learn… | Continue reading
A customer of my popular SQL training (which you should book!) has recently challenged me to optimise a hierarchical query that merges an archive log’s deltas in order to obtain a snapshot of… | Continue reading
I like weird, yet concise language constructs and API usages Yes. I am guilty. Evil? Don’t know. But guilty. I heavily use and abuse the java.lang.Boolean type to implement three valued logic… | Continue reading
In recent times, there have been a couple of tremendously popular blog posts explaining JOINs using Venn Diagrams. After all, relational algebra and SQL are set oriented theories and languages, so … | Continue reading
One of the things that confuse SQL users all the time is how DISTINCT and ORDER BY are related in a SQL query. The Basics Running some queries against the Sakila database, most people quickly under… | Continue reading
Exciting discovery when playing around with PostgreSQL 11! New SQL standard window function clauses have been supported. If you want to play with this, you can do so very easily using docker: docke… | Continue reading
Clock’s ticking. JDK 11 will remove a bunch of deprecated modules through JEP 320, which includes the Java EE modules, which again includes JAXB, a dependency of many libraries, including jOO… | Continue reading
In this much overdue article, I will explain why I think that in almost all cases, you should implement a “database first” design in your application’s data models, rather than a … | Continue reading
The SQL standard knows a lesser known feature called GROUPING SETS. One particular side-effect of that feature is that we can group by “nothing” in SQL. E.g. when querying the Sakila da… | Continue reading
Google’s BigQuery has a very interesting SQL language feature, which I’ve missed many times in other databases: select: SELECT [{ ALL | DISTINCT }] { [ expression. ]* [ EXCEPT ( column_… | Continue reading
Google’s BigQuery has a very interesting SQL language feature, which I’ve missed many times in other databases: select: SELECT [{ ALL | DISTINCT }] { [ expression. ]* [ EXCEPT ( column_… | Continue reading
Much of the ORM criticism of the last decade missed the point, being inaccurate. By the end of this article, we will conclude with the … | Continue reading