views:

72

answers:

1

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.

+2  A: 

Using the simple Adjacency Model where 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.

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:

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.

The other approach is called the Nested Set Model, which is bit more complex. Probably best described by 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.

Michael Barker
This is all rather more complex than what I was looking for, but thanks for the informative response. There's also a simpler alternative to the materialized path model - have each node store the ID of the root node of the tree that it belongs to. This means that you can only use the root node as the starting point of the query, but in my specific case that's not a problem - so that's probably what I'll do.
Mike Baranczak
You could use adjacency lists, managed through JPA, but use a native query to use recursive SQL features. It would be a bit icky, and not portable, but it would be something of a best of both worlds. Nested sets are portable, but fiddly, and don't perform very well for some fairly common kinds of queries.
Tom Anderson
Nice answer (+1)
Pascal Thivent