分页插件结合collection标签后分页数量不准确的问题

问题1:不使用collection 聚合分页正确
简单列子
T_ATOM_DICT表有

T_ATOM_DICT_AUDIT_ROUTE表审核记录表有

我要显示那些原子审核了,我把两个表inner join 就是那些原子审核过了

mapper

    IPage<AtomDict> findAllByAuditRouteNoDuplicate(@Param("criteria") AtomDictQueryCriteria criteria, Page<Object> page);

xml基础的查询类型和返回类型

<!--    分步要用-->
<resultMap id="BaseResultMap" type="com.njry.sjzl.busi.domain.AtomDict"><result column="ATOM_ID" property="atomId"/><result column="ATOM_ID" property="id"/><result column="CATEGORY_ID" property="categoryId"/><result column="CATEGORY_NAME" property="categoryName"/><result column="ATOM_NAME" property="atomName"/><result column="TYPE" property="type"/><result column="TYPE_NAME" property="typeName" /><result column="DATA_CYCLE" property="dataCycle"/><result column="DATA_CYCLE_NAME" property="dataCycleName"/><result column="LIB_TYPE" property="libType"/>
<!--        <result column="AUDIT_RESULT" property="auditResult"/>-->
<!--        <result column="AUDIT_REMARK" property="auditRemark"/>--><result column="LIB_TYPE_NAME" property="libTypeName"/><result column="DATA_GENE_MODE" property="dataGeneMode"/><result column="DATA_GENE_MODE_NAME" property="dataGeneModeName"/><result column="TEMPLATE_ID" property="templateId"/><result column="TEMPLATE_NAME" property="templateName"/><result column="DATABASE" property="database"/><result column="DATABASE_NAME" property="databaseName"/><result column="DATABASE_TABLE" property="databaseTable"/><result column="COLUMN_NAME" property="columnName"/><result column="TABLE_LEVEL" property="tableLevel"/><result column="PROC_NAME" property="procName"/><result column="IS_HZ" property="isHz"/><result column="STATUS" property="status"/><result column="ATOM_TABLE" property="atomTable"/><result column="MODE_FLAG" property="modeFlag"/><result column="CREATE_ID" property="createId"/><result column="CREATE_DATE" property="createDate"/><result column="REC_ID" property="recId"/><result column="REC_DATE" property="recDate"/><result column="SH_ID" property="shId"/><result column="SH_DATE" property="shDate"/><result column="SH_REMARK" property="shRemark"/><result column="DEL_ID" property="delId"/><result column="DEL_DATE" property="delDate"/><result column="START_DATE" property="startDate"/><result column="END_DATE" property="endDate"/><result column="UNIT" property="unit"/><result column="DIMEN_FLAG" property="dimenFlag"/></resultMap><resultMap id="BaseResultMapBoss" type="com.njry.sjzl.busi.domain.AtomDict"><result column="ATOM_ID" property="atomId"/><result column="ATOM_ID" property="id"/><result column="CATEGORY_ID" property="categoryId"/><result column="CATEGORY_NAME" property="categoryName"/><result column="ATOM_NAME" property="atomName"/><result column="TYPE" property="type"/><result column="TYPE_NAME" property="typeName" /><result column="DATA_CYCLE" property="dataCycle"/><result column="DATA_CYCLE_NAME" property="dataCycleName"/><result column="LIB_TYPE" property="libType"/><result column="LIB_TYPE_NAME" property="libTypeName"/><result column="DATA_GENE_MODE" property="dataGeneMode"/><result column="DATA_GENE_MODE_NAME" property="dataGeneModeName"/><result column="TEMPLATE_ID" property="templateId"/><result column="TEMPLATE_NAME" property="templateName"/><result column="DATABASE" property="database"/><result column="DATABASE_NAME" property="databaseName"/><result column="DATABASE_TABLE" property="databaseTable"/><result column="COLUMN_NAME" property="columnName"/><result column="TABLE_LEVEL" property="tableLevel"/><result column="PROC_NAME" property="procName"/><result column="IS_HZ" property="isHz"/><result column="STATUS" property="status"/><result column="ATOM_TABLE" property="atomTable"/><result column="MODE_FLAG" property="modeFlag"/><result column="CREATE_ID" property="createId"/><result column="CREATE_DATE" property="createDate"/><result column="REC_ID" property="recId"/><result column="REC_DATE" property="recDate"/><result column="SH_ID" property="shId"/><result column="SH_DATE" property="shDate"/><result column="SH_REMARK" property="shRemark"/><result column="DEL_ID" property="delId"/><result column="DEL_DATE" property="delDate"/><result column="START_DATE" property="startDate"/><result column="END_DATE" property="endDate"/><result column="UNIT" property="unit"/><result column="DIMEN_FLAG" property="dimenFlag"/></resultMap>
<!--    <collection property="batchList" resultMap="BatchListResultMap"/>-->
<!--    审核表数据还得关联用户表显示用户名--><resultMap id="BatchListResultMap" type="com.njry.sjzl.busi.domain.AtomDictAuditRoute"><result column="ROUTE_ID" property="routeId"/><result column="ROUTE_ID" property="id"/><result column="ATOM_IDREPEAT" property="atomId"/><result column="AUDIT_RESULT" property="auditResult"/><result column="AUDIT_REMARK" property="auditRemark"/><result column="AUDIT_ID" property="auditId"/><result column="AUDIT_NAME" property="auditName"/><result column="AUDIT_DATE" property="auditDate"/></resultMap><sql id="BathList_Column_List">auditroute.ROUTE_ID as ROUTE_ID, auditroute.ATOM_ID as ATOM_IDREPEAT, tuser.NAME AS AUDIT_NAME,auditroute.AUDIT_RESULT as AUDIT_RESULT, auditroute.AUDIT_REMARK as AUDIT_REMARK,auditroute.AUDIT_ID as AUDIT_ID,auditroute.AUDIT_DATE as AUDIT_DATE</sql><sql id="Base_Column_List">tad.ATOM_ID, tad.CATEGORY_ID, tad.ATOM_NAME, tad.TYPE, tad.DATA_CYCLE, tad.LIB_TYPE, tad.DATA_GENE_MODE, tad.TEMPLATE_ID, tad.DATABASE_TABLE, tad."DATABASE",tad.COLUMN_NAME, tad.TABLE_LEVEL, tad.PROC_NAME, tad.IS_HZ, tad.STATUS, tad.ATOM_TABLE, tad.MODE_FLAG, tad.CREATE_ID,tad.CREATE_DATE, tad.REC_ID, tad.REC_DATE, tad.SH_ID, tad.SH_DATE, tad.SH_REMARK, tad.DEL_ID, tad.DEL_DATE, tad.START_DATE, tad.END_DATE, tad.UNIT, tad.DIMEN_FLAG</sql>
<!--    数据字典回显名字--><sql id="Dict_Detail_Column_List">dtable.label AS DATABASE_NAME,dtable1.label AS DATA_GENE_MODE_NAME,dtable2.label AS LIB_TYPE_NAME,dtable3.label AS DATA_CYCLE_NAME,dtable4.label AS TYPE_NAME</sql>

