Urgent help for using groupBY

Thu, 06 Apr 2006 21:52:15 -0700

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

Reply via email to