Duplicating a seemingly simple query in HQL

bbonner · **Joined:** Fri Sep 12, 2003 12:40 pm **Posts:** 65

I'm trying to retrieve a set of Parents and their children using HQL.

In SQL, I would execute the following:

Code:

select * from selected_attribute_value a, attribute b where a.attribute_id = b.attribute_id and b.item_report_id = '402882c5f7d7367100f7d736b3900001'

Where attribute is the parent and selected_attribute_value is the child.

I'd like to return a list of attributes and the values for a given item_report.

I'm struggling with how to do this with HQL. When I use:

Code:

select attribute, sav from SelectedAttributeValue sav, Attribute attribute where sav.itemReport.id = '402882c5f7d7367100f7d736b3900001'

Two points of note. Selected_attribute_value is related to Item_report. Attribute is related to Commodity, Commodity to Subcommodity, Subcommodity to Item, and Item to Item Report.

I receive:

Code:

Hibernate: select attribut1_.attribute_id as x0_0_, selected0_.sav_id as x1_0_ from selected_attribute_value selected0_, attribute attribut1_ where (selected0_.item_report_id='402882c5f7d7367100f7d736b3900001' )
Hibernate: select commodit1_.commodity_id as commodity_id0_, commodit1_.com_code as com_code0_, commodit1_.com_desc as com_desc0_, commodit1_.dept_id as dept_id0_, departme2_.dept_id as dept_id1_, departme2_.dept_code as dept_code1_, departme2_.dept_desc as dept_desc1_, attribut0_.attribute_id as attribute_id2_, attribut0_.attribute_name as attribut2_2_, attribut0_.attribute_type as attribut3_2_, attribut0_.attribute_krog_spec as attribut4_2_, attribut0_.attribute_USDA_spec as attribut5_2_, attribut0_.commodity_id as commodit6_2_ from attribute attribut0_ left outer join commodity commodit1_ on attribut0_.commodity_id=commodit1_.commodity_id left outer join department departme2_ on commodit1_.dept_id=departme2_.dept_id where attribut0_.attribute_id=?
Hibernate: select attribut1_.attribute_id as attribute_id0_, attribut1_.attribute_name as attribut2_0_, attribut1_.attribute_type as attribut3_0_, attribut1_.attribute_krog_spec as attribut4_0_, attribut1_.attribute_USDA_spec as attribut5_0_, attribut1_.commodity_id as commodit6_0_, commodit2_.commodity_id as commodity_id1_, commodit2_.com_code as com_code1_, commodit2_.com_desc as com_desc1_, commodit2_.dept_id as dept_id1_, departme3_.dept_id as dept_id2_, departme3_.dept_code as dept_code2_, departme3_.dept_desc as dept_desc2_, itemrepo4_.item_report_id as item_report_id3_, itemrepo4_.available as available3_, itemrepo4_.last_updated as last_upd3_3_, itemrepo4_.peak_crop as peak_crop3_, itemrepo4_.weeks_to_promote_start as weeks_to5_3_, itemrepo4_.weeks_to_promote_end as weeks_to6_3_, itemrepo4_.risk as risk3_, itemrepo4_.urgent as urgent3_, itemrepo4_.item_id as item_id3_, item5_.item_id as item_id4_, item5_.item_upc as item_upc4_, item5_.item_name as item_name4_, item5_.item_size as item_size4_, item5_.item_pack as item_pack4_, item5_.top20 as top204_, item5_.pallet_ti as pallet_ti4_, item5_.pallet_hi as pallet_hi4_, item5_.cases_per_pallet as cases_pe9_4_, item5_.cluster_type as cluster10_4_, item5_.sub_commodity_id as sub_com11_4_, subcommo6_.sub_commodity_id as sub_commodity_id5_, subcommo6_.sub_com_code as sub_com_2_5_, subcommo6_.sub_com_desc as sub_com_3_5_, subcommo6_.commodity_id as commodit4_5_, fieldoff7_.field_office_id as field_office_id6_, fieldoff7_.field_office_name as field_of2_6_, fieldoff7_.field_office_code as field_of3_6_, costs8_.costs_id as costs_id7_, costs8_.curr_low_cost as curr_low2_7_, costs8_.curr_high_cost as curr_hig3_7_, costs8_.curr_avg_cost as curr_avg4_7_, costs8_.proj_low_cost as proj_low5_7_, costs8_.proj_high_cost as proj_hig6_7_, costs8_.proj_avg_cost as proj_avg7_7_, costs8_.trend as trend7_, costs8_.lastUpdated as lastUpda9_7_, comments9_.comments_id as comments_id8_, comments9_.comments as comments8_, selected0_.sav_id as sav_id9_, selected0_.selected_value as selected2_9_, selected0_.attribute_id as attribut3_9_, selected0_.item_report_id as item_rep4_9_ from selected_attribute_value selected0_ left outer join attribute attribut1_ on selected0_.attribute_id=attribut1_.attribute_id left outer join commodity commodit2_ on attribut1_.commodity_id=commodit2_.commodity_id left outer join department departme3_ on commodit2_.dept_id=departme3_.dept_id left outer join item_report itemrepo4_ on selected0_.item_report_id=itemrepo4_.item_report_id left outer join item item5_ on itemrepo4_.item_id=item5_.item_id left outer join sub_commodity subcommo6_ on item5_.sub_commodity_id=subcommo6_.sub_commodity_id left outer join field_office fieldoff7_ on itemrepo4_.item_report_id=fieldoff7_.field_office_id left outer join costs costs8_ on itemrepo4_.item_report_id=costs8_.costs_id left outer join comments comments9_ on itemrepo4_.item_report_id=comments9_.comments_id where selected0_.sav_id=?
Hibernate: select commodit1_.commodity_id as commodity_id0_, commodit1_.com_code as com_code0_, commodit1_.com_desc as com_desc0_, commodit1_.dept_id as dept_id0_, departme2_.dept_id as dept_id1_, departme2_.dept_code as dept_code1_, departme2_.dept_desc as dept_desc1_, attribut0_.attribute_id as attribute_id2_, attribut0_.attribute_name as attribut2_2_, attribut0_.attribute_type as attribut3_2_, attribut0_.attribute_krog_spec as attribut4_2_, attribut0_.attribute_USDA_spec as attribut5_2_, attribut0_.commodity_id as commodit6_2_ from attribute attribut0_ left outer join commodity commodit1_ on attribut0_.commodity_id=commodit1_.commodity_id left outer join department departme2_ on commodit1_.dept_id=departme2_.dept_id where attribut0_.attribute_id=?
Grapefruit Color: 50% Pink
Hibernate: select commodit1_.commodity_id as commodity_id0_, commodit1_.com_code as com_code0_, commodit1_.com_desc as com_desc0_, commodit1_.dept_id as dept_id0_, departme2_.dept_id as dept_id1_, departme2_.dept_code as dept_code1_, departme2_.dept_desc as dept_desc1_, attribut0_.attribute_id as attribute_id2_, attribut0_.attribute_name as attribut2_2_, attribut0_.attribute_type as attribut3_2_, attribut0_.attribute_krog_spec as attribut4_2_, attribut0_.attribute_USDA_spec as attribut5_2_, attribut0_.commodity_id as commodit6_2_ from attribute attribut0_ left outer join commodity commodit1_ on attribut0_.commodity_id=commodit1_.commodity_id left outer join department departme2_ on commodit1_.dept_id=departme2_.dept_id where attribut0_.attribute_id=?
Orange Color: 50% Pink
Hibernate: select commodit1_.commodity_id as commodity_id0_, commodit1_.com_code as com_code0_, commodit1_.com_desc as com_desc0_, commodit1_.dept_id as dept_id0_, departme2_.dept_id as dept_id1_, departme2_.dept_code as dept_code1_, departme2_.dept_desc as dept_desc1_, attribut0_.attribute_id as attribute_id2_, attribut0_.attribute_name as attribut2_2_, attribut0_.attribute_type as attribut3_2_, attribut0_.attribute_krog_spec as attribut4_2_, attribut0_.attribute_USDA_spec as attribut5_2_, attribut0_.commodity_id as commodit6_2_ from attribute attribut0_ left outer join commodity commodit1_ on attribut0_.commodity_id=commodit1_.commodity_id left outer join department departme2_ on commodit1_.dept_id=departme2_.dept_id where attribut0_.attribute_id=?
Grapefruit Texture: 50% Pink
Hibernate: select attribut1_.attribute_id as attribute_id0_, attribut1_.attribute_name as attribut2_0_, attribut1_.attribute_type as attribut3_0_, attribut1_.attribute_krog_spec as attribut4_0_, attribut1_.attribute_USDA_spec as attribut5_0_, attribut1_.commodity_id as commodit6_0_, commodit2_.commodity_id as commodity_id1_, commodit2_.com_code as com_code1_, commodit2_.com_desc as com_desc1_, commodit2_.dept_id as dept_id1_, departme3_.dept_id as dept_id2_, departme3_.dept_code as dept_code2_, departme3_.dept_desc as dept_desc2_, itemrepo4_.item_report_id as item_report_id3_, itemrepo4_.available as available3_, itemrepo4_.last_updated as last_upd3_3_, itemrepo4_.peak_crop as peak_crop3_, itemrepo4_.weeks_to_promote_start as weeks_to5_3_, itemrepo4_.weeks_to_promote_end as weeks_to6_3_, itemrepo4_.risk as risk3_, itemrepo4_.urgent as urgent3_, itemrepo4_.item_id as item_id3_, item5_.item_id as item_id4_, item5_.item_upc as item_upc4_, item5_.item_name as item_name4_, item5_.item_size as item_size4_, item5_.item_pack as item_pack4_, item5_.top20 as top204_, item5_.pallet_ti as pallet_ti4_, item5_.pallet_hi as pallet_hi4_, item5_.cases_per_pallet as cases_pe9_4_, item5_.cluster_type as cluster10_4_, item5_.sub_commodity_id as sub_com11_4_, subcommo6_.sub_commodity_id as sub_commodity_id5_, subcommo6_.sub_com_code as sub_com_2_5_, subcommo6_.sub_com_desc as sub_com_3_5_, subcommo6_.commodity_id as commodit4_5_, fieldoff7_.field_office_id as field_office_id6_, fieldoff7_.field_office_name as field_of2_6_, fieldoff7_.field_office_code as field_of3_6_, costs8_.costs_id as costs_id7_, costs8_.curr_low_cost as curr_low2_7_, costs8_.curr_high_cost as curr_hig3_7_, costs8_.curr_avg_cost as curr_avg4_7_, costs8_.proj_low_cost as proj_low5_7_, costs8_.proj_high_cost as proj_hig6_7_, costs8_.proj_avg_cost as proj_avg7_7_, costs8_.trend as trend7_, costs8_.lastUpdated as lastUpda9_7_, comments9_.comments_id as comments_id8_, comments9_.comments as comments8_, selected0_.sav_id as sav_id9_, selected0_.selected_value as selected2_9_, selected0_.attribute_id as attribut3_9_, selected0_.item_report_id as item_rep4_9_ from selected_attribute_value selected0_ left outer join attribute attribut1_ on selected0_.attribute_id=attribut1_.attribute_id left outer join commodity commodit2_ on attribut1_.commodity_id=commodit2_.commodity_id left outer join department departme3_ on commodit2_.dept_id=departme3_.dept_id left outer join item_report itemrepo4_ on selected0_.item_report_id=itemrepo4_.item_report_id left outer join item item5_ on itemrepo4_.item_id=item5_.item_id left outer join sub_commodity subcommo6_ on item5_.sub_commodity_id=subcommo6_.sub_commodity_id left outer join field_office fieldoff7_ on itemrepo4_.item_report_id=fieldoff7_.field_office_id left outer join costs costs8_ on itemrepo4_.item_report_id=costs8_.costs_id left outer join comments comments9_ on itemrepo4_.item_report_id=comments9_.comments_id where selected0_.sav_id=?
Orange Texture: 50% Pink
Grapefruit Color: Soft, Wet and Leaking
Orange Color: Soft, Wet and Leaking
Grapefruit Texture: Soft, Wet and Leaking
Hibernate: select attribut1_.attribute_id as attribute_id0_, attribut1_.attribute_name as attribut2_0_, attribut1_.attribute_type as attribut3_0_, attribut1_.attribute_krog_spec as attribut4_0_, attribut1_.attribute_USDA_spec as attribut5_0_, attribut1_.commodity_id as commodit6_0_, commodit2_.commodity_id as commodity_id1_, commodit2_.com_code as com_code1_, commodit2_.com_desc as com_desc1_, commodit2_.dept_id as dept_id1_, departme3_.dept_id as dept_id2_, departme3_.dept_code as dept_code2_, departme3_.dept_desc as dept_desc2_, itemrepo4_.item_report_id as item_report_id3_, itemrepo4_.available as available3_, itemrepo4_.last_updated as last_upd3_3_, itemrepo4_.peak_crop as peak_crop3_, itemrepo4_.weeks_to_promote_start as weeks_to5_3_, itemrepo4_.weeks_to_promote_end as weeks_to6_3_, itemrepo4_.risk as risk3_, itemrepo4_.urgent as urgent3_, itemrepo4_.item_id as item_id3_, item5_.item_id as item_id4_, item5_.item_upc as item_upc4_, item5_.item_name as item_name4_, item5_.item_size as item_size4_, item5_.item_pack as item_pack4_, item5_.top20 as top204_, item5_.pallet_ti as pallet_ti4_, item5_.pallet_hi as pallet_hi4_, item5_.cases_per_pallet as cases_pe9_4_, item5_.cluster_type as cluster10_4_, item5_.sub_commodity_id as sub_com11_4_, subcommo6_.sub_commodity_id as sub_commodity_id5_, subcommo6_.sub_com_code as sub_com_2_5_, subcommo6_.sub_com_desc as sub_com_3_5_, subcommo6_.commodity_id as commodit4_5_, fieldoff7_.field_office_id as field_office_id6_, fieldoff7_.field_office_name as field_of2_6_, fieldoff7_.field_office_code as field_of3_6_, costs8_.costs_id as costs_id7_, costs8_.curr_low_cost as curr_low2_7_, costs8_.curr_high_cost as curr_hig3_7_, costs8_.curr_avg_cost as curr_avg4_7_, costs8_.proj_low_cost as proj_low5_7_, costs8_.proj_high_cost as proj_hig6_7_, costs8_.proj_avg_cost as proj_avg7_7_, costs8_.trend as trend7_, costs8_.lastUpdated as lastUpda9_7_, comments9_.comments_id as comments_id8_, comments9_.comments as comments8_, selected0_.sav_id as sav_id9_, selected0_.selected_value as selected2_9_, selected0_.attribute_id as attribut3_9_, selected0_.item_report_id as item_rep4_9_ from selected_attribute_value selected0_ left outer join attribute attribut1_ on selected0_.attribute_id=attribut1_.attribute_id left outer join commodity commodit2_ on attribut1_.commodity_id=commodit2_.commodity_id left outer join department departme3_ on commodit2_.dept_id=departme3_.dept_id left outer join item_report itemrepo4_ on selected0_.item_report_id=itemrepo4_.item_report_id left outer join item item5_ on itemrepo4_.item_id=item5_.item_id left outer join sub_commodity subcommo6_ on item5_.sub_commodity_id=subcommo6_.sub_commodity_id left outer join field_office fieldoff7_ on itemrepo4_.item_report_id=fieldoff7_.field_office_id left outer join costs costs8_ on itemrepo4_.item_report_id=costs8_.costs_id left outer join comments comments9_ on itemrepo4_.item_report_id=comments9_.comments_id where selected0_.sav_id=?
Orange Texture: Soft, Wet and Leaking
Grapefruit Color: Sheepnose
Orange Color: Sheepnose
Grapefruit Texture: Sheepnose
Orange Texture: Sheepnose

