public List<TdDepartment> createZtreeDep(String compId) { List<TdDepartment> dd = new ArrayList<TdDepartment>(); //查找所有子部门的sql语句
String sql = "select dep_id,dep_name,super_id,folder from td_department "+ "start with super_id in ( "+ "select dep_id from td_department "+ "where valid_flag = 'Y' and comp_id = '"+compId+"')connect by prior dep_id = super_id";
//查找部门本身的sql语句 String parentsql = "select dep_id ,dep_name,super_id,folder from td_department "+ "where valid_flag = 'Y' and comp_id = '"+compId+"'";
EpDB db = new EpDB(); ArrayList<HashMap> parentdeparts = db.getHashData(parentsql); ArrayList<HashMap> departs = db.getHashData(sql); if(parentdeparts == null || parentdeparts.size() <= 0) return null;
//将部门加入到list中 for(int i=0; i<parentdeparts.size(); i++){ String dep_id = parentdeparts.get(i).get("DEP_ID").toString(); String dep_name = parentdeparts.get(i).get("DEP_NAME").toString(); String super_id = parentdeparts.get(i).get("SUPER_ID").toString(); TdDepartment td = new TdDepartment(); td.setId(dep_id); td.setPid(super_id); td.setName(dep_name); if(parentdeparts.get(i).get("FOLDER") != null){ String folder = parentdeparts.get(i).get("FOLDER").toString(); if("Y".equals(folder)){ td.setOpen("true");//非叶子结点则需要展开 }else{ td.setOpen("false"); } } //取消循环读取 //td.setChild(createDepartments(dep_id)); dd.add(td); } //将子部门加入到list中 for(int i=0; i<departs.size(); i++){ String dep_id = departs.get(i).get("DEP_ID").toString(); String dep_name = departs.get(i).get("DEP_NAME").toString(); String super_id = departs.get(i).get("SUPER_ID").toString(); TdDepartment td = new TdDepartment(); td.setId(dep_id); td.setPid(super_id); td.setName(dep_name); if(departs.get(i).get("FOLDER") != null){ String folder = departs.get(i).get("FOLDER").toString(); if("Y".equals(folder)){ td.setOpen("true");//非叶子结点则需要展开 }else{ td.setOpen("false"); } } //取消循环读取 //td.setChild(createDepartments(dep_id)); dd.add(td); } return dd; }
需要优化点:
思考一:通过sql语句的优化来优化
思考二:通过程序递归的方法来优化
2015-9-14
解决结果:
通过sql语句的优化来优化,重点分析:
select dep_id as id ,dep_name as name,super_id as pid,folder from td_department
start with dep_id in
(
select dep_id from td_department
where valid_flag = 'Y' and comp_id = '46'
)
connect by super_id= prior dep_id
1 public List<TdDepartment> createZtreeDep(String compId) { 2 3 List<TdDepartment> dd = new ArrayList<TdDepartment>(); 4 String sql = "select dep_id,dep_name,super_id,folder from td_department "+ 5 "start with dep_id in ( "+ 6 "select dep_id from td_department "+ 7 "where valid_flag = 'Y' and comp_id = '"+compId+"')connect by super_id= prior dep_id"; 8 System.out.println("sql="+sql); 9 EpDB db = new EpDB(); 10 ArrayList<HashMap> departs = db.getHashData(sql); 11 if(departs == null || departs.size() <= 0) 12 return null; 13 System.out.println("获取到的部门List"+dd); 14 for(int i=0; i<departs.size(); i++){ 15 String dep_id = departs.get(i).get("DEP_ID").toString(); 16 String dep_name = departs.get(i).get("DEP_NAME").toString(); 17 String super_id = departs.get(i).get("SUPER_ID").toString(); 18 TdDepartment td = new TdDepartment(); 19 td.setId(dep_id); 20 td.setPid(super_id); 21 td.setName(dep_name); 22 if(departs.get(i).get("FOLDER") != null){ 23 String folder = departs.get(i).get("FOLDER").toString(); 24 if("Y".equals(folder)){ 25 td.setOpen("true");//非叶子结点则需要展开 26 }else{ 27 td.setOpen("false"); 28 } 29 } 30 //取消循环读取 31 //td.setChild(createDepartments(dep_id)); 32 dd.add(td); 33 } 34 35 return dd; 36 }