ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Tamilselvan Radha Krishnan" <Tamilselvan.RadhaKrish...@igate.com>
Subject Need help on nested N + 1 query
Date Mon, 27 Nov 2006 17:39:15 GMT
Hi,
I've written a N + 1 query in Ibatis such a way that it will return the
nested results as below :

   Customer - First object
             DealFacility - An array list of objects within Customer
                     Facility - An array list of objects within
DealFacility
                        LoanInfo - An array list of objects within
Facility
    

The Customer object is having a 'strCustomerId' as an attribute.  I need
to use this 'strCustomerId' in the WHERE clause of the query used for
populating 'LoanInfo'  (<select id="getFacilityLoans"  )  i.e, 3 level
below the Customer, in addition to 'Facility' class above.

I've placed my Login.xml query below.  Any suggestion would be much
helpful.




<resultMap id="customersResults"
class="com.ge.cf.myaccounts.domain.Customer"> 
        <result property="strCustomerId"
column="CUSTOMER_CODE"/>
        <result property="strCustomerName"
column="CUSTOMER_NAME"/>
        <result property="alDealFacilities"
column="CUSTOMER_CODE"  	select="Login.getDealFacilities"/>

   </resultMap>
   
   <select id="getCustomerDetails"  resultMap="customersResults"
parameterClass="com.ge.cf.myaccounts.domain.Customer">
     	SELECT   CUSTOMER_CODE,CUSTOMER_NAME
		FROM     STG_INTM_CUSTOMER_INFO 
		WHERE    CUSTOMER_CODE=#strCustomerId#

   </select>  
   
   
   
   	<resultMap id="dealFacilityResults"
class="com.ge.cf.myaccounts.domain.DealFacility"> 
   	 
   	    <result property="strCustomerId"
column="CUSTOMER_CODE"/>
   	    <result property="strDealFacilityId"
column="DEAL_FACILITY_CODE"/>
        <result property="strlType"
column="DEAL_FACILITY_TYPE"/>
        <result property="dobCommitAmount" 	   nullValue="0"
column="DEAL_FACILITY_COM_AMOUNT"/>
        <result property="dobBalance" 		   nullValue="0"
column="DEAL_FACILITY_BALANCE"/>  
        <result property="alFacilities"
column="DEAL_FACILITY_CODE" select="Login.getFacilities"/>              
   	</resultMap>
   
   	<select id="getDealFacilities"  resultMap="dealFacilityResults"
parameterClass="java.lang.String">  
     	SELECT
	 				ci.CUSTOMER_CODE,
cf.DEAL_FACILITY_CODE,cf.DEAL_FACILITY_TYPE,cf.DEAL_FACILITY_COM_AMOUNT,
cf.DEAL_FACILITY_BALANCE
	 	FROM 		STG_INTM_CUSTOMER_INFO ci
,STG_INTM_DEAL_FACILITY cf 
	 	WHERE 		ci.CUSTOMER_CODE = #strCustomerId# 
	 	AND         ci.CUSTOMER_CODE = cf.CUSTOMER_CODE 
	 	AND         cf.IS_DELETED='N'	 	
   	</select> 
   
   
     <resultMap id="facilityResults"
class="com.ge.cf.myaccounts.domain.Facility">  
	    <result property="strFacilityId"
column="FACILITY_CODE"/>     		
     	<result property="strTypeDescription"
column="FACILITY_TYPE_DESC"/> 
     	<result property="strDescription"
column="FACILITY_DESC"/> 
     	<result property="dobCommitAmount" 	     nullValue="0"
column="FACILITY_COM_AMOUNT"/>
     	<result property="dobBalance" 	         nullValue="0"
column="FACILITY_BALANCE"/>
     	<result property="strBbcId"
column="BBC_ID"/> 
     	<result property="strStatus"
column="STATUS"/>       	
        <result property="alLoanInfos"
column="FACILITY_CODE" select="Login.getFacilityLoans"/>

       		
  	</resultMap>
  	
  	<select id="getFacilities"  resultMap="facilityResults"
parameterClass="java.lang.String">  
  		SELECT DISTINCT f.facility_code, f.facility_desc,
f.facility_type_desc,
                f.facility_com_amount, f.facility_balance, f.bbc_id,
d.status
        FROM tmplte_master_status d,
                (SELECT DISTINCT a.facility_code facility_code,
                                 facility_desc facility_desc,
                                 facility_type_desc facility_type_desc,
                                 facility_com_amount
facility_com_amount,
                                 facility_balance facility_balance,
                                 bbc_id bbc_id, cur_status_id
cur_status_id
                 FROM 			 stg_intm_mem_facility a,
                                 stg_intm_deal_facility b,
                                 tmplte_master c
                 WHERE 			 a.deal_facility_code =
#strDealFacilityId#
                 AND 			 a.facility_code =
c.facility_code(+)
                 AND 			 a.is_deleted = 'N') f
        WHERE d.status_id(+) = f.cur_status_id

	</select>
	
	
	<resultMap id="facilityLoanResults"
class="com.ge.cf.myaccounts.domain.LoanInfo">  
     	<result property="strLoanId"
column="LOAN_CODE"/>
        <result property="strLoanIntRateType"
column="LOAN_INT_RATE_TYPE"/>
        <result property="dobBalance" 	     nullValue="0"
column="LOAN_BALANCE"/>   
        <result property="strSectionId"
column="SECTION_CD"/>
        <result property="strLoanProductGroup"
column="LOAN_PRODUCT_GROUP"/>          	
  	</resultMap>
  	
  	<select id="getFacilityLoans"  resultMap="facilityLoanResults"
parameterClass="java.lang.String">  
  		SELECT  LOAN_CODE, LOAN_INT_RATE_TYPE, LOAN_BALANCE ,
SECTION_CD, LOAN_PRODUCT_GROUP  
		FROM    STG_INTM_LOAN_INFO 
		WHERE   FACILITY_CODE=#strFacilityId#   
		AND     trim(SECTION_CD) IS NULL 
		AND     IS_DELETED='N'  
		ORDER BY LOAN_CODE    
    </select>







_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Information transmitted by this EMAIL is proprietary to iGATE Group of Companies and is intended
for use only by the individual 
or entity to whom it is addressed and may contain information that is privileged, confidential,
or exempt from disclosure under 
applicable law. If you are not the intended recipient of this EMAIL immediately notify the
sender at iGATE or mailadmin@igate.com 
and delete this EMAIL including any attachments
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Mime
View raw message