While having two FOREIGN KEY columns: country_id and locale_id would be better, we’ll assume a legacy database schema that cannot be easily modified. For this reason, we need to use thelocale column since it stores info about both language and country. What we need to do is to parse it and extract the country and language codes which can then be used to join the associated Country and Language tables.

While JPA doesn’t offer any support for such mapping, Hibernate has long been offering the @JoinFormula annotation.

As illustrated by the above execution plan, PostgreSQL can benefit from these two function indexes as the execution plan uses an Index Only Scan for each JOIN relationship.

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

Conclusion

While most of the time the @JoinColumn annotation is the best way to express an entity association, if the database schema doesn’t have a FOREIGN KEY column you could use, then @JoinFormula becomes very useful.