How to fetch a whole entity graph using JPA

Question

I am using JPA 2.0 with OpenJPA as underlying implementation. I have an entity which maps to itself to represent parent-child hierarchy between entities. An entity can have more than one children but a single parent at most. Entity with no parent is thus at top of hierarchy. My objective is to fetch all the hierarchies from data table.So I have query as:

SELECT e FROM MyEntity e where e.parent is null

In MyEntity I have done mapping as:

@ManyToOne
@JoinColumn(name="PARENT")
private MyEntity parent;

@OneToMany(mappedBy="parent", fetch=FetchType.EAGER)
private List<MyEntity> children;

When program runs, entities at top of hierarchies are populated with all of their children, but child entities don't have their children fetched.I was of the view that EAGER fetch would populate whole of the entity graph.But it is not so. In JPA 2.1 there is feature of EntityGraph ASAIK.But how can this be achieved in JPA 2.0?

Answer 1

The EAGER works on one level only, and it was not intended to fetch tree-like structures.

I suggest you change the one-to-many fetch to LAZY because EAGER fetching is a code smell, and fetch the root entity like this:

SELECT e 
FROM MyEntity e 
LEFT JOIN FETCH e.children 
where e.parent is null

The you use recursion to fetch all the graph with additional sub-selects.

void fetchAll(MyEntity root) {
    for(MyEntity child : root.children) {
        fetchAll(child);
    }
}

A more efficient approach is to ditch the children collection altogether and use recursive CTE on the FK association to fetch all ids of all entities in a given tree. Then with a second JPA query you fetch all entities by their ids and reconstruct the tree by matching the parents.

Update with actual solution

I added a test on GitHub to provide a solution for this. Considering the following entity:

@Entity(name = "Node")
public class Node  {

    @Id
    @GeneratedValue
    private Long id;

    @ManyToOne
    @JoinColumn(name = "parent_id")
    private Node parent;

    //This must be transient as otherwise it will trigger an additional collection fetch
    //@OneToMany(mappedBy = "parent", cascade = CascadeType.ALL)
    @Transient
    private List<Node> children = new ArrayList<>();

    public Node() {}

    public Node getParent() {
        return parent;
    }

    public List<Node> getChildren() {
        return children;
    }

    public void addChild(Node child) {
        children.add(child);
        child.parent = this;
    }
}

The following transformer can reconstruct the whole tree as you want to

Node root = (Node) doInHibernate(session -> {
    return session
        .createSQLQuery(
                "SELECT * " +
                "FROM Node " +
                "CONNECT BY PRIOR id = parent_id " +
                "START WITH parent_id IS NULL ")
        .addEntity(Node.class)
        .setResultTransformer(new ResultTransformer() {
            @Override
            public Object transformTuple(Object[] tuple, String[] aliases) {
                Node node = (Node) tuple[0];
                if(node.parent != null) {
                    node.parent.addChild(node);
                }
                return node;
            }

            @Override
            public List transformList(List collection) {
                return Collections.singletonList(collection.get(0));
            }
        })
        .uniqueResult();
});