Create a hibernate relation between parent and children objects (lists) where both tables have a col-CodePudding

I'm sitting in this peculiar situation where I have the following database design

What this image illustrates is the following:

Table A has a_c_id which is "C id" but without a foreign key reference
Table B has c_id which is "C id" but without a foreign key reference.

Table C has id as primary key. I'm trying in my java code to create following entities using hibernate and hibernate annotations

@Entity
@Table(name = "A")
Class A {
  private Long id;
  private List<B> bList; // This is the relation i'm struggling with

}

@Entity
@Table(name = "B")
Class B {
  private Long Id;
  private Long c_id;
}

I've tried alot of difference combinations using:

@JoinColumn(name = "a_c_id", referencedColumnName = "c_id")
private List<B> bList;

And:

@JoinTable(
            name = "C",
            joinColumns = {
                    @JoinColumn(
                            name = "id",
                            referencedColumnName = "a_c_id"
                    )
            },
            inverseJoinColumns = {
                    @JoinColumn(
                            name = "id",
                            referencedColumnName = "c_id"
                    ),
            }
    )
    private List<B> bList;

This last example with @JoinTable gives me an exception:

Repeated column in mapping for collection: id

which makes me wonder if mapping to the same PK from 2 different tables is not possible.

But i'm starting to run out of ideas on how to do this. I didn't make this database and is wondering if it's even possible to achieve making a relation from A to B without a foreign key to reference.

I thought I could perhaps use @JoinTable, however they refer to the same ID rather than 2 different ones as you would in a JoinTable.

If anyone has some input on what to read up on, or perhaps could come with an idea on how to achieve this without changing the database design, I'd apppreciate it!

EDIT: I've added images of table records:

EDIT 2: I've tried to trace binding values based on me and Nikos conversation:

Hibernate: select a0_.id as id1_0_0_, a0_.a_c_id as a_c_id2_0_0_ from a a0_ where a0_.id=?
2022-07-27 10:25:06.101 TRACE 16404 --- [nio-8080-exec-8] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [BIGINT] - [1]
2022-07-27 10:25:06.107 TRACE 16404 --- [nio-8080-exec-8] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([a_c_id2_0_0_] : [BIGINT]) - [41]
2022-07-27 10:25:06.113 TRACE 16404 --- [nio-8080-exec-8] org.hibernate.type.CollectionType        : Created collection wrapper: [eu.sos.auditing.models.HibernateTestModels.A.bList#1]
Hibernate: select blist0_.c_id as c_id2_3_0_, blist0_.id as id1_3_0_, blist0_.id as id1_3_1_, blist0_.c_id as c_id2_3_1_ from b blist0_ where blist0_.c_id=?
2022-07-27 10:25:06.149 TRACE 16404 --- [nio-8080-exec-8] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [BIGINT] - [1]

EDIT 3: What solved my issue.

Due to Nikos help I was able to solve my issue by changing my class A to the following:

@Entity
@Table(name = "A")
@Data
public class A implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "a_c_id")
    private Long aCId;

    @OneToMany
    @JoinColumn(name="c_id", referencedColumnName = "a_c_id") 
    private List<B> bList;

}

Kind regards

CodePudding user response：

The solution can be as straightforward as pretending that B.c_id points to A.a_c_id:

Class A {
  @OneToMany
  // remember, the JoinColumn is in the other table (here B)
  // while the referencedColumnName is in this table
  @JoinColumn(name="c_id ", referencedColumnName = "a_c_id")
  private List<B> bList;
}

EDIT: It is important to include the referencedColumnName, which I has forgotten before the edit. Also see full solution below for another caveat with Serializable.

If the DB constraints are different, you will have to make sure a C record exists before inserting into A and B.

The other solution is NOT to map the relation in the entity classes. Or, rather, map the existing relations, i.e. any of: (1) "A relates to 1 C", (2) "B relates to 1 C", (3) "C relates to many As", (4) "C relates to many Bs". And fetch the Bs that correspond to an A with an independent query.

FULL WORKING SOLUTION (with slightly different names - omitting getters/setters for brevity)

The entities:

@Entity
@Table(name = "AA")
public class Alpha implements Serializable {
    @Id
    private Long id;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "a_c_id")
    private Charlie charlie;

    @OneToMany
    @JoinColumn(name="c_id", referencedColumnName = "a_c_id")
    private List<Bravo> bList;
}

CAVEAT: I had to make Alpha implements Serializable because of HHH-7668. It applies only to the Hibernate implementation of JPA.

@Entity
@Table(name = "BB")
public class Bravo {
    @Id
    private Long id;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "c_id")
    private Charlie charlie;
}

@Entity
@Table(name = "CC")
public class Charlie {
    @Id
    private Long id;
}

Sample code to read, assuming the contents of the tables are as in the question:

    EntityManager em = ...;
    Alpha a = em.find(Alpha.class, 2L);
    a.getbList().forEach(b -> System.out.println(b.getId()));
    // prints 3, 4