真正xml如下:关联很多表回显,T_ATOM_TEMPLATE模板表,T_ATOM_BUSI_CATEGORY业务分类,五个字典表,和(原子审核表,原子审核表要关联用户表差审核用户名)

    <select id="findAllByAuditRouteNoDuplicate" resultMap="BaseResultMapBoss">select tatempalte.TEMPLATE_NAME,tabc.CATEGORY_NAME AS CATEGORY_NAME,<include refid="Base_Column_List"/>,<include refid="Dict_Detail_Column_List"/>,<include refid="BathList_Column_List"/>from T_ATOM_DICT tadleft join T_ATOM_TEMPLATE tatempalte on tatempalte.TEMPLATE_ID = tad.TEMPLATE_IDleft join T_ATOM_BUSI_CATEGORY tabc on tad.CATEGORY_ID = tabc.category_idleft join (select dd.label,dd.value from t_dict d left join t_dict_detail dd on  dd.dict_id = d.dict_idwhere d.name = 'asset_archive_name') dtableon tad.DATABASE = dtable.valueleft join (select dd1.label,dd1.value from t_dict d1 left join t_dict_detail dd1 on  dd1.dict_id = d1.dict_idwhere d1.name = 't_atmo_dict_generation_mechanism') dtable1on tad.DATA_GENE_MODE = dtable1.valueleft join (select dd2.label,dd2.value from t_dict d2 left join t_dict_detail dd2 on  dd2.dict_id = d2.dict_idwhere d2.name = 't_atmo_dict_atomic_library_type') dtable2on tad.LIB_TYPE = dtable2.valueleft join (select dd3.label,dd3.value from t_dict d3 left join t_dict_detail dd3 on  dd3.dict_id = d3.dict_idwhere d3.name = 't_atmo_dict_data_cycle') dtable3on tad.DATA_CYCLE = dtable3.valueleft join (select dd4.label,dd4.value from t_dict d4 left join t_dict_detail dd4 on  dd4.dict_id = d4.dict_idwhere d4.name = 't_atmo_dict_data_type') dtable4on tad.TYPE = dtable4.valueinner join (select ATOM_ID,AUDIT_RESULT,AUDIT_REMARK,ROUTE_ID,AUDIT_ID,AUDIT_DATEfrom T_ATOM_DICT_AUDIT_ROUTE) auditroute on auditroute.ATOM_ID =tad.ATOM_IDleft join t_user tuser on tuser.oper_id = auditroute.AUDIT_ID<where><if test="criteria.atomId != null">and tad.ATOM_ID = #{criteria.atomId}</if><if test="criteria.atomName != null">and tad.ATOM_NAME like concat('%'||#{criteria.atomName},'%')</if></where>order by tad.CREATE_DATE desc, auditroute.AUDIT_DATE asc</select>

