珠海做网站最好的公司,先申请域名后做网站,品牌策划,能自己做网站吗问题1:不使用collection 聚合分页正确 简单列子 T_ATOM_DICT表有
idname1原子12原子23原子34原子45原子56原子6
T_ATOM_DICT_AUDIT_ROUTE表审核记录表有
idaudit1拒绝1通过4拒绝
我要显示那些原子审核了,我把两个表inner join 就是那些原子审核过了
idnameaudit1原子1拒绝…问题1:不使用collection 聚合分页正确 简单列子 T_ATOM_DICT表有
idname1原子12原子23原子34原子45原子56原子6
T_ATOM_DICT_AUDIT_ROUTE表审核记录表有
idaudit1拒绝1通过4拒绝
我要显示那些原子审核了,我把两个表inner join 就是那些原子审核过了
idnameaudit1原子1拒绝1原子1通过4原子4拒绝 mapper IPageAtomDict findAllByAuditRouteNoDuplicate(Param(criteria) AtomDictQueryCriteria criteria, PageObject page);xml基础的查询类型和返回类型
!-- 分步要用--
resultMap idBaseResultMap typecom.njry.sjzl.busi.domain.AtomDictresult columnATOM_ID propertyatomId/result columnATOM_ID propertyid/result columnCATEGORY_ID propertycategoryId/result columnCATEGORY_NAME propertycategoryName/result columnATOM_NAME propertyatomName/result columnTYPE propertytype/result columnTYPE_NAME propertytypeName /result columnDATA_CYCLE propertydataCycle/result columnDATA_CYCLE_NAME propertydataCycleName/result columnLIB_TYPE propertylibType/
!-- result columnAUDIT_RESULT propertyauditResult/--
!-- result columnAUDIT_REMARK propertyauditRemark/--result columnLIB_TYPE_NAME propertylibTypeName/result columnDATA_GENE_MODE propertydataGeneMode/result columnDATA_GENE_MODE_NAME propertydataGeneModeName/result columnTEMPLATE_ID propertytemplateId/result columnTEMPLATE_NAME propertytemplateName/result columnDATABASE propertydatabase/result columnDATABASE_NAME propertydatabaseName/result columnDATABASE_TABLE propertydatabaseTable/result columnCOLUMN_NAME propertycolumnName/result columnTABLE_LEVEL propertytableLevel/result columnPROC_NAME propertyprocName/result columnIS_HZ propertyisHz/result columnSTATUS propertystatus/result columnATOM_TABLE propertyatomTable/result columnMODE_FLAG propertymodeFlag/result columnCREATE_ID propertycreateId/result columnCREATE_DATE propertycreateDate/result columnREC_ID propertyrecId/result columnREC_DATE propertyrecDate/result columnSH_ID propertyshId/result columnSH_DATE propertyshDate/result columnSH_REMARK propertyshRemark/result columnDEL_ID propertydelId/result columnDEL_DATE propertydelDate/result columnSTART_DATE propertystartDate/result columnEND_DATE propertyendDate/result columnUNIT propertyunit/result columnDIMEN_FLAG propertydimenFlag//resultMapresultMap idBaseResultMapBoss typecom.njry.sjzl.busi.domain.AtomDictresult columnATOM_ID propertyatomId/result columnATOM_ID propertyid/result columnCATEGORY_ID propertycategoryId/result columnCATEGORY_NAME propertycategoryName/result columnATOM_NAME propertyatomName/result columnTYPE propertytype/result columnTYPE_NAME propertytypeName /result columnDATA_CYCLE propertydataCycle/result columnDATA_CYCLE_NAME propertydataCycleName/result columnLIB_TYPE propertylibType/result columnLIB_TYPE_NAME propertylibTypeName/result columnDATA_GENE_MODE propertydataGeneMode/result columnDATA_GENE_MODE_NAME propertydataGeneModeName/result columnTEMPLATE_ID propertytemplateId/result columnTEMPLATE_NAME propertytemplateName/result columnDATABASE propertydatabase/result columnDATABASE_NAME propertydatabaseName/result columnDATABASE_TABLE propertydatabaseTable/result columnCOLUMN_NAME propertycolumnName/result columnTABLE_LEVEL propertytableLevel/result columnPROC_NAME propertyprocName/result columnIS_HZ propertyisHz/result columnSTATUS propertystatus/result columnATOM_TABLE propertyatomTable/result columnMODE_FLAG propertymodeFlag/result columnCREATE_ID propertycreateId/result columnCREATE_DATE propertycreateDate/result columnREC_ID propertyrecId/result columnREC_DATE propertyrecDate/result columnSH_ID propertyshId/result columnSH_DATE propertyshDate/result columnSH_REMARK propertyshRemark/result columnDEL_ID propertydelId/result columnDEL_DATE propertydelDate/result columnSTART_DATE propertystartDate/result columnEND_DATE propertyendDate/result columnUNIT propertyunit/result columnDIMEN_FLAG propertydimenFlag//resultMap
!-- collection propertybatchList resultMapBatchListResultMap/--
!-- 审核表数据还得关联用户表显示用户名--resultMap idBatchListResultMap typecom.njry.sjzl.busi.domain.AtomDictAuditRouteresult columnROUTE_ID propertyrouteId/result columnROUTE_ID propertyid/result columnATOM_IDREPEAT propertyatomId/result columnAUDIT_RESULT propertyauditResult/result columnAUDIT_REMARK propertyauditRemark/result columnAUDIT_ID propertyauditId/result columnAUDIT_NAME propertyauditName/result columnAUDIT_DATE propertyauditDate//resultMapsql idBathList_Column_Listauditroute.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/sqlsql idBase_Column_Listtad.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 idDict_Detail_Column_Listdtable.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 idfindAllByAuditRouteNoDuplicate resultMapBaseResultMapBossselect tatempalte.TEMPLATE_NAME,tabc.CATEGORY_NAME AS CATEGORY_NAME,include refidBase_Column_List/,include refidDict_Detail_Column_List/,include refidBathList_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_IDwhereif testcriteria.atomId ! nulland tad.ATOM_ID #{criteria.atomId}/ifif testcriteria.atomName ! nulland tad.ATOM_NAME like concat(%||#{criteria.atomName},%)/if/whereorder by tad.CREATE_DATE desc, auditroute.AUDIT_DATE asc/select问题2:使用collection 聚合出现分页问题 xml基础的查询类型和返回类型 里面把注释的
方法返回类型 id “BaseResultMapBoss” 里 解决办法1(我最蠢的解决办法,把sql分解) 不知道怎么自定义分页解决这种(不会), 我只能把这一个sql语句分开
在impl层 Overridepublic PageResultAtomDict queryAllByAuditRouteDuplicate(AtomDictQueryCriteria criteria, PageObject page){// 去重 inner join(原来一个sql就该搞定的)
// IPageAtomDict all atomDictMapper.findAllByAuditRouteNoDuplicate(criteria, page);
// 去重 inner join(分步1)IPageAtomDict all atomDictMapper.findAllByAuditRouteNoDuplicateSubstep(criteria, page);ListAtomDict 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){SetString taskSetResult new LinkedHashSet();Long categoryId atomDict.getCategoryId();
// 这里拿到第一步去重的审核原子再去查审核记录,放在返回前端的BatchList 里面ListAtomDictAuditRoute atomDictAuditRoutes atomDictMapper.selectBath(atomDict.getAtomId());atomDict.setBatchList(atomDictAuditRoutes);if(categoryId ! null){ListString 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 IPageAtomDict findAllByAuditRouteNoDuplicateSubstep(Param(criteria) AtomDictQueryCriteria criteria, PageObject page);ListAtomDictAuditRoute selectBath(Param(atomId) String atomId);xml
!-- 关联审核表确定原子已经审核,因为原子审核触发,在原子表没有字段判断是否审核字段修改,才导致要关联审核记录表判断(关键还得去重):和原来区别就是不collection并且去重--select idfindAllByAuditRouteNoDuplicateSubstep resultMapBaseResultMapselect 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 refidBase_Column_List/,include refidDict_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_IDwhereif testcriteria.atomId ! nulland tad.ATOM_ID #{criteria.atomId}/ifif testcriteria.atomName ! nulland tad.ATOM_NAME like concat(%||#{criteria.atomName},%)/if/where) stupidwhere stupid.rn 1/selectselect idselectBath resultMapBatchListResultMapselect include refidBathList_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就是我不会的那种,求路过大神指教
