用jOOQ加载PostgreSQL树

Question

我在PostgreSQL中有以下结构：

TableA has many TableB has many TableC

这三个表（简化）如下：

create table TableA (
   idA int,
   nameA varchar(100));


create table TableB (
   idA int,
   idB int,
   nameB varchar(100));


create table TableC (
   idA int,
   idB int,
   idC int,
   nameC varchar(100));

我需要在PostgreSQL上运行jOOQ，选择TableA，但它也应该从TableB和TableC加载任何相关的行。有没有办法运行单个jOOQ语句来加载这棵树？我知道我可以使用连接s来执行此操作，但我正在尝试避免Java中的任何循环。

Answer 1

从jOOQ 3.15开始，可以使用标准的SQL多集运算符嵌套集合，该运算符使用SQL/XML或SQL/JSON进行模拟

var result =
ctx.select(
      TABLE_A.ID_A, TABLE_A.NAME_A,
      multiset(
        select(
          TABLE_B.ID_B, TABLE_B.NAME_B,
          multiset(
            select(TABLE_C.ID_C, TABLE_C.NAME_C)
            .from(TABLE_C)
            .where(TABLE_C.ID_B.eq(TABLE_B.ID_B))
            .and(TABLE_C.ID_A.eq(TABLE_B.ID_A))
          ).as("c")
        )
        .from(TABLE_B)
        .where(TABLE_B.ID_A.eq(TABLE_A.ID_A))
      ).as("b")
    )
   .from(TABLE_A)
   .fetch();

结果的类型推断为：

Result<Record3<
  Integer,          // TABLE_A.ID_A
  String,           // TABLE_A.NAME_A
  Result<Record3< 
    Integer,        // TABLE_B.ID_B
    String,         // TABLE_B.NAME_B
    Result<Record2<
      Integer,      // TABLE_C.ID_C
      String        // TABLE_C.NAME_C
    >>
  >>
>> result = ...

如果您希望将其映射到一些自定义DTO数据结构中，例如。

record TableC(int idC, String nameC) {}
record TableB(int idB, String nameB, List<TableC> c) {}
record TableA(int idA, String nameA, List<TableB> b) {}

您可以使用jOOQ 3.15的新ad-hoc转换功能轻松做到这一点：

List<TableA> result =
ctx.select(
      TABLE_A.ID_A, TABLE_A.NAME_A,
      multiset(
        select(
          TABLE_B.ID_B, TABLE_B.NAME_B,
          multiset(
            select(TABLE_C.ID_C, TABLE_C.NAME_C)
            .from(TABLE_C)
            .where(TABLE_C.ID_B.eq(TABLE_B.ID_B))
            .and(TABLE_C.ID_A.eq(TABLE_B.ID_A))
          ).as("c").convertFrom(r -> r.map(Records.mapping(TableC::new)))
        )
        .from(TABLE_B)
        .where(TABLE_B.ID_A.eq(TABLE_A.ID_A))
      ).as("b").convertFrom(r -> r.map(Records.mapping(TableB::new)))
    )
   .from(TABLE_A)
   .fetch(Records.mapping(TableA::new));

所有映射都是类型安全、编译时检查和无反射的

您可以使用jOOQ 3.14的SQL/XML或SQL/JSON支持来实现这一点，请参阅此处的博客文章

如果类路径上有Gson或Jackson，它们可用于将XML或JSON结构映射回Java类层次结构。手册中关于ConverterProvider的页面给出了一个示例

本质上：

ctx.select(
     jsonObject(
       key("idA").value(TABLE_A.ID_A),
       key("nameA").value(TABLE_A.NAME_A),
       key("b").value(
         select(jsonArrayAgg(jsonObject(
           key("idB").value(TABLE_B.ID_B),
           key("nameB").value(TABLE_B.NAME_B),
           key("c").value(
             select(jsonArrayAgg(jsonObject(
               key("idC").value(TABLE_C.ID_C),
               key("nameC").value(TABLE_C.NAME_C)
             )))
             .from(TABLE_C)
             .where(TABLE_C.ID_B.eq(TABLE_B.ID_B))
           )
         )))
         .from(TABLE_B)
         .where(TABLE_B.ID_A.eq(TABLE_A.ID_A))
       )
     )
   )
   .from(TABLE_A)
   .fetch();

另请参阅此处的相关问题：

• jOOQ-具有多对多关系的嵌套对象
• 具有一对多关系的jooq单个查询
• JOOQ pojos具有一对多和多对多的关系

请注意，JSON\u ARRAYAGG（）将空集聚合为空集，而不是空集。如果这是一个问题，请使用COALESCE（）