Hibernate query on four tables

Question

I have three tables.

• question ( forumId as FK)
• answer (questionId as FK)
• forum
• forum member (forumId as FK)

There can be multiple forums so i want to add a method that returns me all answer from a member in a given forum.

I dont know what is the hibernate query i can do. To get the list of answer bound to the given member i do this

return HibernateUtil.query(Answer.class,"authorId",member.getId());

What can i do to get a list of answer from a member in a given forum.

Answer 1

You're thinking too much about tables and not enough about objects. Hibernate is an object-relational mapping tool.

I see four objects here:

1. Question; has a Collection of Answers
2. Answer
3. Forum; has Collections of Questions and Members
4. Member; has Collections of Questions and Answers

Other relationships depend on whether relationships are uni-directional or bi-directional.

Start thinking in terms of objects and Hibernate will be a lot easier.

If you don't have or want objects, don't use Hibernate. Just go for straight JDBC, do the JOIN, and map the result into an object or data structure in that case.

Answer 2

Use HQL:

public List<Answer> getMemberAnswers(Forum forum, Member member) {
  return getSession().createQuery(
    "select f.answers from Forum f " +
    "join f.member m " +
    "where f.id = :forumId " +
    "and m.id = :memberId")
  .setInteger("forumId", forum.getId())
  .setInteger("memberId", member.getId())
  .list();
}