Bespoke join conditions with Hibernate JoinFormula

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.

This entry was posted in Hibernate and tagged . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

502,426 Spambots Blocked by Simple Comments

HTML tags are not allowed.