I've been banging on keys for two days to find a solution to a problem that probably shouldn't exist.

So here's the set-up.

Someone came up with the brilliant idea to have a "recursive table" and made a nice parent-child hierarchy in our database. The simplified table structure is pseudo-coded thus:

CREATE TABLE foo {
  owner_id int,
  id int,
  other_data varchar(10)
}

The icky part is that the table's primary key is [owner_id,int]. The basic idea is that for a given owner_id, a set of related records may have matching id values. Those records, in turn, may also have related records, turning this into a big recursive entry, starting with a provided owner_id. The foo record may have children who may have children who may have children...

The data might look like the following:

Where we can see that the results for owner_id=0 has two children 1 and 2, and everyone else has one child. So searching for owner_id=0 would get its two children of id=1,2 and their children of 3,4 and their children of 5,6 and the lone child of 7...right? It may well be there are no children (search for owner_id=4 or 5) or few children (search for 2 or 3), or more (search for 0 or 1).

So the corresponding Java is pretty simple, too (again, stripped-down, ignoring getters, setters, etc):

class Foo {
  int ownerId;
  int id;
  String otherData;
  Collection<Foo%gt; foos;
}

So, a shorter example from the data above, would be building Foo with ownerId of 3 would return the short chain of:

Collection<Foo> foos=[ { ownerId=3, id=5, otherData="else", foos=[ { ownerId=5, id=7, otherData="nothing", foos=null } ] } ]

Had we started with 0, it would have had all of the items in the list above, somewhere in the tree...

Now the problem I'm having is the Hibernate mapping, of course. The current version (simplified) looks like this:

<hibernate-mapping><class name="Foo" table="foo">
  <composite-id>
    <key-property name="ownerId" column="owner_id" />
    <key-property name="id" column="id" />
  </composite-id>
  <property name="otherData" column="other_data" />
</class></hibernate-mapping>

This works dandy, and I can pull out any collection of Foo I want with queries like "FROM Foo WHERE ownerId=4" or whatever. I'll get a set with zero or more Foo in it, and the ownerId will all be 4, and the id will vary; with the data above I'd get the one row with owner_id=4,id=6,other_data="zippo". If I did ownerId=0, I'd get two rows with ids 1 and 2...

Now for the problem.

Obviously the mapping above doesn't address the foos Collection, so no Hibernate-mapped hierarchy. This is where I run into problems. I've tried sets and maps and whatever, and every time I run into problems with the foreign key as I'm trying to pass in just one key (owner_id).

Hopefully to neither taint your ideas or show my dumbassedness, here's my latest tries as a set and map...

<set name="foos" lazy="false">
  <key column="owner_id" update="false" property-ref="id" not-null="true" />
  <one-to-many class="Foo"/>
</set>

And I've tried many-to-many and many-to-one and the rest, to no avail. Or (and making proper Java change, of course)

<map name="foos" lazy="false">
  <key column="owner_id" update="false" property-ref="id" />
  <composite-map-key>
    <key-property name="ownerId" />
    <key-property name="id" />
  </composite-map-key>
  <element type="Foo" />
</map>

The MappingException is always the same "number of columns does not match foreign key Foo[owner_id,id]" and I cannot figure out how to say "just use the ownerId, and give me the ids that go with it!" Sadly, I cannot make any database changes, but fortunately, it's all read-only.

There is a code-based work-around in place now; they use the mapping roughly above and gather the child Foo with a second query. This is the process I've been charged with removing, as it is quite slow.