CODEWITHSUNDEEP
hibernatehql
gixlg
gixlg

Reputation: 1355

Hibernate Combine two HQL query without relations

I'm working with Hibernate and this is my scenario:
I have two entities, without any relation between them (the only relations that exist are logic relations, and that's the problem).
The two entities are like Student and FinalExam.

Student and Exam are like that: 

class Student {
    private String name;
    private String surname;
    private int age;
}

class FinalExam {
    private String candidateName;
    private String candidatesurname;
    private int mark; 
}

I'd like to create an HQL query to have the following columns: name, surname, passed.
Name and surname are the name and surname of the Student, the connection between the 2 entities.
The column passed can be true or false. It represents if a Student has passed the exam.
So it is true if exists a row in FinalExam with a mark higher then 6.

How I can achieve that?
I'd like something like that:

select s.name, s.surname, ( Select count(*)>1 from FinalExam exam
                            where exam.name = s.name
                            and exam.surname = s.surname
                            and exam.mark > 6) 
from Student s

Upvotes: 0

Views: 317

Answers (1)

Joe Taras
Joe Taras

Reputation: 15379

You must add a property to Student class, as follow:

private boolean passed;

this property must not saved on DB (if you use JPA annotation is annoted ad @Transient, if you use XML mapping, you don't put in the XML file, or if you put, you can use a formula tag with insert and update property set to false.

About your query (I assume you have added this property in your class):

SELECT s.name, s.surname,
    (SELECT
        CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END
    FROM FinalExam f
    WHERE f.name = s.name
    AND f.surname = s.surname
    AND f.mark > 6)
FROM Student s

Upvotes: 1

Related Questions