dbconnmanager.java

来自「Java课程项目—学生管理系统是北航软件学院2005级大三上学期的选修课期末大作」· Java 代码 · 共 1,541 行 · 第 1/5 页

               "FROM stugrade_352112,stuinf_352112 "+
               "where composite between ? and ? and stuinf_352112.stu_id = stugrade_352112.stu_id";
         else {
           sqlquery =
               "SELECT stugrade_352112.stu_id,stuinf_352112.name,total,stugrade_352112.class " +
               "FROM stugrade_352112,stuinf_352112 " +
               "where total between ? and ? and stuinf_352112.stu_id = stugrade_352112.stu_id";
         }
       }
       else if (aclass.equals("352113")) {
         if (grade.equals("chinese"))
           sqlquery =
               "SELECT stugrade_352113.stu_id,stuinf_352113.name,chinese,stugrade_352113.class " +
               "FROM stugrade_352113,stuinf_352113 " +
               "where chinese between ? and ? and stuinf_352113.stu_id = stugrade_352113.stu_id";
         else if (grade.equals("math"))
           sqlquery =
               "SELECT stugrade_352113.stu_id,stuinf_352113.name,math,stugrade_352113.class " +
               "FROM stugrade_352113,stuinf_352113 " +
               "where math between ? and ? and stuinf_352113.stu_id = stugrade_352113.stu_id";
         else if (grade.equals("english"))
           sqlquery =
               "SELECT stugrade_352113.stu_id,stuinf_352113.name,english,stugrade_352113.class " +
               "FROM stugrade_352113,stuinf_352113 " +
               "where english between ? and ? and stuinf_352113.stu_id = stugrade_352113.stu_id";
         else if (grade.equals("composite"))
           sqlquery =
               "SELECT stugrade_352113.stu_id,stuinf_352113.name,composite,stugrade_352113.class " +
               "FROM stugrade_352113,stuinf_352113 " +
               "where composite between ? and ? and stuinf_352113.stu_id = stugrade_352113.stu_id";
         else {
           sqlquery =
               "SELECT stugrade_352113.stu_id,stuinf_352113.name,total,stugrade_352113.class " +
               "FROM stugrade_352113,stuinf_352113 " +
               "where total between ? and ? and stuinf_352113.stu_id = stugrade_352113.stu_id";
         }
       }
       else        if (aclass.equals("352114")) {
           if (grade.equals("chinese"))
               sqlquery =
                   "SELECT stugrade_352114.stu_id,stuinf_352114.name,chinese,stugrade_352114.class " +
                   "FROM stugrade_352114,stuinf_352114 " +
                   "where chinese between ? and ? and stuinf_352114.stu_id = stugrade_352114.stu_id";
             else if (grade.equals("math"))
               sqlquery =
                   "SELECT stugrade_352114.stu_id,stuinf_352114.name,math,stugrade_352114.class " +
                   "FROM stugrade_352114,stuinf_352114 " +
                   "where math between ? and ? and stuinf_352114.stu_id = stugrade_352114.stu_id";
             else if (grade.equals("english"))
               sqlquery =
                   "SELECT stugrade_352114.stu_id,stuinf_352114.name,english,stugrade_352114.class " +
                   "FROM stugrade_352114,stuinf_352114 " +
                   "where english between ? and ? and stuinf_352114.stu_id = stugrade_352114.stu_id";
             else if (grade.equals("composite"))
               sqlquery =
                   "SELECT stugrade_352114.stu_id,stuinf_352114.name,composite,stugrade_352114.class " +
                   "FROM stugrade_352114,stuinf_352114 " +
                   "where composite between ? and ? and stuinf_352114.stu_id = stugrade_352114.stu_id";
             else {
               sqlquery =
                   "SELECT stugrade_352114.stu_id,stuinf_352114.name,total,stugrade_352114.class " +
                   "FROM stugrade_352114,stuinf_352114 " +
                   "where total between ? and ? and stuinf_352114.stu_id = stugrade_352114.stu_id";
             }
           }
           else if (aclass.equals("352115")) {
             if (grade.equals("chinese"))
               sqlquery =
                   "SELECT stugrade_352115.stu_id,stuinf_352115.name,chinese,stugrade_352115.class " +
                   "FROM stugrade_352115,stuinf_352115 " +
                   "where chinese between ? and ? and stuinf_352115.stu_id = stugrade_352115.stu_id";
             else if (grade.equals("math"))
               sqlquery =
                   "SELECT stugrade_352115.stu_id,stuinf_352115.name,math,stugrade_352115.class " +
                   "FROM stugrade_352115,stuinf_352115 " +
                   "where math between ? and ? and stuinf_352115.stu_id = stugrade_352115.stu_id";
             else if (grade.equals("english"))
               sqlquery =
                   "SELECT stugrade_352115.stu_id,stuinf_352115.name,english,stugrade_352115.class " +
                   "FROM stugrade_352115,stuinf_352115 " +
                   "where english between ? and ? and stuinf_352115.stu_id = stugrade_352115.stu_id";
             else if (grade.equals("composite"))
               sqlquery =
                   "SELECT stugrade_352115.stu_id,stuinf_352115.name,composite,stugrade_352115.class " +
                   "FROM stugrade_352115,stuinf_352115 "+
                   "where composite between ? and ? and stuinf_352115.stu_id = stugrade_352115.stu_id";
             else {
               sqlquery =
                   "SELECT stugrade_352115.stu_id,stuinf_352115.name,total,stugrade_352115.class " +
                   "FROM stugrade_352115,stuinf_352115 " +
                   "where total between ? and ? and stuinf_352115.stu_id = stugrade_352115.stu_id";
             }
           }
           else if (aclass.equals("352116")) {
             if (grade.equals("chinese"))
               sqlquery =
                   "SELECT stugrade_352116.stu_id,stuinf_352116.name,chinese,stugrade_352116.class " +
                   "FROM stugrade_352116,stuinf_352116 " +
                   "where chinese between ? and ? and stuinf_352116.stu_id = stugrade_352116.stu_id";
             else if (grade.equals("math"))
               sqlquery =
                   "SELECT stugrade_352116.stu_id,stuinf_352116.name,math,stugrade_352116.class " +
                   "FROM stugrade_352116,stuinf_352116 " +
                   "where math between ? and ? and stuinf_352116.stu_id = stugrade_352116.stu_id";
             else if (grade.equals("english"))
               sqlquery =
                   "SELECT stugrade_352116.stu_id,stuinf_352116.name,english,stugrade_352116.class " +
                   "FROM stugrade_352116,stuinf_352116 " +
                   "where english between ? and ? and stuinf_352116.stu_id = stugrade_352116.stu_id";
             else if (grade.equals("composite"))
               sqlquery =
                   "SELECT stugrade_352116.stu_id,stuinf_352116.name,composite,stugrade_352116.class " +
                   "FROM stugrade_352116,stuinf_352116 " +
                   "where composite between ? and ? and stuinf_352116.stu_id = stugrade_352116.stu_id";
             else {
               sqlquery =
                   "SELECT stugrade_352116.stu_id,stuinf_352116.name,total,stugrade_352116.class " +
                   "FROM stugrade_352116,stuinf_352116 " +
                   "where total between ? and ? and stuinf_352116.stu_id = stugrade_352116.stu_id";
             }
           }
     }
     con = getConnection("Access");
     ps = con.prepareStatement(sqlquery);
     if (aschool.equals("全院"))
     {
       ps.setFloat(1, first);
       ps.setFloat(2, last);
       ps.setFloat(3, first);
       ps.setFloat(4, last);
       ps.setFloat(5, first);
       ps.setFloat(6, last);
     }else{
       ps.setFloat(1, first);
       ps.setFloat(2, last);
     }
     rs = ps.executeQuery();
     while (rs.next()) {
       resultdate.add(rs.getString("stu_id"));
       resultdate.add(rs.getString("name"));
       resultdate.add(rs.getString(grade));
       resultdate.add(rs.getString("class"));
     }
   }
   catch (SQLException e) {
     e.printStackTrace();
   }
   finally {
     if (rs != null)try {
       rs.close();
     }
     catch (SQLException ignore) {}
     if (ps != null)try {
       ps.close();
     }
     catch (SQLException ignore) {}
     if (con != null)try {
       con.close();
     }
     catch (SQLException ignore) {}
     finally {
       releaseConnection("Access", con);
     }
   }
   return resultdate;
 }

