• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 305
  • Last Modified:

one hibernate select generating a lot of sql statements

I am running a following statement in hibernate:
somelist = session.find("from Rfq rfq order by rfq.po.poDate asc where rfq.po.buyerCode='GAK'");

Instead of generating one line for this select I am getting a few dozen of lines of sql from hibernate:

The first one makes sense:

Hibernate: select rfq0_.RFQ_ID as RFQ_ID, rfq0_.ISSUED_BY as ISSUED_BY, rfq0_.RELEASE_DATE as RELEASE_3_, rfq0_.BID_CLOSE_DATE as BID_CLOS4_, rfq0_.TAX_INFORMATION as TAX_INFO5_, rfq0_.FOB as FOB, rfq0_.SPECIAL_INSTRUCTIONS as SPECIAL_7_, rfq0_.RFQ_CONSTANT as RFQ_CONS8_, rfq0_.RFQ_BUYER_CODE as RFQ_BUYE9_, rfq0_.RFQ_BLUES_CODE as RFQ_BLU10_, rfq0_.RFQ_SEQ_REVISION_NUMBER as RFQ_SEQ11_, rfq0_.RFQ_STATUS as RFQ_STATUS, rfq0_.PO_ID as PO_ID, rfq0_.INCREMENTAL_PRICE_QUOTE as INCREME14_ from RFQ_TABLE rfq0_, PO_LIST po1_ where rfq0_.PO_ID=po1_.PO_ID and po1_.SOURCE != 'WEBPO - IDS' AND po1_.active = 1 and ((po1_.BUYER_CODE='GAK'  and rfq0_.PO_ID=po1_.PO_ID)) order by  po1_.PO_DATE asc

And then a few dozen times of:

Hibernate: select po0_.PO_ID as PO_ID0_, po0_.PARENT_PO_ID as PARENT_P2_0_, po0_.PO_NUMBER as PO_NUMBER0_, po0_.CHANGE_ORDER_SEQ as CHANGE_O4_0_, po0_.SOURCE as SOURCE0_, po0_.PO_DATE as PO_DATE0_, po0_.LOAD_DATE as LOAD_DATE0_, po0_.FILENAME as FILENAME0_, po0_.LOAD_INFO as LOAD_INFO0_, po0_.CREATOR as CREATOR0_, po0_.SUPPLIER_NAME as SUPPLIE11_0_, po0_.SUPPLIER_NUMBER as SUPPLIE12_0_, po0_.BUYER_CODE as BUYER_CODE0_, po0_.BUYER_DEPT as BUYER_DEPT0_, po0_.CONTRACT as CONTRACT0_, po0_.ACTIVE as ACTIVE0_, po0_.PO_TYPE_ID as PO_TYPE_ID0_ from PO_LIST po0_ where po0_.PO_ID=?

Here are the relevant mapping files:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
                            "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
                            "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd" >

<!-- DO NOT EDIT: This is a generated file that is synchronized -->
<!-- by MyEclipse Hibernate tool integration.                   -->
<!-- Created Mon Mar 21 14:36:39 CST 2005                         -->
<hibernate-mapping package="com.talisen.lbwebpo.model">

    <class name="Po" table="PO_LIST" where="SOURCE != 'WEBPO - IDS' AND active = 1">
        <id name="poId" column="PO_ID" type="integer">
            <generator class="native"/>
        </id>
 
        <property name="parentPoId" column="PARENT_PO_ID" type="integer" />
        <property name="poNumber" column="PO_NUMBER" type="string"  not-null="true" />
        <property name="changeOrderSeq" column="CHANGE_ORDER_SEQ" type="string" />
        <property name="source" column="SOURCE" type="string"  not-null="true" />
        <property name="poDate" column="PO_DATE" type="date"  not-null="true" />
        <property name="loadDate" column="LOAD_DATE" type="date"  not-null="true" />
        <property name="filename" column="FILENAME" type="string"  not-null="true" />
        <property name="loadInfo" column="LOAD_INFO" type="string" />
        <property name="creator" column="CREATOR" type="string" />
        <property name="supplierName" column="SUPPLIER_NAME" type="string" />
        <property name="supplierNumber" column="SUPPLIER_NUMBER" type="integer" />
        <property name="buyerCode" column="BUYER_CODE" type="string" />
        <property name="buyerDept" column="BUYER_DEPT" type="string" />
        <property name="contract" column="CONTRACT" type="string" />
        <property name="active" column="ACTIVE" type="byte"  not-null="true" />
        <property name="poTypeId" column="PO_TYPE_ID" type="integer" not-null="true"/>
 
    </class>
   
</hibernate-mapping>

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
                            "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
                            "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd" >

<!-- DO NOT EDIT: This is a generated file that is synchronized -->
<!-- by MyEclipse Hibernate tool integration.                   -->
<!-- Created Sun Mar 20 20:16:12 CST 2005                         -->
<hibernate-mapping package="com.talisen.lbwebpo.model">

      <class name="Rfq" table="RFQ_TABLE">
            <id name="rfqId" column="RFQ_ID" type="integer">
                  <generator class="native" />
            </id>

            <property name="issuedBy" column="ISSUED_BY" type="integer" />
            <property name="releaseDate" column="RELEASE_DATE" type="date" />
            <property name="bidCloseDate" column="BID_CLOSE_DATE" type="date" />
            <property name="taxInformation" column="TAX_INFORMATION" type="string" />
            <property name="fob" column="FOB" type="string" />
            <property name="specialInstructions" column="SPECIAL_INSTRUCTIONS" type="string" />
            <property name="rfqConstant" column="RFQ_CONSTANT" type="string" />
            <property name="rfqBuyerCode" column="RFQ_BUYER_CODE" type="string" />
            <property name="rfqBluesCode" column="RFQ_BLUES_CODE" type="string" />
            <property name="rfqSeqRevisionNumber" column="RFQ_SEQ_REVISION_NUMBER" type="integer" />
            <property name="rfqStatus" column="RFQ_STATUS" type="string" />
            <property name="poId" column="PO_ID" type="integer"/>
            <property name="incrementalPriceQuote" column="INCREMENTAL_PRICE_QUOTE" type="string" />

            <many-to-one name="po" column="PO_ID" class="Po"  not-null="true" insert="false" update="false"/>
            
      </class>

