Hibernate Community • View topic - Eager-fetching multiple sets without outer join

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

Eager-fetching multiple sets without outer join

Page 1 of 1

[ 5 posts ]

Previous topic | Next topic

Author

Message

jaalex

Post subject: Eager-fetching multiple sets without outer join

Posted: Fri Jan 16, 2009 12:01 am

Newbie

Joined: Mon May 19, 2008 12:42 am
Posts: 10

Hi,

I have a Product entity which has two <set>s simpleProductAttributes and complexProductAttributes. They need to be fetched eagerly when a Product is queried ; ie the output should be List<Product> each item having the simple and complex attributes populated.

Reading abt eager fetch strategies, i see that it's suggested we don't use fetch=join in the mapping hbm when there are multiple sets since that'll result in a huge outer join with lot of redundancies. So, i'm trying to do the same in the code using Criteria.

Code:

       prodListSimple = (ArrayList<Product>) currentSession.createCriteria(Product.class).setFetchMode("simpleProductAttributes", FetchMode.JOIN).list();
...
       prodListComplex = (ArrayList<Product>) currentSession.createCriteria(Product.class).setFetchMode("complexProductAttributes", FetchMode.JOIN).list();

Now, i've to programatically combine prodListSimple and prodListComplex into a single prodList which has both the simple and complex attributes populated for each Product instance..and this seems ugly. Is there a way to do the same thing in a single Criteria fetch so that Hibernate itself can give the prodList fully populated - and have an efficient query(ies) at the same time?

Below are excerpts from hbm for the classes in question:

Code:

   <class
      name="com.xyz.dao.Product"
      table="PRODUCT" lazy="false">
      <set name="simpleProductAttributes" inverse="false" lazy="true"
         sort="com.xyz.dao.ProductAttributeComparator">
      <key...         
      <one-to-many entity-name="SimpleProductAttribute" />      
      <filter..
      </set>
         ....
      <set name="complexProductAttributes" inverse="false" lazy="true"
         sort="com.xyz.dao.ProductAttributeComparator">
      <key...         
      <one-to-many entity-name="ComplexProductAttribute" />      
      <filter..
      </set>
   </class>
   <class
      name="com.xyz.dao.ProductAttribute"
      table="PRODUCT_ATTRIBUTE" entity-name="SimpleProductAttribute"
      lazy="true">
      <composite-id>
         <key-many-to-one name="product"
            class="com.xyz.dao.Product">
            <column name="PRODUCT_ID" precision="22" scale="0" />
         </key-many-to-one>
         <key-many-to-one name="attribute"
            class="com.xyz.dao.Attribute">
            <column name="ATTRIBUTE_ID" precision="22" scale="0" />
         </key-many-to-one>
         <key-many-to-one name="erPricing"
            class="com.xyz.dao.Pricing">
            <column name="ERP_ID" precision="22" scale="0" />
         </key-many-to-one>
      </composite-id>
      
   </class>

   <class
      name="com.xyz.dao.ProductAttribute"
      table="PRODUCT_ATTRIBUTE_CLOB"
      entity-name="ComplexProductAttribute" lazy="true">
   </class>   
   

Any suggestions will be greatly appreciated.

Top

pkleindl

Post subject:

Posted: Fri Jan 16, 2009 4:46 am

Senior

Joined: Thu Jan 08, 2009 3:48 pm
Posts: 168

Mapping the collections with fetch="subselect" should result in 3 queries all the time, 1 to fetch the Product(s) and one each to fetch all simple and complex attributes for all the Products loaded.
This also prevents the need to remove double entries in the product list resulting from the join of the two collections

Top

jaalex

Post subject:

Posted: Mon Jan 19, 2009 11:36 am

Newbie

Joined: Mon May 19, 2008 12:42 am
Posts: 10

Thanks pkleindl. I've a filter defined to select only certain attributes (i.e on the ATTRIBUTE_ID column) and i've enabled it, along with fetch=subselect

The SQL generated is like (for 4 attribute_ids in filter):

Code:

Hibernate: select this_.PRODUCT_ID as PRODUCT1_5_0_, this_.PRODUCT_CODE as PRODUCT2_5_0_, this_.PRODUCT_STATUS as PRODUCT3_5_0_ from PRODUCT this_

Hibernate: select complexpro0_.PRODUCT_ID as PRODUCT1_1_, complexpro0_.ATTRIBUTE_ID as ATTRIBUTE2_1_, complexpro0_.ERP_ID as ERP3_1_, complexpro0_.PRODUCT_ID as PRODUCT1_7_0_, complexpro0_.ATTRIBUTE_ID as ATTRIBUTE2_7_0_, complexpro0_.ERP_ID as ERP3_7_0_, complexpro0_.ATTRIBUTE_VALUE as ATTRIBUTE4_7_0_ from PRODUCT_ATTRIBUTE_CLOB complexpro0_ where  complexpro0_.ATTRIBUTE_ID IN (?, ?, ?, ?) and complexpro0_.PRODUCT_ID in (select this_.PRODUCT_ID from PRODUCT this_)

select simpleprod0_.PRODUCT_ID as PRODUCT1_1_, simpleprod0_.ATTRIBUTE_ID as ATTRIBUTE2_1_, simpleprod0_.ERP_ID as ERP3_1_, simpleprod0_.PRODUCT_ID as PRODUCT1_6_0_, simpleprod0_.ATTRIBUTE_ID as ATTRIBUTE2_6_0_, simpleprod0_.ERP_ID as ERP3_6_0_, simpleprod0_.ATTRIBUTE_VALUE as ATTRIBUTE4_6_0_  from PRODUCT_ATTRIBUTE simpleprod0_ where  simpleprod0_.ATTRIBUTE_ID IN (?, ?, ?, ?) and simpleprod0_.PRODUCT_ID in (select this_.PRODUCT_ID from PRODUCT this_)

4 SQLs like ( 1 per Attribute in the filter) :

Code:

Hibernate: select attribute0_.ATTRIBUTE_ID as ATTRIBUTE1_3_0_, attribute0_.ATTRIBUTE_NAME as ATTRIBUTE2_3_0_, attribute0_.ALIAS_NAME as ALIAS3_3_0_ from ATTRIBUTE attribute0_ where attribute0_.ATTRIBUTE_ID=?

The sub-selects are fetching all the data i need, both simple and complex attributes that match the filter. But i dont understand why the last 4 SQLs are fired, when the Attribute values have been already fetched. Any idea?

Top

pkleindl

Post subject:

Posted: Mon Jan 19, 2009 4:55 pm

Senior

Joined: Thu Jan 08, 2009 3:48 pm
Posts: 168

The last 4 SELECTs go to the table ATTRIBUTE, not PRODUCT_ATTRIBUTE

This usually happens when you do a loop over prodListSimple and access the attribute association with getAttribute()....

Top

jaalex

Post subject:

Posted: Mon Jan 19, 2009 11:43 pm

Newbie

Joined: Mon May 19, 2008 12:42 am
Posts: 10

Ok..but i'm not looping over the prodlist (though i'll have to do it eventually).

The only code is:

Code:

currentSession.enableFilter("validAttributes").setParameterList("validAttributeList", attrList);
       prodList = (ArrayList<Product>) currentSession.createCriteria(Product.class).list();

but still the SQLs on ATTRIBUTE table are fired.

Top

Page 1 of 1

[ 5 posts ]

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum