Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version: 2.1.1

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


I have posted a topic "Avoid making more sql for one-to-many relationship"

http://forum.hibernate.org/viewtopic.ph ... cf2fae3963


I have tried to use outer-fetch but it is still generating one sql for each one-to-many relationship. I am wondering if I haven't used the outer-fetch properties correctly. I am MS-SQL 2000.

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>


I have tried to put the outer-join="true" in the <one-to-many> like the following:


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


But I have exception when the Hibernate initialises telling there is no such properties defined.



Thanks a lot!


Best regards

Use HQL queries to load the domain objects that you require in the manor that you requirement, eg, 'left join fetch'.

First of all, thank you!


But I am still not getting what I want, I believe I am still missing something.

hql queries I made:

String hql = "from Product as product left join fetch ";

And I have already added outer-join="true" in the xml file. Could you please give me an example?


Thanks again!.

The XML mappings are not complete enough but anyway something like:



Hope this helps.

Hi all

I am still not getting what i need and I am very fraustrated about it. I have attached the source code, the xml file and the console output. Please help!


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!");
}


XML of the Product

<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>


Show SQL = true and the output of the console

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!
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!
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!
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!
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!
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!
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!
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!