I have an Employee object with a set of supervisors and a set of direct reports. Supervisors and Direct Reports are employee objects.

I retrieve an employee and then get his first supervisor. When I try to access all of the supervisors direct reports I get the error
"illegal access to loading collection" which I have traced into AbstractPersistentClass at line 338. It appears that the system is already initializing.

Is there a trick for going up and back down a tree?

This is only a partial drawing.

Employee
|___Supervisors (Employee Object)
| |_____Direct Reports (Employee Object)
|___Direct Reports (Employee Object)

Of course the Supervisor has Direct Reports and the Direct Reports have Supervisors and Direct Reports.

What is the best way to map and access this?

Hibernate version: 3.1.3

Mapping documents:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >

<hibernate-mapping package="com.duanemorris.specials.mainlist">
<class
name="MainlistFull"
table="uView_DMPeople_MainList_Full"
lazy="true"
>
<id
name="id"
type="integer"
column="DMPeople_ID"
>
<generator class="org.hibernate.id.MultipleHiLoPerTableGenerator">
<param name="table">system_keys</param>
<param name="primary_key_column">TableName</param>
<param name="value_column">UniqueKey</param>
<param name="max_lo">1</param>
</generator>
</id>

<property
name="personLink"
column="Person_Link"
type="integer"
not-null="true"
length="10"
/>
<property
name="personFullName"
column="PersonFullName"
type="string"
not-null="false"
length="103"
/>
<property
name="phoneExtension"
column="PhoneExtension"
type="string"
not-null="false"
length="100"
/>
<property
name="departmentName"
column="DepartmentName"
type="string"
not-null="false"
length="100"
/>
<property
name="jobClassification"
column="JobClassification"
type="string"
not-null="false"
length="100"
/>
<property
name="officeName"
column="OfficeName"
type="string"
not-null="false"
length="100"
/>

<set name="DirectReports" table="Admin_Supervisors_Lookup" order-by="EmpFullname" inverse="true" lazy="true" fetch="select">
<key column="SupervisorID" property-ref="personLink"/>
<many-to-many column="EmployeeID" class="MainlistFull" property-ref="personLink"/>
</set>

<set name="Supervisors" table="Admin_Supervisors_Lookup" order-by="SUPfullname" inverse="true" lazy="true" fetch="select">
<key column="EmployeeID" property-ref="personLink"/>
<many-to-many column="SupervisorID" class="MainlistFull" property-ref="personLink"/>
</set>

</class>
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():

Full stack trace of any exception that occurs:

Name and version of the database you are using: SQLServer 2000

The generated SQL (show_sql=true): It seems to be generating the SQL fine. I have run it in Enterprise manager and it returns the correct results.

Debug level Hibernate log excerpt:

Are there cut and paste errors in that post? Or are both sets identical except for the key columns? Same table, same class (and further, same class as the owning class), etc.?

If that's all correct, then your only solution (that I can think of) is to finish loading one collection before loading the next. You can either initialize the collection completely, or evict it so that hibernate knows that you don't intend to fetch any more items via it.

It is possible that mapping Employee, Supervisor and DirectReport as three separate classes (that reference the same tables) may resolve this, but I've never dealt with a schema like this before. I'd have gone with three separate tables, even if they do have identical columns: in addition to being more "human readable", it would cut down index sizes, and make for better index clustering for faster collection loading.

Thanks for taking a look at this.

There are no errors in the Cut and Paste.

Emp Table
--EmpID
--EmpName

Admin_Supervisor_Lookup
--EmpID
--SupervisorID (Foreign Key into Emp Table)

In essences, a many to many for Supervisors and Direct Reports.

I've tried to do an Eager fetch on the Supervisor collection, however I still seem to get the same problem. Just iterating through the top level causes the problem.


Finally, I disagree with:
I'd have gone with three separate tables, even if they do have identical columns: in addition to being more "human readable", it would cut down index sizes, and make for better index clustering for faster collection loading.

It is all the same table. An employee can be both a supervisor and a direct report at the same time. To put the data into more than one place just asks for trouble and I do not see how it could possible be faster.

Fair enough.

I don't believe I've ever encountered this sort of mapping before: recursive mappings work fine, but two recursive mappings at the same time? It's entirely possible that it simply can't be done due to the way JDBC result sets work. Or that it can, but hibernate doesn't cater for it because noone thought of it before.

Try mapping the three logical versions of the table as three separate classes, to see if that helps hibernate. There won't be any significant overhead if you keep MainlistFull, and extend it three times (three empty classes, all extending MainlistFull). Remove all references to MainlistFull from your mapping files, and replace them with the subclasses. Unfortunately you'll need to cut'n'paste your mapping a couple of times. And of course, there's no guarantee that this will work. But I can't see anything wrong with what you've done, so experimentation is all I can suggest.

Something you said earlier put me on the right track to work around this probelm.

First I get the employee, then I reference the Supervisor collection (.size()), then I evict the employee from the session. For each supervisor in the collection I sess.update(supervisor). No more problem.

Another workaround I found was to create a new session for each supervisor and retrieve the supervisor object in the new session.

Again, thanks for think through this with me. It sure beats beating my head against the wall alone.