ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jeynes, Paul [CIB-IT]" <paul.jey...@citigroup.com>
Subject RE: Need help on nested N + 1 query
Date Mon, 27 Nov 2006 17:47:02 GMT
Check page 38 of the iBatis SQL Map Developers Guide (2.0).  I think that has what you need
(complex parameter properties).

-----Original Message-----
From: Tamilselvan Radha Krishnan [mailto:Tamilselvan.RadhaKrishnan@igate.com]
Sent: 27 November 2006 17:39
To: user-java@ibatis.apache.org
Subject: Need help on nested N + 1 query 



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