Hibernate 3, Oracle 9i.

I've got a problem creating a one-to-many association between two tables. Essentially the foreign-key column in the child object is not actually a column but is the result of a forumla property mapping. I can't work out how to declare the mapping either way. What I would ideally like is tobe able to declare an association between a column on the parent and a property on the child but I can't see a way of doing this.

For example, in the child I've tried using the following. The name of the column is the actual name of the property.



Obviously this doesn't work as there is no olumn in the table called parentSectorId. So I've tried using the property-ref instead, but this is used to determine which property is used in the parent, not the child. So, I'm now stuck.

I have issues with the schema which limit what I can actually do. The tables actually map out a many-to-many relationship between the child and the parent but this doesn't reflect the business logic so I want to map it as a one-to-many. I don't own the schema so I can't change it. Also there is another table which restricts some of the data returned that I need to include to further complicate things.

As a final attempt I tried changing the mapping file to use a <subselect> instead of a table declaration. This caused different issues as I ended up with Oracle errors around how a single-row subquery was returning more than one row (Duh, its a subselect, what do you expect?), only when a where clause was added to the subselect by Hibernate when trying to do the associations. Nice.

So, I'm now stuck. And I need so help. Can anyone out there help?

Thanks,

Rob

I'm trying to wrap my head around the problem being solved.



If I understand this correctly the foreign key does not exist as a column value in the database but is only defined by a function and is returned as a method result. Is the value returned by the function constant for a given row or dynamic?

If it's constant for a given row, it seems that you could use a more traditional approach (at least from a Hibernate mapping POV) by adding a column that contains the value of the function and populate that database column when the entity is created or persisted. Of course, if this is a legacy data table then adding columns may not be a solution.

If it's dynamic (i.e. can change over the life cycle of an entity) for a given row, it seems that you probably don't want a fixed mapping anyway. Prehaps a data layer method that returns the "current set" of related entities might be solution.

HI, thanks for the reply. The data is static but the problem is that I have no control over the schema. We're integrating with another application via their database (which is a really bad way to do it) so we can't add new columns, or create views or generally muck around with their schema.

Essentially the database is very badly designed with a many-to-many recursive (or pigs-ear) relationship from a table to itself. With the data that I need the business relationship is actually that of a one-to-many (think tree-structure) relationship, hence me trying to do it this way rather than a many-to-many.