Recursive JPA query?

user_365719 0
Viewed 28032

Does JPA 2 have any mechanism for running recursive queries?

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?

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

EDIT: According to this item, Hibernate does not have 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.

recursive-query jpa-2.0 jpql orm java
4 Answers

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-Persistence offers support for working with recursive CTEs on top of the JPA model.

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.

@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

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

user_4124460

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-Many Association like this:

    @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.

user_65839340
Related