[
https://issues.apache.org/jira/browse/IBATIS-406?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12521165
]
raja chawat commented on IBATIS-406:
------------------------------------
Work around this is to have unique keys:
<resultMap id="UserAbsenceResult"
class="com.principal.ris.expwf.domain.UserAbsence" groupBy="absenseUserId,
absenceBeginDate" >
<result property="absenseUserId" column="USER_ID"/>
<result property="absenceBeginDate" column="ABSN_BEG_DT"/>
<result property="absenceEndDate" column="ABSN_END_DT"/>
<result property="absenceReasonCode" column="ABSN_RSN_CD"/>
<result property="backupUserId" column="BKUP_USER_ID"/>
<result property="absenceBeginTimeCount" column="ABSN_BEG_TM_CNT"/>
<result property="absenceEndTimeCount" column="ABSN_END_TM_CNT"/>
</resultMap>
<resultMap id="UserSkillResult"
class="com.principal.ris.expwf.domain.UserSkill" groupBy="skillUserId,
skillLevelCode">
<result property="skillUserId" column="USER_ID"/>
<result property="skillLevelCode" column="SKILL_LEVEL_CD"/>
<result property="skillOrderCode" column="SKL_ORD_CD"/>
<result property="skillStatCode" column="SKL_STAT_CD"/>
<result property="skillHomeCode" column="SKL_HOME_CD"/>
<result property="skillOrderGroupCode" column="SKL_ORD_GRP_CD"/>
<result property="skillReorderCode" column="SKL_RORD_CD"/>
<result property="checkUserID" column="CHK_USR_ID"/>
</resultMap>
It works for me.
> Bug in Ibatis sqlMap when one resultMap has multiple results that are
> resultMaps... then the groupby attribute only seems to work for the first
> embedded resultMap.
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Key: IBATIS-406
> URL: https://issues.apache.org/jira/browse/IBATIS-406
> Project: iBatis for Java
> Issue Type: Bug
> Components: SQL Maps
> Reporter: Shirley
> Priority: Blocker
> Attachments: IBATIS-406.patch
>
>
> In Ibatis using sqlMaps, we are able to do embedded resultMaps within
> resultMaps. If we have one resultMap with only one result that is a
> resultMap (ie <result property="userAbsence"
> resultMap="User.UserAbsenceResult"/> )then using the groupby attribute has
> the effect of grouping by that property.
> However, if we have one resultMap that has multiple results that are
> resultMap then the group by only works for the first embedded item.
> Below is a working code snippet that uses embedded resultMaps. Adding the
> groupby attribute forces the class that is returned to only return one item
> per groupby key.
> <resultMap id="UserResult"
> class="com.principal.ris.expwf.domain.User" groupBy="userId">
> <result property="userId" column="USER_ID"/>
> <result property="userName" column="USER_NM"/>
> <result property="authLevelCode" column="AUTH_LEVEL_CD"/>
> <result property="certPrinCode" column="CERT_PRIN_CD"/>
> <result property="locationCode" column="LOCATION_CD"/>
> <result property="currentRoleCode" column="CRNT_ROLE_CD"/>
> <result property="skillChangeFlag" column="SKL_CHG_FLG"/>
> <result property="reprocessFlag" column="RPRCS_FLG"/>
> <result property="productUseCode" column="PRDCT_USE_CD"/>
> <result property="skillOrderFlag" column="SKL_ORD_FLG"/>
> <result property="queueOrderFlag" column="QUEUE_ORD_FLG"/>
> <result property="workGroupOrderCode" column="WRK_GRP_ORD_CD"/>
> <result property="excludeLocationCode" column="EXCL_LOC_CD"/>
> <result property="workflowVersionCode" column="WF_VRNS_CD"/>
> <result property="userAbsence" resultMap="User.UserAbsenceResult"/>
> </resultMap>
> <resultMap id="UserAbsenceResult"
> class="com.principal.ris.expwf.domain.UserAbsence" groupBy="absenceBeginDate"
> >
> <result property="userId"
> column="USER_ID"/>
> <result property="absenceBeginDate"
> column="ABSN_BEG_DT"/>
> <result property="absenceEndDate"
> column="ABSN_END_DT"/>
> <result property="absenceReasonCode" column="ABSN_RSN_CD"/>
> <result property="backupUserId"
> column="BKUP_USER_ID"/>
> <result property="absenceBeginTimeCount" column="ABSN_BEG_TM_CNT"/>
> <result property="absenceEndTimeCount"
> column="ABSN_END_TM_CNT"/>
> </resultMap>
> <select id="retrieveUser" parameterClass="java.lang.String"
> resultMap="UserResult">
>
> SELECT
> U.USER_ID,
> U.USER_NM ,
> U.AUTH_LEVEL_CD,
> U.CERT_PRIN_CD,
> U.LOCATION_CD,
> U.CRNT_ROLE_CD,
> U.SKL_CHG_FLG,
> U.RPRCS_FLG,
> U.PRDCT_USE_CD,
> U.SKL_ORD_FLG,
> U.QUEUE_ORD_FLG,
> U.WRK_GRP_ORD_CD,
> U.EXCL_LOC_CD,
> U.WF_VRNS_CD,
> S.SKILL_LEVEL_CD,
> S.SKL_ORD_CD,
> S.SKL_STAT_CD,
> S.SKL_HOME_CD,
> S.SKL_ORD_GRP_CD,
> S.SKL_RORD_CD,
> S.CHK_USR_ID,
> A.ABSN_BEG_DT,
> A.ABSN_END_DT,
> A.ABSN_RSN_CD,
> A.BKUP_USER_ID,
> A.ABSN_BEG_TM_CNT,
> A.ABSN_END_TM_CNT
> from XPRS_WF_USER U
> left outer join XPRS_WF_USER_SKL S
> on U.USER_ID = S.USER_ID
> left outer join XPRS_WF_USER_ABSN A
> on U.USER_ID = A.USER_ID
> where U.USER_ID=#value#
> </select>
> However if there is more than one result that is resultMap then the groupby
> attribute only seems to work for the first embedded item. Example below only
> does the grouping for the first embedded property ie (userAbsence). When I
> switched it to have the userSkill before the userAbsence, then the group by
> only worked for the userSkill.
> <resultMap id="UserResult" class="com.principal.ris.expwf.domain.User"
> groupBy="userId">
> <result property="userId" column="USER_ID"/>
> <result property="userName" column="USER_NM"/>
> <result property="authLevelCode"
> column="AUTH_LEVEL_CD"/>
> <result property="certPrinCode" column="CERT_PRIN_CD"/>
> <result property="locationCode" column="LOCATION_CD"/>
> <result property="currentRoleCode" column="CRNT_ROLE_CD"/>
> <result property="skillChangeFlag" column="SKL_CHG_FLG"/>
> <result property="reprocessFlag" column="RPRCS_FLG"/>
> <result property="productUseCode" column="PRDCT_USE_CD"/>
> <result property="skillOrderFlag" column="SKL_ORD_FLG"/>
> <result property="queueOrderFlag" column="QUEUE_ORD_FLG"/>
> <result property="workGroupOrderCode" column="WRK_GRP_ORD_CD"/>
> <result property="excludeLocationCode" column="EXCL_LOC_CD"/>
> <result property="workflowVersionCode" column="WF_VRNS_CD"/>
> <result property="userAbsence" resultMap="User.UserAbsenceResult"/>
> <result property="userSkill"
> resultMap="User.UserSkillResult"/>
> </resultMap>
> <resultMap id="UserAbsenceResult"
> class="com.principal.ris.expwf.domain.UserAbsence" groupBy="absenceBeginDate"
> >
> <result property="userId" column="USER_ID"/>
> <result property="absenceBeginDate" column="ABSN_BEG_DT"/>
> <result property="absenceEndDate" column="ABSN_END_DT"/>
> <result property="absenceReasonCode" column="ABSN_RSN_CD"/>
> <result property="backupUserId" column="BKUP_USER_ID"/>
> <result property="absenceBeginTimeCount"
> column="ABSN_BEG_TM_CNT"/>
> <result property="absenceEndTimeCount"
> column="ABSN_END_TM_CNT"/>
> </resultMap>
> <resultMap id="UserSkillResult"
> class="com.principal.ris.expwf.domain.UserSkill" groupBy="skillLevelCode">
> <result property="userId" column="USER_ID"/>
> <result property="skillLevelCode"
> column="SKILL_LEVEL_CD"/>
> <result property="skillOrderCode" column="SKL_ORD_CD"/>
> <result property="skillStatCode" column="SKL_STAT_CD"/>
> <result property="skillHomeCode" column="SKL_HOME_CD"/>
> <result property="skillOrderGroupCode"
> column="SKL_ORD_GRP_CD"/>
> <result property="skillReorderCode"
> column="SKL_RORD_CD"/>
> <result property="checkUserID" column="CHK_USR_ID"/>
> </resultMap>
>
> Planning to get around this by creating separate SQLs for the userAbesence
> and the userSkill. Since we are only expecting 1 userID at a time we don't
> expect much overhead, then from the individual sqls for userSkill and
> userAbsence I can do the group by within the query. Does anyone have any
> other suggestions?
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
