JOOQ can get the union of two tables in the corresponding POJO

Question

In jOOQ, if I want to get a table row into auto-generated POJO jOOQ, for example:

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

Now suppose I want to make a join between two tables, for example. USERand ROLE, how can I get the result in POJO 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)
   );

Please note that if you want to use it instead LEFT JOIN(in case the user does not necessarily have any roles and you want to get an empty list for each user), you will have to translate the roles NULLinto empty lists yourself.

, equals() hashCode() POJO, HashMap :

<pojosEqualsAndHashCode>true</pojosEqualsAndHashCode>