Can someone help to find a solution with JPA and annotations if there are composite foreign keys to other tables where one of the columns is repeated across those foreign keys.

For hibernate XML configuration there is a workarround using the <formula> tag for the repeaded columns (not nice but ok). For JPA I see no solution so far.

The best and preferred solution would be to allow mixing of insert/update attributes on @JoinColumns (plural) annotations, but this results in a exception:




E.g. Having the following simple scenario:
There is a user table and a user can have one or more addresses and to distiguish between data of different clients a client_key has been added to all tables.



second table:



the coresponding join column annotation then looks like this:



1. This scenario ends up in a AnnotationException as shown above because all insert and update attributes within the same @JoinColumns annotation must have the same value. (Why? I don't care, see my suggestions below).

2. Setting all values to insert/upate = true results also in a exception and at least this configuration is wrong since I don't want to use both columns for insert/update. I only need the address_id when saving a user but not the repeated column client_key since it is already in the primary key.


I don't see the reason why to deny mixing insert/update=ture/false values within one compsite foreign keys except one: "What if someone adds a address of a different client than the user's client_key". Sure, this is a problem, but my answer to this is why not letting hibernate take care on this when a user object is saved. The logic to check would be quite simple:

Quote:

If a repeated column which exists in the primary key is used in a JoinColumn as well then do a logical check if the values are the same otherwise throw a integrity exception.

Sorry if there is a similar post somewhere in the forum. But I saw a lot of issues but every issue was a little bit different or no solution was provided etc.

Any help would be welcome. A patch would also be nice.
M

The proper answer is probably to support formula. There is an open JIRA issue, but I haven't been able to qork on it. Someone proposed to give it a try, but I don't know if he started.

Thanks for your answer Emmanuel, but why not support mixing of insert/update = true/false on composite foreign keys, this would possibly be much easier to implement and no hibernate specific annotations need to be used anymore.

I read in a different thread that there was a note in the ejb-3_0-edr2-spec-persistence specification saying the following:



Seem as this sentence has not been finished. In addition this note has been removed in the final spec!

So why not allowing the mixing of the insert/update on composite foreign keys and let the persistence provider take care on the data integrity when a repeaded column exists in the fk relation and persist() is called?

M

I don't think Hibernate core support such a constraint today.


You cannot let the persistence provider decide wich version of the data has to be inserted, it would lead to inconsistency

Emmanuel can you pls explain why this is the fact? So far I don't understand your answer.

So why are you writing a persistence provide can not decide which version should be inserted?
A persistence provider needn't to take care on the version this has to be done by the developer by declaring max. one repeated column with insert/update=true and all others to insert/update=false. The persistence provider only has to take care that if repeated columns are used that the values of theses are compared with each other and check if they are the same right before save, nothing more.

In this example it means if there is a repeated column client_key one for the User table (insert/update=true) and one for the Address table (insert/update=false) Hibernate should check prePersist or preUpdate if the User.client_key equals Address.client_key and if not throw an Exception. Since Hibernate detects automatically if there are repeated columns in the mapping maybe this can be easilly extended to Hibernate. This is a basic need especially for those developers which have a lot of legacy systems. Transforming complex fk to simple properties is pain in the neck and from my point of view unnecessary.

M

I disagree, it's better to ensure that as a static contract.
You don't want things to blow up in prod.

Maybe you are right, but then hibernate should leave out all checks or only show a warnings when parsing the configuration instead of an error and leave it up to the developer to decide what he will do there. I see no differences between the workarround of a hibernate specific formula tag where the developer has to take care on the insert/update version compared with allowing the mixture of insert/update=true/false on composite keys. What about adding a new global property saying "Allow mixture of insert/update on composite keys" an showing only warning if properties are mixed on a composite key is set to true?

Frankly, my opinion is that the benefit of such a flag does not counter balance the additional code complexity and yet another flag to explain to users.

This is very poor, like your answer. After all this discussion at the end there is not solution for a really existend problem. What is your solution for leagcy systems? Writing SQL or what. Code complexity...tsss

Surely you can write a patch for it and use it.
And legacy system are prefectly covered. There is a solution, you simply want an other.

Hi. does it mean that there's a workaround solution for that?

i faced the same problem that the same column can't be mapped at PK and FK and allowing INSERT and UPDATE of child table.

Thanks!

map the FK with insert=false update=false, since the column is already part of the PK there is no need to update it twice

But, it has error if the Child Table itself has TWO PK

<hibernate-mapping>
<class
name="OrderAllocsVO"
table="ORDER_ALLOCS"
dynamic-insert="true"
optimistic-lock="version"
batch-size="1000"
>
<composite-id name="id" class="OrderAllocsIdVO">
<key-property name="allocId" type="string">
<column name="ALLOC_ID" length="15" />
</key-property>
<key-property name="tradeDate" type="date">
<column name="TRADE_DATE" length="7" not-null="true" />
</key-property>
</composite-id>
<version
name="version"
column="version"
type="long"
/>
<many-to-one name="FXOrder" class="OrderVO"
fetch="select" insert="false" update="false">
<column name="ORDER_ID" length="15" not-null="true" />
<column name="TRADE_DATE" length="7" not-null="true" />
</many-to-one>


Since TRADE_DATE is both PK and FK. I have to define on composite-id and many-to-one. But then, I have to set
insert="false" and update="false"

But, it make order_id CANNOT be inserted and updated as it won't be included on the SQL statement and so NULL value passed. Then, failed.

Define a read/write orderId property aside. It can even be a private getter/setter and populated when FXOrder is set.

I had a similar problem, and the method you suggested (private getter/setters for the columns) appears to work.

Thank you, Emmanuel!