You should also consider that this is really an (N*N*N)+1 query - the number of selects sent to the DB grows exponentially as more rows are returned. I think it would be worth trying this all in one query and using iBATIS groupBy solution for dealing with N+1 queries. You'll have a lot of redundant data in the result set, but I think it will still perform better than sending lots of seperate select statements to the DB. Here's a link to a discussion about doing multiple nested groupBys:
http://www.mail-archive.com/[email protected]/msg06596.html Jeff Butler On 11/27/06, Jeynes, Paul [CIB-IT] <[EMAIL PROTECTED]> wrote:
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: [EMAIL PROTECTED] *Sent:* 27 November 2006 17:39 *To:* [email protected] *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 [EMAIL PROTECTED] and delete this EMAIL including any attachments _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
