jOOQ query with nested list

Question

Can jOOQ map query results to multiple nested lists within a POJO? For example, I have a POJO VM, which represents a virtual machine. It has a property of 'networks', which is a list of type Network. It also has a property of 'hdds', which is a list of type 'HDD'. The query joins the VM, HDD, and Networks tables. Can I just 'fetch' into VM.class and expect jOOQ to 'do the right thing'?

Answer 1

Using MULTISET in jOOQ 3.15

Starting with jOOQ 3.15, the preferred approach here is to use MULTISET. Assuming you have:

record Vm(String name, List<Network> networks, List<Hdd> hdds) {}

You can write

List<Vm> result =
ctx.select(
      VM.NAME,
      multiset(
        select(
          // Using implicit joins can help here, though entirely optional
          NETWORK_TO_VM.network().ID, 
          NETWORK_TO_VM.network().NAME)
        .from(NETWORK_TO_VM)
        .where(NETWORK_TO_VM.VM_ID.eq(VM.VM_ID))
      ).convertFrom(r -> r.map(Records.mapping(Network::new))),
      multiset(
        select(
          HDD_TO_VM.hdd().ID,
          HDD_TO_VM.hdd().NAME)
        .from(HDD_TO_VM)
        .where(HDD_TO_VM.VM_ID.eq(VM.VM_ID))
      ).convertFrom(r -> r.map(Records.mapping(Hdd::new)))
   )
   .from(VM)
   .fetch(Records.mapping(Vm::new));

Notice that the above approach is combining:

• Multiset
• Implicit joins
• Ad-hoc converters

All of this to make sure you can type check your object hierarchy directly from your SQL query, which fetches all data in only one go.

Using SQL/XML or SQL/JSON to nest collections in jOOQ 3.14

Starting with jOOQ 3.14, an option here is to use SQL/XML or SQL/JSON to nest collections directly in SQL. For example:

class Vm {
  String name;
  List<Network> networks;
  List<Hdd> hdds;
}

Now, you can write:

List<Vm> result =
ctx.select(
      VM.NAME,
      field(
        select(jsonArrayAgg(jsonObject(
          key("id").value(NETWORK.ID),
          key("name").value(NETWORK.NAME),
          // ...
        )))
        .from(NETWORK)
        .join(NETWORK_TO_VM).on(NETWORK.NETWORK_ID.eq(NETWORK_TO_VM.NETWORK_ID))
        .where(NETWORK_TO_VM.VM_ID.eq(VM.VM_ID))
      ).as("networks"),
      field(
        select(jsonArrayAgg(jsonObject(
          key("id").value(HDD.ID),
          key("name").value(HDD.NAME),
          // ...
        )))
        .from(HDD)
        .join(HDD_TO_VM).on(HDD.HDD_ID.eq(HDD_TO_VM.HDD_ID))
        .where(HDD_TO_VM.VM_ID.eq(VM.VM_ID))
      ).as("hdds")
   )
   .from(VM)
   .fetchInto(Vm.class);

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

Historic answer (pre jOOQ 3.14)

The main reason why such mapping features are only possible to a limited extent in a library like jOOQ is the fact that by joining tables and denormalising results, jOOQ no longer has the required information to correctly deduplicate the top-level tuples (VM in your case). Popular ORMs implementing JPA do not suffer from this restriction, as they do not allow you to express arbitrary joins.

But you have certain possibilities to map denormalised result sets into nested collections:

• With jOOQ's Result.intoGroups()
• With Java 8 Stream.collect()
• With ModelMapper
• With JPA