These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 4 posts ] 
  Previous topic | Next topic 
Author Message
cchalmer
 Post subject: Eager loading of collections.
PostPosted: Mon Dec 08, 2008 1:35 pm 
Newbie

Joined: Fri Dec 05, 2008 3:36 pm
Posts: 3
I have a one to many association (line item ==> discounts) that I am trying to eagerly fetch in a single select statement. The documenation states that by enabling "fetch=join" you can eagerly fetch all of the line items and their associated discounts in a single select statement. Enabling "fetch=join" in the mapping metadata does not force a single select statement. All of the line items are fetched in a single select statement and then individual selects are performed to retrieve the discounts for a given line item. Since there are six discounts for every line item the number of select statements executed results in a performance hit.

I even added an index column to the discounts table, changed the mapping definition from a <bag> to a <list> and Hibernate still did not generate a single select statement.

Can someone tell me how to get this to work without changing the query with an explicit outer join on these tables.

Here is the mapping document for the line item. The highlighted text is lineitem discount definition.

Code:
<hibernate-mapping package="com.sybase.it.quoting.beans" default-cascade="none" default-lazy="true" auto-import="true">

    <class name="com.sybase.it.quoting.beans.QuoteLineitem" table="quote_lineitem" dynamic-update="true" dynamic-insert="true" select-before-update="true" optimistic-lock="version">
        <id name="lineitemId" column="lineitem_id" type="java.lang.Integer">
            <generator class="native"/>
        </id>
        <version name="version" column="version" type="java.lang.Short"/>

        <property name="listIndex" column="list_index" type="java.lang.Integer"  not-null="true" />
        <property name="catalogNo" column="catalog_no" type="java.lang.String"  not-null="true" />
        <property name="licenseType" column="license_type" type="java.lang.String"  not-null="true" />
        <property name="cpuClass" column="cpu_class" type="java.lang.String" />
        <property name="numUsers" column="num_users" type="java.lang.Integer" />
        <property name="productVersion" column="product_version" type="java.lang.String"  not-null="true" />
        <property name="productRevision" column="product_revision" type="java.lang.String"  not-null="true" />
        <property name="productMedia" column="product_media" type="java.lang.String"  not-null="true" />
        <property name="productDesc" column="product_desc" type="java.lang.String" />
        <property name="platformDesc" column="platform_desc" type="java.lang.String" />
        <property name="osDesc" column="os_desc" type="java.lang.String" />
        <property name="itemTypeCode" column="item_type_code" type="java.lang.String" />
        <property name="discTypeCode" column="disc_type_code" type="java.lang.String" />
        <property name="quantityLicense" column="quantity_license" type="java.lang.Integer"  not-null="true" />
        <property name="quantityShip" column="quantity_ship" type="java.lang.Integer"  not-null="true" />
        <property name="quantitySupport" column="quantity_support" type="java.lang.Integer"  not-null="true" />
        <property name="pricePerLicense" column="price_per_license" type="java.lang.Double"  not-null="true" />
        <property name="discountedLicenseCost" column="discounted_license_cost" type="java.lang.Double"  not-null="true" />
        <property name="pricePerSupport" column="price_per_support" type="java.lang.Double"  not-null="true" />
        <property name="discountedSupportCost" column="discounted_support_cost" type="java.lang.Double"  not-null="true" />
        <property name="usPricePerLicense" column="us_price_per_license" type="java.lang.Double"  not-null="true" />
        <property name="usPricePerSupport" column="us_price_per_support" type="java.lang.Double"  not-null="true" />
        <property name="licenseDiscountId" column="license_discount_id" type="java.lang.Short" />
        <property name="behavior" column="behavior" type="java.lang.String" />
        <property name="percentOfNet" column="percent_of_net" type="java.lang.Float" />
        <property name="priceCode" column="price_code" type="java.lang.String" />
        <property name="partnerPlsd" column="partner_plsd" type="java.lang.Boolean"  not-null="true" />
        <property name="royalty" column="royalty" type="java.lang.Boolean"  not-null="true" />
        <property name="modBy" column="mod_by" type="java.lang.String"  not-null="true" />
        <property name="modDate" column="mod_date" type="java.util.Date"  not-null="true" />
        <property name="licenseKeyType" column="license_key_type" type="java.lang.String" />
        <property name="serialNo" column="serial_no" type="java.lang.Integer" />
        <property name="serialType" column="serial_type" type="java.lang.String" />
        <property name="enduserName" column="enduser_name" type="java.lang.String" />
        <property name="supportBeginDate" column="support_begin_date" type="java.util.Date" />
        <property name="supportEndDate" column="support_end_date" type="java.util.Date" />

        <many-to-one name="quote" column="quote_id" class="com.sybase.it.quoting.beans.Quote"  not-null="true" />
       
        [b]<bag name="lineItemDiscounts" inverse="true" cascade="all,delete-orphan" lazy="false" fetch="join">
            <key column="lineitem_id"/>
            <one-to-many class="com.sybase.it.quoting.beans.LineitemDiscount"/>
        </bag>[/b]       
    </class>
   
