需求：页面展示单位中同一地市重名的单位信息？
我的思路：
1、利用地市和名称通过groupby、having的形式查出，重复的地市和名称；
public List getRepeatElevatorBySafetyMan(){
return dsl.select(DSL.count(TBL_ORG.ID).as("count"))
.select(TBL_ORG.RESPONSIBLE_MAN.as("safetyMan"))
.select(TBL_ORG.RESPONSIBLE_MAN_PHONE.as("safetyManPhone"))
.from(TBL_ORG)
.where(TBL_ORG.RESPONSIBLE_MAN.isNotNull())
.and(TBL_ORG.RESPONSIBLE_MAN_PHONE.isNotNull())
.groupBy(TBL_ORG.RESPONSIBLE_MAN,TBL_ORG.RESPONSIBLE_MAN_PHONE)
.having("count(emsm.tbl_org.id)>1")
.fetchInto(RepeatElevator.class);
}
2、根据地市和名称反查单位的id，对1的结果进行遍历，得出所有重复的单位id；
public List getRepeatElevatorIdBySafetyMan(String safetyMan,String safetyManPhone){
TblOrg usingOrg = TBL_ORG.as("usingOrg");
return dsl.select(TBL_USER_DEV.ID)
.from(TBL_USER_DEV)
.leftJoin(usingOrg)
.on(usingOrg.ID.eq(TBL_USER_DEV.USING_ORGANIZATION_ID))
.where(usingOrg.RESPONSIBLE_MAN.eq(safetyMan))
.and(usingOrg.RESPONSIBLE_MAN_PHONE.eq(safetyManPhone))
.fetchInto(Long.class);
}
3、根据id查出所有单位的信息，进行页面展示；
public List getRepeatElevatorInfoBySafetyMan(List idList,Pagination pc){
Integer currentPage = pc.getActivePage();
Integer pageSize = pc.getPageSize();
return dsl.select(TBL_USER_DEV.ID.as("id"))
.select(TBL_USER_DEV.USER_DEV_SN.as("userDevSn"))
.select(TBL_USER_DEV.RESCUE_CODE.as("rescueCode"))
.select(TBL_USER_DEV.ADDRESS.as("address"))
.select(TBL_ORG.ORG_NAME.as("usingOrgName"))
.select(TBL_ORG.RESPONSIBLE_MAN.as("safetyMan"))
.select(TBL_ORG.RESPONSIBLE_MAN_PHONE.as("safetyManPhone"))
.from(TBL_USER_DEV)
.leftJoin(TBL_ORG)
.on(TBL_ORG.ID.eq(TBL_USER_DEV.USING_ORGANIZATION_ID))
.where(TBL_USER_DEV.ID.in(idList))
.groupBy(TBL_USER_DEV.ID,TBL_USER_DEV.USER_DEV_SN,TBL_USER_DEV.RESCUE_CODE,TBL_USER_DEV.ADDRESS,TBL_ORG.ORG_NAME,TBL_ORG.RESPONSIBLE_MAN,TBL_ORG.RESPONSIBLE_MAN_PHONE)
.limit(pageSize)
.offset(currentPage * pageSize)
.fetchInto(UserDev.class);
}
主要因为需要进行分页，所有要把所有的id查出来在查询，方便分页。
现在的问题是6万多条数据，耗时超过两分钟才会展现页面，后台对方法耗时进行分析，主要是在第二步遍历添加id的时候耗时比较久，各位有没有什么好的建议和意见？不论是思路或者优化方法，只要可以缩短耗时即可。
List idList=new ArrayList();
List<RepeatElevator> list=userDevService.getRepeatElevatorBySafetyMan();
for(RepeatElevator repeatElevator:list) {
List<Long> partIdList=userDevService.getRepeatElevatorIdBySafetyMan(repeatElevator.getSafetyMan(), repeatElevator.getSafetyManPhone());
idList.addAll(partIdList);
}
List<UserDev> userDevList=userDevService.getRepeatElevatorInfoBySafetyMan(idList, page);