问题2:使用collection 聚合出现分页问题
xml基础的查询类型和返回类型 里面把注释的

方法返回类型 id = “BaseResultMapBoss” 里

解决办法1(我最蠢的解决办法,把sql分解)
不知道怎么自定义分页解决这种(不会),
我只能把这一个sql语句分开

在impl层

    @Overridepublic PageResult<AtomDict> queryAllByAuditRouteDuplicate(AtomDictQueryCriteria criteria, Page<Object> page){//        去重 inner join(原来一个sql就该搞定的)
//        IPage<AtomDict> all = atomDictMapper.findAllByAuditRouteNoDuplicate(criteria, page);
//        去重 inner join(分步1)IPage<AtomDict> all = atomDictMapper.findAllByAuditRouteNoDuplicateSubstep(criteria, page);List<AtomDict> records = all.getRecords();//        根据每条数据类的busiSort递归向下查找归属业务分类(回显)
//        每条数据类还要找自己的审批过程(分步2)if(records.size() > 0 ){for (int i = 0; i < records.size(); i++) {AtomDict atomDict = records.get(i);if(atomDict != null){Set<String> taskSetResult  = new LinkedHashSet<>();Long categoryId = atomDict.getCategoryId();
//        			这里拿到第一步去重的审核原子再去查审核记录,放在返回前端的BatchList 里面List<AtomDictAuditRoute> atomDictAuditRoutes = atomDictMapper.selectBath(atomDict.getAtomId());atomDict.setBatchList(atomDictAuditRoutes);if(categoryId != null){List<String> subCategory = atomBusiCategoryMapper.findSubCategory(categoryId);String currentCategoryName = atomBusiCategoryMapper.findCategoryNameByCateforyId(categoryId);taskSetResult.addAll(subCategory);taskSetResult.add(currentCategoryName);String temp = "";for(String item : taskSetResult){temp += ","+item;}String result = temp.substring(1);atomDict.setCategoryName(result);}}}}return PageUtil.toPage(all);}

mapper

    IPage<AtomDict> findAllByAuditRouteNoDuplicateSubstep(@Param("criteria") AtomDictQueryCriteria criteria, Page<Object> page);List<AtomDictAuditRoute> selectBath(@Param("atomId") String atomId);

xml

<!--    关联审核表确定原子已经审核,因为原子审核触发,在原子表没有字段判断是否审核字段修改,才导致要关联审核记录表判断(关键还得去重):和原来区别就是不collection并且去重--><select id="findAllByAuditRouteNoDuplicateSubstep" resultMap="BaseResultMap">select stupid.* from (select row_number() over(partition by tad.ATOM_ID order by tad.CREATE_DATE desc) rn,tatempalte.TEMPLATE_NAME,tabc.CATEGORY_NAME AS CATEGORY_NAME,<include refid="Base_Column_List"/>,<include refid="Dict_Detail_Column_List"/>from T_ATOM_DICT tadleft join T_ATOM_TEMPLATE tatempalte on tatempalte.TEMPLATE_ID = tad.TEMPLATE_IDleft join T_ATOM_BUSI_CATEGORY tabc on tad.CATEGORY_ID = tabc.category_idleft join (select dd.label,dd.value from t_dict d left join t_dict_detail dd on  dd.dict_id = d.dict_idwhere d.name = 'asset_archive_name') dtableon tad.DATABASE = dtable.valueleft join (select dd1.label,dd1.value from t_dict d1 left join t_dict_detail dd1 on  dd1.dict_id = d1.dict_idwhere d1.name = 't_atmo_dict_generation_mechanism') dtable1on tad.DATA_GENE_MODE = dtable1.valueleft join (select dd2.label,dd2.value from t_dict d2 left join t_dict_detail dd2 on  dd2.dict_id = d2.dict_idwhere d2.name = 't_atmo_dict_atomic_library_type') dtable2on tad.LIB_TYPE = dtable2.valueleft join (select dd3.label,dd3.value from t_dict d3 left join t_dict_detail dd3 on  dd3.dict_id = d3.dict_idwhere d3.name = 't_atmo_dict_data_cycle') dtable3on tad.DATA_CYCLE = dtable3.valueleft join (select dd4.label,dd4.value from t_dict d4 left join t_dict_detail dd4 on  dd4.dict_id = d4.dict_idwhere d4.name = 't_atmo_dict_data_type') dtable4on tad.TYPE = dtable4.valueinner join (select ATOM_ID,AUDIT_RESULT,AUDIT_REMARK,ROUTE_ID,AUDIT_ID,AUDIT_DATEfrom T_ATOM_DICT_AUDIT_ROUTE) auditroute on auditroute.ATOM_ID =tad.ATOM_IDleft join t_user tuser on tuser.oper_id = auditroute.AUDIT_ID<where><if test="criteria.atomId != null">and tad.ATOM_ID = #{criteria.atomId}</if><if test="criteria.atomName != null">and tad.ATOM_NAME like concat('%'||#{criteria.atomName},'%')</if></where>) stupidwhere stupid.rn = 1</select><select id="selectBath" resultMap="BatchListResultMap">select <include refid="BathList_Column_List"/>from (select ATOM_ID,AUDIT_RESULT,AUDIT_REMARK,ROUTE_ID,AUDIT_ID,AUDIT_DATEfrom T_ATOM_DICT_AUDIT_ROUTE) auditrouteleft join t_user tuser on tuser.oper_id = auditroute.AUDIT_IDwhere auditroute.ATOM_ID = #{atomId}</select>

解决方法2(设计表时候应该加一个冗余字段)
我仔细想了一下,都是因为T_ATOM_DICT这个原子表缺少一个是否审核字段,(只要触发审核,就修改这个原子是否审核的状态)如果加这个字段就可以直接用collection收集这个原子的审批记录

解决方法3就是我不会的那种,求路过大神指教