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. The question is jOOQ specific, but imagine, you have a query that nests collections using JSON in PostgreSQL. Assuming, as always, … Continue reading How to Filter a SQL Nested Collection by a Value
Tag: sql
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
Changing SELECT .. FROM Into FROM .. SELECT Does Not “Fix” SQL
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 recently here in a Youtube comment reply to a recent jOOQ/kotlin talk. Let's look at why jOOQ didn't fall into this trap of trying … Continue reading Changing SELECT .. FROM Into FROM .. SELECT Does Not “Fix” SQL
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
Setting the JDBC Statement.setFetchSize() to 1 for Single Row Queries
An interesting hint by Vladimir Sitnikov has made me think about a new benchmark for jOOQ: https://twitter.com/lukaseder/status/1407662449331949568 The benchmark should check whether single row queries should have a JDBC Statement.setFetchSize(1) call made to them by default. The Javadoc of the method says: Gives the JDBC driver a hint as to the number of rows that … Continue reading Setting the JDBC Statement.setFetchSize() to 1 for Single Row Queries
How to Typesafely Map a Nested SQL Collection into a Nested Java Map with 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, which allows for nesting collections in jOOQ. This time, instead of nesting data into a List<UserType>, why not nest it in … Continue reading How to Typesafely Map a Nested SQL Collection into a Nested Java Map with 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
How to Fetch Sequence Values with 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, using jOOQ, assuming you're using the code generator: // import static com.example.generated.Sequences.*; System.out.println(ctx.fetchValue(S.nextval())); The sequence expression translates to a variety … Continue reading How to Fetch Sequence Values with jOOQ
Various Meanings of SQL’s PARTITION BY Syntax
For SQL beginners, there's a bit of an esoteric syntax named PARTITION BY, which appears all over the place in SQL. It always has a similar meaning, though in quite different contexts. The meaning is similar to that of GROUP BY, namely to group/partition data sets by some grouping/partitioning criteria. For example, when querying the … Continue reading Various Meanings of SQL’s PARTITION BY Syntax
Use MULTISET Predicates to Compare Data Sets
Questions that might be a bit more difficult to solve using ordinary SQL are questions of the kind: What films have the same actors as a given film X? As always, we're using the sakila database for this example. What would be a possible way to solve this with SQL (for example, PostgreSQL, to be … Continue reading Use MULTISET Predicates to Compare Data Sets