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

one hibernate select generating lot of sql statement when using property-ref

(This is in a sense a follow up to my question http://www.experts-exchange.com/Programming/Programming_Languages/Java/Q_21396914.html#13879622
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.sourceforge.net/hibernate-mapping-2.0.dtd" >
<hibernate-mapping package="com.talisen.lbwebpo.model">
<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.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" 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_ID where po0_.SOURCE != 'WEBPO - IDS' AND po0_.active = 1 and ((po0_.PO_TYPE_ID=11 )and(po0_.BUYER_CODE='N1R' )) order by po0_.PO_DATE asc

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 as SPECIAL_7_0_, rfq1_.RFQ_CONSTANT as RFQ_CONS8_0_, rfq1_.RFQ_BUYER_CODE as RFQ_BUYE9_0_, rfq1_.RFQ_BLUES_CODE as RFQ_BLU10_0_, rfq1_.RFQ_SEQ_REVISION_NUMBER as RFQ_SEQ11_0_, rfq1_.RFQ_STATUS as RFQ_STATUS0_, rfq1_.PO_ID as PO_ID0_, rfq1_.STATUS as STATUS0_, rfq1_.INCREMENTAL_PRICE_QUOTE as INCREME15_0_, rfq1_.PROCUREMENT_NOTES as PROCURE16_0_, po2_.PO_ID as PO_ID1_, po2_.PARENT_PO_ID as PARENT_P2_1_, po2_.PO_NUMBER as PO_NUMBER1_, po2_.CHANGE_ORDER_SEQ as CHANGE_O4_1_, po2_.SOURCE as SOURCE1_, po2_.PO_DATE as PO_DATE1_, po2_.LOAD_DATE as LOAD_DATE1_, po2_.FILENAME as FILENAME1_, po2_.LOAD_INFO as LOAD_INFO1_, po2_.CREATOR as CREATOR1_, po2_.SUPPLIER_NAME as SUPPLIE11_1_, po2_.SUPPLIER_NUMBER as SUPPLIE12_1_, po2_.BUYER_CODE as BUYER_CODE1_, po2_.BUYER_DEPT as BUYER_DEPT1_, po2_.CONTRACT as CONTRACT1_, po2_.ACTIVE as ACTIVE1_, po2_.PO_TYPE_ID as PO_TYPE_ID1_ from RFQ_REQS_TABLE rfqreqs0_ left outer join RFQ_TABLE rfq1_ on rfqreqs0_.RFQ_ID=rfq1_.RFQ_ID left outer join PO_LIST po2_ on rfq1_.PO_ID=po2_.PO_ID where rfqreqs0_.REQ_ID=?
0
aturetsky
Asked:
aturetsky
  • 5
  • 3
1 Solution
 
aozarovCommented:
>> 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] + "'");
0
 
aturetskyAuthor Commented:
It's interesting that you mention this.  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.

Also, if you look at the sqls, they are not going against the rfq_table, but against the rfq_reqs_table.
0
 
aozarovCommented:
>> 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 ".
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
aturetskyAuthor Commented:
ok, thanks so much - I'm putting out another fire right now, but will try this ASAP
0
 
aturetskyAuthor Commented:
>>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
0
 
aturetskyAuthor Commented:
any suggestions?
0
 
aozarovCommented:
This: PO_LIST po2_ on rfq1_.PO_ID=po2_.PO_ID
suggest that there is a direct relation between rfq to Po.
Can you provide the hibernate-mapping for the RFQ class ? What happens if you take out this (probably one side) relation between RFQ to PO ?
0
 
aturetskyAuthor Commented:
sorry, haven't had a chance to respond - I've modified the rfq mapping since so I have to put in some time to recreate the problem, but I still want to get to the bottom of this.
I will respond later.  Once again, sorry for the delay.

Please do not remove this question.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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