Question 160595 in PostgreSQL Question posted in
The official documentation can be found here.

Can jOOQ handle multiple levels of nesting in Postgresql?

I am trying to fetch complex structure from the database using jOOQ. I will try to explain on an example where the problem is.

So, let’s imagine we have classes Foo, Bar and Bazz:

public class Foo {
    private String fooName;
    private List<Bar> bars;
}

public class Bar {
    private Bazz bazz;
}

public class Bazz {
    private String name;
}

I want to fetch and populate Foo -> List<Bar> -> Bazz relationship.

I tried with MULTISET and it works, but I want to avoid subqueries. I want to fetch and map that using JOIN. I wonder if it is possible to fetch that using reflection, something like this.

Here is an example with MULTISET :

public List<FooResponse> findByFooName(String fooName) {
        return ctx().select(FOO.asterisk(),
                        multiset(select(BAR.asterisk(),
                                BAR.bazz().as("bazz"))
                              .from(BAR)
                              .where(BAR.FOO_ID.eq(FOO.ID))).as("bars"))
        .from(FOO)
        .where(FOO.FOO_NAME.eq(fooName))
        .fetchInto(FooResponse.class);
    }
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I've made a little research and found several solutions to the aforementioned problem.

    1. Using multiset() -> the previous example

      • PROS:
        • least verbose way
        • Java part is pretty simple if you know what you are doing
      • CONS:
        • besides knowing SQL, you'll have to research how DSL.multiset() works
        • you have to use subselect
        • the generated SQL might look very complicated, probably hard to debug

    2. Using multisetAgg()

      return ctx().select(FOO.ID,
                    FOO.FOO_NAME,
                    multisetAgg(
                            BAR.ID,
                            row(BAR.bazz().ID,
                                    BAR.bazz().NAME
                            ).mapping(Bazz::new),
                            BAR.SOME_ATTRIBUTE,
                            BAR.SOME_OTHER_ATTRIBUTE
                    ).convertFrom(r -> r.map(Records.mapping(Bar::new))).as("bars"),
                    FOO.CREATED_AT,
                    FOO.MODIFIED_AT
            )
            .from(FOO)
            .join(BAR).on(BAR.FOO_ID.eq(FOO.ID))
            .groupBy(FOO.ID)
            .having(FOO.FOO_NAME.eq(fooName))
            .fetchInto(Foo.class);
      • PROS:
        • still looks pretty simple
        • you don't have to use subselect
      • CONS:
        • besides knowing SQL, you'll have to research how DSL.multisetAgg() works
        • the order of the columns in the SELECT statement must match the order of the parameters in constructors
        • the generated SQL might look very complicated, probably hard to debug

    3. Using modelmapper

      @Bean
public ModelMapper modelMapper() {
    var modelMapper = new ModelMapper();
    modelMapper.getConfiguration()
            .addValueReader(new RecordValueReader())
            .setSourceNameTokenizer(NameTokenizers.UNDERSCORE);
    return modelMapper;
}

      return ctx().select(FOO.asterisk(),
                        BAR.asterisk(),
                        BAZZ.asterisk()
                )
                .from(FOO)
                .join(BAR).on(BAR.FOO_ID.eq(FOO.ID))
                .join(BAZZ).on(BAZZ.ID.eq(BAR.BAZZ_ID))
                .where(FOO.FOO_NAME.eq(fooName))
                .orderBy(FOO.ID, BAR.ID)
                .collect(groupingBy(
                        r -> modelMapper.map(r.into(FOO), Foo.class), filtering(
                                r -> r.get(BAR.ID) != null, mapping(
                                        r -> new Pair(modelMapper.map(r.into(BAR), Bar.class),
                                                modelMapper.map(r.into(BAZZ), Bazz.class)),
                                        toList()
                                )
                        )
                ))
                .entrySet().stream().map(o -> {
                    var foo = o.getKey();
                    return foo.setBars(o.getValue().stream().map(v -> {
                        var bar = (Bar) v.getValue0();
                        var bazz = (Bazz) v.getValue1();
                        return bar.setBazz(bazz);
                    }).toList());
                }).toList();
      • PROS:
        • 1:1 equivalence between SQL syntax and the jOOQ API
      • CONS:
        • pretty verbose
        • easy to mess up (manual grouping and mapping)
        • external library

    4. Using SimpleFlatMapper

      private final JdbcMapper<Foo> fooResponseJdbcMapper;

public FooRepository(...) {
    this.fooResponseJdbcMapper = JdbcMapperFactory.newInstance()
            .addKeys("id")
            .ignorePropertyNotFound()
            .newMapper(Foo.class);
    }
.
.
.
public List<Foo> findByCustomerId(Long id) {
    try (var result = ctx().select(FOO.ID,
                        FOO.FOO_NAME,
                        FOO.CREATED_AT,
                        FOO.MODIFIED_AT,
                        BAR.ID.as("bars_id"),
                        BAR.SOME_ATTRIBUTE.as("bars_some_attribute"),
                        BAR.SOME_OTHER_ATTRIBUTE.as("bars_some_other_attribute"),
                        BAZZ.ID.as("bars_bazz_id"),
                        BAZZ.NAME.as("bars_bazz_name")
                )
                .from(FOO)
                .join(BAR).on(BAR.FOO_ID.eq(FOO.ID))
                .join(BAZZ).on(BAZZ.ID.eq(BAR.BAZZ_ID))
                .where(FOO.FOO_NAME.eq(fooName))
                .orderBy(FOO.ID, BAR.ID)
                .fetchResultSet()) {
            return fooResponseJdbcMapper.stream(result).toList();
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }
      • PROS:
        • 1:1 equivalence between SQL syntax and the jOOQ API
        • looks pretty simple
      • CONS:
        • external library
        • you have to assign aliases to selected columns if you want SimpleFlatMapper to map everything correctly

  2. 0

    In your particular case, you can nest with JOIN and GROUP BY / MULTISET_AGG, instead of using the MULTISET value constructor, because you’re nesting only one level of nested collections (the second nesting is not a collection). The main difference will be what happens when you don’t have any BAR for a FOO. With aggregation, this produces a NULL value, not an empty collection.

    However, there are a few limitations in the API (e.g. #15008, #15007, #13937) making this a bit difficult using the DefaultRecordMapper (the fetchInto(X.class) call). Here’s a solution using ad-hoc conversion and type safety:

    ctx().select(
        FOO.FOO_NAME,
        multisetAgg(

          // Add more BAR columns here, if needed
          row(

            // Add more BAZZ columns here, if needed
            BAR.bazz().NAME
          ).mapping(Bazz::new)
        ).convertFrom(r -> r.map(Records.mapping(Bar::new)))
      )
     .from(FOO)
     .join(BAR).on(BAR.FOO_ID.eq(FOO.ID))
     .groupBy(FOO.ID)
     .where(FOO.FOO_NAME.eq(fooName))
     .fetch(Records.mapping(Foo::new));

    This is assuming you have the relevant constructors. If you don’t, you can always revert to into(X.class) calls on each level.

    One of the benefits of using jOOQ’s code generator is that you have type safe nested collection mapping as shown above.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search