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, which builds on top of these features to offer type-safe nested collections, the way every ORDBMS should implement them. Building (dogfooding) on top of … Continue reading Standard SQL/JSON – The Sobering Parts
Tag: sql
Reactive SQL with jOOQ 3.15 and R2DBC
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. You can continue using jOOQ the way you were used to, providing you with type safe, embedded SQL in Java, … Continue reading Reactive SQL with jOOQ 3.15 and R2DBC
jOOQ 3.15’s New Multiset Operator Will Change How You Think About SQL
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 those who did, didn't agree on syntax. But this is about to change. Thanks to the now ubiquitous SQL/JSON support (which jOOQ … Continue reading jOOQ 3.15’s New Multiset Operator Will Change How You Think About SQL
How to Get an RDBMS Server Version with SQL
Do you need to know what RDBMS Server version you're on, and you only have SQL at your disposal? No problem. Most RDBMS provide you with that information in some form of meta data table. Here's how: -- ClickHouse select version(); -- CockroachDB select version(); -- Db2 select service_level from table (sysproc.env_get_inst_info()) t -- Derby … Continue reading How to Get an RDBMS Server Version with SQL
Use IN List Padding to Your JDBC Application to Avoid Cursor Cache Contention Problems
A problem few developers are aware of is the possibility of running into "cursor cache contention" or "execution plan cache contention" problems when using IN lists in SQL. The problem that is described in lengths in previous articles, can be summarised as this. All of these are distinct SQL queries and need to be parsed … Continue reading Use IN List Padding to Your JDBC Application to Avoid Cursor Cache Contention Problems
Calculating Pagination Metadata Without Extra Roundtrips in SQL
When paginating results in SQL, we use standard SQL OFFSET .. FETCH or a vendor specific version of it, such as LIMIT .. OFFSET. For example: SELECT first_name, last_name FROM actor ORDER BY actor_id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY As always, we're using the Sakila database for this example. This is rather … Continue reading Calculating Pagination Metadata Without Extra Roundtrips in SQL
Translating Stored Procedures Between Dialects
In the past years, we've invested a lot of effort into improving our procedural language capabilities in jOOQ. What started with a simple internal API to support the emulations of DDL clauses like these: -- Some dialect that supports this create table if not exists t (i varchar(10)); -- Db2 begin declare continue handler for … Continue reading Translating Stored Procedures Between Dialects
Implementing a generic REDUCE aggregate function with SQL
So, @rotnroll666 nerd sniped me again. Apparently, the Neo4j Cypher query language supports arbitrary reductions, just like any functional collection API, oh say, the JDK Stream API: Stream.of(2, 4, 3, 1, 6, 5) .reduce((i, j) -> i * j) .ifPresent(System.out::println); // Prints 720 SQL doesn't have this, yet it would be very useful to be … Continue reading Implementing a generic REDUCE aggregate function with SQL
jOOQ Internals: Pushing up SQL fragments
Over the past 13 years, jOOQ has accrued quite some internal features, which you, the user, are not exposed to. One very interesting feature is the capability for any arbitrary jOOQ expression tree element to push a SQL fragment up to a higher level. How does it work? The jOOQ expression tree model When you … Continue reading jOOQ Internals: Pushing up SQL fragments
Automatically Transform Oracle Style Implicit Joins to ANSI JOIN using jOOQ
While jOOQ is mostly being used as an internal SQL DSL for embedded, dynamic SQL in Java, where it offers the best solution on the market, jOOQ is increasingly also used for one of its secondary features: Its parser. Having been introduced in jOOQ 3.9 primarly for the purpose of being able to parse DDL … Continue reading Automatically Transform Oracle Style Implicit Joins to ANSI JOIN using jOOQ