Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 3 posts ] 
Author Message
 Post subject: HQL with NOT EXISTS and @ManyToMany association
PostPosted: Thu Mar 16, 2017 10:47 am 
Newbie

Joined: Thu Mar 16, 2017 10:42 am
Posts: 7
Hello,

I have 3 tables - a student table, a course table and a student_course table.

The table ddl's are as follows:

CREATE TABLE STUDENT
(
studentId VARCHAR2(10) NOT NULL,
studentName VARCHAR2(50) NOT NULL,
PRIMARY KEY(studentId)
);

CREATE TABLE COURSE
(
courseId VARCHAR2(10) NOT NULL,
courseName VARCHAR2(50) NOT NULL,
PRIMARY KEY(courseId)
);

CREATE TABLE STUDENT_COURSE
(
SC_STUDENT_ID VARCHAR2(10) NOT NULL,
SC_COURSE_ID VARCHAR2(10) NOT NULL,
PRIMARY KEY(SC_STUDENT_ID, SC_COURSE_ID));

ALTER TABLE STUDENT_COURSE
ADD CONSTRAINT FK_STUDENTS FOREIGN KEY(SC_STUDENT_ID)
REFERENCES STUDENT (studentId)
ON DELETE CASCADE;

ALTER TABLE STUDENT_COURSE
ADD CONSTRAINT FK_COURSES FOREIGN KEY(SC_COURSE_ID)
REFERENCES COURSE (courseId);

Now, I want to fetch all students who are NOT enrolled in a particular course.

The SQL would be:

Select * from Student s
where not exists (
Select * from Student_course sc
where sc.student_id = s.student_id
and sc.course_id = :course);

In the above SQL, I would pass in the :course parameter.

Is the following HQL an equivalent of the above SQL and if so, is it correct?

from Student s join fetch s.courses courses
where not exists (
from STUDENT_COURSE stdcrse left join stdcrse.course c
where stdcrse.studentid = s.studentid
and c.courseid = :course)


Thank you!


Top
 Profile  
 
 Post subject: Re: HQL with NOT EXISTS and @ManyToMany association
PostPosted: Thu Mar 16, 2017 12:46 pm 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1541
Location: Romania
Easy. The best @ManyToMany association is when you don't use @ManyToMany at all.

Just map the StudentCourse table as an entity which has 2 @ManyToOne associations to Course and Student. This way, your HQL query is the same with the SQL one:

Code:
Select s
from Student s
where not exists (
    Select 1 from
    StudentSourse sc
    where sc.student = s and sc.course = :course
)

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
 Post subject: Re: HQL with NOT EXISTS and @ManyToMany association
PostPosted: Thu Mar 16, 2017 2:06 pm 
Newbie

Joined: Thu Mar 16, 2017 10:42 am
Posts: 7
Wow! Excellent! Thank you so much. I will try it out.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 3 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
cron
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.