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. This means that while join elimination is a powerful feature of many RDBMS, it isn't part of jOOQ's feature … Continue reading A Hidden Benefit of Implicit Joins: Join Elimination
Tag: java
jOOQ 3.19’s new Explicit and Implicit to-many path joins
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: Explicit path joins To-many path joins Implicit join path correlation What are these features? Many ORMs (e.g. JPA, Doctrine, jOOQ 3.11 and others) support "path joins" (they may have different names for … Continue reading jOOQ 3.19’s new Explicit and Implicit to-many path joins
How to Pass a Table Valued Parameter to a T-SQL Function with jOOQ
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 write: CREATE TYPE u_number_table AS TABLE (column_value INTEGER); CREATE FUNCTION f_cross_multiply ( @numbers u_number_table READONLY ) RETURNS @result TABLE ( i1 INTEGER, i2 … Continue reading How to Pass a Table Valued Parameter to a T-SQL Function with jOOQ
How to Turn a List of Flat Elements into a Hierarchy in Java, SQL, or jOOQ
Occasionally, you want to write a SQL query and fetch a hierarchy of data, whose flat representation may look like this: SELECT id, parent_id, label FROM t_directory; The result might be: |id |parent_id|label | |---|---------|-------------------| |1 | |C: | |2 |1 |eclipse | |3 |2 |configuration | |4 |2 |dropins | |5 |2 |features | … Continue reading How to Turn a List of Flat Elements into a Hierarchy in Java, SQL, or jOOQ
Create Dynamic Views with jOOQ 3.17’s new Virtual Client Side Computed Columns
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. What's a computed column? A computed column is a column that is derived ("computed") from an expression. It cannot be written to. It works like … Continue reading Create Dynamic Views with jOOQ 3.17’s new Virtual Client Side Computed Columns
The Performance of Various To-Many Nesting Algorithms
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 times is to run a few benchmarks comparing the performance of various approaches to nesting to-many relationships with jOOQ. This article will … Continue reading The Performance of Various To-Many Nesting Algorithms
The Many Different Ways to Fetch Data in 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<Record1<String>> result = ctx.select(BOOK.TITLE) .from(BOOK) .fetch(); for (Record1<String> record : result) { // ... } It fetches the entire result set into memory … Continue reading The Many Different Ways to Fetch Data in jOOQ
Nested Transactions in 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 reactive applications. As with everything jOOQ, transactions are implemented using explicit, API based logic. The implicit logic implemented … Continue reading Nested Transactions in jOOQ
Java’s Checked Exceptions Are Just Weird Union Types
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 JVM language that never really … Continue reading Java’s Checked Exceptions Are Just Weird Union Types
Write C-Style Local Static Variables in Java 16
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 member is a constant variable. This means … Continue reading Write C-Style Local Static Variables in Java 16