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 Da...@aol.com on 2006/04/07 06:51:40 UTC
Urgent help for using groupBY
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