jOOQ can I fetch a join of two tables into the res

Question

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 1

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>