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

Question

我有一个样本记录如下的数据库设计。问题和答案表共享相同的内容表，存储它们的措辞翻译。通过在内容指示符中指定1，我知道内容的引用是指问题的id(2表示答案)。

问题

+---------+----------+
| 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 |
+---------+-----------+----------------+----------+------------------+

我尝试使用以下代码将关系与 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.hhibernate引起。MappingException：实体jpatest.model的映射中出现重复列。内容列：引用（应使用insert=“false”update=“fa”映射）位于org.hhibernate.mapping.PersistentClass.checkColumnDuplication（PersistenceClass.java:709）~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]，位于org.hHibernate.mapping.PerssistentClass.checkPropertyColumnDuplicationorg.hhibernate.mapping.PersistentClass.checkColumnDuplication（PersistenceClass.java:753）~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]，位于org.hhi伯nate.mapging.PersiteClass.validate（PersintentClass.java：506）~[hibernate-core-4.3.11.Final.jar:4.3.11.Finall]，位于org.hhibernate.cfg.Configuration.validate（Configuration.java:1360）~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]，位于org.hhipernate.cfg.Configuration.buildSessionFactory（Configurance.java:1851）~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]，位于.org.hhibertate.jpa.boot.internal.EntityManagerFactoryBuilderImpl$4.perform~[hibernate-entitymanager-4.3.11.Final.jar:4.3.11.Final]…省略48个公共帧

然而，如果我将< code>insertable = false放入两个< code > @ ManyToOne < code > @ join column 中，我可以运行下面的示例代码，但结果不是我所期望的。内容中的所有引用均为空。有什么修改模型的线索吗？

@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);
   }
}

Answer 1

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

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

这将以静默方式失败，因为外键具有相同的名称：

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

如果现有数据库没有此约束，则可能没有问题。您在引用列中没有看到任何内容的原因是您没有尝试在其中插入任何内容，您应该这样做：

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]

如果您有两列要处理，为什么不将一列引用问题，另一列引用答案？

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

然后，您需要测试代码中哪一个是空的，真的很难看，但并不比一直测试引用更糟糕。最好将Content制作为@MappedSuperclass，并创建两个新实体， 和 ，每个实体都扩展 。这将意味着更多的桌子，但这是一个干净的设计。