Jooq中的一对多选择

Question

我正在尝试JOOQ，并试图使用连接语句从3个表（作者，书籍和文章）中进行选择。ERD如下：

Author ----< Books
   |
   |
   ^
Articles

我的问题如下：

    final List<Tuple3<AuthorRecord, BooksRecord, ArticlesRecord>> tupleList =
        persistenceContext.getDslContext()
            .select()
            .from(Author.AUTHOR)
            .join(Books.BOOKS)
            .on(Author.AUTHOR.ID.eq(Books.BOOKS.AUTHOR_ID))
            .join(Articles.ARTICLES)
            .on(Author.AUTHOR.ID.eq(Articles.ARTICLES.AUTHOR_ID))
            .where(Author.AUTHOR.ID.eq(id))
            .fetch()
            .map(r -> Tuple.tuple(r.into(Author.AUTHOR).into(AuthorRecord.class),
                r.into(Books.BOOKS).into(BooksRecord.class),
                r.into(Articles.ARTICLES).into(ArticlesRecord.class)));

我也有一个原型对象如下：

message Author {
    int64 id = 1;
    string name = 2;
    repeated string books = 3;
    repeated string articles = 4;
}

（或任何其他pojo）将所有实体（作者详细信息图书列表文章列表）保存到一个对象中。我的问题是，是否有某种方法可以使用JOOQ将所有三个表映射到一个对象中。

提前谢谢。

Answer 1

您的查询将相当低效，因为如果您以这种方式使用连接，您将在图书和文章表之间创建笛卡尔乘积，导致数据库和Java客户端中的内存和CPU消耗相当大-复制所有无意义的组合。

正确的SQL方法是使用MULTISET，如本文所述。不幸的是，jOOQ 3.9还不支持MULTISET（许多数据库也不支持）。因此，您应该创建两个独立的查询：

1. 取所有的书
2. 获取所有文章

然后使用Java8流之类的东西将它们映射到单个对象中。

幸运的是，从jOOQ 3.15开始，有一个现成的解决方案可以使用MULTISET在SQL中嵌套集合。您的查询如下所示：

使用反射

List<Author> authors =
ctx.select(
      AUTHOR.ID,
      AUTHOR.NAME,
      multiset(
        select(BOOKS.TITLE)
        .from(BOOKS)
        .where(BOOKS.AUTHOR_ID.eq(AUTHOR.ID))
      ).as("books"),
      multiset(
        select(ARTICLES.TITLE)
        .from(ARTICLES)
        .where(ARTICLES.AUTHOR_ID.eq(AUTHOR.ID))
      ).as("articles")
    )
   .from(AUTHOR)
   .where(AUTHOR.ID.eq(id))
   .fetchInto(Author.class);

使用类型安全的特殊转换

java prettyprint-override">List<Author> authors =
ctx.select(
      AUTHOR.ID,
      AUTHOR.NAME,
      multiset(
        select(BOOKS.TITLE)
        .from(BOOKS)
        .where(BOOKS.AUTHOR_ID.eq(AUTHOR.ID))
      ).as("books").convertFrom(r -> r.map(Record1::value1)),
      multiset(
        select(ARTICLES.TITLE)
        .from(ARTICLES)
        .where(ARTICLES.AUTHOR_ID.eq(AUTHOR.ID))
      ).as("articles").convertFrom(r -> r.map(Record1::value1))
    )
   .from(AUTHOR)
   .where(AUTHOR.ID.eq(id))
   .fetch(Records.mapping(Author::new));

有关MULTISET的更多信息，请参阅本博文或手册部分：

• MULTISET值构造函数
• MULTISET\u AGG

从JOOQ3.14开始，如果RDBMS支持，您可以通过SQL/XML或SQL/JSON嵌套集合。您可以生成一个文档，然后使用Gson、Jackson或JAXB之类的工具将其映射回您的Java类。例如：

List<Author> authors =
ctx.select(
      AUTHOR.ID,
      AUTHOR.NAME,
      field(
        select(jsonArrayAgg(BOOKS.TITLE))
        .from(BOOKS)
        .where(BOOKS.AUTHOR_ID.eq(AUTHOR.ID))
      ).as("books"),
      field(
        select(jsonArrayAgg(ARTICLES.TITLE))
        .from(ARTICLES)
        .where(ARTICLES.AUTHOR_ID.eq(AUTHOR.ID))
      ).as("articles")
    )
   .from(AUTHOR)
   .where(AUTHOR.ID.eq(id))
   .fetchInto(Author.class);

请注意，JSON_ARRAYAGG（）将空集聚合为NULL，而不是聚合为空的[]。如果这是一个问题，请使用COALESCE（）