(This is in a sense a follow up to my question http://www.experts-exchang
which was successfully answered. The difference in this case is that the association that's resulting in multiple SQL statements being generated is mapped using a property-ref attribute, and the solution to the question mentioned above does not seem to work in this case.)
Anyhow, here the problem:
I have a class called Po, a member of which has an associated RfqReqs. The latter has a field that keeps track of this relationship. So the Po class mapping has the following line: <one-to-one name="rfqReqs" class="RfqReqs" property-ref="reqPo"/>
while the RfqReqs class mapping has the following line:
<one-to-one name="rfqReqs" class="RfqReqs" property-ref="reqPo"/>
It is mapped that way based on the recommendation on page 221-222 of Hibernate in Action.
I have the RfqReqs class declared as lazy, so that it's not pulled up every time I do a find on a Po class. When I do need it, however, I retrieve it using the "left join fetch po.rfqReqs" as recommended in the answer to my question mentioned above, where, like here, I was looking for a way to prevent multiple SQL statements from being generated.
In this case, however, that solution does not seem to work: I still get multiple SQL statements generated as a result of the RfqReqs association and I suspect it has to do with the fact that I am using the property-ref attribute here.
Please help.
Hibernate version:
2.7
Mapping documents:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN" "http://hibernate.sourcefo
<hibernate-mapping package="com.talisen.lbweb
<class name="RfqReqs" table="RFQ_REQS_TABLE" lazy="true">
<id name="rfqReqsId" column="RFQ_REQS_ID" type="integer">
<generator class="native"/>
</id>
<many-to-one name="reqPo" column="REQ_ID" class="Po" unique="true"/>
<many-to-one name="rfq" column="RFQ_ID" class="Rfq"/>
</class>
</hibernate-mapping>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourcefo
<hibernate-mapping package="com.talisen.lbweb
<class name="Po" table="PO_LIST" where="SOURCE != 'WEBPO - IDS' AND active = 1" lazy="true">
<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"/>
<one-to-one name="rfqReqs" class="RfqReqs" property-ref="reqPo"/>
</class>
</hibernate-mapping>
Data Retrieval Code:
reqListForOneBuyerCode = session.find("from Po po left join fetch po.rfqReqs order by po.poDate asc where po.poTypeId=11 and po.buyerCode='" + buyerCode[i] + "'");
Full stack trace of any exception that occurs:
N/A
Database I'm using:
Oracle 9i
The generated SQL :
Hibernate: select po0_.PO_ID as PO_ID0_, rfqreqs1_.RFQ_REQS_ID as RFQ_REQS1_1_, 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_, rfqreqs1_.REQ_ID as REQ_ID1_, rfqreqs1_.RFQ_ID as RFQ_ID1_ from PO_LIST po0_ left outer join RFQ_REQS_TABLE rfqreqs1_ on po0_.PO_ID=rfqreqs1_.REQ_I
And then this statement about twenty times:
Hibernate: select rfqreqs0_.RFQ_REQS_ID as RFQ_REQS1_2_, rfqreqs0_.REQ_ID as REQ_ID2_, rfqreqs0_.RFQ_ID as RFQ_ID2_, rfq1_.RFQ_ID as RFQ_ID0_, rfq1_.ISSUED_BY as ISSUED_BY0_, rfq1_.RELEASE_DATE as RELEASE_3_0_, rfq1_.BID_CLOSE_DATE as BID_CLOS4_0_, rfq1_.TAX_INFORMATION as TAX_INFO5_0_, rfq1_.FOB as FOB0_, rfq1_.SPECIAL_INSTRUCTIONS
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
>> Also, if you look at the sqls, they are not going against the rfq_table, but against the rfq_reqs_table.
The first query joins against RFQ_REQS_TABLE, "po0_ left outer join RFQ_REQS_TABLE", as one expect after having "po left join fetch po.rfqReqs" in your finder.
>> I thought it was that at first, but then I tried it out without the rfq element altogether and it was still generating multiple sqls.
Did you remove from RfqReqs class and RfqReqs hibernate definition any reference to rfq and still got the extra queries?
I am pretty sure that should not be the case as those extra queries fetch the RFQ parts of RFQ_REQS (after that was loaded in the first query), see "from RFQ_REQS_TABLE rfqreqs0_ left outer join RFQ_TABLE ".
>>Did you remove from RfqReqs class and RfqReqs hibernate definition any reference to rfq and still got the extra queries?
Yes, I have - and the problem still persists.
>>I am pretty sure that should not be the case as those extra queries fetch the RFQ parts of RFQ_REQS (after that was loaded in the first query), see "from RFQ_REQS_TABLE rfqreqs0_ left outer join RFQ_TABLE ".
Well, yeah, but even when I remove any reference to rfq, it still executes the extra selects, except those selects no longer say left outer join RFQ_TABLE.
Any thoughts?
Alex
Business Accounts
Answer for Membership
by: aozarovPosted on 2005-05-04 at 20:51:03ID: 13932897
>> I suspect it has to do with the fact that I am using the property-ref attribute here.
I don't think so.
>> And then this statement about twenty times:
This is probably because you have another many-to-one relation in RfqReqs (to Rfq class).
The left join fetch po.rfqReqs was doing it correctly and loaded rfqReqs columns when you applied the finder as
there was no request to join/load the related rfq.
I am not sure if you can apply multiple join fetch in HQL but you can try somethig like that:
reqListForOneBuyerCode = session.find("from Po po left join fetch po.rfqReqs rfqReqs left join fetch rfqReqs.rfq order by po.poDate asc where po.poTypeId=11 and po.buyerCode='" + buyerCode[i] + "'");