jOOQ我可以将两个表的联接提取到各自的POJO中吗 [英] jOOQ can I fetch a join of two tables into the respective POJOs

问题描述

在jOOQ中，如果我想将一张表的行提取到jOOQ自动生成的POJO中，例如:

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);

现在，假设我想在两个表之间进行联接，例如USER和ROLE，如何将这两个表的结果提取到POJO中?

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?

推荐答案

将POJO提取到Map

这是使用 ResultQuery.fetchGroups(RecordMapper, RecordMapper)

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)
   );

请注意，如果您想改用LEFT JOIN(如果用户不一定具有任何角色，并且希望获得每个用户的空列表)，则必须将NULL角色转换为空列出自己.

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.

确保已激活在POJO上生成equals()和hashCode()的功能，以便能够将它们作为键放置在HashMap中:

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>

使用自定义的分层POJO并将其提取到嵌套集合中

一个经常发生的问题是如何在jOOQ中获取嵌套的集合，即，如果结果数据结构看起来像这样:

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;
}

从jOOQ 3.14开始，如果您的RDBMS支持它，则现在可以使用SQL/XML或SQL/JSON作为中介格式来嵌套集合，然后使用Jackson，Gson或JAXB将文档映射回您的Java类(或保留XML或JSON，如果您首先需要的是XML或JSON).例如:

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);

这篇关于jOOQ我可以将两个表的联接提取到各自的POJO中吗的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！