Fetch join in criteria API for one-to-many relations

TiMiD · **Joined:** Sun Sep 09, 2007 9:26 pm **Posts:** 8

Hibernate version:3.2.4.sp1

Hello !

I've got the following objects relations :

[Product]1--*[Price]*--1[company]

So one price per company for one product.

The Product object contains a set of prices, here are some relevant parts of code :

Product class :

Code:

public class Product{
    private int id;
    ....
    private Set<Price> prices;
    ....
}

Product's prices set mapping :

Code:

<set name="prices" fetch="join" lazy="false">
    <key column="product_id"/>
    <one-to-many class="Price"/>
</set>

If I run the following HQL request, everything goes all right (I get only one item in the "prices" set and it's fetched with the first request) :

Code:

FROM Product AS product JOIN FETCH product.prices AS price WHERE product.id=56688000 AND price.company.id=10022

On the other hand I cannot reproduce the behaviour with the criteria API ; here is how I translated the request :

Code:

Criteria productCriteria=session.createCriteria(Product.class);
productCriteria.add(Restrictions.idEq(56688000));
productCriteria.setFetchMode("prices", FetchMode.JOIN);
Criteria priceCriteria=productCriteria.createCriteria("prices", JoinFragment.INNER_JOIN);
priceCriteria.add(Restrictions.eq("company.id",10022));
List<Product> products=(List<Product>)priceCriteria.list();

In that case, a second SQL request is run to fech the prices set and it contains all the prices for all the companies (for the current product), so it seems that my criterias on "prices" are ignored.

Is that a bug or I'm I doing something wrong ?

sethrohit1977 · **Joined:** Mon Jan 22, 2007 10:32 am **Posts:** 101

I think you need to create another criteria / alias after creating criteria for prices.

try something like this

Criteria productCriteria=session.createCriteria(Product.class);
productCriteria.add(Restrictions.idEq(56688000));
productCriteria.createAlias("prices","pr")
.createAlias("company","c").
add(Restrictions.idEq(10022));

List<Product> products=(List<Product>)productCriteria.list();

TiMiD · **Joined:** Sun Sep 09, 2007 9:26 pm **Posts:** 8

Unfortunately it doesn't work, it tries to get all the products with ID=56688000 AND ID=10022.

I tried another request :

Code:

createCriteria(Product.class); 
productCriteria.add(Restrictions.idEq(56688000)); 
productCriteria.createAlias("prices","price");
productCriteria.add(Restrictions.eq("price.company.id",10022));

But it gives the same results as the one I wrote previously.
Right now I'm using an HQL query constructed by concatenation, but it would be nice to understand how to do that kind of joins in hibernate with the criteria API.
I wonder if it's even possible.
(cf http://www.hibernate.org/hib_docs/reference/en/html/querycriteria.html#querycriteria-associations)

sethrohit1977 · **Joined:** Mon Jan 22, 2007 10:32 am **Posts:** 101

try this

Criteria productCriteria=session.createCriteria(Product.class);
productCriteria.add(Restrictions.idEq(56688000));
productCriteria.createAlias("prices","pr")
.createAlias("pr.company","c")
.add(Restrictions.eq("c.id",10022));

List<Product> products=(List<Product>)productCriteria.list();

TiMiD · **Joined:** Sun Sep 09, 2007 9:26 pm **Posts:** 8

This time I get an SQL error :

Code:

Hibernate: select this_.id as id10_1_, this_.name as name10_1_, this_.small_box_quantity as small4_10_1_, this_.large_box_quantity as large5_10_1_, this_.data_type as data2_10_1_, pr1_.product_id as product1_9_0_, pr1_.company_id as company2_9_0_, pr1_.unit_price as unit3_9_0_, pr1_.company_specific_product_id as company4_9_0_, pr1_.abbreviation as abbrevia5_9_0_, pr1_.price_quantity as price6_9_0_ from product this_ inner join price pr1_ on this_.id=pr1_.product_id where this_.id = ? and c2_.id=?

SQL Error: 0, SQLState: 42P01
ERREUR: Entrée manquante de la clause FROM pour la table «c2
(translation : c2 cannot be found in the FROM statement)

I suppose that here c2 refers to the company table, but it's not loaded (And doesn't needs to since I'm only refering to the ID which is contained in the price table)
Very weird problem.
By the way doesn't an SQL error means a bug in hibernate (excepted when you mistake in your mappings) ?

sethrohit1977 · **Joined:** Mon Jan 22, 2007 10:32 am **Posts:** 101

Can you post your mapping files?

TiMiD · **Joined:** Sun Sep 09, 2007 9:26 pm **Posts:** 8

Sure, here I go :

Product :

Code:

<hibernate-mapping>
   <class name="dto.Product" table="product" discriminator-value="0">
      <id name="id" column="id">
         <generator class="sequence">
            <param name="sequence">product_id_seq</param> 
         </generator>
      </id>
      <discriminator column="data_type" type="integer"/>

      <set name="prices" fetch="join" lazy="false">
         <key column="product_id"/>
         <one-to-many class="dto.Price"/>
      </set>
      <property name="name" column="name"/>
      <property name="smallBoxQuantity" column="small_box_quantity"/>
      <property name="largeBoxQuantity" column="large_box_quantity"/>
      <subclass name="dto.LocalizedProduct" discriminator-value="1"/>
      <subclass name="dto.GlobalProduct" discriminator-value="2"/>
   </class>
</hibernate-mapping>

Price :

Code:

<hibernate-mapping>
   <class name="dto.Price" table="price">
      <composite-id>
         <key-many-to-one name="product" class="dto.Product" column="product_id"/>
         <key-many-to-one name="company" class="dto.Company" column="company_id" />
      </composite-id>
      <property name="unitPrice" column="unit_price"/>
      <property name="companySpecificProductId" column="company_specific_product_id"/>
      <property name="abbreviation" column="abbreviation"/>
      <property name="priceQuantity" column="price_quantity"/>
   </class>
</hibernate-mapping>

Company :

Code:

<hibernate-mapping>
   <class name="dto.Company" table="company">
      <cache usage="read-write"/>
      <id name="id" column="id"/>
      <property name="name" column="name"/>
      <property name="discountPercent" column="discount_percent"/>
      <property name="taxPercent" column="tax_percent"/>
      <many-to-one name="currency" class="dto.Currency" column="currency_id"/>
      <many-to-one name="companyGroup" class="dto.CompanyGroup" column="group_id"/>
   </class>
</hibernate-mapping>

sethrohit1977 · **Joined:** Mon Jan 22, 2007 10:32 am **Posts:** 101

Ok I will try once again, I tried this on my set up was able to extarct the sql also

session.createCriteria(.Product.class).add(Restrictions.idEq(new Long(56688000))).createAlias("prices","pr").add(Restrictions.eq("pr.company.id",new Long(10022))).list();

This generates a sql like

"select this_.product_id as product1_207_1_, this_.VERSION as VERSION207_1_, this_.name as name207_1_, this_.small_box_quantity as small5_207_1_, this_.large_box_quantity as large6_207_1_, pr1_.product_id as product1_208_0_, pr1_.company_id as company2_208_0_, pr1_.VERSION as VERSION208_0_, pr1_.unit_price as unit4_208_0_, pr1_.company_specific_product_id as company5_208_0_, pr1_.abbreviation as abbrevia6_208_0_, pr1_.price_quantity as price7_208_0_ from product this_ inner join price pr1_ on this_.product_id=pr1_.product_id where this_.product_id = ? and pr1_.company_id=?"

HTH

TiMiD · **Joined:** Sun Sep 09, 2007 9:26 pm **Posts:** 8

Thank you for your help,

Running the code you posted on my setup gives me 2 requests :

Code:

select this_.id as id10_1_, this_.name as name10_1_, this_.small_box_quantity as small4_10_1_, this_.large_box_quantity as large5_10_1_, this_.data_type as data2_10_1_, pr1_.product_id as product1_9_0_, pr1_.company_id as company2_9_0_, pr1_.unit_price as unit3_9_0_, pr1_.company_specific_product_id as company4_9_0_, pr1_.abbreviation as abbrevia5_9_0_, pr1_.price_quantity as price6_9_0_ from product this_ inner join price pr1_ on this_.id=pr1_.product_id where this_.id = ? and pr1_.company_id=?

select prices0_.product_id as product1_1_, prices0_.company_id as company2_1_, prices0_.product_id as product1_9_0_, prices0_.company_id as company2_9_0_, prices0_.unit_price as unit3_9_0_, prices0_.company_specific_product_id as company4_9_0_, prices0_.abbreviation as abbrevia5_9_0_, prices0_.price_quantity as price6_9_0_ from price prices0_ where prices0_.product_id=?

So there may be a problem elsewhere.

By the way here is an hibernate.cfg.xml mapping file with which I can reproduce the problem (in case I did something wrong ...) :

Code:

<hibernate-configuration>
   <session-factory >
      <!-- local connection properties -->
      <property name="hibernate.connection.url">jdbc:postgresql:internet_order</property>
      <property name="hibernate.connection.driver_class">org.postgresql.Driver</property>
      <property name="hibernate.connection.username">postgres</property>
      <property name="hibernate.connection.password">secret</property>

      <property name="c3p0.min_size">3</property>
      <property name="c3p0.max_size">5</property>
      <property name="c3p0.timeout">1800</property>

      <!-- dialect for PostgreSQL -->
      <property name="dialect">org.hibernate.dialect.PostgreSQLDialect</property>
      <property name="hibernate.show_sql">true</property>
      <property name="hibernate.transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory</property>

      <property name="hibernate.cache.use_query_cache">true</property>
      <property name="hibernate.cache.provider_class">org.hibernate.cache.EhCacheProvider</property>

      <property name="hibernate.jdbc.batch_size">40</property>
      <property name="hibernate.max_fetch_depth">5</property> 

      <!-- Cache -->
      <property name="hibernate.cache.use_second_level_cache">true</property>
      <property name="hibernate.cache.use_query_cache">true</property>
      <property name="hibernate.cache.provider_class">net.sf.ehcache.hibernate.EhCacheProvider</property>
      <property name="net.sf.ehcache.configurationResourceName">/ehcache.xml</property>

      <!-- Mapping files -->
      <mapping resource="dto/Company.hbm.xml"/>
      <mapping resource="dto/Price.hbm.xml"/>
      <mapping resource="dto/Product.hbm.xml"/>
   </session-factory>
</hibernate-configuration>

sethrohit1977 · **Joined:** Mon Jan 22, 2007 10:32 am **Posts:** 101

Are you getting the desired results?

First sql do put an restriction on price.company_id. Second sql, I believe, is for fetching the price set associated with product.

TiMiD · **Joined:** Sun Sep 09, 2007 9:26 pm **Posts:** 8

Hmm no I get the same results as before (all prices loaded for one product regardless of the company)

By the way the second request doesn't specifie a condition on company_id

Also it would be better to fetch all the data with one single request (as it's done when using the HQL query) instead of n+1 requests ... that makes the performances very low

TomAtoms · **Joined:** Tue Sep 04, 2007 2:21 pm **Posts:** 14

I think you are close - did you try specifing the join type on the createAlias?

productCriteria.createAlias("prices","pr", CriteriaSpecification.INNER_JOIN)
.createAlias("company","c", CriteriaSpecification.INNER_JOIN)

HTH
Tom

TiMiD · **Joined:** Sun Sep 09, 2007 9:26 pm **Posts:** 8

I tried but that didn't changed anything unfortunately.
(btw I already specify the join type in the mapping)