Mapping a read-only database with a many-to-many relation without a join table

Question

I have a question similar to @ManyToMany without join table (legacy database) with an additional issue.

I have two tables A and B

• A with a multiple column primary key (ID and ID2)
• B with a multiple column primary key (ID and ID3)

An row in A can reference several rows in B (B.ID = A.ID) and a row in B can be referenced by several rows in A.

EDIT: the database is a read-only legacy database that I cannot change. I do not need to map the relationships with JPA (I could just do it in my program logic with additional selects) but it would be nice.

It is basically a many-to-many relationship without a join table. Since, as for the linked question, I just have to read the tables, I tried with two one-to-many relationships in both classes.

The additional problem that I have is that both IDs used for the join are not the primary key.

I have the following classes:

@Entity
@Table( name = "A" )
@IdClass( PrimaryKeysA.class )
public class A {

    @Id
    @Column( name = "ID", insertable = false, updatable = false, columnDefinition = "char" )
    private String id;

    @Id
    @Column( name = "ID2", insertable = false, updatable = false )
    private int id2;

    @OneToMany( cascade = CascadeType.ALL )
    @JoinColumn( name = "ID", columnDefinition = "char", referencedColumnName = "ID" )
    private Set< B > setOfBs;

}

@Entity
@Table( name = "B" )
@IdClass( PrimaryKeysB.class )
public class B {

    @Id
    @Column( name = "ID", insertable = false, updatable = false, columnDefinition = "char" )
    private String id;

    @Id
    @Column( name = "ID3", insertable = false, updatable = false )
    private int id3;

    @OneToMany( cascade = CascadeType.ALL )
    @JoinColumn( name = "ID", columnDefinition = "char", referencedColumnName = "ID" )
    private Set< A > setOfAs;

}

Hibernate generates the following error:

Exception while preparing the app : referencedColumnNames(ID) of package.B referencing package.A not mapped to a single property

I don't really get the message: B.id is referencing a single property in A (A.id).

EDIT: as requested:

public class PrimaryKeysA implements Serializable {

private static final long   serialVersionUID    = 1L;

private int    id1;
private int    id2;

    // getters/setters/equals/hashcode

}

PrimaryKeysB is similar with id3 instead of id2. Both classes A and B are simplified (anonymized) examples.

Answer 1

You could create a view that would act as join table:

CREATE VIEW AJOINB AS
SELECT A.ID as AID, A.ID2 as AID2, B.ID as BID, B.ID3 as BID3
FROM A JOIN B ON A.ID = B.ID

And then map it in JPA as a ManyToMany with AJOINB as join table.

If A.ID2 and B.ID3 were unique by themselves, you wouldn't even need to map A.ID and B.ID in your JPA beans.

Answer 2

Can you share some sample records from you tables?

The problem is very clear. For any one-many relationship, on the "one" side, there should be only one record that can be uniquely identified. Here, i think, since id is not unique there are multiple entries.

You may try to use @JoinColumns and add both the columns to uniquely identify the entity on the "one" side.

@OneToMany
@JoinColumns({
    @JoinColumn(name="yourID1", referencedColumnName="yourID1"),
    @JoinColumn(name="yourid2", referencedColumnName="yourid2")
})

I'm assuming that you have the following data.

table A:

id2    c1          id
100    content1    1000
101    content2    1001

table B:

id3    s1          id
100    content1    1000
101    content2    1000
102    content3    1001
103    content4    1001

Here id2 and id3 are unique. A.id is unique but b.id is not; a typical OneToMany scenario.

If I map A to B using A.id and B.id, then this becomes one-to-many where A(100) can refer to B(100, 101) as the id is 1000

This will work fine i think. But if you have to map from B to A using the same columns (as stated in the question) it will not work as the one side (B) has duplicates.

Am I understanding your question correctly?