java HQL 递归，我该怎么做？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2486757/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
HQL recursion, how do I do this?
提问by niklassaers
I have a tree structure where each Nodehas a parent and a Set<Node> children. Each Node has a String title, and I want to make a query where I select Set<String> titles, being the title of this node and of all parent nodes. How do I write this query?
我有一个树结构，其中每个Node都有一个父级和一个Set<Node> children. 每个节点都有一个String title, 我想在我选择的地方进行查询Set<String> titles，作为这个节点和所有父节点的标题。如何编写此查询？
The query for a single title is this, but like I said, I'd like it expanded for the entire branch of parents.
对单个标题的查询是这样的，但就像我说的，我希望它扩展到父母的整个分支。
SELECT node.title FROM Node node WHERE node.id = :id
Cheers
干杯
Nik
尼克
采纳答案by Bozho
You can't do recursive queries with HQL. See this. And as stated there it is not even standard SQL. You have two options:
您不能使用 HQL 进行递归查询。看到这个。正如那里所说，它甚至不是标准的 SQL。您有两个选择：
- write a vendor-specific recursive native SQL query
make multiple queries. For example:
// obtain the first node using your query while (currentNode.parent != null) { Query q = //create the query q.setParameter("id", currentNode.getParentId()); Node currentNode = (Node) q.getSingleResult(); nodes.add(currentNode); // this is the Set }
- 编写特定于供应商的递归原生SQL 查询
进行多次查询。例如：
// obtain the first node using your query while (currentNode.parent != null) { Query q = //create the query q.setParameter("id", currentNode.getParentId()); Node currentNode = (Node) q.getSingleResult(); nodes.add(currentNode); // this is the Set }
I'd definitely go for the 2nd option.
我肯定会选择第二个选项。
回答by James Gregory
While it isn't possible to write the recursive query you're asking for, it is possible to eager fetch the hierarchy with HQL; doing this would at least allow you to walk the tree in memory without hitting the database for each level.
虽然无法编写您所要求的递归查询，但可以使用 HQL 预先获取层次结构；这样做至少可以让您在内存中遍历树，而无需为每个级别访问数据库。
select n from Node n
left join fetch n.Children
回答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 NodeCTE {
@Id Integer id;
}
A query for your example could look like the following
您的示例的查询可能如下所示
List<String> titles = criteriaBuilderFactory.create(entityManager, String.class)
.withRecursive(NodeCTE.class)
.from(Node.class, "n1")
.bind("id").select("n1.id")
.where("n1.id").eq(nodeId)
.unionAll()
.from(Node.class, "n2")
.innerJoinOn(NodeCTE.class, "cte")
.on("cte.id").eq("n2.parent.id")
.end()
.bind("id").select("n2.id")
.end()
.from(Node.class, "n")
.select("n.title")
.where("n.id").in()
.from(NodeCTE.class, "c")
.select("c.id")
.end()
.getResultList();
This renders to SQL looking like the following
这将呈现给 SQL，如下所示
WITH RECURSIVE NodeCTE(id) AS (
SELECT n1.id
FROM Node n1
WHERE n1.parent_id = :id
UNION ALL
SELECT n2.id
FROM Node n2
INNER JOIN NodeCTE cte ON n2.parent_id = cte.id
)
SELECT n.title
FROM Node n
WHERE n.id IN (
SELECT c.id
FROM NodeCTE 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
