Load PostgreSQL tree with jOOQ

I have the following structure in PostgreSQL:

TableA has many TableB has many TableC

These three tables (simplified) are as follows:

create table TableA (
   idA int,
   nameA varchar(100));


create table TableB (
   idA int,
   idB int,
   nameB varchar(100));


create table TableC (
   idA int,
   idB int,
   idC int,
   nameC varchar(100));

I need to run with jOOQ on PostgreSQL a select of TableA, but it should also load any related rows from TableB and TableC. Is there a way to run a single jOOQ statement to load this tree? I know that I could do this with joins, but I'm trying to avoid any loops in Java.

Solution

jOOQ 3.15 solution using `MULTISET`

Starting with jOOQ 3.15, you can nest collections using the standard SQL MULTISET operator, which is emulated using SQL/XML or SQL/JSON

var result =
ctx.select(
      TABLE_A.ID_A, TABLE_A.NAME_A,
      multiset(
        select(
          TABLE_B.ID_B, TABLE_B.NAME_B,
          multiset(
            select(TABLE_C.ID_C, TABLE_C.NAME_C)
            .from(TABLE_C)
            .where(TABLE_C.ID_B.eq(TABLE_B.ID_B))
            .and(TABLE_C.ID_A.eq(TABLE_B.ID_A))
          ).as("c")
        )
        .from(TABLE_B)
        .where(TABLE_B.ID_A.eq(TABLE_A.ID_A))
      ).as("b")
    )
   .from(TABLE_A)
   .fetch();

The type of result is inferred to:

Result<Record3<
  Integer,          // TABLE_A.ID_A
  String,           // TABLE_A.NAME_A
  Result<Record3< 
    Integer,        // TABLE_B.ID_B
    String,         // TABLE_B.NAME_B
    Result<Record2<
      Integer,      // TABLE_C.ID_C
      String        // TABLE_C.NAME_C
    >>
  >>
>> result = ...

If you prefer to map this into some custom DTO data structure, e.g.

record TableC(int idC, String nameC) {}
record TableB(int idB, String nameB, List<TableC> c) {}
record TableA(int idA, String nameA, List<TableB> b) {}

You can do this easily using jOOQ 3.15's new ad-hoc conversion feature:

List<TableA> result =
ctx.select(
      TABLE_A.ID_A, TABLE_A.NAME_A,
      multiset(
        select(
          TABLE_B.ID_B, TABLE_B.NAME_B,
          multiset(
            select(TABLE_C.ID_C, TABLE_C.NAME_C)
            .from(TABLE_C)
            .where(TABLE_C.ID_B.eq(TABLE_B.ID_B))
            .and(TABLE_C.ID_A.eq(TABLE_B.ID_A))
          ).as("c").convertFrom(r -> r.map(Records.mapping(TableC::new)))
        )
        .from(TABLE_B)
        .where(TABLE_B.ID_A.eq(TABLE_A.ID_A))
      ).as("b").convertFrom(r -> r.map(Records.mapping(TableB::new)))
    )
   .from(TABLE_A)
   .fetch(Records.mapping(TableA::new));

All of the mappings are type safe, compile time checked, and reflection free

jOOQ 3.14 solution using SQL/XML or SQL/JSON

You can use jOOQ 3.14's SQL/XML or SQL/JSON support for this, see this blog post here

If you have Gson or Jackson on the classpath, they can be used to map the XML or JSON structure back to your Java class hierarchies. An example for that is given on the manual's page about ConverterProvider

Essentially:

ctx.select(
     jsonObject(
       key("idA").value(TABLE_A.ID_A),
       key("nameA").value(TABLE_A.NAME_A),
       key("b").value(
         select(jsonArrayAgg(jsonObject(
           key("idB").value(TABLE_B.ID_B),
           key("nameB").value(TABLE_B.NAME_B),
           key("c").value(
             select(jsonArrayAgg(jsonObject(
               key("idC").value(TABLE_C.ID_C),
               key("nameC").value(TABLE_C.NAME_C)
             )))
             .from(TABLE_C)
             .where(TABLE_C.ID_B.eq(TABLE_B.ID_B))
           )
         )))
         .from(TABLE_B)
         .where(TABLE_B.ID_A.eq(TABLE_A.ID_A))
       )
     )
   )
   .from(TABLE_A)
   .fetch();

Load PostgreSQL tree with jOOQ

jOOQ 3.15 solution using MULTISET

jOOQ 3.14 solution using SQL/XML or SQL/JSON

jOOQ 3.15 solution using `MULTISET`