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
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.