//学生统计
 public Vector getpeopleSum(String aclass, String aschool, String sumstr,String aethnic, String range1, String range2) {
   Connection con = null;
   PreparedStatement ps = null;
   ResultSet rs = null;
   Vector resultdate = new Vector();
   String sqlquery = null;
   try {
     //全院学生统计
     if (aschool.equals("全院")){
       if (sumstr.equals("男"))
         sqlquery =
             "SELECT stu_id,name,sex,class FROM stuinf_352111 " +
             "where sex = '男' " +
             "UNION SELECT stu_id,name,sex,class FROM stuinf_352112 "+
             "where sex = '男' " +
             "UNION SELECT stu_id,name,sex,class FROM stuinf_352113 "+
             "where sex = '男'"+
             "UNION SELECT stu_id,name,sex,class FROM stuinf_352114 "+
             "where sex = '男' " +
             "UNION SELECT stu_id,name,sex,class FROM stuinf_352115 "+
             "where sex = '男'"+
             "UNION SELECT stu_id,name,sex,class FROM stuinf_352116 "+
             "where sex = '男' ";
       else if (sumstr.equals("女"))
         sqlquery =
             "SELECT stu_id,name,sex,class FROM stuinf_352111 " +
             "where sex = '女' " +
             "UNION SELECT stu_id,name,sex,class FROM stuinf_352112 "+
             "where sex = '女' " +
             "UNION SELECT stu_id,name,sex,class FROM stuinf_352113 "+
             "where sex = '女'"+
             "UNION SELECT stu_id,name,sex,class FROM stuinf_352114 "+
             "where sex = '女' " +
             "UNION SELECT stu_id,name,sex,class FROM stuinf_352115 "+
             "where sex = '女'"+
             "UNION SELECT stu_id,name,sex,class FROM stuinf_352116 "+
             "where sex = '女' ";
       else if (sumstr.equals("民族"))
         sqlquery =
             "SELECT stu_id,name,ethnic,class FROM stuinf_352111 " +
             "where ethnic LIKE '"+aethnic+"' "+
             " UNION SELECT stu_id,name,ethnic,class FROM stuinf_352112 "+
             "where ethnic LIKE '"+aethnic+"' "+
             "UNION SELECT stu_id,name,ethnic,class FROM stuinf_352113 "+
             "where ethnic LIKE '"+aethnic+"' "+
             " UNION SELECT stu_id,name,ethnic,class FROM stuinf_352114 "+
             "where ethnic LIKE '"+aethnic+"' "+
             "UNION SELECT stu_id,name,ethnic,class FROM stuinf_352115 "+
             "where ethnic LIKE '"+aethnic+"' "+
             " UNION SELECT stu_id,name,ethnic,class FROM stuinf_352116 "+
             "where ethnic LIKE '"+aethnic+"' ";
       else if (sumstr.equals("出生日期"))
         sqlquery =
             "SELECT stu_id,name,birthday,class FROM stuinf_352111 " +
             "where birthday between "+range1+" and "+range2+
             " order by birthday ASC "+
             " UNION SELECT stu_id,name,birthday,class FROM stuinf_352112 "+
             "where birthday between "+range1+" and "+range2+
             " order by birthday ASC "+
             " UNION SELECT stu_id,name,birthday,class FROM stuinf_352113 "+
             "where birthday between "+range1+ " and "+range2+" order by birthday ASC"+
             " UNION SELECT stu_id,name,birthday,class FROM stuinf_352114 "+
             "where birthday between "+range1+" and "+range2+
             " order by birthday ASC "+
             " UNION SELECT stu_id,name,birthday,class FROM stuinf_352115 "+
             "where birthday between "+range1+" and "+range2+
             " order by birthday ASC "+
             " UNION SELECT stu_id,name,birthday,class FROM stuinf_352116 "+
             "where birthday between "+range1+" and "+range2+
             " order by birthday ASC ";
     }
     if (!aclass.equals("")) { //班级成绩统计
       if (aclass.equals("352111")) {
         if (sumstr.equals("男"))
           sqlquery =
               "SELECT stu_id,name,sex,class FROM stuinf_352111 " +
               "where sex = '男' ";
         else if (sumstr.equals("女"))
           sqlquery =
               "SELECT stu_id,name,sex,class FROM stuinf_352111 " +
               "where sex = '女' ";
         else if (sumstr.equals("民族"))
           sqlquery =
               "SELECT stu_id,name,ethnic,class FROM stuinf_352111 " +
               "where ethnic LIKE '"+aethnic+"'";
         else if (sumstr.equals("出生日期"))
           sqlquery =
               "SELECT stu_id,name,birthday,class FROM stuinf_352111 " +
               "where birthday between " +range1+ " and "+range2+" order by birthday ASC";
       }
       else if (aclass.equals("352112")) {
         if (sumstr.equals("男"))
           sqlquery =
               "SELECT stu_id,name,sex,class FROM stuinf_352112 " +
               "where sex = '男' ";
         else if (sumstr.equals("女"))
           sqlquery =
               "SELECT stu_id,name,sex,class FROM stuinf_352112 " +
               "where sex = '女' ";
         else if (sumstr.equals("民族"))
           sqlquery =
               "SELECT stu_id,name,ethnic,class FROM stuinf_352112 " +
               "where ethnic LIKE '"+aethnic+"'";
         else if (sumstr.equals("出生日期"))
           sqlquery =
               "SELECT stu_id,name,birthday,class FROM stuinf_352112 " +
               "where birthday between "+range1+" and "+range2+
               " order by birthday ASC";
       }
       else if (aclass.equals("352113")) {
         if (sumstr.equals("男"))
           sqlquery =
               "SELECT stu_id,name,sex,class FROM stuinf_352113 " +
               "where sex = '男' ";
         else if (sumstr.equals("女"))
           sqlquery =
               "SELECT stu_id,name,sex,class FROM stuinf_352113 " +
               "where sex = '女' ";
         else if (sumstr.equals("民族"))
           sqlquery =
               "SELECT stu_id,name,ethnic,class FROM stuinf_352113 " +
               "where ethnic LIKE '"+aethnic+"'";
         else if (sumstr.equals("出生日期"))
           sqlquery =
               "SELECT stu_id,name,birthday,class FROM stuinf_352113 " +
               "where birthday between "+range1+" and "+range2+
               " order by birthday ASC";
       }
       else        if (aclass.equals("352114")) {
           if (sumstr.equals("男"))
               sqlquery =
                   "SELECT stu_id,name,sex,class FROM stuinf_352114 " +
                   "where sex = '男' ";
             else if (sumstr.equals("女"))
               sqlquery =
                   "SELECT stu_id,name,sex,class FROM stuinf_352114 " +