在OneToMany关系中使用javax.persistence连接3个表

Question

这3个表分别是"analyticalgroups"、"labinstructions"one_answers"observedproperties"。每个表都有一个"id"主键列。

我想使用第四个表（"analyticalgroups_observedpropertys_labinstructions"）来存储OneToMany关系。最终，我希望输出的结构像这样：

analyticalGroup: {
  id: "...",
  observedPropertyLabInstructions: [
    {observedProperty, labInstruction},
    {observedProperty, labInstruction},
    {observedProperty, labInstruction},
    ...etc...
  ]
}

我在网上学习了一些例子，但无法将其付诸实践。问题是当我尝试这个时，我会得到以下错误：

"message"："存储库出现错误：PSQL异常：错误：observedpr0_.observedpropertyentitylabinstructionentitymap_id列不存在\n位置：6550"，"errorCode"："gaia.domain.exceptions.RepositoryException"，

---

这是联接表的结构。

CREATE TABLE analyticalgroups_observedproperties_labinstructions
(
  analyticalgroupid character varying(36) NOT NULL,
  labinstructionid character varying(36) NOT NULL,
  observedpropertyid character varying(36) NOT NULL,
  CONSTRAINT fk_analyticalgroups_observedproperties_labinstructions_groupid FOREIGN KEY (analyticalgroupid)
      REFERENCES analyticalgroups (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE,
  CONSTRAINT fk_analyticalgroups_observedproperties_labinstructions_labinstr FOREIGN KEY (labinstructionid)
      REFERENCES labinstructions (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE,
  CONSTRAINT fk_analyticalgroups_observedproperties_labinstructions_observed FOREIGN KEY (observedpropertyid)
      REFERENCES observedproperties (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
)

---

@Entity
@Data
public class AnalyticalGroupEntity {
    public static final String ENTITY_NAME = "analyticalGroups";
    public static final String JOIN_OBSERVEDPROPERTIES_LABINSTRUCTIONS_TABLE_NAME =
            ENTITY_NAME +
                    IDomainEntity.UNDERSCORE +
                    ObservedPropertyEntity.ENTITY_NAME +
                    IDomainEntity.UNDERSCORE +
                    LabInstructionEntity.ENTITY_NAME;
    @Id
    @Column(name = IDomainEntity.ID_KEY, nullable = false, columnDefinition = IDomainEntity.COLUMN_TYPE_UUID)
    private String id;
    @OneToMany
    @JoinTable(
            name = JOIN_OBSERVEDPROPERTIES_LABINSTRUCTIONS_TABLE_NAME,
            joinColumns = @JoinColumn(name = LabInstructionEntity.ID_KEY, referencedColumnName = IDomainEntity.ID_KEY, table = "labinstructions")
    )
    @MapKeyJoinColumn(name = ObservedPropertyEntity.ID_KEY, referencedColumnName = IDomainEntity.ID_KEY, table = "observedproperties")
    private Map<ObservedPropertyEntity, LabInstructionEntity> observedPropertyLabInstructions;
}

希望我已经把这一切都说清楚了。非常感谢你的帮助。感谢阅读！

Answer 1

编辑实际上。。。事实证明这是行不通的。它成功地获得了我想要的数据，但无论何时我发出GET请求*flip-table*，它都会删除联接表中的每一行

太奇怪了！

@OneToMany
@JoinTable(
        name = JOIN_OBSERVEDPROPERTIES_LABINSTRUCTIONS_TABLE_NAME,
        joinColumns = @JoinColumn(name = "analyticalgroupid", referencedColumnName = IDomainEntity.ID_KEY, table = "labinstructions"),
        inverseJoinColumns = @JoinColumn(name = LabInstructionEntity.ID_KEY, referencedColumnName = IDomainEntity.ID_KEY, table = "labinstructions")
)
@MapKeyJoinColumn(name = ObservedPropertyEntity.ID_KEY, referencedColumnName = IDomainEntity.ID_KEY, table = "observedproperties")
private Map<ObservedPropertyEntity, LabInstructionEntity> observedPropertyEntityLabInstructionEntityMap;