Hi,

I have a tree like database model. Meaning I have a root entity with a one-to-many dependency, each of the entities of the collection have also a one-to-many relation with another entity...

A -> o-n B -> 0-n C -> ...

Could someone show me how to fetch eagerly ALL the tree using Criteria.setFetchMode("xxx", FetchMode.JOIN).

The goal is to have a single SQL query to fetch the whole tree instead of N+1 SQL queries.

thanks..

schuer

Do you know the depth of tree in advance?

If not,
I am not sure its is possible to execute a single SQL/HQL/criteria.
The reason I say this is that if the tree had 3 levels it would be trivial to do 2 left outer joins to fetch all the levels. However each extra level requires an additional join.


Have you considered changing your design slightly to make the problem easier. For example create a header and then do a simple one-to-many between the header and the nodes in the tree. You will need to do a little extra work to build/update the tree (yourself) however the load performance should be good.


If this is something you are not willing to do, you could also look at the batch fetching using the "batch-size" attribute. You will still run quite a lot of SQL's however it should be less than if you don't use batch fetching.

I cannot change my design.
For the moment I can fetch the whole tree structure using HQL:

StringBuffer strQuery = new StringBuffer(1000);
strQuery.append("from Project p ")
.append("left join fetch p.savedQuantification q ")
.append("left join fetch q.blocks bl ")
.append("left join fetch bl.blockDetails bd ")
.append("left join fetch bd.savings s ")
.append("left join fetch p.spendArea ")
.append("left join fetch p.cluster ")
.append("left join fetch p.spendCategory ")
.append("left join fetch p.region ")
.append("left join fetch p.businessGroup ")
.append("left join fetch p.hub ")
.append("left join fetch p.site ")
.append("left join fetch p.owner ")
.append("left join fetch p.milestoneStatus ")
.append("left join fetch p.backupOwner ")
.append("left join fetch p.projectStatus ")
.append("left join fetch p.multipleSite ")
.append("left join fetch p.milestones ")
.append("left join fetch p.implementationBarriers ")
.append("where p.id = " + id);

return (Project) uniqueElement(getHibernateTemplate().find(strQuery.toString()));


I have tried to translate this code not using HQL but Criteria and its methods (setFetchMode, createAlias, createCriteria) but without success...

Any idea?

damn you're fetching at least 6 collections in the same query, this is pure evil. Consider fetch="subselect"

Emmanuel,

We are working on the performance in our application. We have a 'project' that has a 'savedQuantification'. That quantification contains a collection of 'blocks'. Each block has 'blockDetails' (collection) which will contain a collection of 'savings'. We know we have a complex design, but we cannot change it. It's the only flexible way to solve our problem... When using lazy initialization we have a lot of calls to the persistent storage. (n+1 select problem). In order to solve our perfomance problem we are thinking to fetch our data eagerly. How can this be done with the Criteria class. We would like to avoid using HQL.

So in short:
Project - (1..1)SavedQuantification - (0..n)Block - (0..n)BlockDetail - (0..n) Saving

Even considering our poor database model, we would like to know how to do it with the Criteria API.

Any help would be appreciated.

schuer