Hi, I have the unenviable task of applying Hibernate to a legacy database that (big surprise) makes extensive use of composite keys and isn't very "object-based". I've reached a mapping problem that I don't see the answer to, and I sure hope there is one.

Hibernate version: 3.1.3

Mapping documents:

Consider two tables:

Table A with your typical composite primary key:
Column1 (PK)
Column2 (PK)
Column3 (PK)
Other columns (not PK, not important)

Table B:
Column2 (PK)
Column3 (PK)
Column4 (PK)
Column5 (PK)
Other columns (not PK, not important)

Note how Table A shares two columns with Table B, but the ones that A has is only part of the full PK of table B.

When I get a entity of table A, I want it to pull down all Table B rows that share those two columns. I don't need to update anything, just read the data in as few database trips as possible. I tried

<many-to-one name="cmsConversion" class="tableB" insert="false" update="false">
<column name="Column2" not-null="true"/>
<column name="Column3" not-null="true"/>
</many-to-one>

But the number of columns on the 'foreign' side of the relation must match the number of columns in the primary key of the referenced object.

I have also tried to construct a <bag> mapping that does this, but I tend to get the same problem.

I realize this is an advanced situation. I hope that there some way to make Hibernate mappings do what I want.

Thanks
Dan

There is, but do you know that what you've described can't accurately describe a many-to-one relationship? If there are two A rows with the same values for Column2 and Column3, but different Column1 values, then all B rows that relate to one A will relate to both As.. hence many-to-many, not many-to-one.

If that situation can't arise because there is only one value of Column1 for every combination of Column2+Column3, then Column1 should not be in the ID for the mapping. It can stay in the PK, but it's not adding anything to the ID, and should be turned into a simple property (with unique="true" not-null="true", because it's part of the PK).

Anyway, to answer your question: in mapping A, create a <properties> with Column2 and Column3 (insert="false" update="false", because their mappings in the id are the real, updatable ones). In the key of the set of Bs (in A), use property-ref with that properties. And in the many-to-one in B (referring to A), use property-ref with that properties too.

Thanks for the reply.

Yes, you are correct, this is actually a many-to-many. It's possible for rows of A to differ only by column1. A strange many-to-many without a join table... but anyway...

What you've suggested doesn't seem to work. It appears to be similiar to what I've already tried, declaring a set of B's (in A) with a key of [column2, column3] is insufficient because the PK of B includes other columns not present in A. Here are my mapping files:

A.hbm.xml



B.hbm.xml



And of course I have a List of B's in A and an instance of A in B. The error I get is what I'm used to at this point:

org.hibernate.MappingException: property mapping has wrong number of columns: B.relatedA type: A

Ideas? Did I misunderstand? I still need a way to retrieve these related B's without having to do O(N) database hits

You have to specify the columns to use in the many-to-one association:Don't forget to remove the C1 from the properties in A, that should have only C2 and C3.

However, since this is a many-to-many, you should map it as such. many-to-many doesn't need a join table: if you use the many-to-many element but don't specify a table attribute, you get a many-to-many with no join table. There's an example, fairly similar to this, in the ref docs, section 23.4.3.

Ok a different error, but still similar. I made the changes you recommended.

A.hbm.xml



B.hbm.xml


I was unable to reuse the property-ref in they key of B's bag because it said the property was not found, so I just put in the key columns by hand. I hope that's ok.

I get the following error

Foreign key (FKBBC59F5BC559B2B7:relatedAs [C2, C3])) must have same number of columns as the referenced primary key (B [C2, C3, C4, C5])

This is fishy. Why would B's set be referencing the primary key of B? As you can see from the mapping files, B's set (relatedAs) maps to instances of A. So I'm not sure what that means. The mappings make sense to me. I'm defining two sets and in both cases, I map 2 columns on one end to 2 columns on the other. Hibernate insists on matching the columns to the PK. :(

In the example 23.4.3, it looks similar (I tried to make mine look like theirs), but it looks like they use 'formula' to make the number of columns in the many-to-many match the number in the PK. I wasn't able to find very good documentation about what formula does in the Hibernate docs, so maybe there's a solution in their somewhere.

Otherwise, please let me know what's wrong with my mappings. Thanks.

Ok, I did a LOT more reading and now I see that property-ref is going to be my friend here. I am mapping this as many-to-many as you requested. So, I'm going to have bags in both classes, containing many-to-many's back at each other. The question is what will the <key>'s be in each and where will I put property-ref's (I could put them on both <key>'s or on both <many-to-many>'s) I wasn't able to find (or didn't understand) the documentation explaining exactly what all the combinations would do, so I went with my best guess, and of course it still doesn't work :)

A.hbm.xml



B.hbm.xml



I tried to make them symmetric. Each entity has a <properties> containing the two properties involved in the link, each <key> references it's own <properties> and each many-to-many references the other guy's <properties>

And yet:

org.hibernate.MappingException: collection element mapping has wrong number of columns: com.carescience.dm.ui.beans.ClientHl7Conversion.relatedMappings type: com.carescience.dm.ui.beans.Hl7CmsConversion

I also tried this on the latest 3.2 in case it's a bug in 3.1.3

Thanks so much for you help so far. I hope I'm getting close...

Dan

I don't think that you need the property-ref on the many-to-many. The key mapping is all you need: the property-ref there refers to the properties in the "this" class, and the key columns refer to columns in the "that" table.

It does the same thing even if I remove the property-refs from the many-to-many but leave them on the keys.

I'm going to try to create a simple test case and submit a JIRA, unless you feel I'm still doing something wrong, this should work.

Thanks.

Well they shot down my JIRA with barely a second look.

I'm still not sure if this is a bug or something I'm doing wrong. Any help is apprecitated.

There's no reason to think that this is a bug. The exception is pretty clear: the mapping is obviously wrong somewhere.

I still think that ref docs section 23.4.3 is the best example to work with. The formula tag is just like the column tag, the only difference is that it's derived, not an actual column. You can use column where the example uses formula.

for many to many relation, join table must exists?

mtyoung, for a many-to-many releationship, you need to maintain the relationships somehow. A join talbe is the way to go. It's not unique to Hibernate to need this.

Here's a tutorial on mapping many to many relationships with Hibernate and JPA, if you like:

Mapping Many-to-Many Associations with Hibernate and JPA

database schema was designed and i cannot change

the design was similar to dhannum's design
Table A with composite key KA1, KA2
Table B with composite key KB1, KB2

KA2, KB2 are keys to join 2 table together