java 递归JPA查询？

Recursive JPA query?

提问by Mike Baranczak

Does JPA 2 have any mechanism for running recursive queries?

JPA 2 是否有任何运行递归查询的机制？

Here's my situation: I have an entity E, which contains an integer field x. It also may have children of type E, mapped via @OneToMany. What I'd like to do is find an E by primary key, and get its value of x, along with the x values of all its descendants. Is there any way to do this in a single query?

这是我的情况：我有一个实体 E，它包含一个整数字段 x。它也可能有 E 类型的孩子，通过@OneToMany 映射。我想要做的是通过主键找到一个 E，并获得它的 x 值，以及它所有后代的 x 值。有没有办法在单个查询中做到这一点？

I'm using Hibernate 3.5.3, but I'd prefer not to have any explicit dependencies on Hibernate APIs.

我使用的是 Hibernate 3.5.3，但我不希望对 Hibernate API 有任何显式依赖。

---

---

EDIT: According to thisitem, Hibernate does nothave this feature, or at least it didn't in March. So it seems unlikely that JPA would have it, but I'd like to make sure.

编辑：根据这一项目，Hibernate并没有具备这个功能，或者至少是在三月份没有。因此 JPA 似乎不太可能拥有它，但我想确定一下。

回答by Michael Barker

Using the simple Adjacency Modelwhere each row contains a reference to its parents which will refer to another row in same table doesn't co-operate well with JPA. This is because JPA doesn't have support for generating queries using the Oracle CONNECT BY clause or the SQL standard WITH statement. Without either of those 2 clauses its not really possible to make the Adjacency Model useful.

使用简单的邻接模型，其中每一行都包含对其父项的引用，该引用将引用同一表中的另一行，与 JPA 不能很好地协作。这是因为 JPA 不支持使用 Oracle CONNECT BY 子句或 SQL 标准 WITH 语句生成查询。如果没有这两个子句中的任何一个，就不可能真正使邻接模型有用。

However, there are a couple of other approaches to modelling this problem that can applied to this problem. The first is the Materialised Path Model. This is where the full path to the node is flattened into a single column. The table definition is extended like so:

但是，有几种其他方法可以对这个问题进行建模，这些方法可以应用于这个问题。第一个是物化路径模型。这是节点的完整路径被展平为单列的地方。表定义扩展如下：

CREATE TABLE node (id INTEGER,
                   path VARCHAR, 
                   parent_id INTEGER REFERENCES node(id));

To insert a tree of nodes looks some thing like:

插入节点树看起来像这样：

INSERT INTO node VALUES (1, '1', NULL);  -- Root Node
INSERT INTO node VALUES (2, '1.2', 1);   -- 1st Child of '1'
INSERT INTO node VALUES (3, '1.3', 1);   -- 2nd Child of '1'
INSERT INTO node VALUES (4, '1.3.4', 3); -- Child of '3'

So to get Node '1' and all of its children the query is:

因此，要获取节点“1”及其所有子节点，查询是：

SELECT * FROM node WHERE id = 1 OR path LIKE '1.%';

To map this to JPA just make the 'path' column an attribute of your persistent object. You will however have to do the book-keeping to keep the 'path' field up to date. JPA/Hibernate won't do this for you. E.g. if you move the node to a different parent you will have to update both the parent reference and determine the new path value from the new parent object.

要将其映射到 JPA，只需将“路径”列作为持久对象的属性即可。但是，您必须进行簿记以使“路径”字段保持最新。JPA/Hibernate 不会为你做这件事。例如，如果您将节点移动到不同的父节点，则必须更新父引用并从新的父对象确定新的路径值。

The other approach is called the Nested Set Model, which is bit more complex. Probably best describedby its originator (rather than added verbatim by me).

另一种方法称为嵌套集模型，它有点复杂。可能最好由其创始人描述（而不是我逐字添加）。

There is a third approach called Nested Interval Model, however this has a heavy reliance of stored procedures to implement.

还有第三种方法称为嵌套间隔模型，但是它严重依赖存储过程来实现。

A much more complete explanation to this problem is described in chapter 7 of The Art of SQL.

The Art of SQL 的第 7 章描述了对这个问题的更完整的解释。

回答by 99Sono

The best answer in this post seems like a massive work-around hack to me. I've already had to deal with data models where brilliant engineers decided it would be a good Idea to code Tree Hiarchies in DB fields as text such as: "Europe|Uk|Shop1|John" and with massive volumes of data in these tables. Not surprsingly, the performance of query of the form MyHackedTreeField LIKE 'parentHierharchy%' where killers. Addressing this type of problem ultimately required creating In memory cache of the tree hiearchies and so many others...

