多集嵌套集合使不再有MultipleBagFetchException

我最近偶尔发现了这个有趣的Stack Overflow问题，关于Hibernate盛行的MultipleBagFetchException。这个问题超受欢迎，答案也很多。整个问题中讨论了各种限制，这一切都归结为一个简略的事实。

衔接是嵌套调集的错误东西

给出一个像Sakila数据库的形式：

之间存在着许多对许多的联系：

• ACTOR和FILM
• FILM和CATEGORY

没有什么特别的。问题是，当运用ORM时，考虑到O（Object，面向对象）的性质，咱们想用层次结构来表明这些数据，作为一个图，或许至少是一个树。当咱们想用JSON或XML表明时也是如此。

例如，在Java中，下面的DTO是上述形式的自然表明：

record Actor(
    String firstName, 
    String lastName
) {}
record Category(
    String name
) {}
record Film(
    String title,
    List<Actor> actors,
    List<Category> categories
) {}

在JSON中，这些数据或许看起来像这样：

[  {    "title": "ACADEMY DINOSAUR",    "actors": [      {        "first_name": "PENELOPE",        "last_name": "GUINESS"      },      {        "first_name": "CHRISTIAN",        "last_name": "GABLE"      },      {        "first_name": "LUCILLE",        "last_name": "TRACY"      },      {        "first_name": "SANDRA",        "last_name": "PECK"      },      ...    ],
    "categories": [
      { "name": "Documentary" }
    ]
  },
  {
    "title": "ACE GOLDFINGER",
    "actors": [
      {
        "first_name": "BOB",
        "last_name": "FAWCETT"
      },
  ...

运用衔接进行模仿嵌套

但在Hibernate和一般的SQL中，问题在于衔接会发生笛卡尔的产物。这实践上不是一个问题。这是SQL和联系代数的一个特色。咱们有一整篇博文讲述了咱们的行业是怎么运用Venn Diagrams来教授衔接的错误。

衔接是过滤的笛卡尔产品。这里有一个笛卡尔乘积的例子（没有过滤）：

现在，如果你想只用衔接来发生前面的嵌套调集表明法，那么你或许会写成这样的东西：

SELECT *
FROM film AS f
  JOIN film_actor AS fa USING (film_id)
    JOIN actor AS a USING (actor_id)
  JOIN film_category AS fc USING (film_id)
    JOIN category AS c USING (category_id)

我特意缩进了这些衔接，以阐明这种去规范化的树状结构。关于每部电影，咱们都要衔接：

• 许多艺人（例如：M ）。
• 许多类别（例如：N ）。

这意味着，由于衔接的性质是笛卡尔产品，咱们将电影重复了M * N 次。不仅如此，更糟糕的是，咱们还重复了：

• 每个艺人N 次（每个类别一次）。
• 每个类别M 次（每个艺人一次）。

最终，这甚至或许导致错误的成果，例如在聚合时，由于一些组合不该该被兼并。

除了潜在的正确性问题外，这也是一个非常大的功能问题。正如无处不在的Vlad在他的回答中解说的那样，JOIN FETCH 语法被主张与DISTINCT 和多重查询一起作为一种解决方法。然后你必须手动重新组合成果 (我更正一下，Hibernate负责重新组合，见下面Vlad的谈论)并恰当注意急迫和懒惰加载的问题(尽管你一般要小心处理这些问题，但它们在这里并不适用，请再看看下面Vlad的谈论).如果你问我的话，这是个很费事的工作。

这是我最喜欢的关于这个主题的谷歌查找：

我就把这个留在这里吧😂t.co/MHARwHbq2i[…

– Lukas Eder (@lukaseder)August 24, 2020

公平地说，在过去，jOOQ也有这样的费事–至少你不会由于不小心加载了整个数据库而射伤自己的脚。

实践嵌套

自从ORDBMS 被引入后（例如Informix、Oracle、PostgreSQL），以及更盛行的SQL/XML和SQL/JSON扩展被加入后，能够直接在SQL中履行实践嵌套。我现已在这个博客上多次提到过这个问题：

• jOOQ 3.14发布，支撑SQL/XML和SQL/JSON
• 用jOOQ 3.14的SQL/XML或SQL/JSON支撑嵌套调集
• 用jOOQ在其他RDBMS上运用SQL Server FOR XML和FOR JSON语法
• 中止在你的中间件中映射东西。运用SQL的XML或JSON操作符替代
• jOOQ 3.15的新多集操作符将改动你对SQL的思考方式

嵌套调集的正确方法是经过上述3种序列化格式（本地、JSON、XML）中的一种运用SQL。

经过上述技能，你能够将你的数据嵌套到Java中的任何嵌套DTO结构，或许任何嵌套JSON格式。这能够经过本地SQL或jOOQ完成。将来也或许用Hibernate，或许用其他在这个范畴跟随jOOQ的ORMs来完成。

鉴于这个Stack Overflow问题的受欢迎程度，咱们很难忽视多对多联系的嵌套是一个多么重要的问题，以及SQL（语言）和ORM长期以来是怎么忽视这个问题的，只提供乖僻的变通方法，让用户手动完成他们的序列化，而jOOQ现已展现了它是多么简略和透明。

今日就试试jOOQ的MULTISET操作符，无需等候，它就像这样简略：

List<Film> result =
dsl.select(
      FILM.TITLE,
      multiset(
        select(
          FILM_ACTOR.actor().FIRST_NAME, 
          FILM_ACTOR.actor().LAST_NAME)
        .from(FILM_ACTOR)
        .where(FILM_ACTOR.FILM_ID.eq(FILM.FILM_ID))
      ).as("actors").convertFrom(r -> r.map(mapping(Actor::new))),
      multiset(
        select(FILM_CATEGORY.category().NAME)
        .from(FILM_CATEGORY)
        .where(FILM_CATEGORY.FILM_ID.eq(FILM.FILM_ID))
      ).as("categories").convertFrom(r -> r.map(Record1::value1))
   )
   .from(FILM)
   .orderBy(FILM.TITLE)
   .fetch(mapping(Film::new));

而且上面的查询是类型安全的!只需你修改了你的DTO，该查询就不再编译了。不仅如此！jOOQ还有一个解析器，所以你能够假装你最喜欢的SQL方言今日现已支撑MULTISET了。在这里试试这个查询：https://www.jooq.org/translate/

SELECT
  f.title,
  MULTISET(
    SELECT a.first_name, a.last_name
    FROM film_actor AS fa
    JOIN actor AS a USING (actor_id)
    WHERE fa.film_id = f.film_id
  ) AS actors,
  MULTISET(
    SELECT c.name
    FROM film_category AS fc
    JOIN category AS c USING (category_id)
    WHERE fc.film_id = f.film_id
  ) AS categories
FROM film AS f
ORDER BY f.title

jOOQ的翻译器会把这个翻译成PostgreSQL上的以下内容：

SELECT
  f.title,
  (
    SELECT coalesce(
      jsonb_agg(jsonb_build_array("v0", "v1")),
      jsonb_build_array()
    )
    FROM (
      SELECT
        a.first_name AS "v0",
        a.last_name AS "v1"
      FROM film_actor AS fa
        JOIN actor AS a
          USING (actor_id)
      WHERE fa.film_id = f.film_id
    ) AS "t"
  ) AS actors,
  (
    SELECT coalesce(
      jsonb_agg(jsonb_build_array("v0")),
      jsonb_build_array()
    )
    FROM (
      SELECT c.name AS "v0"
      FROM film_category AS fc
        JOIN category AS c
          USING (category_id)
      WHERE fc.film_id = f.film_id
    ) AS "t"
  ) AS categories
FROM film AS f
ORDER BY f.title