Hi,
We have an organisation unit class that can be a parent to child organisation classes. We need to know for each object all the children and nested children. HQL doesn't support hierarchical queries so we have used a native oracle SQL CONNECT BY statement to retrieve a set of data using sql-query and load-collection in our mapping file. We can see from the logs that the query is executed and the correct object are returned, however when the set is iterated there is only ever one object in the set (the parent object).

Can anyone provide any further documentation on sql-query and load-collection mappings or offer any explanation as to why this is happening?

Thanks,
Chris

Use of sql-query and load-collection for hierarchical object:

// Set in the class - this is all nested children that belong to the parent
private Set<OrganisationUnit> nestedChildOrganisationUnits = new HashSet<OrganisationUnit>();

// Out
<set name="nestedChildOrganisationUnits" inverse="true" lazy="true">
<key/>
<one-to-many class="OrganisationUnit"/>
<loader query-ref="nestedChildOrganisationUnitsQuery"/>
</set>

<sql-query name="nestedChildOrganisationUnitsQuery">
<load-collection alias="ou" role="OrganisationUnit.nestedChildOrganisationUnits"/>
SELECT {ou.*}
FROM organisation_unit ou
START WITH ou.id = :id
CONNECT BY PRIOR ou.id = ou.parent_organisation_unit_id
</sql-query>