How to customize the query that is used to fetch the "many" related entities of a one-to-many relationship?

60 Views Asked by At

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?

0

There are 0 best solutions below