0

I need to join two entities using hibernate annotations. I tried few ways, but i didnt found a solution.

The point of my problem is that: tables should be joined when first key (String) contains second key (Long), like this: first = {ABC:12345678XYZ}, second = 12345678

in sql it could be like this:

SELECT *
FROM entityB
LEFT JOIN entityA
ON '{"ABC":'||entityB.id||'XYZ}' = entityA.stringWithEntityBId

Here is an classes example and one of my latest try:

public class EntityA {
    private Long id;
    private String stringWithEntityBId;
}

public class EntityB {
    private Long id;
    @OneToMany
    @JoinTable(
       name="EntityA",
       joinColumns = @JoinColumn( name="id"),            
       inverseJoinColumns = @JoinColumn( name="stringWithEntityBId"))
    @WhereJoinTable(clause=" entityA.stringWithEntityBId = '{\"ABC\":'||entityB.id||'XYZ}' ")
    private List<EntityA> entityiesA;
}

Could anyone give me an example how to solve that? Thanks!

nico999
  • 1
  • Any reason why you can't just put the relevant data into a second column and join on that? The way you intend to do this, even if it was possible, would result in pretty bad performance because the database engine could not make good use of any indices you should have on the foreign key column and its target. – Crusha K. Rool Sep 01 '19 at 12:50
  • You can declare a [generated column](https://stackoverflow.com/questions/8250389/computed-calculated-virtual-derived-columns-in-postgresql) and use to for joining. – Konstantin Triger Sep 01 '19 at 21:57

0 Answers0