JPA将两个表与一个具有常数值的表连接起来

问题描述：

我有一个带有样本记录的数据库设计，如下所示。 Question和Answer表共享相同的内容表，用于存储他们的措辞翻译。通过在内容指标中指定1，我知道内容的引用是指问题（答案为2）的标识。JPA将两个表与一个具有常数值的表连接起来

问题

+---------+----------+ 
| id (PK) | sequence | 
+---------+----------+ 
|  1 |  1 | 
+---------+----------+

回答

+---------+----------+ 
| id (PK) | sequence | 
+---------+----------+ 
|  1 |  1 | 
+---------+----------+ 
|  2 |  2 | 
+---------+----------+

内容

+---------+-----------+----------------+----------+------------------+ 
| id (PK) | indicator | reference (FK) | language | value   | 
+---------+-----------+----------------+----------+------------------+ 
|  1 |   1 |    1 | en  | English question | 
+---------+-----------+----------------+----------+------------------+ 
|  2 |   1 |    1 | zh_TW | Chinese question | 
+---------+-----------+----------------+----------+------------------+ 
|  3 |   2 |    1 | en  | English answer 1 | 
+---------+-----------+----------------+----------+------------------+ 
|  4 |   2 |    1 | zh_TW | Chinese answer 1 | 
+---------+-----------+----------------+----------+------------------+ 
|  5 |   2 |    2 | en  | English answer 2 | 
+---------+-----------+----------------+----------+------------------+ 
|  6 |   2 |    2 | zh_TW | Chinese answer 2 | 
+---------+-----------+----------------+----------+------------------+

我尝试了relationsh链接腹膜使用JPA以下代码：

@Entity 
public class Question 
{ 
    @Id 
    @GeneratedValue 
    private Integer  id; 

    @Column 
    private Integer  sequence; 

    @OneToMany(cascade = CascadeType.ALL) 
    @JoinColumn(name = "reference", referencedColumnName = "id") 
    @Where(clause = "indicator = 1") 
    private List<Content> contents = new ArrayList<Content>(); 
} 

@Entity 
public class Answer 
{ 
    @Id 
    @GeneratedValue 
    private Integer id; 

    @Column 
    private Integer sequence; 

    @OneToMany(cascade = CascadeType.ALL) 
    @JoinColumn(name = "reference", referencedColumnName = "id") 
    @Where(clause = "indicator = 2") 
    private List<Content> contents = new ArrayList<Content>(); 
} 

@Entity 
public class Content 
{ 
    @Id 
    @GeneratedValue 
    private Integer id; 

    @Column 
    private Integer indicator; 

    @Column 
    private String language; 

    @Column 
    private String value; 

    @ManyToOne 
    @JoinColumn(name = "reference", referencedColumnName = "id", updatable = false) 
    @Filter(name = "questionIndicator", condition = "indicator = 1") 
    private Question question; 

    @ManyToOne 
    @JoinColumn(name = "reference", referencedColumnName = "id", updatable = false) 
    @Filter(name = "answerIndicator", condition = "indicator = 2") 
    private Answer answer; 
}

它在编译时期间抛出以下情况例外：

所致：org.hibernate.MappingException：在对实体映射重复柱：jpatest.model.Content柱：引用（应该用insert =“false”update =“false”映射） at org.hibernate.mapping.PersistentClass.checkColumnDuplication（PersistentClass.java:709）〜[hibernate-core-4.3.11.Final.jar： 4.3.11.Final] at org.hibernate.mapping.PersistentClass.checkPropertyColumnDuplication（PersistentClass.java:731）〜[hibernate-core-4.3.11.Final.jar：4.3.11.Fina l] at org.hibernate.mapping.PersistentClass.checkColumnDuplication（PersistentClass.java:753）〜[hibernate-core-4.3.11.Final.jar：4.3.11.Final] at org.hibernate.mapping.PersistentClass。验证（PersistentClass.java:506）〜[hibernate-core-4.3.11.Final.jar：4.3.11.Final] at org.hibernate.mapping.RootClass.validate（RootClass.java:270）〜[hibernate- core-4.3.11.Final.jar：4.3.11.Final] at org.hibernate.cfg.Configuration.validate（Configuration.java:1360）〜[hibernate-core-4.3.11.Final.jar：4.3。 11.Final] at org.hibernate.cfg.Configuration.buildSessionFactory（Configuration.java:1851）〜[hibernate-core-4.3.11.Final.jar：4.3.11.Final] at org.hibernate.jpa。 boot.internal.EntityManagerFactoryBuilderImpl $ 4.perform（EntityManagerFactoryBuilderImpl.java:857）〜[hibernate-entitymanager-4.3.11.Final.jar：4.3.11.Final] 个... 48共同框架省略

