hibernate many-to-many

Question

Hi,

i have three tables and 2 JPA model classes:

Unit
------------
id [PK]    - Integer
code       - String
unitGroups - List<UnitGroup>


UnitGroup
------------
id [PK]    - Integer
ugKey      - String
units      - List<Unit>

units and unitGroups have many-to-many relationship between themselves. briefly i want to write an HQL query to get the output of following sql:

SELECT u.* 
FROM units u, unit_groups ug, unit_group_pairs ugp 
WHERE ugp.UnitID = u.ID 
AND ugp.UnitGroupID = ug.ID 
AND ug.UGKey = 'amount' AND u.ID = 10

Answer 1

I hope this will work, but not sure. Please no negatives :). I haven't tried this out myself. Just come up with this, so it might help you. Cheers.

from Unit as units 
inner join fetch units.unitGroups grp
inner join fetch grp.units
where grp.ugKey = 'amount' and units.id = 10

Answer 2

Try this

select u from  unit as u 
where u.ID = 10 and 
'amount' = any elements(u.unitGroups.UGKey)

Answer 3

at last:

select u from Unit u left join u.unitGroups ug where u.id = 10 and ug.ugKey = 'amount'