You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Jeff Butler <je...@gmail.com> on 2006/11/27 18:57:50 UTC
Re: Need help on nested N + 1 query
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/user-java@ibatis.apache.org/msg06596.html
Jeff Butler
On 11/27/06, Jeynes, Paul [CIB-IT] <pa...@citigroup.com> 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:
> 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
>
> _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
>
>
mapping results to a join sql query
Posted by "Ou, Stan (Mission Systems)" <ST...@ngc.com>.
hi,
I read the manual but didn't find anything. there might be a simple
solution to this.
1. I've a join sql, say select a,b,c,x,y,z from tableA, tableX where a
=x
2. i've 2 pojo's say pojoA and pojoB that contains properties a,b,c and
x,y,z respectively.
3. i've 2 ibatis xml's that each maps the pojo to its table. they're in
the same namespace.
here in pojoA.xml, I've the join sql mentioned in #1. my question is
how do I map the properties from pojoB.xml?
i tried append them to the existing properties like below, but ibatis
complainted there is no writable properties.
//existing properties
<result property="a" column="a"/>
//newly appended
<result property="x" column="myNamespace.x"/>
the error was no writable property for x.
any help is appreciated. thanks
Stan