Hibernate version: 2.1.1

Name and version of the database you are using: MS SQL 2000


The problem is about how to use the outer-join properties to increase the performance of the problem by making less sql to the database. However, I have posted this topic already few times but still couldn't find the answer. I describe my problem more clearly here and hope someone could help.

In my program, it has a Product table and the product table has a one-to-many relationship with productPacking.

I have added the following line in the hibernate.properties

hibernate.max_fetch_depth=2

The xml file:

<bag
name="productPackagings"
lazy="true"
inverse="true"
cascade="delete"
outer-join="true"
>
<key>
<column name="product_id" />
</key>
<one-to-many
class="erp.dataaccesslayer.hibernate.dataobjects.ProductPackaging"
/>
</bag>


HQL and the code to test the left join fetch

String hql = "from Product as product left join fetch product.productPackagings left join fetch product.productColors ";
Session session = HibernateUtil.currentSession();
Query query = session.createQuery(hql);
query.setFirstResult(start);
query.setMaxResults(ErpSetting.getInstence().getQueryMaxResults());
records = query.list();

for (Object obj: records) {
Product record = (Product) obj;
((ProductPackaging)(record.getProductPackagings().get(0))).getId();
System.out.println("Still generated SQL!");
}



The following output from hibernate show that every in the for loop it makes a sql to the database to retrieve data from ProductPackaging table.

The generated SQL (show_sql=true):

Hibernate: select top 20 product0_.id as id0_, productpac1_.id as id1_, productcol2_.id as id2_, product0_.name as name0_, product0_.english_name as english_3_0_, product0_.description as descript4_0_, product0_.cost as cost0_, product0_.price as price0_, product0_.image_url as image_url0_, product0_.local_memo as local_memo0_, product0_.public_memo as public_m9_0_, product0_.type as type0_, productpac1_.inner_box as inner_box1_, productpac1_.outer_box as outer_box1_, productpac1_.inner_length as inner_le4_1_, productpac1_.inner_width as inner_wi5_1_, productpac1_.inner_height as inner_he6_1_, productpac1_.outer_length as outer_le7_1_, productpac1_.outer_width as outer_wi8_1_, productpac1_.outer_height as outer_he9_1_, productpac1_.product_size as product10_1_, productpac1_.raw_weight as raw_weight1_, productpac1_.weight as weight1_, productpac1_.has_bag as has_bag1_, productpac1_.packing_id as packing_id1_, productpac1_.pack_cost as pack_cost1_, productpac1_.memo as memo1_, productpac1_.product_id as product_id1_, productcol2_.color_id as color_id2_, productcol2_.color_name as color_name2_, productcol2_.memo as memo2_, productcol2_.product_id as product_id2_, productcol2_.product_id as product_id__, productcol2_.id as id__ from Products product0_ left outer join ProductPackaging productpac1_ on product0_.id=productpac1_.product_id left outer join ProductColor productcol2_ on product0_.id=productcol2_.product_id
Hibernate: select productpac0_.product_id as product_id__, productpac0_.id as id__, productpac0_.id as id0_, productpac0_.inner_box as inner_box0_, productpac0_.outer_box as outer_box0_, productpac0_.inner_length as inner_le4_0_, productpac0_.inner_width as inner_wi5_0_, productpac0_.inner_height as inner_he6_0_, productpac0_.outer_length as outer_le7_0_, productpac0_.outer_width as outer_wi8_0_, productpac0_.outer_height as outer_he9_0_, productpac0_.product_size as product10_0_, productpac0_.raw_weight as raw_weight0_, productpac0_.weight as weight0_, productpac0_.has_bag as has_bag0_, productpac0_.packing_id as packing_id0_, productpac0_.pack_cost as pack_cost0_, productpac0_.memo as memo0_, productpac0_.product_id as product_id0_ from ProductPackaging productpac0_ where productpac0_.product_id=?
Still generated SQL!
Hibernate: select productpac0_.product_id as product_id__, productpac0_.id as id__, productpac0_.id as id0_, productpac0_.inner_box as inner_box0_, productpac0_.outer_box as outer_box0_, productpac0_.inner_length as inner_le4_0_, productpac0_.inner_width as inner_wi5_0_, productpac0_.inner_height as inner_he6_0_, productpac0_.outer_length as outer_le7_0_, productpac0_.outer_width as outer_wi8_0_, productpac0_.outer_height as outer_he9_0_, productpac0_.product_size as product10_0_, productpac0_.raw_weight as raw_weight0_, productpac0_.weight as weight0_, productpac0_.has_bag as has_bag0_, productpac0_.packing_id as packing_id0_, productpac0_.pack_cost as pack_cost0_, productpac0_.memo as memo0_, productpac0_.product_id as product_id0_ from ProductPackaging productpac0_ where productpac0_.product_id=?
Still generated SQL!


Please help!!!


Kind regards
_________________
Edmond Hung
Credit Card DNA Security System (Holdings) Ltd.

Why do you use this HQL ? :


Why don't you just use


And then set lazy="false" on the bag, so the complete collection of ProductPackings for each Product is directly fetched.

HQL queries ignore the outer-join attribute (the outer-join attribute in the mapping file is relevant for criteria query only)

But this is not the problem. It seems that you are doing the right thing in the hql statement: you have two yoins, you use left join in the query and you set the hibernate.max_fetch_depth=2

But looking more closely you can see that you are not doing a join with depht 2, instead you are doing two joins with depth 1 (i.e. both associations you are start from the product)

I remember to have read that this is a limitation of hibernate 2: can do only one join at a time (although this one may be chained to a depth of max_fetch_depth)

Multiple joins should work fine with hibernate 3.

I fear that the only thing you can do to get a single sql statement is to upgrade to hibernate 3.


lnz

ps: for a quick fix you might think about using native sql ...

Found the reference:

from the book Hibernate in Action, Manning (1. Edition, hibernate 2.x)

'Hibernate currently limits you to fetching just one collection eagerly . This is a reasonable restriction, since fetching more than one collection in an single query would be a Cartesion product result. This restriction might be relaxed in a future version of Hibernate.'

I think this applies to hql as well as to criteria query

lnz

just an idea:

you could rewrite the hql, starting from the color joining to the product and than joining to the packages (use a select to get the products).
(use a theta style join if the association between color and product is not bidirectional)

This would be a chain of joins
but still you are fetching two collections from the same class (product).

I fear the above mentioned restrictions will apply in this case as well
but maybe it is worth a try


lnz