</hibernate-mapping> 



And here is the line item discount mapping metadata.

Code:
<hibernate-mapping package="com.sybase.it.quoting.beans">

    <class name="com.sybase.it.quoting.beans.LineitemDiscount" table="lineitem_discount">
        <id name="lineitemDiscountId" column="lineitem_discount_id" type="java.lang.Integer">
            <generator class="native"/>
        </id>
         <version name="version" column="version" type="java.lang.Short"/>

        <property name="defaultPercent" column="default_percent" type="java.lang.Float"  not-null="true" />
        <property name="actualPercent" column="actual_percent" type="java.lang.Float"  not-null="true" />
        <property name="comment" column="comment" type="java.lang.String" />
        <property name="modBy" column="mod_by" type="java.lang.String"  not-null="true" />
        <property name="modDate" column="mod_date" type="java.util.Date"  not-null="true" />

        [b]<many-to-one name="quoteLineitem" column="lineitem_id" class="com.sybase.it.quoting.beans.QuoteLineitem"  not-null="true" fetch="join" />[/b]
        <many-to-one name="discountType" column="discount_type_id" class="com.sybase.it.quoting.beans.DiscountType"  not-null="true" />
    </class>
   
</hibernate-mapping>
[/quote]
Top
 Profile  
 
nordborg
 Post subject:
PostPosted: Mon Dec 08, 2008 3:25 pm 
Expert
Expert

Joined: Wed Mar 03, 2004 6:35 am
Posts: 1240
Location: Lund, Sweden
How are you loading the QuoteLineitem:s in the first place? What you specify in the 'fetch' attribute doesn't apply to all types of queries. See http://www.hibernate.org/hib_docs/v3/re ... ing-custom for more information.

For example, if you use HQL you need to specify JOIN FETCH if you want that.
Top
 Profile  
 
cchalmer
 Post subject:
PostPosted: Tue Dec 09, 2008 1:14 pm 
Newbie

Joined: Fri Dec 05, 2008 3:36 pm
Posts: 3
We are using the following HQL:
Code:
public Quote getSingleQuoteSummary(int quoteId) {

      Query q = null;
      Quote quote;
      try {
         q = getSession().createQuery(
               " select q from Quote q" + " join fetch q.quoteStatus"
                     + " left join fetch q.customer c"
                     + " left join fetch q.licensee l"
                     + " left join fetch q.payment"
                     + " left join fetch c.shipDestination"
                     + " left join fetch c.billTo"
                     + " where q.id = :quote_id");

         q.setInteger("quote_id", quoteId);
         quote = (Quote) q.uniqueResult();

      } catch (HibernateException ex) {
         throw new DAOException(
               ExceptionCodes.USERQUOTE_CANNOT_GET_USERQUOTE, ex
                     .getMessage());
      }

      return assignObservers(quote);
   }



And here is the mapping metadata for the Quote object. As you can see it has a list association for line items which are lazy loaded.

