2023年6月23日发(作者：)

MyBatis中使⽤collection⼀对多分页查询时记录数量不准确的问题假设有如下实体表USER和USER_GROUP，及其映射表USER_MAPPING_USER_GROUPUSERID1USER_ACCOUNTzhangsan

USER_NAME张三USER_GROUPID1USER_GROUP_NAME管理员组USER_MAPPING_USER_GROUPUSER_ID1USER_GROUP_ID1现在要根据传⼊的查询参数：USER_ACCONT和USER_GROUP_NAME 对 USER表进⾏分页查询，在MyBatis中使⽤resultMap中的collection实现⼀对多查询时查询返回的记录数量和总数量会出现不符合实际情况的问题，如下的sql代码所⽰，原因在于主表USER leftjoin映射表USER_MAPPING_USR_GROUP和从表USER_GROUP时，返回的记录中包含主表字段（USER_ACCOUNT等）和从表字段(USER_GROUP_NAME等)，⾃然⽽然就多于只查询主表返回的记录数量了。

select uu.*, USER_GROUP_ID, _GROUP_NAME from ( select ,UPPER(_NAME) as USER_NAME,UPPER(_ACCOUNT) as USER_ACCOUNT from "USER" u WHERE 1=1 AND UPPER(_ACCOUNT) LIKE UPPER(CONCAT(CONCAT('%',#{count,jdbcType=VARCHAR}),'%')) ) uu left join "USER_MAPPING_USER_GROUP" umug on = _ID left join "USER_GROUP" ug on _GROUP_ID = where 1=1 AND UPPER(_GROUP_NAME) = #{oupName,jdbcType=VARCHAR} and _DELETE = 'N' ORDER BY

如何解决该问题，pagehelper已经提⽰不⽀持上⾯这种情况的查询了。

解决该问题的思路很直接，⼿写分页逻辑就可以了，因为⾮常简单。于是上⾯的sql查询语句就变为下⾯的。 SELECT pagea.*, _GROUP_NAME, USER_GROUP_ID FROM (SELECT * FROM (select pageb.*, ROWNUM ROW_ID from ( select distinct ,_NAME,_ACCOUNT from ( select ,UPPER(_NAME) as USER_NAME,UPPER(_ACCOUNT) as USER_ACCOUNT from "USER" u WHERE 1=1 AND UPPER(_ACCOUNT) LIKE UPPER(CONCAT(CONCAT('%',#{count,jdbcType=VARCHAR}),'%')) ) uu left join "USER_MAPPING_USER_GROUP" umug on = _ID left join "USER_GROUP" ug on _GROUP_ID = where 1=1 AND UPPER(_GROUP_NAME) = #{oupName,jdbcType=VARCHAR} ORDER BY ) pageb WHERE ROWNUM < #{,jdbcType=DECIMAL}) WHERE ROW_ID >= #{,jdbcType=DECIMAL} ) pagea left join "USER_MAPPING_USER_GROUP" umug on = _ID left join "USER_GROUP" ug on _GROUP_ID = select count (distinct ) from ( select ,UPPER(_NAME) as USER_NAME,UPPER(_ACCOUNT) as USER_ACCOUNT from "USER" u WHERE 1=1

AND UPPER(_ACCOUNT) LIKE UPPER(CONCAT(CONCAT('%',#{count,jdbcType=VARCHAR}),'%')) ) uu left join "USER_MAPPING_USER_GROUP" umug on = _ID left join "USER_GROUP" ug on _GROUP_ID = where 1=1 AND UPPER(_GROUP_NAME) = #{oupName,jdbcType=VARCHAR} ORDER BY