jOOQ can I fetch a join of two tables into the respective POJOs

In jOOQ if I want to fetch a row of a table into a jOOQ autogenerated POJOs I do, for instance:

dsl.selectFrom(USER)
                .where(USER.U_EMAIL.equal(email))
                .fetchOptionalInto(User.class);

Now, suppose that I want to do a join between two tables, e.g. USER and ROLE, how can I fetch the result into the POJOs for these two tables?

Answer

Using nested collections

With more recent versions of jOOQ, you’ll typically use a set of ORDBMS features, including:

• nested collections using MULTISET
• nested records and nested table records
• ad-hoc converters

You’ll write something like this, to produce jOOQ types:

Result<Record2<UserRecord, Result<Record1<RoleRecord>>>> result =
dsl.select(
        USER,
        multiset(
            selectFrom(USER_ROLE.role())
            .where(USER_ROLE.USER_ID.eq(USER.ID))
        ))
   .from(USER)
   .where(USER.U_EMAIL.equal(email))
   .fetch();

Or, by using said ad-hoc converters, to produce your own types:

List<User> result =
dsl.select(
        USER.U_ID,
        USER.U_EMAIL, 
        ...
        multiset(
            selectFrom(USER_ROLE.role())
            .where(USER_ROLE.USER_ID.eq(USER.ID))
        ).convertFrom(r -> r.map(Records.mapping(Role::new))))
   .from(USER)
   .where(USER.U_EMAIL.equal(email))
   .fetch(Records.mapping(User::new));

Historic answers / alternatives

There are other ways to achieve something like the above, for completeness’ sake:

Fetching the POJOs into a Map

This is one solution using ResultQuery.fetchGroups(RecordMapper, RecordMapper)

Map<UserPojo, List<RolePojo>> result =
dsl.select(USER.fields())
   .select(ROLE.fields())
   .from(USER)
   .join(USER_TO_ROLE).on(USER.USER_ID.eq(USER_TO_ROLE.USER_ID))
   .join(ROLE).on(ROLE.ROLE_ID.eq(USER_TO_ROLE.ROLE_ID))
   .where(USER.U_EMAIL.equal(email))
   .fetchGroups(

       // Map records first into the USER table and then into the key POJO type
       r -> r.into(USER).into(UserPojo.class),

       // Map records first into the ROLE table and then into the value POJO type
       r -> r.into(ROLE).into(RolePojo.class)
   );

Note, if you want to use LEFT JOIN instead (in case a user does not necessarily have any roles, and you want to get an empty list per user), you’ll have to translate NULL roles to empty lists yourself.

Make sure you have activated generating equals() and hashCode() on your POJOs in order to be able to put them in a HashMap as keys:

<pojosEqualsAndHashCode>true</pojosEqualsAndHashCode>

Using custom, hierarchical POJOs and fetching them into a nested collection

A frequently re-occurring question is how to fetch nested collections in jOOQ, i.e. what if your result data structures look like this:

class User {
  long id;
  String email;
  List<Role> roles;
}

class Role {
  long id;
  String name;
}

Starting with jOOQ 3.14, and if your RDBMS supports it, you can now use SQL/XML or SQL/JSON as an intermediary format to nest collections, and then use Jackson, Gson, or JAXB to map the document back to your Java classes (or keep the XML or JSON, if that’s what you needed in the first place). For example:

List<User> users =
ctx.select(
      USER.ID,
      USER.EMAIL,
      field(
        select(jsonArrayAgg(jsonObject(ROLE.ID, ROLE.NAME)))
        .from(ROLES)
        .join(USER_TO_ROLE).on(ROLE.ROLE_ID.eq(USER_TO_ROLE.ROLE_ID))
        .where(USER_TO_ROLE.USER.ID.eq(USER.ID))
      ).as("roles")
    )
    .from(USER)
    .where(USER.EMAIL.eq(email))
    .fetchInto(User.class);

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