java - 如何使用 Jooq 获取多级一对多嵌套

Question

这是场景，我有 5 个表，彼此之间有一对多的关系，我必须在下面给定的 Pojos 和 Jooq 中以分层方式映射结果数据。
数据库表是 a、b、c、d、e

// Here are response Pojo's
Class APojo {
  public string name;
  public List<BPojo> listOfB;

}

Class BPojo {
  public string name;
  public List<CPojo> listOfC;
}

Class CPojo {
  public string name;
  public List<DPojo> listOfD;

}

Class DPojo {
  public string name;
  public List<EPojo> listOfE;

}

Class EPojo {
  public string name;

}

预期样本响应

{
  "name":"A1",
  "list_of_b":[
    {
      "name":"A1B1",
      "list_of_c":[
        {
          "name":"A1B1C1",
          "list_of_d":[
            {
              "name":"A1B1C1D1",
              "list_of_e":[
                {
                  "name":"A1B1C1D1E1"
                },
                {
                  "name":"A1B1C1D1E2"
                }
              ]
            },
            {
              "name":"A1B1C1D2",
              "list_of_e":[
                {
                  "name":"A1B1C1D2E1"
                },
                {
                  "name":"A1B1C1D2E2"
                }
              ]
            }
          ]
        },
        {
          "name":"A1B1C2",
          "list_of_d":[
            {
              "name":"A1B1C2D1",
              "list_of_e":[
                {
                  "name":"A1B1C2D1E1"
                },
                {
                  "name":"A1B1C2D1E2"
                }
              ]
            },
            {
              "name":"A1B1C2D2",
              "list_of_e":[
                {
                  "name":"A1B1C2D2E1"
                },
                {
                  "name":"A1B1C2D2E2"
                }
              ]
            }
          ]
        }
      ]
    },
    {
      "name":"A1B2",
      "list_of_c":[
        {
          "name":"A1B2C1",
          "list_of_d":[
            {
              "name":"A1B2C1D1",
              "list_of_e":[
                {
                  "name":"A1B1C1D1"
                },
                {
                  "name":"A1B1C1D2"
                }
              ]
            },
            {
              "name":"A1B2C1D2",
              "list_of_e":[
                {
                  "name":"A1B1C1D1"
                },
                {
                  "name":"A1B1C1D2"
                }
              ]
            }
          ]
        },
        {
          "name":"A1B2C2",
          "list_of_d":[
            {
              "name":"A1B2C2D1",
              "list_of_e":[
                {
                  "name":"A1B1C1D1"
                },
                {
                  "name":"A1B1C1D2"
                }
              ]
            },
            {
              "name":"A1B2C2D2",
              "list_of_e":[
                {
                  "name":"A1B1C1D1"
                },
                {
                  "name":"A1B1C1D2"
                }
              ]
            }
          ]
        }
      ]
    }
  ]
}

我首先尝试了这样的事情，但它没有用，因为 fetch groups 只接受 2 个参数

