ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From DaqiQi...@aol.com
Subject Urgent help for using groupBY
Date Fri, 07 Apr 2006 00:51:40 GMT
All,

I have following data set from a query:

member_id    saving_card_id   coupon_id
1                        1                    123
1                        1                    345
1                        1                    678
1                        2                    123
1                        2                    345
1                        2                    678
2                        3                    123
2                        3                     567
2                        3                     345
3                        4                     456
3                        4                    123
3                        4                     567
3                        4                     789

I need to map above data to a list of MemberData object. MemberData has 
member_id and two lists. One list is for list of saving_card_ids and another list 
is for coupon ids. Above data need to be mapped as

for member 1, MemberData ={
                      memberId=1,
                      savingCardList={1,2},
                      couponList ={123, 345, 678}
}

for member 2 MemberData={
                      memberId=2,
                      savingCardList={3}
                      couponList={123,567,345}
}

For member 3 MemberData={
                      memberId=3,
                      savingCardList={4},
                      couponList={456,123,567,789}
}

For same member, each saving card will have same number of coupons. Also, the 
couponIds are also same. 

I had following sqlMap:

  <resultMap id="memberDataMap" class="MemberData" groupBy="memberId">
          <result property="memberId" column="member_id" jdbcType="BIGINT" />
          <result property="savingCardList" 
resultMap="member_coupon.savingCardMap" />
          <result property="couponList" resultMap="member_coupon.couponMap" />
  </resultMap>

  <resultMap id="savingCardMap" class="SavingCard" groupBy="savingCardId">
          <result property="savingCardId" column="saving_card_id" 
jdbcType="SMALLINT" />
  </resultMap>

  <resultMap id="couponMap" class="Coupon" groupBy="couponId" >
            <result property="couponId" column="coupon_id" 
jdbcType="INTEGER"/>
   </resultMap>         

Above mapping works correctly for only one member. If more than one member, 
all coupons went into the first member's coupons list. the coupon lists for 
member 2 and 3 were empty.  If i use groupBy="{couponId, savingCardId) for 
couponMap, the couponList of member 1 had 6 entries {123, 345, 678,123, 345, 678). 
Two for each coupon which is not desirable.

I have struggled for more than one day. Any help and suggestions are really 
appreciated.

Tony

Mime
View raw message