Apply formula to referenced column name

Hibernate ORM
1

Hi there,

I am mapping a (legacy) DB view to JPA/Hibernate entities. The view contains, among other things, the following join:

FROM a
JOIN b
  on trim(a.b_foo) = trim(b.foo)

I want to create a @ManyToOne relation from A entity to B.
I know I can use @JoinFormula to trim the column of the owning (A) entity, but I was unable to find a way to apply a trim to the other part of the condition (as referencedColumnName only allows exact column names).
It’s pretty straightforward in JPQL/QueryDSL/etc., but I want to do it in my relation so I don’t have to define this join in multiple queries.

Is there a way to do this without changing my DB schema (I don’t want to create a view of table b that trims the value nor do I want to add computed/virtual columns)?
I am using Hibernate 6.

Thanks in advance

2

That’s not possible and IMO you also shouldn’t do this. This seems to be an ad-hoc association/relation which shouldn’t be part of your model. If it really is a proper relation of your model, you should consider adding the virtual column or trim the original column in the first place and create a FK constraint as well as an index.

3

I agree that I “shouldn’t do this”, but I wanted to explore other options before considering to touch the legacy DB (schema) I’m working with. Thanks for your answer!