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?
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>
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()
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With