• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

hibernate question - left outer join (best practices)

 
Caden Whitaker
Greenhorn
Posts: 24
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Table 1: Question
Columns:
QuestionID
QuestionName

Object:


Table 2: Answer
Columns:
AnswerID
QuestionID
AnswerValue

Object:


To display the entire survey and check off the questions they have completed this is how I would expect to see the results.



So to get that, in sql, this is the sort of query I would write


That would give me a list of all questions including the ones that have already been answered. I could then loop over that list to check off the questions that are complete and continue to display the remaining questions that are incomplete.

So - in hibernate, what would be the best practice to achieve this? Any help would be greatly appreciated.
 
Xin Zoo
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
BTW: AnswerValue should probably not be nullable. Do you want null-Anwers?

I suggest you add a One-To-Many relationship from questions to answers:

The you can do something like this using JPQL:
 
Caden Whitaker
Greenhorn
Posts: 24
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello, thanks for the reply.. I don't think I accurately described my issue.

This is how the data works, I forgot to add a column in the answer table (I've added it here, called userid). This is why the left join is important.
Table 1: Question
Columns:
QuestionID
QuestionName


Table 2: Answer
Columns:
AnswerID
QuestionID
UserID



So then, if I ran this query


I would get these results


If I ran this query


I would get this


I don't see how OneToMany solves this, I don't need an array of answers on the question object. I need to map a single question to a single answer, however in the database a question can be mapped to multiple answers. I hope I am describing this correctly.

 
Xin Zoo
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you want exactly one question to one answer, then you can use the One-To-One Mapping.


If you want a bidirectional association add it also to the Answer class:


Then you can use JPQL like this:
 
Caden Whitaker
Greenhorn
Posts: 24
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello, I really appreciate you taking the time to provide some insight into this issue. I have one more question:



How do I specify the one-to-one relationship in Question to Answer as the Question table has no ID mapping to the Answer table? Is there something specific I have to do to infer this relationship?
 
Xin Zoo
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

sorry, your foreign key column is in the answer table. Remeber if you really want to ensure there is only one answer to a question, this column needs a unique constraint. Try this mapping:


In this case the Class Answer has to implement Serializable.

You can turn the mapping around and make Question the passive part if you like.

There is also a special type of one-to-one mapping. instead of using the column questionid as a foreign key, you can even use the primary key of Answer to be the foreign key of question at the same time. this saves you one column and also it guarantees you that a question can only have one answer. if you choose this, you should be sure, that you will never want a question to have more than one answer. otherwise you will have to change the database schema.

If you like to have detailed information on the mapping with annotations i suggest you have a look at the manual. There are far more possibilities to solve this issue than i can explain in this forum: Hibernate Annotations Manual (PDF)
 
Ubba Bazooka
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello,

I have nearly the same problem, but the query doesn't return the required data for me.

In this sample:

there are only "questions" with assigned answeres for the given ":user" returned.

In SQL with the left join "on userid = xxx", all questions are returned ... not only this one with assigned answers for this user.

If I need also the questions with no assigned answer, how do I build the left join in ejbql?

I tried something like

but it doesn't work :-(


Thanks,
Ubba

 
Salil Vverma
Ranch Hand
Posts: 257
Hibernate Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hey Guys,

I think, the problem that you are trying to tackle is the scenario of a test module. In this module with following condition.
1-There are many questions in the system
2-One user can appear in test only once.
3- Few questions from complete set of questions in the system is presented to user.
4- User might choose not to answer some of these questions.

If this is the scenario, then we shall have to use three tables 1- Question 2- Answer 3- Users

As same question might have been answered by various users, there would be one to many relationship between question- answer table.
As same user might have given multiple answers , there would be one to many relationship between answer to user

Implement the relationship in this way and let us know if you face any challenge in getting desired result.


 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic