Hi, I'm using hibernate 3.2 and need some help setting up a mapping file that maps to a legacy database.

Schema:
Billing Table
PK contractId
FK billHistoryId
FK billHistorySuffixId
[rest of the columns]

BillingHistory Table
PK billHistoryId
PK billHistorySuffixId
PK, FK contractId
[rest of the columns]

I'm trying to map it in such a way that the Billing object has a getBillingHistory() method, which returns a BillingHistory object. billinghistory.hbm.xml (the mapping file for the history piece) is set up to have a composite-id of all three PK fields. This mapping should be one-to-one, with the history piece possibly nullable.

Things I've tried:
Mostly using one-to-many inside of sets, since one-to-one doesn't have much in the way of constraint options.


This fails because collection foreign key mapping has wrong number of columns: component[billHistoryId,billHistorySuffixId]
also have tried:

This fails saying the number of foreign key must have same number of columns as reference key.


Basically all I want is a mapping that outputs something analogous to:


Thanks for any pointers you can give me.

If it is a one-to-one relation why do you need three primary-key columns in the BillingHistory table? Since the contractId has to be unique it should be enough to have just this column as the primary key. And... then there is really no reason to have billHistoryId or billHistorySuffixId in any of the tables.

Because each contract can have multiple billing histories. The key is actually the 3 fields concatenated. I can probably even get away with dropping the contract Id, since that is the least unique of the PK's. Yes, I know the data model is insane. No, I don't get to change it. The historyId may be sufficient, though, I'll have to investigate the DB a bit more. I'd really prefer to know how to use the multiple keys, as this occurs elsewhere in the schema. I have a sneaking suspicion I'll have to try to interface with this again in the future.

I don't get it. The Billing table you showed have a single column as a primary key: contractId. And you say that this have a one-to-one as to BillingHistory, which means that the BillingHistory table could also use the contractId as a primary key.... Maybe there is a typo or is this information not correct?



So where is the Contract table/mapping file?

Sorry, you are correct. It's actually a one-to-many relationship from billing to billingHistory. When a new bill issued, it is added to the billHistory table and the billHistoryId and billHistorySuffixId are updated in the Billing table to point to it, so using contractId for the constraint will get all bills for a contractId, whereas using all 3 keys will get the most recent bill.

Ok, this makes more sense. And I don't think it would be too hard to make this work. To get all bills in the BillingHistory, use a <set>.



This needs to be complemented with a composite primary key in the mapping for the BillingHistory table. See http://www.hibernate.org/hib_docs/v3/re ... ompositeid for more information and examples.

Now, to get the latest bill, it should be possible to use <many-to-one> except that there is one problem.



The problem with this is that you have already mapped 'contractId' as the primary key and Hibernate doesn't allow duplicate column mappings unless you also set <many-to-one ... update="false" insert="false" ... />, but that means that the other two columns will not get populated. The only workaround I can think of right now is to also map the 'billHistoryId' and 'billHistorySuffixId' as regular properties using <property> and then in your code make sure that everything stays synchronized. For example, when your Bill.setLastBill() method should also update the Bill.setBillHistoryId() and Bill.setBillHistorySuffixId(). The latter methods could be kept as package internal methods.

Hope this will help you a bit forward.

Thanks for this, it got me 90% of the way there. I can now read fine, but I'm unsure as to how updates to the BillingHistory object will go. BillingHistory will need to be updated/inserted at some point, and I did try to only use billHistoryId and billHistorySuffixId as columns, but that gave an error about returning multiple rows. It seems that contractId is relevant to uniqueness after all.

One solution that was offered around here was selecting on contractId, then going through in java code and matching up the other two id's. This worries me from a performance standpoint, as there can be an arbitrary number of bills per contract.

Maybe a better solution is to return a set based upon billHistoryId and billHistorySuffixId and then look for contractId from Java? That seems like it would be a smaller list. How would I map that?