Use ResultQuery.collect() to Implement Powerful Mappings

In our opinion, any Iterable<T> should offer a <R> collect(Collector<T, ?, R>) method to allow for transforming the the content to something else using standard JDK collectors, jOOλ collectors from org.jooq.lambda.Agg or your own.

When using jOOQ, you don’t have to wait for the JDK to finally add these useful utilities to the Iterable API. jOOQ’s ResultQuery<R> already implements Iterable<R>, and offers additional convenience like collect() on top of it.

For example, using a Java 16 record type:

record Book (int id, String title) {}

List<Book> books =
ctx.select(BOOK.ID, BOOK.TITLE)
   .from(BOOK)
   .collect(Collectors.mapping(
        r -> r.into(Book.class),
        Collectors.toList()
   ));

There are other ways to map things, but why not use a Collector. The best thing about Collector types is, they compose, type safely, and arbitrarily, almost like Stream pipelines.

I found a very interesting use-case recently on Stack Overflow. The problem there was that fetchGroups() is quite simple and not left-join aware, meaning that when an AUTHOR (parent) has no BOOK (child), instead of an empty list, there will be a list with a single NULL item:

Map<AuthorRecord, List<BookRecord>> result =
ctx.select()
   .from(AUTHOR)
   .leftJoin(BOOK).onKey()
   .fetchGroups(AUTHOR, BOOK);

The above works well for inner joins, but it doesn’t really make sense for left joins. We should fix this in jOOQ, of course (https://github.com/jOOQ/jOOQ/issues/11888), but using Collectors, you can already work around this problem today.

Simply write

Map<AuthorRecord, List<BookRecord>> result =
ctx.select()
   .from(AUTHOR)
   .leftJoin(BOOK).onKey()
   .collect(groupingBy(
        r -> r.into(AUTHOR), 
        filtering(
            r -> r.get(BOOK.ID) != null, 
            mapping(
                r -> r.into(BOOK), 
                toList()
            )
        )
    ));

// All assuming the usual static imports:
import static org.jooq.impl.DSL.*;
import static com.example.generated.Tables.*;
import static java.util.stream.Collectors.*;

Step by step:

  1. Group results by AUTHOR, mapping keys to AuthorRecord just like jOOQ’s fetchGroups()
  2. For each AUTHOR filter out those BOOK records whose BOOK.ID is null. Given that BOOK.ID is the primary key, the only reason why it could be null is because of the left join
  3. Map values to BookRecord, just like jOOQ’s fetchGroups()
  4. Collect the child records into a list.

And you’re done. Just like when you use the ResultQuery as an Iterable in a foreach loop, the collect() call executes your query automatically, managing all resources, bypassing the intermediate Result data structure, which isn’t needed here.

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:

-- CockroachDB
select version();

-- Db2
select service_level from table (sysproc.env_get_inst_info()) t

-- Derby
select getdatabaseproductversion() from (values (1)) t (a);

-- Exasol
select param_value 
from exa_metadata 
where param_name = 'databaseProductVersion';

-- Firebird
select rdb$get_context('SYSTEM', 'ENGINE_VERSION')
from rdb$database;

-- H2
select h2version(); 

-- HANA
select * from m_database;

-- HSQLDB
select character_value
from information_schema.sql_implementation_info
where implementation_info_name = 'DBMS VERSION'

-- Informix
select dbinfo('version', 'full') from systables where tabid = 1;

-- MariaDB
select version();

-- MemSQL (SingleStore)
select version();

-- MySQL
select version();

-- Oracle
select * from v$version;

-- PostgreSQL
select version();

-- Snowflake
select current_version();

-- SQL Server
select @@version;

-- SQLite
select sqlite_version();

-- Teradata
select infodata from dbc.dbcinfov where infokey = 'VERSION'

Missing this info for your own RDBMS? Feel free to comment.