using(configuration()).select(A.fields())
    .select(B.fields())
    .select(C.fields())
    .select(D.fields())
    .select(E.fields())
    .from(A)
    .join(B).on(A.ID.eq(B.A_ID)
    .join(C).on(B.ID.eq(C.B_ID)
    .join(D).on(C.ID.eq(D.C_ID)
    .join(E).on(D.ID.eq(E.D_ID)
    .fetchGroups(
        r -> r.into(A).into(APojo.class),
        r -> r.into(B).into(BPojo.class),
        r -> r.into(C).into(CPojo.class),
        r -> r.into(D).into(DPojo.class),
        r -> r.into(E).into(EPojo.class)
     ); 

Then I got this post并按照下面给出的方法和帖子中给出的其他 2 种方法进行了尝试，但这也没有奏效，因为 Collectors.toMap只接受 2 个参数，我必须获取 5 级分层数据。

using(configuration()).select(A.fields())
        .select(B.fields())
        .select(C.fields())
        .select(D.fields())
        .select(E.fields())
        .from(A)
        .join(B).on(A.ID.eq(B.A_ID)
        .join(C).on(B.ID.eq(C.B_ID)
        .join(D).on(C.ID.eq(D.C_ID)
        .join(E).on(D.ID.eq(E.D_ID)
        .collect(Collectors.groupingBy(
            r -> r.into(A).into(APojo.class),
            Collectors.toMap(
                r -> r.into(B).into(BPojo.class),
                r -> r.into(C).into(CPojo.class)
                r -> r.into(D).into(DPojo.class)
                r -> r.into(E).into(EPojo.class)
 )));

Answer 1

JOIN方法
历史上，大多数 ORM 尝试使用连接以某种方式嵌套集合，因为连接是 SQL 中“连接”集合(但不嵌套)的唯一广泛支持的方式。结果是一个扁平的、非规范化的表，很难再次规范化。有很多重复项，甚至可能是不需要的笛卡尔积，甚至可能无法确定哪个嵌套集合属于父值。在您的情况下，这是可能的，但在服务器和客户端上都非常浪费。 A 的值会重复很多很多次。
已经实现了一些变通方法，包括第三方(对于 jOOQ)。替代方法包括运行多个查询并在之后连接值。所有这些都非常乏味。
幸运的是，jOOQ 3.14+ 支持开箱即用的嵌套集合!
使用 SQL/JSON 的 jOOQ 3.14 方法
The jOOQ 3.14 appraoch to nesting collections is using SQL/JSON在幕后(或 SQL/XML，但在您的情况下，JSON 似乎更合适)。
从你的问题来看，我不明白为什么你需要 POJO 中间步骤，所以也许你可以绕过它并直接在数据库中生成 JSON。如果没有，请参见下文。
编写此查询:

ctx
  .select(
    // Optionally, wrap this level in jsonArrayAgg(jsonObject()) too, like the others
    A.NAME,
    field(
      select(jsonArrayAgg(jsonObject(
        key("name").value(B.NAME),
        key("list_of_c").value(
          select(jsonArrayAgg(jsonObject(
            key("name").value(C.NAME),
            key("list_of_d").value(
              select(jsonArrayAgg(jsonObject(
                key("name").value(D.NAME),
                key("list_of_e").value(
                  select(jsonArrayAgg(jsonObject(key("name").value(E.NAME))))
                  .from(E)
                  .where(E.D_ID.eq(D.ID))
                )
              )))
              .from(D)
              .where(D.C_ID.eq(C.ID))
            )
          )))
          .from(C)
          .where(C.B_ID.eq(B.ID))
        )
      )))
      .from(B)
      .where(B.A_ID.eq(A.ID))
    ).as("list_of_b")
  )
  .from(A)
  .fetch();

假设通常的静态导入:

import static ord.jooq.impl.DSL.*;

由于 jOOQ 是关于 dynamic SQL ，很有可能，您可以使用动态 SQL 自动执行一些嵌套。
以上所有内容也适用于 JSONB在 PostgreSQL 中，只需使用 jsonbArrayAgg() 和 jsonbObject() 反而。
请注意 JSON_ARRAYAGG()将空集聚合为 NULL ，不成空 [] . If that's a problem, use COALESCE()
将上述内容映射到 POJO
如果你的类路径上有 Jackson 或 Gson，你现在可以写 fetchInto(APojo.class) 最后映射生成的 JSON 树。但是您可能只是要使用 Jackson 或 Gson 再次将 POJO 映射回 JSON，因此从高层次来看，我认为您不会从这一步中获得很多值(value)。
jOOQ 3.15 嵌套集合的方法
从 jOOQ 3.15 开始，将实现对类型安全映射和嵌套集合的一些改进

#3884 MULTISET和 ARRAY来自子查询支持的构造函数(终于!)

#7100特设 Field数据类型转换方便

#11804 Record[N] 的类型安全映射类型到构造函数引用

#11812支持嵌套 ROW投影中的表达式

有了上述所有内容，以防您真的需要 POJO 中间步骤(并假设您的 POJO 上将具有必要的“不可变构造函数”，例如 Java 16+ 中的规范记录构造函数)。例如。

record EPojo (String name) {}
record DPojo (String name, EPojo[] listOfE) {}
record CPojo (String name, DPojo[] listOfD) {}
record BPojo (String name, CPojo[] listOfC) {}
record APojo (String name, BPojo[] listOfB) {}

在这种情况下，您将能够编写如下内容:

ctx
  .select(A.NAME, array(
     select(row(B.NAME, array(
       select(row(C.NAME, array(
         select(row(D.NAME, array(
           select(row(E.NAME).mapping(EPojo::new))
           .from(E)
           .where(E.D_ID.eq(D.ID))
         )).mapping(DPojo::new))
         .from(D)
         .where(D.C_ID.eq(C.ID))
       )).mapping(CPojo::new))
       .from(C)
       .where(C.B_ID.eq(B.ID))
     )).mapping(BPojo::new))
     .from(B)
     .where(B.A_ID.eq(A.ID))
  )
  .from(A)
  .fetch(Records.mapping(APojo::new));

如果您喜欢 List<SomePojo>在 SomePojo[] ，那么您只需要使用新的从数组的临时转换来列出数组表达式，例如

array(select(...)).convertFrom(Arrays::asList)

一旦 API 稳定下来，我将更新答案的这一部分。
进一步展望
由于这些类型的“嵌套集合连接”在 jOOQ 中将变得非常普遍，无论 SQL 集合是嵌套在 SQL、JSON 集合还是 XML 集合中，jOOQ 的 future 版本可能会提供更方便的语法， similar to the implicit join syntax for ordinary to-one joins .