I am developing a Q&A-style web application in which two of the entities are Question and Answer. A Question has-many Answers. In addition, an Answer has-many Answers (which I refer to as child answers of an answer):
@Entity
@Table(name = "questions")
public class Question implements Serializable {
@Id
private Long id;
@OneToMany(mappedBy = "question")
private Set<Answer> answers;
//...
}
@Entity
@Table(name = "answers")
public class Answer implements Serializable {
@Id
private Long id;
@ManyToOne
private Question question;
@ManyToOne
private Answer parentAnswer;
@OneToMany(mappedBy = "parentAnswer")
private Set<Answer> childAnswers;
//...
}
When fetching child answers of a specific answer, Hibernate uses a query similar to:
select ... from answers childanswe0_ where childanswe0_.parent_answer_id=?
What I would like to do is customize the WHERE clause so that the question ID is also included; I would like something like this:
select ... from answers childanswe0_ where
childanswe0_.question_id=? and childanswe0_.parent_answer_id=?
The reason why is that I have an index on answers(question_id, parent_answer_id) that I would like to be used. I do not currently have, and I do not want to add, an index on answers(parent_answer_id), so the current query results in a full table scan.
I tried using @JoinColumns on the childAnswers field, but this resulted in the error: org.hibernate.AnnotationException: Associations marked as mappedBy must not define database mappings like @JoinTable or @JoinColumn: test.domain.Answer.childAnswers
I also tried using @JoinColumns on the parentAnswer field, but this resulted in the error: org.hibernate.AnnotationException: A Foreign key refering test.domain.Answer from test.domain.Answer has the wrong number of column. should be 1
My JPA provider is Hibernate 5.2.17.
How do I ensure that the WHERE clause of the query used to select the child answers of a parent answer includes the parent answer's question ID?
