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

我还有一个 protobuf 对象如下:

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

(或与此相关的任何其他 pojo)将所有实体(作者详细信息 + 书籍列表 + 文章列表)保存到一个对象中。我的问题是，是否有某种方法可以使用 JOOQ 将所有三个表开箱即用地映射到一个对象中。

提前致谢。

Answer 1

使用 JOIN 对此不起作用。

您的查询将相当低效，因为如果您以这种方式使用连接，您将创建一个 cartesian product在 books 和 articles 表之间，在删除所有无意义的组​​合之前，会导致数据库和 Java 客户端中相当多的内存和 CPU 消耗。

“正确”的 SQL 方法是使用 MULTISET as described in this article here .不幸的是，jOOQ 3.9 doesn't support MULTISET yet (也没有很多数据库)。因此，您应该创建两个单独的查询:

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

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

从 jOOQ 3.15 开始使用 MULTISET

幸运的是，从 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);

使用类型安全，ad-hoc conversion

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

For more information about MULTISET, please refer to this blog post ，或手册部分:

• MULTISET value constructor
• MULTISET_AGG

从 jOOQ 3.14 开始使用 SQL/XML 或 SQL/JSON

从 jOOQ 3.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 中，而不是聚合到空的 [] 中。 If that's a problem, use COALESCE()