Categories
hibernate hql sql

HQL: using elements() on objects

say I have a database with two tables: classes and students. Each table contains metadata about the relevant topic. For example, classes contains fields ‘classid’, ‘name’ and ‘room_number’ and students contains fields ‘name’, ‘classid’, ‘age’ and ‘height’ (say a student can only be in one class).

What if I want to write an HQL query to get me all classes that include students with names ‘Joe’, ‘Bob’, and ‘Fred’? If students only contained ‘classid’ and ‘name’ I think I could write the following in HQL.

from Classes as class where
'Joe' in elements(class.students) and
'Bob' in elements(class.students) and
'Fred' in elements(class.students)

However, students is an object in this case. Do I need to perform the joins explicitly?

from Classes as class
join class.students as s1
join class.students as s2
join class.students as s2
where
s1.name="Joe"
s2.name="Bob"
s3.name="Fred"

I imagine that using 'Fred' in elements also performs these joins, but it is much more compact to write! Or is there a better way of doing this altogether?