How to Typesafely Map a Nested SQL Collection into a Nested Java Map with jOOQ – Java, SQL and 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 a Map<UserType1, UserType2> instead?

Looking at the Sakila database, let’s look at how we can fetch data into this Java record type:

record Film(
    String title,
    Map<LocalDate, BigDecimal> revenue
) {}

The result type should wrap up the FILM.TITLE along with the amount of money or DVD rental store made on each day, per film. We could use other data structures, but let’s assume this is what we want to consume in the UI or service or whatever.

In jOOQ, as ever so often, thanks to MULTISET, we can do it in a single query that looks like this:

List<Film> result =
ctx.select(
        FILM.TITLE,
        multiset(
            select(
                PAYMENT.PAYMENT_DATE.cast(LOCALDATE), 
                sum(PAYMENT.AMOUNT))
            .from(PAYMENT)
            .where(PAYMENT.rental().inventory().FILM_ID
                .eq(FILM.FILM_ID))
            .groupBy(PAYMENT.PAYMENT_DATE.cast(LOCALDATE))
            .orderBy(PAYMENT.PAYMENT_DATE.cast(LOCALDATE))
        )
        // Convert Field<Result<Record2<LocalDate, BigDecimal>>>
        // to Field<Map<LocalDate, BigDecimal>>
        .convertFrom(r -> r.collect(Records.intoMap())
   )
   .from(FILM)
   .orderBy(FILM.TITLE)

   // Convert Record2<String, Map<LocalDate, BigDecimal>>
   // to List<Film>
   .fetch(Records.mapping(Film::new))

We can then consume the result, e.g. like this:

for (Film film : result) {
    System.out.println();
    System.out.println("Film %s with revenue: "
        .formatted(film.title()));

    // Inferred types are LocalDate d and BigDecimal r
    film.revenue().forEach((d, r) -> 
        System.out.println("  %s: %s".formatted(d, r))
    );
}

To produce:

Film ACADEMY DINOSAUR with revenue: 
  2005-05-27: 0.99
  2005-05-30: 1.99
  2005-06-15: 0.99
  [...]

Film ACE GOLDFINGER with revenue: 
  2005-07-07: 4.99
  2005-07-28: 9.99
  2005-08-01: 4.99
  [...]

Everything is, as always with jOOQ, completely type safe! Try it yourself, change some of the column expressions in the query, or the resulting record or Map type to see that the query will stop compiling!

The interesting bit here is:

.convertFrom(r -> r.collect(Records.intoMap())

The Field.convertFrom() method is from jOOQ 3.15’s new ad-hoc conversion API, which allows for ad-hoc converting a Field<T> column expression to a Field<U> column expression. In this case, the conversion goes:

• From Field<Result<Record2<LocalDate, BigDecimal>>> (the multiset field type)
• To Field<Map<LocalDate, BigDecimal>> (the mapped type)

It does so by collecting all the Record2<LocalDate, BigDecimal> records of the nested collection into a Map<LocalDate, BigDecimal> using the Records.intoMap() collector. The signature of that method is:

public static final <K, V, R entends Record2<K, V>> 
Collector<R, ?, Map<K, V>> intoMap() { ... }

That specific usage of generics allows for avoiding the repetition of the key and value expressions of the field, knowing that a collection of Record2<K, V> has an obvious way to collect into a Map<K, V> (or Map<K, List<V>> if you’re using Records.intoGroups(), if keys can be duplicate).

Note that both of these collectors will produce an insertion order preserving Map (e.g. LinkedHashMap), such that any MULTISET ordering will be preserved.

Conclusion

The sky is the limit, when you’re using jOOQ 3.15’s new nesting capabilities for nested collections (MULTISET or MULTISET_AGG) or nested records (ROW). Together with ad-hoc converters, you can map the jOOQ representation into any Java representation in the middle of your query, to adhere to any target type of your choosing, including nested Map<K, V>, with arbitrary types of K and V