Jooq中的一对多选择 [英] One-to-many select in Jooq

问题描述

我正在尝试JOOQ，并尝试使用join语句从3个表(作者，书籍和文章)中进行选择.ERD如下:

I am trying out JOOQ and trying to select from 3 tables (Author, Books and Articles) using a join statement. The ERD is as follows:

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对象，如下所示:

I also have a protobuf object as follows:

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

(或与此有关的任何其他pojo)会将所有实体(作者详细信息+图书清单+文章清单)保存在一个对象中.我的问题是，是否有某种方法可以使用JOOQ将所有三个表映射到一个对象.

(or any other pojo for that matter) which will hold all the entities (author details + list of books + list of articles) into one object. My question is, is there some way to map out of the box all three tables into one object using JOOQ.

谢谢.

推荐答案

使用 JOIN 对此无效.

您的查询效率会非常低下，因为如果您以这种方式使用联接，则会创建书籍和文章表格之间的笛卡尔积，导致在删除所有无意义的组​​合之前，数据库和Java客户端中的内存和CPU消耗都相当大.

Using JOIN doesn't work for this.

Your query will be rather inefficient because if you're using joins this way, you're creating a cartesian product between the books and the articles table, resulting in quite some memory and CPU consumption both in the database and in your Java client, before you de-duplicate all the meaningless combinations.

正确"的SQL方法将使用 jOOQ 3.9目前还不支持 MULTISET (也没有很多数据库).因此，您应该创建两个单独的查询:

The "correct" SQL approach would be to use MULTISET as described in this article here. Unfortunately, jOOQ 3.9 doesn't support MULTISET yet (nor do many databases). So, you should create two separate queries:

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

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

And then use something like Java 8 Streams to map them into a single object.

幸运的是，从jOOQ 3.14开始，如果您的RDBMS支持，则可以通过SQL/XML或SQL/JSON对这种样式的嵌套集合提供开箱即用的解决方案.您可以生成一个文档，然后使用诸如Gson，Jackson或JAXB之类的文档将其映射回Java类.例如:

Luckily, starting from jOOQ 3.14, there's an out-of-the-box solution to this style of nesting collections via SQL/XML or SQL/JSON, if your RDBMS supports that. You can produce a document, and then use something like Gson, Jackson, or JAXB to map it back to your Java classes. For example:

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

这篇关于Jooq中的一对多选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！