The best way to Typesafely Map a Nested SQL Assortment right into a Nested Java Map with jOOQ – Java, SQL and jOOQ.

A extremely cool, current query on Stack Overflow was about the way to map a nested assortment right into a Java Map with jOOQ. Prior to now, I’ve blogged in regards to the highly effective MULTISET operator many instances, which permits for nesting collections in jOOQ. This time, as a substitute of nesting knowledge right into a Record, why not nest it in a Map as a substitute?

Wanting on the Sakila database, let’s have a look at how we will fetch knowledge into this Java document sort:

document Movie(
    String title,
    Map income
) {}

The end result sort ought to wrap up the FILM.TITLE together with the sum of money or DVD rental retailer made on every day, per movie. We might use different knowledge buildings, however let’s assume that is what we wish to eat within the UI or service or no matter.

In jOOQ, as ever so usually, because of MULTISET, we will do it in a single question that appears like this:

Record end result =
ctx.choose(
        FILM.TITLE,
        multiset(
            choose(
                PAYMENT.PAYMENT_DATE.forged(LOCALDATE), 
                sum(PAYMENT.AMOUNT))
            .from(PAYMENT)
            .the place(PAYMENT.rental().stock().FILM_ID
                .eq(FILM.FILM_ID))
            .groupBy(PAYMENT.PAYMENT_DATE.forged(LOCALDATE))
            .orderBy(PAYMENT.PAYMENT_DATE.forged(LOCALDATE))
        )
        // Convert Area>>
        // to Area>
        .convertFrom(r -> r.gather(Information.intoMap())
   )
   .from(FILM)
   .orderBy(FILM.TITLE)

   // Convert Record2>
   // to Record
   .fetch(Information.mapping(Movie::new))

We are able to then eat the end result, e.g. like this:

for (Movie movie : end result) {
    System.out.println();
    System.out.println("Movie %s with income: "
        .formatted(movie.title()));

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

To supply:

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

Movie ACE GOLDFINGER with income: 
  2005-07-07: 4.99
  2005-07-28: 9.99
  2005-08-01: 4.99
  [...]

Every little thing is, as at all times with jOOQ, utterly sort protected! Attempt it your self, change a few of the column expressions within the question, or the ensuing document or Map sort to see that the question will cease compiling!

The attention-grabbing bit right here is:

.convertFrom(r -> r.gather(Information.intoMap())

The Area.convertFrom() technique is from jOOQ 3.15’s new ad-hoc conversion API, which permits for ad-hoc changing a Area column expression to a Area column expression. On this case, the conversion goes:

From Area>> (the multiset discipline sort)
To Area >
(the mapped sort)

It does so by gathering all of the Record2 information of the nested assortment right into a Map utilizing the Information.intoMap() collector. The signature of that technique is:

public static remaining > 
Collector> intoMap() { ... }

That particular utilization of generics permits for avoiding the repetition of the important thing and worth expressions of the sphere, understanding {that a} assortment of Record2 has an apparent option to gather right into a Map (or Map> in the event you’re utilizing Information.intoGroups(), if keys could be duplicate).

Be aware that each of those collectors will produce an insertion order preserving Map (e.g. LinkedHashMap), such that any MULTISET ordering can be preserved.

Conclusion

The sky is the restrict, if you’re utilizing jOOQ 3.15’s new nesting capabilities for nested collections (MULTISET or MULTISET_AGG) or nested information (ROW). Along with ad-hoc converters, you possibly can map the jOOQ illustration into any Java illustration in the course of your question, to stick to any goal sort of your selecting, together with nested Map, with arbitrary kinds of Okay and V