Find out how to Typesafely Map a Nested SQL Assortment right into a Nested Java Map with jOOQ – Java, SQL and jOOQ.

A very cool, current query on Stack Overflow was about the right way to map a nested assortment right into a Java Map with jOOQ. Previously, 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 information right into a Checklist<UserType>, why not nest it in a Map<UserType1, UserType2> as a substitute?

Trying on the Sakila database, let’s take a look at how we are able to fetch information into this Java report kind:

report Movie(
    String title,
    Map<LocalDate, BigDecimal> income
) {}

The consequence kind ought to wrap up the FILM.TITLE together with the amount of cash or DVD rental retailer made on every day, per movie. We may use different information buildings, however let’s assume that is what we wish to devour within the UI or service or no matter.

In jOOQ, as ever so usually, due to MULTISET, we are able to do it in a single question that appears like this:

Checklist<Movie> consequence =
ctx.choose(
        FILM.TITLE,
        multiset(
            choose(
                PAYMENT.PAYMENT_DATE.forged(LOCALDATE), 
                sum(PAYMENT.AMOUNT))
            .from(PAYMENT)
            .groupBy(PAYMENT.PAYMENT_DATE.forged(LOCALDATE))
            .orderBy(PAYMENT.PAYMENT_DATE.forged(LOCALDATE))
        )
        // Convert Subject<Outcome<Record2<LocalDate, BigDecimal>>>
        // to Subject<Map<LocalDate, BigDecimal>>
        .convertFrom(r -> r.gather(Information.intoMap())
   )
   .from(FILM)
   .orderBy(FILM.TITLE)

   // Convert Record2<String, Map<LocalDate, BigDecimal>>
   // to Checklist<Movie>
   .fetch(Information.mapping(Movie::new))

We will then devour the consequence, e.g. like this:

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

    // Inferred sorts 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-24: 29.92
  2005-05-25: 573.63
  2005-05-26: 754.26
  [...]

Movie ACE GOLDFINGER with income: 
  2005-05-24: 29.92
  2005-05-25: 573.63
  2005-05-26: 754.26
  [...]

The whole lot is, as all the time with jOOQ, fully kind protected! Strive it your self, change among the column expressions within the question, or the ensuing report or Map kind to see that the question will cease compiling!

The attention-grabbing bit right here is:

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

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

• From Subject<Outcome<Record2<LocalDate, BigDecimal>>> (the multiset subject kind)
• To Subject<Map<LocalDate, BigDecimal>> (the mapped kind)

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

public static closing <Ok, V, R entends Record2<Ok, V>> 
Collector<R, ?, Map<Ok, V>> 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<Ok, V> has an apparent approach to gather right into a Map<Ok, V> (or Map<Ok, Checklist<V>> in case you’re utilizing Information.intoGroups(), if keys may be duplicate).

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

Conclusion

The sky is the restrict, once 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 midst of your question, to stick to any goal kind of your selecting, together with nested Map<Ok, V>, with arbitrary kinds of Ok and V