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 [EMAIL PROTECTED]
and delete this EMAIL including any attachments
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
