【无标题】多集嵌套集合使不再有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的评论)

.如果你问我的话，这是个很麻烦的事情。

这是我最喜欢的关于这个主题的谷歌搜索：

我就把这个留在这里吧:joy: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

复制代码