企业表 ent_EnterpriseArchives 有id,企业名称 entName
veh_Vehicle 车辆表,有所属企业id companyId,车辆id,车牌号licPlate
目的是查询企业和车辆的树状结果。如下图,然后返回前端。
执行如下sql得到的结果是:【根据车牌号或者企业名称模糊查询】
SELECT ent_EnterpriseArchives.id entId, ent_EnterpriseArchives.entName entName, veh_Vehicle.id vehId, veh_Vehicle.licPlate vehPlate from ent_EnterpriseArchives JOIN veh_Vehicle on ent_EnterpriseArchives.id = veh_Vehicle.companyId where ent_EnterpriseArchives.`status` = 1 and veh_Vehicle.`status` = 1 and ( ent_EnterpriseArchives.entName like '%杭州%' or veh_Vehicle.licPlate like '%杭州%' ) ORDER BY ent_EnterpriseArchives.id
【上述数据结果为造的假数据】
所以要对这个sql的查询结果进行去重公司名称,返回前端树状结果。下面的代码即为处理过程:
public ResponseList enterpriseVehicleTree(String paramName, HttpSession session) { ResponseList response = new ResponseList(); List<Map<String,List<VehVehicleVO>>> resultList = new ArrayList<Map<String,List<VehVehicleVO>>>(); if(paramName == null) { paramName = ConstantUtil.EMPTYSTRING; } try { List<EnterpriseVehicleTreeVO> tempRes = baseMapper.enterpriseVehicleTree(paramName);//上述sql的查询结果 if(tempRes == null || tempRes.size() < ConstantUtil.INTNUM1) { return response; } Integer tempEntId = null; Map<String,List<VehVehicleVO>> tempMap = new HashMap<String, List<VehVehicleVO>>(); List<VehVehicleVO> tempListStr = new ArrayList<VehVehicleVO>(); for (int i = 0 ; i < tempRes.size(); i++) { EnterpriseVehicleTreeVO enterpriseVehicleTreeVO = tempRes.get(i); if(i == ConstantUtil.INTNUM0) { //第一家公司 tempEntId = enterpriseVehicleTreeVO.getEntId(); tempListStr.add( new VehVehicleVO(enterpriseVehicleTreeVO.getVehId(),enterpriseVehicleTreeVO.getVehLicplate())); tempMap.put(enterpriseVehicleTreeVO.getEntName(), tempListStr); if((i+1) == tempRes.size()) { resultList.add(tempMap); break; } }else { //还是同一家公司 if(tempEntId == enterpriseVehicleTreeVO.getEntId()) { tempListStr.add( new VehVehicleVO(enterpriseVehicleTreeVO.getVehId(),enterpriseVehicleTreeVO.getVehLicplate())); tempMap.put(enterpriseVehicleTreeVO.getEntName(), tempListStr); if((i+1) == tempRes.size()) { resultList.add(tempMap); } }else { //新的公司,先处理上一个公司的数据 resultList.add(tempMap); tempMap = null; //注意,这个地方不可以直接调用clear方法去清空,必须重新创建tempMap对象,否则后面的新数据就把之前已经存到list中的上个公司的数据给覆盖掉了 tempMap = new HashMap<String, List<VehVehicleVO>>(); tempListStr = null; //原因同上 tempListStr = new ArrayList<VehVehicleVO>(); tempEntId = enterpriseVehicleTreeVO.getEntId(); tempListStr.add( new VehVehicleVO(enterpriseVehicleTreeVO.getVehId(),enterpriseVehicleTreeVO.getVehLicplate())); tempMap.put(enterpriseVehicleTreeVO.getEntName(), tempListStr); if((i+1) == tempRes.size()) { resultList.add(tempMap); } } } } response.setObj(resultList); } catch (Exception e) { log.info("xxxxxxxxxxxxxxxxxxxxxx异常,==>e.getMessage:" + e.getMessage() + ",==>e.getStackTrace():" + e.getStackTrace()+ ",==>e:" + e); response.setSuccess(false).setMsg("xxxxxxxxxxxxxxxxxxxxxxxxx异常"); } return response; }
最终效果:
{ "success": true, "msg": "", "obj": [ { "杭州科大讯飞": [ { "id": 13, "licPlate": "豫QA3586" }, { "id": 14, "licPlate": "豫QA3585" }, { "id": 12, "licPlate": "豫QA3587" } ] }, { "杭州网易": [ { "id": 8, "licPlate": "浙A36W52" } ] } ] }