Recently someone posted a question on stackoverflow asking how to deal with a database join, where the foreign key could reside in one of two different columns. This situation is sometimes found in a legacy database schema, where someone has chosen to use two different columns, because they want them to represent different sorts of relationship, but relationships that join to the same kind of entity. In the example on stackoverflow, a customer has to link to a UserAccount object, but this could either be their own, using a foreign key in the “accountId” column, or a partner account, with the foreign key in the “partnerId” column. How do you deal with this?
The answer is to use a join formula to pick out the non-null column and use it to join:
@ManyToOne
@JoinColumnsOrFormulas(
{@JoinColumnOrFormula(
formula = @JoinFormula(
value = "case when accountId is not null then accountId " +
"when partnerId is not null then partnerId end",
referencedColumnName="id")) }
)
public UserAccount getUserAccount() {
return userAccount;
}
If you want to see the full working example, it is on github:
https://github.com/hedleyproctor/hibernate-mapping-and-query-examples
The Customer class shows the join formula and you can see a test which demonstrates it in the Tests class, in the joinFormula() test method.
