关于数据库的优化查询

本来是要关联三张表的查询，现优化成先查两张表，再取出第三张表转换成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语句中取得数据，这个程序刚开始就是用的三表连接查询，效率上不高，经过改进后应该能提升不少速度