</hibernate-mapping>




0
aturetsky
Asked:
aturetsky
  • 8
  • 5
  • 3
2 Solutions
 
Dejan PažinCommented:


Try changing the many-to-one tag

<many-to-one name="po" column="PO_ID" class="Po"  not-null="true" insert="false" update="false"/>

into

<many-to-one name="po" column="PO_ID" class="Po"  not-null="true" insert="false" update="false" outer-join="true"/>

And also make sure the lazy setting is not set:

<hibernate-mapping package="com.talisen.lbwebpo.model"  default-lazy="true">
0
 
aturetskyAuthor Commented:
When I try to set default-lazy="true" , I get an error saying it must be declared for this element type, i.e. it's not part of the dtd.

Is that because I am using 2.7?
0
 
aturetskyAuthor Commented:
also, adding the outer-join didn't change anything
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
aturetskyAuthor Commented:
Adding points due to increased urgency.
0
 
Dejan PažinCommented:

Which database are you using?
0
 
aturetskyAuthor Commented:
Oracle 9i
0
 
Dejan PažinCommented:

This is what I would try:

1. add lazy="false" here (works for hibernate 2.1.7):

<class name="Po" table="PO_LIST" where="SOURCE != 'WEBPO - IDS' AND active = 1" lazy="false">

2. Check the generated sqls for any occurance of 'join' keyword

3. If there is still none, try setting up a simple application using hibernate 3, to see if it works there
0
 
aozarovCommented:
This is the common n+1 problem for HQL (see http://www.hibernate.org/118.html#A24)
try this:
somelist = session.find("from Rfq rfq left join fetch rfq.po as po order by po.poDate asc where po.buyerCode='GAK'");
or this
somelist = session.find("from Rfq rfq left join fetch rfq.po order by rfq.po.poDate asc where rfq.po.buyerCode='GAK'");
0
 
aturetskyAuthor Commented:
dejanpazin, I am curious why would advice to turn the lazy to false, if the hibernate link mentioned by aozarov below says:  Ensure that all <class> and <collection> mappings specify lazy="true"
0
 
aturetskyAuthor Commented:
ok, so I tried aozarov's suggestion.  Adding left join fetch didn't change anything.  What did help is adding an attribute lazy="true" in <class name="Po" table="PO_LIST" where="SOURCE != 'WEBPO - IDS' AND active = 1" lazy="true">.  The problem now, though, is that I do want the data in Po associated with that Rfq available to me.  For example, my jsp uses <c:out value="${rfq.po.poNumber}.

There's got to be a way to prevent all those selects from being generated and still get those fields.
0
 
aozarovCommented:
1. What did  lazy="true" for Po changed in the generated queries?
2. what was the result (generated queries) when doing session.find("from Rfq rfq left join fetch rfq.po order by rfq.po.poDate asc where rfq.po.buyerCode='GAK'") with option 1?
 
0
 
Dejan PažinCommented:

If you close the session, then you will not be able to get the data which werent already loaded before closing the session (cause hibernate wont be able to execute any more queries). By setting  lazy="true", you tell hibernate to load that object only when it is needed.

I did try aozarovs solution with hibernate 2.17 and it works, it gets the data in a single select statement. I dont know why it doesnt work for you. Could you show the full mapping files as you have them now?


I'm not sure why you have to set lazy="true". In my case I've set both classes to lazy="false" and using aozarov solution still fetches the results in a single select. If you add a set on the other class (thats PO in your case), setting lazy="true" on that set will prevent hibernate from fetching results in a single select, but you dont have the set on the other side, so I dont see what is causing the problem.

Hibernate documentation states that '... a "fetch" join allows associations or collections of values to be initialized along with their parent objects, using a single select... It effectively overrides the outer join and lazy declarations of the mapping file for associations and collections.'

You can read more about it here:
http://www.hibernate.org/hib_docs/v3/reference/en/html_single/#queryhql-joins
0
 
aturetskyAuthor Commented:
aozarov, both 1 and 2 did solve my problem.  I previously did not implement your suggestion correctly.  Once I did, it worked, thank you so much!!!
dejanpazin - are you saying setting lazy to false should have solved my problem w/o aozarov's fetch change? I thought lazy is false by default?  Also, in order to prevent association from generating those multiple sqls, don't you have to set lazy on the associated class, like Po, to true? (the truth is I can really try this out - but I'd just been in a major rush).
0
 
Dejan PažinCommented:

>> are you saying setting lazy to false should have solved my problem w/o aozarov's fetch change?

I am saying that aozarov's solution works for me regardless of how I set lazy.

>> Also, in order to prevent association from generating those multiple sqls, don't you have to set lazy on the associated class, like Po, to true?

That's what hibernate documentation says, but I dont know why that is so.

>> the truth is I can really try this out - but I'd just been in a major rush

Trying it our is really best option, so you should do it.
0
 
aturetskyAuthor Commented:
Thanks so much to both of you!
0
 
aozarovCommented:
You are welcome :-)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 8
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now