mat_boy mat_boy - 5 months ago 73
Java Question

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

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>