Query a Hibernate many-to-many association
- by Perry Hoekstra
In Hibernate HQL, how would you query through a many-to-many association. If I have a Company with multiple ProductLines and other companies can offer these same product lines, I have a Company entity, a ProductLine entity and an association table CompanyProductLine. In SQL, I can get what I need like this:
select * from company c where c.companyId in (select companyId from companyProductLine cpl, productline pl where cpl.productLineId = pl.productLineId and pl.name= 'some value');
My problem sees to lie with the association I defined in the Company.hbm.xml file:
<set name="productLines"
cascade="save-update"
table="CompanyProductLine">
<key column="companyId"/>
<many-to-many class="com.foo.ProductLine" column="productLineId" />
</set>
Any HQL I seem to come up with will throw a: 'expecting 'elements' or 'indices"' Hibernate exception.
Thoughts on what the proper HQL would be?