Can someone help me with what is going on here?

Here's the mapping files:

<hibernate-mapping>

<class name="eg.Attribute" table="attribute">

<id name="id" type="string" unsaved-value="null">
<column name="attribute_id" length="32" not-null="true"/>
<generator class="uuid.hex"/>
</id>

<property name="attributeName" column="attribute_name" type="string" length="50"/>
<property name="attributeType" column="attribute_type" type="eg.AttributeType"/>
<property name="attributeSpec" column="attribute_krog_spec" type="string" length="40"/>

<set name="attributeChoices" table="attribute_choice"
cascade="all" inverse="true" lazy="true">
<key column="attribute_id"/>
<one-to-many class="eg.AttributeChoice"/>
</set>
<many-to-one name="commodity" class="eg.Commodity" column="commodity_id" />
</class>

</hibernate-mapping>

<hibernate-mapping>

<class name="eg.SelectedAttributeValue" table="selected_attribute_value">

<id name="id" type="string" unsaved-value="null">
<column name="sav_id" length="32" not-null="true"/>
<generator class="uuid.hex"/>
</id>

<property name="selectedValue" column="selected_value" type="string" length="30"/>

<many-to-one name="attribute" class="eg.Attribute" column="attribute_id" />
</class>

</hibernate-mapping>

gavin · **Posted:** Fri Oct 03, 2003 4:14 pm

LOL! That is one of The Biggest SQL statements I have ever seen!

heh heh

anyway, there are two things you need to know:

(1) use find() instead of iterate()
(2) use proxies!

bbonner · **Joined:** Fri Sep 12, 2003 12:40 pm **Posts:** 65

Yes, it's about to blow out the sql buffer ;)

I'll take a look at find(). I was using a Query with iterate().

I'm using lazy initialization with Hibernate's proxies. Did I read the docs incorrectly? I thought they would only help with 1:M relationships?

I noticed that Region didn't show up. There is a 1:M between ItemReport and Region.

Attribute had a M:1 with Commodity. Commodity has a M:1 with Dept.
ItemReport has a 1:1 with Costs, Comments and a M:1 with Item.
Item has a M:1 with SubCommodity.

Thanks for the quick response.

bbonner · **Joined:** Fri Sep 12, 2003 12:40 pm **Posts:** 65

The queries have been minimized. I still used the query, but used list() on Query instead of iterate().

I'll look into the proxies.