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