It spent a day trying solve this problem however it's still not successful.
I have Test entity :
public class Test {
@Id
@Column(name = "id")
@GeneratedValue(strategy = GenerationType.AUTO)
private long id;
@Column(name = "duration", nullable = false)
private int duration;
@Column(name = "test_name", nullable = false, unique = true)
private String testName;
@Column(name = "archived", nullable = false)
private boolean archived;
@OneToMany(cascade = CascadeType.ALL, mappedBy = "test", fetch = FetchType.EAGER)
private Set<Question> questions;
@ManyToMany(mappedBy = "tests")
private Set<User> users;
This Test entity has Set of questions, in such way Question Entity has Set of answers.
I wrote HQL query to get test:
@NamedQuery(name = "getCurrentTestById",
query = "SELECT test From Result result JOIN result.test test JOIN result.user user " +
"JOIN test.questions question JOIN question.answers answer " +
"WHERE test.id = :testId AND user.id = :userId " +
"AND result.permission.id = :permissionId AND question.isArchived = false AND answer.isArchived = false")
My DAO:
public Test getCurrentTest(long id, long testId, long permissionId){
Query query = em.createNamedQuery("getCurrentTestById");
query.setParameter("userId", id);
query.setParameter("testId", testId);
query.setParameter("permissionId", permissionId);
return (Test)query.getSingleResult();
}
So, everything is OK until i set any Question or Answer as "archived". I am still getting all of them, even if they are archived. Moreover, if i will mark all Set of questions\answers for current test as archived - i will get exception : no entity found for query
I think we have some misunderstood of the Hibernate concept here.
First of all, when using Hibernate you can't fetch only a few rows from a determined collection from an entity using a query, in other words, when you retrieve a entity fetching one of it's collection, all the records present in this collection will also be retrieved, even using a query to specifically remove those undesired records from the collection.
One way to do this is using the Hibernate's filtering, but that would require a few changes in your mapping and configurations, so I will not make more explanations about this approach, instead I will use your actual configuration to explain what can be done to resolve your problem.
In the query you have showed you are only telling Hibernate that you want a Test record which it's questions and answers are not archived, so by any means Hibernate would be able to determine that the collection inside the Test entity should also be filtered by the WHERE clause.
One way you can get all the Questions from a Test that are not archived would be doing selecting directly the Question objects from the Test entity making the desired WHERE inside of it, like the following:
SELECT question FROM Result result
JOIN result.test test
JOIN result.user user
JOIN test.questions question
JOIN question.answers answer
WHERE test.id = :testId
AND user.id = :userId
AND result.permission.id = :permissionId
AND question.isArchived = false
AND answer.isArchived = false
In this example you will receive a collection of Questions from a Test that are also getting filtered by your WHERE clause.
However, if you are really in need to use the Test entity for your logic, you can just create a get method in the Test entity that will the return a set of Questions that are not archived, and then use the results from this method in your logic:
public Set<Question> getQuestionsNotArchived() {
Set<Question> notArchivedQuestions = new HashSet<>();
for (Question question : questions) {
if (!question.isArchived()) {
notArchivedQuestions.add(question);
}
}
return notArchivedQuestions;
}
It is a perfectly fine approach to filter Hibernate collections, I personally use this quite often.
With this we solve the first problem you described, since now you can get a Test entity with the filtered values of the Questions collection.
Now, for the second problem, the no entity found for query, it's quite simple: The error is being raised because you are using the getSingleResult() from Query class, so it will throw an exception if it do not find any result from your query. The reason you are not getting any result if because you are explicit saying in you WHERE clause that you want only Tests which have at least one Question/Answer that is not archived, and as you also said, it only happen when you mark all the Question/Answer as archived, so the error is quite expected, since you in fact do not have any Test with Question/Answer not archived.
Hope with this explanations you can resolve your problems, good luck!
I found that the best solution to do that will be to save using Test entity (in case you don't want to create wrappers or several queries) and write NATIVE SQL query:
@NamedNativeQuery(name = "getCurrentTestById",
query = "SELECT t.id as tId, t.test_name, t.duration, q.id as qId, " +
"q.question, q.is_multichoice, q.is_open, a.id as aId, a.answer_text FROM result r " +
"JOIN test t ON r.test_id = t.id " +
"JOIN user u ON r.user_id = u.id " +
"JOIN question q ON t.id = q.test_id JOIN answer a ON q.id = a.question_id " +
"WHERE t.id = :testId AND u.id = :userId AND r.permission = :permissionId " +
"AND q.archived = false AND a.archived = false")
