CODEWITHSUNDEEP
javasqlhibernateentity
quento
quento

Reputation: 1114

SQL query: incorrect work with entity

I spent 2 days trying to resolve this problem. I have Test Entity : 

    @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(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.

Question Entity:

    @Id
@Column(name = "id")
@GeneratedValue(strategy = GenerationType.AUTO)
private long id;
@Column(name = "is_multichoice", nullable = false)
private boolean isMultichoice;
@Column(name = "is_open", nullable = false)
private boolean isOpen;
@Column(name = "picture")
private String picture;
@Column(name = "question")
private String question;
@ManyToOne
@JoinColumn(name = "test_id", nullable = false)
private Test test;
@Column(name = "archived", nullable = false)
private boolean isArchived;
@OneToMany(mappedBy = "question", fetch = FetchType.EAGER)
private Set<Answer> answers;

Answer Entity:

    @Id
@Column(name = "id")
@GeneratedValue(strategy = GenerationType.AUTO)
private long id;
@Column(name = "answer_text", nullable = false)
private String answerText;
@Column(name = "mark", nullable = false)
private int mark;
@ManyToOne
@JoinColumn(name = "question_id")
private Question question;
@Column(name = "picture")
private String picture;
@Column(name = "archived", nullable = false)
private boolean isArchived;

Then, my goal is to get Test from DB. I wrote HQL query with condition: questions and answers should't be archived. Here it is:

@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")

However, this query still returned me even archived questions/answers. Then, i tried to write native SQL query : 

               query = "SELECT t.id, t.test_name, t.duration, q.id as qId, " +
                    "q.question as question, q.is_multichoice as is_multichoice, " +
                    "q.is_open as is_open, a.id as aId, a.answer_text as 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", resultClass = com.bionic.entities.Test.class)

BINGO! I tested that in MYSQL workbench and that worked, i got only not archived records. HOWEVER, when i used it for my entity archived questions and answers still appear!

I need to solve that problem without creating wrappers and methods (in Java) to get not-archived records from all. So, i hope to get just what i need from query.

Upvotes: 0

Views: 56

Answers (1)

v.ladynev
v.ladynev

Reputation: 19956

If you have a concrete test that can have archived and not archived questions — your HQL is not correct. Hibernate find a test having one or more not archived questions and after that load a test fetching all questions (archived or not archived). So you need to query a list of questions. Something like this

from Question question inner join question.test test WHERE test.id = :testId AND question.archived = false

Upvotes: 1

Related Questions