Is there a way in JOOQ to pull a number of records without multiple DB calls?

Question

In our webapp we have a number of places where you would be updating a number of tables in one complex form/view. In raw SQL I would probably select a bunch of columns from a bunch of tables and edit that one record on the primary table as well as related parent/child tables.

In hibernate I would probably just pull a JPA entity for the main table and let hibernate fetch the parent/child relationships as I populate the view. And then later pull from my view back to the entity and call entitymanger .perist/merge.

In JOOQ I have a number of options but it appears you can pull a main record via selectFrom/fetch then use fetchChild fetchParent to pull typed related records like so...

    LoadsRecord load = dslContext.selectFrom(LOADS)
            .where(LOADS.ID.eq(id))
            .fetchOne();
    SafetyInspectionsRecord safetyInspection = load.fetchParent(Keys.LOADS__FK_SAFETY_INSPECTION);

So this way I am able to pull related records in a typesafe manner. The only annoying thing is I have to run another full query every time I call fetchParent or fetchDhild. Is there a way to eagerly fetch these all at once to avoid multiple round trips to the DB?

It is really nice to have these classes like LoadsRecord for CRUD screens, it makes updating the DB easy.

Answer 1

Classic approach using joins

There are various ways you can achieve materialising a to-one relationship. The simplest one being a simple JOIN or LEFT JOIN if the relationship is optional.

E.g.:

Result<?> result =
ctx.select()
   .from(LOADS)
   .join(SAFETY_INSPECTIONS)
   .on(LOADS.SAFETY_INSPECTIONS_ID.eq(SAFETY_INSPECTIONS.ID))
   .fetch();

You probably want to work with the generated records thereafter, so you can use various mapping tools to map the generic Record types to the two UpdatableRecord types for further CRUD:

for (Record r : result) {
    LoadsRecord loads = r.into(LOADS);
    SafetyInspectionsRecord si = r.into(SAFETY_INSPECTIONS);
}

Using nested records

Starting from jOOQ 3.15 and #11812, MULTISET and ROW operators can be used to create nested collections and records. So, in your query, you could write:

Result<?> result =
ctx.select(
      row(LOADS.ID, ...), 
      row(SAFETY_INSPECTIONS.ID, ...))
   .from(LOADS)
   .join(SAFETY_INSPECTIONS)
   .on(LOADS.SAFETY_INSPECTIONS_ID.eq(SAFETY_INSPECTIONS.ID))
   .fetch();

That would already help map the nested data structures into the desired format. Starting from jOOQ 3.17 and #4727, you can even use table expressions directly to generate nested records:

Result<Record2<LoadsRecord, SafetyInspectionsRecord>> result =
ctx.select(LOADS, SAFETY_INSPECTIONS)
   .from(LOADS)
   .join(SAFETY_INSPECTIONS)
   .on(LOADS.SAFETY_INSPECTIONS_ID.eq(SAFETY_INSPECTIONS.ID))
   .fetch();

This new feature is definitely going to close one of jOOQ's biggest gaps. You could even simplify the above using implicit joins to this:

Result<Record2<LoadsRecord, SafetyInspectionsRecord>> result =
ctx.select(LOADS, LOADS.safetyInspections())
   .from(LOADS)
   .fetch();