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?

one dumb way is to call getters on each children recursively inside a transaction — AdamSkywalker, Dec 19 '15 at 13:13
I don't know if there is a better solution, we used something like this and it was OK — AdamSkywalker, Dec 19 '15 at 13:26

Vlad Mihalcea · Answer 1 · 2018-01-05T17:38:45.020

3

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();
});

edited Jan 05 '18 at 17:38

answered Dec 19 '15 at 19:37

Vlad Mihalcea

142,745
71
566
911

Hmm, depth-first tree traversal might incur in tons of queries, no? Can't the entire tree be fetched and mapped in one go? – Lukas Eder Dec 19 '15 at 19:59
There's any feature supporting this use case. The former requires many subselects, while the latter requires many ids passed to the IN query. Neither is a silver bullet. – Vlad Mihalcea Dec 19 '15 at 20:19
I'm talking about fetching the entire tree with hierarchical SQL, and then matching parents / children in memory. We already have all the IDs – Lukas Eder Dec 19 '15 at 22:01
So I experimented a bit with different permutations(original query + Vlad's query,EAGER+FETCH),and with small dataset of 7 entities: Vlad query+LAZY:1312 ms,5 queries fired Original query+LAZY:1192 ms,8 queries fired Vlad query+EAGER:1272 ms,5 queries fired Original query+EAGER:1229 ms,6 queries fired – Mandroid Dec 20 '15 at 04:16
2

@lukaseder JPA can only fetch collections with joins or subselect. It cannot reconstruct collections from disparate children because it cannot guarantee that all children have been fetched – Vlad Mihalcea Dec 20 '15 at 06:57
@mandroid It takes too much in my opinion. Make sure the FK is indexed. – Vlad Mihalcea Dec 20 '15 at 06:59
@VladMihalcea: Good point about the guarantee. But since IKnowWhatIAmDoing™ - isn't there any way to tell JPA (or the underlying implementation), that I *KNOW* I have fetched all the children (after all, I wrote a native query and I'm good at it), and that it shouldn't hit the database again? – Lukas Eder Dec 20 '15 at 10:20
I don't think there's anything like this. But it's difficult to reconstruct the whole graph back by following the links. But nothing out-of-the-box yet. – Vlad Mihalcea Dec 20 '15 at 10:44
2

@VladMihalcea it's easy to reconstruct a graph from sets based on links: Hib. has the type graph, it knows which values to produce hashes for (pk/fk pairs) and with that merging child sets with parents is easy. With hashmaps it's a simple tree merge algorithm which can benefit from pre-generated byte code if you want to make it really fast. There's no logical reason why children nodes can't be merged with parent nodes in the graph, all info is there. – Frans Bouma Dec 20 '15 at 12:01

How to fetch a whole entity graph using JPA

1 Answers1

Update with actual solution