关于数据库的优化查询

本来是要关联三张表的查询，现优化成先查两张表，再取出第三张表转换成Map，然后在程序中拼装现在DAO中封装，分别去两个List

public List getAssistanceStudentList(VarifySearch search) {  String sql = "";  sql="select sa,stu from StudentAssistance as sa,Student as stu where sa.stuid = stu.id"   +" and sa.unitguid = '"+search.getSchoolid()   +"' and sa.xn='"+search.getAcadyear()   +"' and sa.xq='"+search.getTerm()      +"' and stu.nj like '"+search.getSection()+"'";  List result = getHibernateTemplate().find(sql);  //System.out.println("!!!!!!!!!!!!!!!getAssistanceStudentList.length:::"+result.size());  if(result == null){   result = new ArrayList();  }  return result; }  public List getStudentClassinfo(VarifySearch search) {  String sql = "";  sql="select cls from ClassInfo as cls where cls.xn='"+search.getAcadyear()   +"' and cls.xq='"+search.getTerm()   +"' and cls.unitguid='"+search.getSchoolid()+"'";  List result = getHibernateTemplate().find(sql);   System.out.println("!!!!!!!!!!!!!!!getAssistanceStudentList.length:::"+result.size());  if(result == null){   result = new ArrayList();  }  return result; } 

然后在Manager中组装起来

public List getStudentAssistanceInfo(VarifySearch search) {  //取学生补助的信息(StudentAssistance,Student)    List assistance = studentAssistanceDao.getAssistanceStudentList(search);  //取得学生的班级信息(ClassInfo)  List classinfo = studentAssistanceDao.getStudentClassinfo(search);  //合并补助信息和班级信息,将取出来的信息组装成dto　list  List all=mergeAssistanceClass(assistance,classinfo);  return  all; } private List mergeAssistanceClass(List assistance, List classinfo) {  List resultList = new ArrayList();  if (assistance == null || assistance.isEmpty()) {   return resultList;  }  int len = assistance.size();  //将List转化成Map  Map classMap = convertToClassMap(classinfo);  for (int i = 0; i < len; i++) {   // 取出对象,与sql中的对象顺序一致   Object[] objects = (Object[]) assistance.get(i);   if(objects == null || objects.length == 0 ){    continue;   }   StudentAssistance sa=(StudentAssistance) objects[0];   Student stu=(Student) objects[1];   ClassInfo ci=(ClassInfo) classMap.get(stu.getBh());   // 进行属性填充   StudentAssistanceDto dto=new StudentAssistanceDto();   //班级   ..........   ...省略.....   ..........      //将dto组装进List   resultList.add(dto);  }  return resultList; }   /**  * 将班级信息list转换为一个Map,其Key就是将来取出时的关键字  *   * @param classinfo  * @return  */ private Map convertToClassMap(List classinfo) {  Map classMap = new HashMap();  if (classinfo == null || classinfo.isEmpty()) {   return classMap;  }  for (int i = 0; i < classinfo.size(); i++) {   ClassInfo cls = (ClassInfo) classinfo.get(i);   classMap.put(cls.getBh(),cls);  }  return classMap; } //判断金额是否为null，进行转化，这个方法很好 private String isNull(Double d){  //金额统计小数后保留两位  DecimalFormat df = new DecimalFormat("######0.00");   String defult="0.00";  if(null==d || d.equals("")){   return defult;  }else{   return String.valueOf(df.format(d.doubleValue()));  } }

以前只知道直接从SQL语句中取得数据，这个程序刚开始就是用的三表连接查询，效率上不高，经过改进后应该能提升不少速度