Hibernate Combine two HQL query without relations

Question

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

Possible duplicate of [Spring Data JPA - Custom Query with multiple aggregate functions in result](https://stackoverflow.com/questions/32049001/spring-data-jpa-custom-query-with-multiple-aggregate-functions-in-result) — sam, Oct 04 '19 at 17:55
I don't think so, in the answer that you mentioned all the data are fetched from only one entity that is UserVideoRating. In my case, I have 2 entity. — gixlg, Oct 04 '19 at 19:52
your syntax is incorrect. you will find the correct syntax to write that sort of queries in the link above. — sam, Oct 05 '19 at 04:59
Still is not clear what is the right syntax. Can you please help me @Sam? — gixlg, Oct 05 '19 at 15:46
First of all if you execute count(*)>1, then you will get `ERROR: No query specified`. Update your question with the correct query. — sam, Oct 08 '19 at 08:50
What is the "correct query"? I know that the query that I wrote is not the right syntax, but my goal was to explain what I want to achieve. — gixlg, Oct 08 '19 at 09:06
read this article. https://thoughts-on-java.org/how-to-join-unrelated-entities/ — sam, Oct 08 '19 at 09:07
by no means you can have count(*)>1 or something. you can take the count and use if condition by looping throughout the list of data. — sam, Oct 08 '19 at 17:07
also the thing you are trying is not a good idea using the given query, from a performance point of view. — sam, Oct 08 '19 at 17:12

score 1 · Accepted Answer · answered Oct 15 '19 at 13:42

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

Hibernate Combine two HQL query without relations

1 Answers1