CODEWITHSUNDEEP
javamysqlhibernate
Surendheran
Surendheran

Reputation: 197

HQL equivalent query to criteria query in hibernate many to many relationship?

I have two tables stock and category having many-to-many relationship each other.

Stock.hbm.xml

<hibernate-mapping>
<class name="hibernate.mapping.manytomany.Stock" table="stock">
    <id name="stockId" type="java.lang.Integer">
        <column name="STOCK_ID" />
        <generator class="identity" />
    </id>
    <property name="stockCode" type="string">
        <column name="STOCK_CODE" length="10" not-null="true" unique="true" />
    </property>
    <property name="stockName" type="string">
        <column name="STOCK_NAME" length="20" not-null="true" unique="true" />
    </property>
    <set name="categories" table="stock_category" 
        inverse="false" lazy="true" fetch="select" cascade="all" >
        <key>
            <column name="STOCK_ID" not-null="true" />
        </key>
        <many-to-many entity-name="hibernate.mapping.manytomany.Category">
            <column name="CATEGORY_ID" not-null="true" />
        </many-to-many>
    </set>
</class>
</hibernate-mapping>

Category.hbm.xml

<hibernate-mapping>
<class name="hibernate.mapping.manytomany.Category" table="category">
    <id name="categoryId" type="java.lang.Integer">
        <column name="CATEGORY_ID" />
        <generator class="identity" />
    </id>
    <property name="name" type="string">
        <column name="NAME" length="10" not-null="true" />
    </property>
    <property name="desc" type="string">
        <column name="[DESC]" not-null="true" />
    </property>
    <set name="stocks" table="stock_category" inverse="true" lazy="true"
                                 fetch="select">
        <key>
            <column name="CATEGORY_ID" not-null="true" />
        </key>
        <many-to-many entity-name="hibernate.mapping.manytomany.Stock">
            <column name="STOCK_ID" not-null="true" />
        </many-to-many>
    </set>
</class>
</hibernate-mapping>

And this is my criteria query,

Criteria c = session.createCriteria(Category.class, "c");
c.createAlias("c.stocks", "s");
c.add(Restrictions.eq("c.categoryId", 1));
c.setProjection(Projections.projectionList()
 .add(Projections.property("s.stockId"))
 .add(Projections.property("s.stockName")));

I need equivalent HQL for this scenario..I've tried this but it gives different result,

String query = "select c.stocks.stockId, c.stocks.stockName from Category c where
                    c.categoryId=1"

Let me know if you need more details.

Upvotes: 1

Views: 1181

Answers (2)

gyanu
gyanu

Reputation: 995

I am planning to investigate this issue thoroughly. What i doubt from previous answer for now is there should be ON Clause in join statement. Pardon me if my expectation is wrong.

select s.stockId,s.stockName from Category c join c.stocks s 
  on c.stockId=s.stockId where c.categoryId = 1 ;
  //it might be on c.CATEGORY_ID=s.CATEGORY_ID also

Upvotes: 0

JB Nizet
JB Nizet

Reputation: 691735

OK, so apparently, you missed the section about joins in the documentation:

select s.stockId,               // equivalent to the s.stockId projection
       s.stockName              // equivalent to the s.stockName projection
       from Category c          // equivalent to the root criteria creation
       join c.stocks s          // equivalent to the alias creation
       where c.categoryId = 1   // equivalent to the restriction addition

Upvotes: 1

Related Questions