jOOQ – DefaultRecordMapper – List

Can the DefaultRecordMapper handle lists?

Model example (really getter/setter are used):

class Head {
    public Integer id;
    public List<Position> positions;
    ...
}

class Position {
    public Integer id;
    public Integer headId;
    ...
}

I first tried it with a simple select:

dsl.select()
    .from(HEAD)
    .join(POSITION).onKey()
    .fetchInto(Head.class);

Also I have tried using the nested syntax but it didn’t work:

dsl.select(..., POSITION.ID.as("positions.id"), ...)
    .from(HEAD)
    .join(POSITION).onKey()
    .fetchInto(Head.class);

The list never gets initialized. I am unsure if I could be doing something wrong. Is this possible with the DefaultRecordMapper?

I am aware that jOOQ provides functionality like “intoGroups”. However this would mean that our company cannot use our existing models.

Or are third party libraries like i.e. ModelMapper necessary in this case?

Thanks very much in advance!

Answer

The recommended approach of nesting collections

The out of the box support for nested collections in jOOQ is via SQL/XML or SQL/JSON, depending on what works best in your SQL dialect.

Starting with jOOQ 3.14, this was offered as an API, which you can use directly, with jOOQ 3.15, new MULTISET and MULTISET_AGG functions were added to keep the serialisation formats transparent to your code, and to re-add type safety, see also this blog post.

Using jOOQ 3.14 SQL/XML and SQL/JSON directly

The idea is that you nest collections directly in SQL, serialise the results as XML or JSON, and use JAXB (for XML), or Gson or Jackson (for JSON) behind the scenes to map the document into a hierarchy of Java classes. All of this is quite straightforward and automatic, as soon as you have either JAXB, Gson, or Jackson on your classpath.

An example for your data structures:

dsl.select(
      HEAD.ID,
      field(
        select(jsonArrayAgg(jsonObject(
          key("id").value(POSITION.ID),
          key("x").value(POSITION.X), ...
        )))
        .from(POSITIONS)
        .where(POSITIONS.HEAD_ID.eq(HEAD.ID))
      ).as("positions")
    )
    .from(HEAD)
    .fetchInto(Head.class);

More examples are documented here:

• https://blog.jooq.org/2020/10/09/nesting-collections-with-jooq-3-14s-sql-xml-or-sql-json-support/
• https://www.jooq.org/doc/latest/manual/sql-execution/fetching/converter-provider/

Note that JSON_ARRAYAGG() aggregates empty sets into NULL, not into an empty []. If that’s a problem, use COALESCE()

Using jOOQ 3.15 MULTISET

Starting from jOOQ 3.15, the recommendation is to use MULTISET. Your query would now look like this (all type safe!):

dsl.select(
      HEAD.ID,
      multiset(
        select(POSITION.ID, POSITION.X, ...)
        .from(POSITIONS)
        .where(POSITIONS.HEAD_ID.eq(HEAD.ID))
      ).convertFrom(r -> r.map(Records.mapping(Position::new)))
    )
    .from(HEAD)
    .fetch(Records.mapping(Head::new));

The above is assuming you have appropriate constructors on your types in order to profit from the new ad-hoc converter features.

Other options

There are also a few third parties that help simplify this task of mapping to nested collections, including:

• SimpleFlatMapper
• ModelMapper

Or, to some extent, you can use jOOQ’s various built-in approaches including the many overloads of Result.intoGroups() or JDK Collector based ResultQuery.collect(), which is more composable.

Most of these approaches work to a certain extent only, because they’re all based on de-duplicating flat, denormalised result sets that arise from joining tables. Nesting collections directly in SQL is much more powerful for arbitrary target models, so if you get that to work, it’ll work much better.