使用多对多关系映射只读数据库，而不使用连接表

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

我有一个类似于@ManyToMany的问题，没有连接表(遗留数据库)，还有一个问题。

I have two tables A and B

我有两张桌子A和B

• A with a multiple column primary key (ID and ID2)
• A具有多列主键(ID和ID2)
• B with a multiple column primary key (ID and ID3)
• B具有多列主键(ID和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.

A中的一行可以引用B (B)中的几行。ID = a .ID)和B中的一行可以被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.

编辑:这个数据库是一个只读的遗留数据库，我不能修改它。我不需要映射与JPA的关系(我可以在我的程序逻辑中使用附加的选择)，但是这样做很好。

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.

我遇到的另一个问题是用于连接的两个id都不是主键。

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:

Hibernate生成以下错误:

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).

我不太明白这个信息:B。id在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.

PrimaryKeysB与id3类似，而不是id2类似。A类和B类都是简化的(匿名的)示例。

2 个解决方案

#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.

然后将它映射到JPA中的ManyToMany，并将AJOINB映射为join表。

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.

如果一个。ID2和B。ID3是唯一的，你甚至不需要映射A。ID和B。您的JPA bean中的ID。

#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.

问题很明显。对于任何一段关系，在“一个”方面，应该只有一个记录可以唯一标识。这里，我认为，由于id不是唯一的，所以有多个条目。

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

您可以尝试使用@JoinColumns并添加两列，以唯一地标识“one”方面的实体。

@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.

这里id2和id3是唯一的。一个。id是唯一的，但是b。id没有;一个典型的对场景。

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

如果我用A映射A到B。id和B。id，然后这变成一对多，其中A(100)可以引用B(100,101)，因为id是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.

我想这没问题。但是，如果你必须使用相同的列(如问题中所述)从B映射到A，它就不能工作，因为这一侧(B)有重复的列。

Am I understanding your question correctly?

我对你的问题理解正确吗?