Code:
<hibernate-mapping package="com.sybase.it.quoting.beans" default-cascade="none" default-lazy="true" auto-import="true">

    <class name="com.sybase.it.quoting.beans.Quote" table="quote" dynamic-update="true" dynamic-insert="true" select-before-update="true" optimistic-lock="version">
        <id name="quoteId" column="quote_id" type="java.lang.Integer">
            <generator class="native"/>
        </id>
        <version name="version" column="version" type="java.lang.Short"/>

        <property name="quoteName" column="quote_name" type="java.lang.String"  not-null="true" />
        <property name="licenseModel" column="license_model" type="java.lang.Short" />
        <property name="supportProgram" column="support_program" type="java.lang.String" />
          <property name="supportCatalog" column="support_catalog" type="java.lang.String" />       
        <property name="usageId" column="usage_id" type="java.lang.Integer" />       
        <property name="quoteDate" column="quote_date" type="java.util.Date"  not-null="true" />
        <property name="origAgreementDate" column="orig_agreement_date" type="java.util.Date" />
        <property name="taxable" column="taxable" type="java.lang.Boolean"  not-null="true" />
        <property name="tin" column="tin" type="java.lang.String" />
        <property name="shipCarrier" column="ship_carrier" type="java.lang.String" />
        <property name="customerShipCarrier" column="customer_ship_carrier" type="java.lang.String" />
        <property name="shipAccountNumber" column="ship_account_number" type="java.lang.String" />       
        <property name="publicVis" column="public_vis" type="java.lang.Boolean"  not-null="true" />
        <property name="orderNumber" column="order_number" type="java.lang.String" />
        <property name="currencyCode" column="currency_code" type="java.lang.String"  not-null="true" />
        <property name="totalLicenseCost" column="total_license_cost" type="java.lang.Double" />
        <property name="discountedLicenseCost" column="discounted_license_cost" type="java.lang.Double" />
        <property name="totalSupportCost" column="total_support_cost" type="java.lang.Double" />
        <property name="discountedSupportCost" column="discounted_support_cost" type="java.lang.Double" />
        <property name="numLineItems" column="num_line_items" type="java.lang.Integer" />
        <property name="numLicenses" column="num_licenses" type="java.lang.Integer" />
        <property name="mgmtApproval" column="mgmt_approval" type="java.lang.Boolean"  not-null="true" />
        <property name="opsCenterCode" column="ops_center_code" type="java.lang.String" />
        <property name="fobCode" column="fob_code" type="java.lang.String" />
        <property name="dropShip" column="drop_ship" type="java.lang.Boolean"  not-null="true" />
        <property name="endUserKnown" column="end_user_known" type="java.lang.Boolean"  not-null="true" />
          <property name="comment" column="comment" type="java.lang.String" />
        <property name="modBy" column="mod_by" type="java.lang.String"  not-null="true" />
        <property name="modDate" column="mod_date" type="java.util.Date"  not-null="true" />
        <property name="ownerLogin" column="owner_login" type="java.lang.String"  not-null="true" />
        <property name="lastOwnerLogin" column="last_owner_login" type="java.lang.String" />
        <property name="expirationDate" column="expiration_date" type="java.util.Date" />
        <property name="orderNotes" column="order_notes" type="java.lang.String" />
        <property name="contractBeginDate" column="contract_begin_date" type="java.util.Date" />
        <property name="contractEndDate" column="contract_end_date" type="java.util.Date" />
        <property name="defaultSupportBeginDate" column="default_support_begin_date" type="java.util.Date" />
        <property name="defaultSupportEndDate" column="default_support_end_date" type="java.util.Date" />
               
        <many-to-one name="customer" column="customer_id" class="com.sybase.it.quoting.beans.Customer"/>

        <many-to-one name="licensee" column="licensee" class="com.sybase.it.quoting.beans.Customer" outer-join="true"/>

        <many-to-one name="quoteStatus" column="quote_status_id" class="com.sybase.it.quoting.beans.QuoteStatus" />
       
        <many-to-one name="payment" column="payment_id" class="com.sybase.it.quoting.beans.Payment"/>

        <list name="lineItems" inverse="true" cascade="all-delete-orphan" lazy="true">
            <key column="quote_id"/>
            <index column="list_index"/>
            <one-to-many class="com.sybase.it.quoting.beans.QuoteLineitem"/>
        </list>

        <list name="salesReps" inverse="true" cascade="all" lazy="true">
            <key column="quote_id"/>
            <index column="list_index"/>
            <one-to-many class="com.sybase.it.quoting.beans.SalesRep"/>
        </list>

        <list name="licenseDiscountThresholds" inverse="true" cascade="all" lazy="true">
            <key column="quote_id"/>
            <index column="list_index"/>
            <one-to-many class="com.sybase.it.quoting.beans.LicenseDiscountThreshold"/>
        </list>
       
        <bag name="quoteMessages" inverse="true" cascade="all" lazy="true">
            <key column="quote_id"/>
            <one-to-many class="com.sybase.it.quoting.beans.QuoteMessage"/>
        </bag>
               
        <list name="jmsMessages" inverse="true" cascade="all" lazy="true">
            <key column="quote_id"/>
            <index column="list_index"/>
            <one-to-many class="com.sybase.it.quoting.beans.JmsMessageTracker"/>
        </list>

    </class>
   
</hibernate-mapping>


We were thinking that by setting "fetch=join" in the lineItemDiscounts property of the QuoteLineItem mapping metadata (attached previously) that when the lineItems were retrieved with a getter that all associated line item discounts would be retreieved together in a single select statement.

Is it supposed to work this way OR is an explicit join fetch required in the HQL?


Code:
<bag name="lineItemDiscounts" inverse="true" cascade="all,delete-orphan" lazy="false" fetch="join">
            <key column="lineitem_id"/>
            <one-to-many class="com.sybase.it.quoting.beans.LineitemDiscount"/>
        </bag>
Top
 Profile  
 
nordborg
 Post subject:
PostPosted: Tue Dec 09, 2008 1:36 pm 
Expert
Expert

Joined: Wed Mar 03, 2004 6:35 am
Posts: 1240
Location: Lund, Sweden
Quote:
Is it supposed to work this way OR is an explicit join fetch required in the HQL?


With HQL you need an explicit fetch join. The fetch="join" attribute is not used with HQL queries. It is pretty clear if you read the documentation http://www.hibernate.org/hib_docs/v3/re ... ing-custom
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 4 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.