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?
Your hql query should look like this:
from Company c join c.productLines pl where pl.name = :name
And mapping like this:
<hibernate-mapping>
<class name=com.example.ProductLine" table="productLine">
<cache usage="read-write"/>
<id name="id" column="id" type="long">
<generator class="native"/>
</id>
<property name="name" column="name"/>
</class>
</hibernate-mapping>
<hibernate-mapping>
<class name="com.example.Company" table="company">
<cache usage="read-write" />
<id name="id" column="id" type="long">
<generator class="native" />
</id>
<set name="productLines" table="companyProductLine" lazy="false">
<key column="companyId" />
<many-to-many class="com.example.ProductLine" column="productLineId" />
</set>
</class>
</hibernate-mapping>