Hi all,

I'm attempting to optimize an application with some familiar object relationships, and I'm unsure if it's possible (or wise) to try to fetch a whole object graph in one SELECT. I've searched and found posts from 2004, 5 and 6, but nothing very recent. The documentation describes optimizing SELECTs using FetchMode.JOIN for single level relationships, but not multiple level relationships.

I found a forum post saying that Hibernate does support this kind of fetching, while NHibernate does not .. so, I believe there might be some tricks I don't know about Hibernate. I've spent the day investigating options, and also playing with the second level cache (ehcache), and reading lots of documentation. I'd appreciate some crticism of my approach.

So, here goes.

Say I have an entity model like this:



So a Classroom will be associated with (OneToMany) many Person instances, and each Person instance will be associated with (OneToMany) collections of Limbs, Doodads, and Expectations. All these relationships use the default fetch strategy, LAZY.

I'd like to leave the default fetch strategy as LAZY, but when necessary, completely initialise a Classroom entity and all the related sub-entities in one Criteria query.

When selecting a Classroom I am doing:



So I expect to get back a single Classroom instance, with its collection of Person entities populated, and each Person should have its Limbs, Doodads and Expectations collections populated.

Is this sensible? Does it make sense to setFetchMode for "persons" and several collections of the "persons" relationship as I have done?

I don't believe a SQL SELECT statement, with a single result set, is suitable for this problem. In my real application the generated SQL seems basically OK, but I fear I'm basically misusing the concept of JOIN fetching by trying to fetch too many 'levels' of relationship in a single query.

My reason for asking is that I have an inner loop that wants to dump the entire object graph to a data structure (for return as JSON to a web UI):



Without .setFetchMode() used at all, each person.getLimbs(), person.getExpectations() etc. calls result in a SELECT statement to the database, so the inner loop is inefficient. I'm trying to get all the database work done in as few calls as possible, to get the whole entity graph in memory before the for loop is entered, so no database hits are required during its traversal of the object graph.

Nick

I've now enabled ehcache and my collections and entities are being retrieved from the cache, which has greatly alleviated the need for this optimisation. I can just specify a fetch of one level when fetching a Classroom, and not worry about the accessing of Person collections causing further fetches, since the Person attributes are all coming from the second level cache.

Would be nice to get an answer to the original question, though :)

I have the same question as Nick about nested collections and FetchMode.JOIN on a Criteria. Can one of the Hibernate gurus please answer or point us to the documentation / wiki that has the answer?