这篇文章中的最佳答案对我来说似乎是一个巨大的解决方法。我已经不得不处理数据模型，杰出的工程师认为将 DB 字段中的树层次结构编码为文本是一个好主意，例如：“Europe|Uk|Shop1|John”以及这些表中的大量数据. 不出所料，MyHackedTreeField LIKE 'parentHierharchy%' 形式的查询的性能是致命的。解决此类问题最终需要创建树层次结构的内存缓存和许多其他...

If you need to run a recursive query and your data volume is not massive... make your life simple and simply load the DB fields you need to run your plan. And code your recursion in java. Don't make it in the DB unless you have a good reason to do it.

如果您需要运行递归查询并且您的数据量并不大……让您的生活变得简单，只需加载运行您的计划所需的数据库字段。并在java中编码你的递归。除非有充分的理由，否则不要在数据库中使用它。

And even if the volume of data you have is massive, you most likely can subdivide your problem into indepent recursive tree batches and process those one at time without needing to load all the data at once.

即使您拥有的数据量很大，您也很可能可以将问题细分为独立的递归树批次，并一次处理这些批次，而无需一次加载所有数据。

回答by Mehdi Pourrostam

I had problem like this, querying a menu nodes from one table, The way I founded was this: suppose we have a class named Node,created a Unidirectional One-to-ManyAssociation like this:

我遇到了这样的问题，从一个表中查询菜单节点，我创建的方式是这样的：假设我们有一个名为 Node 的类，创建了一个Unidirectional One-to-Many像这样的关联：

    @OneToMany(  fetch = FetchType.EAGER)
    @JoinColumn(name = "parent_id", referencedColumnName = "id")
    private List<Node> subNodeList;

also have a filed named for example boolean isRoot in entity, to mention if this node is root menu item , and then, by querying for nodes that there isRoot is true, we just get top nodes and because of FetchType.EAGER, we also get sub nodes in List. This will cause multiple queries , but for small menu like things it will be ok.

在实体中还有一个命名为例如 boolean isRoot 的字段，以提及该节点是否为根菜单项，然后，通过查询 isRoot 为真的节点，我们只获得顶级节点，因为FetchType.EAGER，我们还获得了子节点列表。这将导致多个查询，但对于小菜单之类的东西就可以了。

回答by Christian Beikov

I know this question is old, but as it was linked in a different question, I wanted to give an update on this, as Blaze-Persistenceoffers support for working with recursive CTEs on top of the JPA model.

我知道这个问题很旧，但由于它与另一个问题相关联，因此我想对此进行更新，因为Blaze-Persistence支持在 JPA 模型之上使用递归 CTE。

Blaze-Persistence is a query builder on top of JPA which supports many of the advanced DBMS features on top of the JPA model. To model CTEs or recursive CTEs, which is what you need here, you first need to introduce a CTE entity that models the result type of the CTE.

Blaze-Persistence 是一个基于 JPA 的查询构建器，它支持基于 JPA 模型的许多高级 DBMS 功能。要对 CTE 或递归 CTE 进行建模，这正是您在这里需要的，您首先需要引入一个 CTE 实体，该实体对 CTE 的结果类型进行建模。

@CTE
@Entity
public class GroupCTE {
  @Id Integer id;
}

A query that fetches a hierarchy of groups could look like the following

获取组层次结构的查询可能如下所示

List<Group> groups = criteriaBuilderFactory.create(entityManager, Group.class)
  .withRecursive(GroupCTE.class)
    .from(Group.class, "g1")
    .bind("id").select("g1.id")
    .where("g1.parent").isNull()
  .unionAll()
    .from(Group.class, "g2")
    .innerJoinOn(GroupCTE.class, "cte")
      .on("cte.id").eq("g2.parent.id")
    .end()
    .bind("id").select("g2.id")
  .end()
  .from(Group.class, "g")
  .fetch("groups")
  .where("g.id").in()
    .from(GroupCTE.class, "c")
    .select("c.id")
  .end()
  .getResultList();

This renders to SQL looking like the following

这将呈现给 SQL，如下所示

WITH RECURSIVE GroupCTE(id) AS (
    SELECT g1.id
    FROM Group g1
    WHERE g1.parent_group_id IS NULL
  UNION ALL
    SELECT g2.id
    FROM Group g2
    INNER JOIN GroupCTE cte ON g2.parent_group_id = cte.id
)
SELECT *
FROM Group g
LEFT JOIN Group gsub ON gsub.parent_group_id = g.id
WHERE g.id IN (
  SELECT c.id
  FROM GroupCTE c
)

You can find out more about recursive CTEs in the documentation: https://persistence.blazebit.com/documentation/core/manual/en_US/index.html#recursive-ctes

您可以在文档中找到有关递归 CTE 的更多信息：https: //persistence.blazebit.com/documentation/core/manual/en_US/index.html#recursive-ctes