Query a Hibernate many-to-many association

Question

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?

Answer 1

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>