JPA joining two tables with one table with constant value

Question

I have a database design with sample records as below. Both Question and Answer tables share the same Content table that stores their wordings translation. By specifying 1 in the indicator of Content, I know that the reference of Content refer to the id of Question (2 for Answer).

Question

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

Answer

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

Content

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

I try to link the relationship with JPA using following code:

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

It throws following exception during compile time:

Caused by: org.hibernate.MappingException: Repeated column in mapping for entity: jpatest.model.Content column: reference (should be mapped with 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.Final] 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.validate(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 common frames omitted

However, if I put insertable = false in both @ManyToOne @JoinColumn, I can run below sample code but the result is not my expectation. All of the reference in Content are null. Any clue to modify the model?

@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

The answer depends on whether or not there are constraints in the database. If you are creating the database with the Entities, then a constraint will be added. If the database already exists, and there is no constraint, read on. You can set the reference column to updatable=false and insertable=false, as you need to, but if it creates the database, JPA will put foreign key constraints places on the reference column.

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

This will silently fail because the foreign key has the same name:

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

If you have an existing database that does not have this constraint, there may not be an issue. The reason that you didn't see anything in the reference column is that you did not try to insert anything into it, which you should do like so:

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

However, if there is a constraint, this will fail because of the constraint:

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]

If you have two columns to work with, why not make one a reference to a Question and one a reference to an Answer?

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

Then you will need to test which one is null in your code, truly ugly, but no worse than testing reference all of the time. It would be better to make Content a @MappedSuperclass and create two new entities, QuestionContent and AnswerContent, each that extends Content. This will mean more tables, but it is a clean design.