You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@ibatis.apache.org by "raja chawat (JIRA)" <ib...@incubator.apache.org> on 2007/08/20 19:19:32 UTC
[jira] Commented: (IBATIS-406) 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.
[ 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.