但是，如果我在这两个@ManyToOne@JoinColumn把insertable = false，我可以运行下面的示例代码，但结果不是我的期望。内容中的所有参考都为空。任何线索修改模型？

@RunWith(SpringJUnit4ClassRunner.class) 
@SpringApplicationConfiguration(classes = Application.class) 
public class ApplicationTest 
{ 
    private static final String ENGLISH = "en"; 
    private static final String CHINESE = "zh_TW"; 

    @Autowired 
    private AnswerRepository answerRepository; 

    @Autowired 
    private ContentRepository contentRepository; 

    @Autowired 
    private QuestionRepository questionRepository; 

    private void saveQuestion(String english, String chinese, int sequence) 
    { 
     Question question = new Question(); 
     question.setSequence(sequence); 
     question = questionRepository.save(question); 

     Content englishContent = new Content(); 
     englishContent.setQuestion(question); 
     englishContent.setIndicator(1); 
     englishContent.setValue(english); 
     englishContent.setLanguage(ENGLISH); 
     englishContent = contentRepository.save(englishContent); 

     Content chineseContent = new Content(); 
     chineseContent.setQuestion(question); 
     chineseContent.setIndicator(1); 
     chineseContent.setValue(chinese); 
     chineseContent.setLanguage(CHINESE); 
     chineseContent = contentRepository.save(chineseContent); 
    } 

    private void saveAnswer(String english, String chinese, int sequence) 
    { 
     Answer answer = new Answer(); 
     answer.setSequence(sequence); 
     answer = answerRepository.save(answer); 

     Content englishContent = new Content(); 
     englishContent.setAnswer(answer); 
     englishContent.setIndicator(2); 
     englishContent.setValue(english); 
     englishContent.setLanguage(ENGLISH); 
     englishContent = contentRepository.save(englishContent); 

     Content chineseContent = new Content(); 
     chineseContent.setAnswer(answer); 
     chineseContent.setIndicator(2); 
     chineseContent.setValue(chinese); 
     chineseContent.setLanguage(CHINESE); 
     chineseContent = contentRepository.save(chineseContent); 
    } 

    @Test 
    public void test() throws Exception 
    { 
     saveQuestion("English question", "Chinese question", 1); 
     saveAnswer("English answer 1", "Chinese answer 1", 1); 
     saveAnswer("English answer 2", "Chinese answer 2", 2); 
    } 
}

内容表中的foreignKey同时指向两个不同的实体。有两个单独的表格会更好，否则你将永远在看指标来确定你的内容是什么。 –

答

答案取决于数据库中是否存在约束条件。如果您正在使用实体创建数据库，则会添加一个约束。如果数据库已经存在，并且没有约束，请继续阅读。根据需要，您可以将reference列设置为updatable=false和insertable=false，但如果它创建数据库，则JPA将在reference列上放置foreign key约束位置。

alter table Content add constraint FK_2l306mlep79l4pr7i0ltcbf7y foreign key (reference) references Answer 
alter table Content add constraint FK_2l306mlep79l4pr7i0ltcbf7y foreign key (reference) references Question

这将默默地失败，因为foreign key具有相同的名称：

Unsuccessful: alter table Content add constraint FK_2l306mlep79l4pr7i0ltcbf7y foreign key (reference) references Question

如果您有没有这样的限制现有的数据库，也有可能不是一个问题。你没看到reference列东西的原因是，你没有尝试任何东西插入到它，你应该做的，像这样：

List<Content> contents = new ArrayList<Content>(); 
contents.add(chineseContent); 
contents.add(englishContent); 
question.setContents(contents); 
em.merge(question);

但是，如果有一个约束，这将失败因为约束：

Referential integrity constraint violation: "FK_2L306MLEP79L4PR7I0LTCBF7Y: PUBLIC.CONTENT FOREIGN KEY(REFERENCE) REFERENCES PUBLIC.ANSWER(ID) (1)";SQL statement: 
update Content set reference=? where id=? [23506-173]

如果你有两列的工作，为什么不能让一个参照Question和一个参考的Answer？

@ManyToOne 
private Question question; 
@ManyToOne 
private Answer answer;

然后，您将需要测试哪一个在您的代码中为空，确实很难看，但并不比测试引用更糟糕。制作Content a @MappedSuperclass会更好，并创建两个新实体QuestionContent和AnswerContent，每个实体都可以扩展Content。这将意味着更多的表格，但它是一个干净的设计。

感谢您的详细解答。我选择最后的选择，因为该项目仍在开发中，我有时间去改变设计。另外，我可能会在第三张桌子上分享未来的内容。 –

不客气，祝你好运。 –

JPA将两个表与一个具有常数值的表连接起来

相关推荐