asked on

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




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




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

Dejan Pažin

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

aturetsky

ASKER

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?

aturetsky

ASKER

also, adding the outer-join didn't change anything

aturetsky

ASKER

Adding points due to increased urgency.

Dejan Pažin

Which database are you using?

aturetsky

ASKER

Oracle 9i

Dejan Pažin

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

SOLUTION

aozarov

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

aturetsky

ASKER

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"

aturetsky

ASKER

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.

aozarov

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?

Dejan Pažin

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

aturetsky

ASKER

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

ASKER CERTIFIED SOLUTION

Dejan Pažin

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

aturetsky

ASKER

Thanks so much to both of you!

aozarov

You are welcome :-)