dbconnmanager.java

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

      con = getConnection("Access");
      statement = con.createStatement();
      rs = statement.executeQuery(sqlquery);
      while (rs.next()) {
        result.add(rs.getString("stu_id"));
        result.add(rs.getString("name"));
        result.add(rs.getString("class"));
        result.add(rs.getString("sex"));
        result.add(rs.getString("birthday"));
        result.add(rs.getString("ethnic"));
        result.add(rs.getString("nativeplace"));
      }
    }
    catch (SQLException e) {
      e.printStackTrace();
    }
    finally {
      if (rs != null)try {
        rs.close();
      }
      catch (SQLException ignore) {}
      if (statement != null)try {
        statement.close();
      }
      catch (SQLException ignore) {}
      if (con != null)try {
        con.close();
      }
      catch (SQLException ignore) {}
      finally {
        releaseConnection("Access", con);
      }
    }
    return result;
  }

  //查询学生成绩信息
 public Vector stuGradeQuery( int astuid, String aclass ) {
   Connection con = null;
   ResultSet rs = null;
   Statement statement = null;
   String sqlquery = null;
   Vector result = new Vector();
   String sstuid = "";

   try {
     if (astuid != 0) {
       String stuid = String.valueOf(astuid);
       sstuid = stuid.substring(0, 6);
       if (sstuid.equals("352111"))
         sqlquery =
             "select stugrade_352111.stu_id, name, chinese, math, english, " +
             "composite, total, stuinf_352111.class from stuinf_352111,stugrade_352111 " +
             "where stuinf_352111.stu_id = stugrade_352111.stu_id " +
             "and stugrade_352111.stu_id =" + astuid;
       else if (sstuid.equals("352112"))
         sqlquery =
             "select stugrade_352112.stu_id, name, chinese, math, english, " +
             "composite, total, stuinf_352112.class from stuinf_352112,stugrade_352112 " +
             "where stuinf_352112.stu_id = stugrade_352112.stu_id " +
             "and stugrade_352112.stu_id =" + astuid;
       else if (sstuid.equals("352113"))
    	 sqlquery =
           "select stugrade_352113.stu_id, name, chinese, math, english, " +
           "composite, total, stuinf_352113.class from stuinf_352113,stugrade_352113 " +
           "where stuinf_352113.stu_id = stugrade_352113.stu_id " +
           "and stugrade_352113.stu_id =" + astuid;
       else if (sstuid.equals("352114"))
           sqlquery =
               "select stugrade_352114.stu_id, name, chinese, math, english, " +
               "composite, total, stuinf_352114.class from stuinf_352114,stugrade_352114 " +
               "where stuinf_352114.stu_id = stugrade_352114.stu_id " +
               "and stugrade_352114.stu_id =" + astuid;
         else if (sstuid.equals("352115"))
           sqlquery =
               "select stugrade_352115.stu_id, name, chinese, math, english, " +
               "composite, total, stuinf_352115.class from stuinf_352115,stugrade_352115 " +
               "where stuinf_352115.stu_id = stugrade_352115.stu_id " +
               "and stugrade_352115.stu_id =" + astuid;
         else if (sstuid.equals("352116"))
      	 sqlquery =
             "select stugrade_352116.stu_id, name, chinese, math, english, " +
             "composite, total, stuinf_352116.class from stuinf_352116,stugrade_352116 " +
             "where stuinf_352116.stu_id = stugrade_352116.stu_id " +
             "and stugrade_352116.stu_id =" + astuid;
     }
     else if (!aclass.equals("")) {
       if (aclass.equals("352111")) sqlquery =
           "select stugrade_352111.stu_id, name, chinese, math, english, " +
           "composite, total, stuinf_352111.class from stuinf_352111,stugrade_352111 " +
           "where stuinf_352111.stu_id = stugrade_352111.stu_id ";
       else if (aclass.equals("352112")) sqlquery =
           "select stugrade_352112.stu_id, name, chinese, math, english, " +
           "composite, total, stuinf_352112.class from stuinf_352112,stugrade_352112 " +
           "where stuinf_352112.stu_id = stugrade_352112.stu_id ";
       else if (aclass.equals("352113")) sqlquery =
           "select stugrade_352113.stu_id, name, chinese, math, english, " +
           "composite, total, stuinf_352113.class from stuinf_352113,stugrade_352113 " +
           "where stuinf_352113.stu_id = stugrade_352113.stu_id ";
       else if (aclass.equals("352114")) sqlquery =
           "select stugrade_352114.stu_id, name, chinese, math, english, " +
           "composite, total, stuinf_352114.class from stuinf_352114,stugrade_352114 " +
           "where stuinf_352114.stu_id = stugrade_352114.stu_id ";
       else if (aclass.equals("352115")) sqlquery =
           "select stugrade_352115.stu_id, name, chinese, math, english, " +
           "composite, total, stuinf_352115.class from stuinf_352115,stugrade_352115 " +
           "where stuinf_352115.stu_id = stugrade_352115.stu_id ";
       else if (aclass.equals("352116")) sqlquery =
           "select stugrade_352116.stu_id, name, chinese, math, english, " +
           "composite, total, stuinf_352116.class from stuinf_352116,stugrade_352116 " +
           "where stuinf_352116.stu_id = stugrade_352116.stu_id ";
     }
     con = getConnection("Access");
     statement = con.createStatement();
     rs = statement.executeQuery(sqlquery);
     while (rs.next()) {
       result.add(rs.getString("stu_id"));
       result.add(rs.getString("name"));
       result.add(rs.getString("chinese"));
       result.add(rs.getString("math"));
       result.add(rs.getString("english"));
       result.add(rs.getString("composite"));
       result.add(rs.getString("total"));
       result.add(rs.getString("class"));
     }
   }
   catch (SQLException e) {
     e.printStackTrace();
   }
   finally {
     if (rs != null)try {
       rs.close();
     }
     catch (SQLException ignore) {}
     if (statement != null)try {
       statement.close();
     }
     catch (SQLException ignore) {}
     if (con != null)try {
       con.close();
     }
     catch (SQLException ignore) {}
     finally {
       releaseConnection("Access", con);
     }
   }
   return result;
 }

//学生成绩统计
 public Vector getSum(String aclass, String aschool, String grade, float first, float last) {
   Connection con = null;
   PreparedStatement ps = null;
   ResultSet rs = null;
   Vector resultdate = new Vector();
   String sqlquery = null;
   try {
     //全院成绩统计
     if (!aschool.equals("")) {
       if (grade.equals("chinese"))
         sqlquery =
             "SELECT stugrade_352111.stu_id,stuinf_352111.name,chinese,stugrade_352111.class " +
             "FROM stugrade_352111,stuinf_352111 " +
             "where chinese between ? and ? and stuinf_352111.stu_id = stugrade_352111.stu_id " +
             "UNION SELECT stugrade_352112.stu_id,stuinf_352112.name,chinese,stuinf_352112.class " +
             "FROM stugrade_352112,stuinf_352112 " +
             "where chinese between ? and ? and stuinf_352112.stu_id = stugrade_352112.stu_id " +
             "UNION SELECT stugrade_352113.stu_id,stuinf_352113.name,chinese,stuinf_352113.class " +
             "FROM stugrade_352113,stuinf_352113 where chinese " +
             "between ? and ? and stuinf_352113.stu_id = stugrade_352113.stu_id"+
             "UNION SELECT stugrade_352114.stu_id,stuinf_352114.name,chinese,stuinf_352114.class " +
             "FROM stugrade_352114,stuinf_352114 " +
             "where chinese between ? and ? and stuinf_352114.stu_id = stugrade_352114.stu_id " +
             "UNION SELECT stugrade_352115.stu_id,stuinf_352115.name,chinese,stuinf_352115.class " +
             "FROM stugrade_352115,stuinf_352115 " +
             "where chinese between ? and ? and stuinf_352115.stu_id = stugrade_352115.stu_id " +
             "UNION SELECT stugrade_352116.stu_id,stuinf_352116.name,chinese,stuinf_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_352111.stu_id,stuinf_352111.name,math,stugrade_352111.class " +
             "FROM stugrade_352111,stuinf_352111 " +
             "where math between ? and ? and stuinf_352111.stu_id = stugrade_352111.stu_id " +
             "UNION SELECT stugrade_352112.stu_id,stuinf_352112.name,math,stuinf_352112.class " +
             "FROM stugrade_352112,stuinf_352112 " +
             "where math between ? and ? and stuinf_352112.stu_id = stugrade_352112.stu_id " +
             "UNION SELECT stugrade_352113.stu_id,stuinf_352113.name,math,stuinf_352113.class " +
             "FROM stugrade_352113,stuinf_352113 where math " +
             "between ? and ? and stuinf_352113.stu_id = stugrade_352113.stu_id"+
             "UNION SELECT stugrade_352114.stu_id,stuinf_352114.name,math,stuinf_352114.class " +
             "FROM stugrade_352114,stuinf_352114 " +
             "where math between ? and ? and stuinf_352114.stu_id = stugrade_352114.stu_id " +
             "UNION SELECT stugrade_352115.stu_id,stuinf_352115.name,math,stuinf_352115.class " +
             "FROM stugrade_352115,stuinf_352115 " +
             "where math between ? and ? and stuinf_352115.stu_id = stugrade_352115.stu_id " +
             "UNION SELECT stugrade_352116.stu_id,stuinf_352116.name,math,stuinf_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_352111.stu_id,stuinf_352111.name,english,stugrade_352111.class " +
             "FROM stugrade_352111,stuinf_352111 " +
             "where english between ? and ? and stuinf_352111.stu_id = stugrade_352111.stu_id " +
             "UNION SELECT stugrade_352112.stu_id,stuinf_352112.name,english,stuinf_352112.class " +
             "FROM stugrade_352112,stuinf_352112 " +
             "where english between ? and ? and stuinf_352112.stu_id = stugrade_352112.stu_id " +
             "UNION SELECT stugrade_352113.stu_id,stuinf_352113.name,english,stuinf_352113.class " +
             "FROM stugrade_352113,stuinf_352113 where english " +
             "between ? and ? and stuinf_352113.stu_id = stugrade_352113.stu_id"+
             "UNION SELECT stugrade_352114.stu_id,stuinf_352114.name,english,stuinf_352114.class " +
             "FROM stugrade_352114,stuinf_352114 " +
             "where english between ? and ? and stuinf_352114.stu_id = stugrade_352114.stu_id " +
             "UNION SELECT stugrade_352115.stu_id,stuinf_352115.name,english,stuinf_352115.class " +
             "FROM stugrade_352115,stuinf_352115 " +
             "where english between ? and ? and stuinf_352115.stu_id = stugrade_352115.stu_id " +
             "UNION SELECT stugrade_352116.stu_id,stuinf_352116.name,english,stuinf_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_352111.stu_id,stuinf_352111.name,composite,stugrade_352111.class " +
             "FROM stugrade_352111,stuinf_352111 " +
             "where composite between ? and ? and stuinf_352111.stu_id = stugrade_352111.stu_id " +
             "UNION SELECT stugrade_352112.stu_id,stuinf_352112.name,composite,stuinf_352112.class " +
             "FROM stugrade_352112,stuinf_352112 " +
             "where composite between ? and ? and stuinf_352112.stu_id = stugrade_352112.stu_id " +
             "UNION SELECT stugrade_352113.stu_id,stuinf_352113.name,composite,stuinf_352113.class " +
             "FROM stugrade_352113,stuinf_352113 where composite " +
             "between ? and ? and stuinf_352113.stu_id = stugrade_352113.stu_id"+
             "UNION SELECT stugrade_352114.stu_id,stuinf_352114.name,composite,stuinf_352114.class " +
             "FROM stugrade_352114,stuinf_352114 " +
             "where composite between ? and ? and stuinf_352114.stu_id = stugrade_352114.stu_id " +
             "UNION SELECT stugrade_352115.stu_id,stuinf_352115.name,composite,stuinf_352115.class " +
             "FROM stugrade_352115,stuinf_352115 " +
             "where composite between ? and ? and stuinf_352115.stu_id = stugrade_352115.stu_id " +
             "UNION SELECT stugrade_352116.stu_id,stuinf_352116.name,composite,stuinf_352116.class " +
             "FROM stugrade_352116,stuinf_352116 " +
             "where composite between ? and ? and stuinf_352116.stu_id = stugrade_352116.stu_id " ;
       else {
         sqlquery =
             "SELECT stugrade_352111.stu_id,stuinf_352111.name,total,stugrade_352111.class " +
             "FROM stugrade_352111,stuinf_352111 " +
             "where total between ? and ? and stuinf_352111.stu_id = stugrade_352111.stu_id " +
             "UNION SELECT stugrade_352112.stu_id,stuinf_352112.name,total,stuinf_352112.class " +
             "FROM stugrade_352112,stuinf_352112 " +
             "where total between ? and ? and stuinf_352112.stu_id = stugrade_352112.stu_id " +
             "UNION SELECT stugrade_352113.stu_id,stuinf_352113.name,total,stuinf_352113.class " +
             "FROM stugrade_352113,stuinf_352113 where total " +
             "between ? and ? and stuinf_352113.stu_id = stugrade_352113.stu_id"+
             "UNION SELECT stugrade_352114.stu_id,stuinf_352114.name,total,stuinf_352114.class " +
             "FROM stugrade_352114,stuinf_352114 " +
             "where total between ? and ? and stuinf_352114.stu_id = stugrade_352114.stu_id " +
             "UNION SELECT stugrade_352115.stu_id,stuinf_352115.name,total,stuinf_352115.class " +
             "FROM stugrade_352115,stuinf_352115 " +
             "where total between ? and ? and stuinf_352115.stu_id = stugrade_352115.stu_id " +
             "UNION SELECT stugrade_352116.stu_id,stuinf_352116.name,total,stuinf_352116.class " +
             "FROM stugrade_352116,stuinf_352116 " +
             "where total between ? and ? and stuinf_352116.stu_id = stugrade_352116.stu_id " ;
       }
     }
     if (!aclass.equals("")) { //班级成绩统计
       if (aclass.equals("352111")) {
         if (grade.equals("chinese"))
           sqlquery =
               "SELECT stugrade_352111.stu_id,stuinf_352111.name,chinese,stugrade_352111.class " +
               "FROM stugrade_352111,stuinf_352111 " +
               "where chinese between ? and ? and stuinf_352111.stu_id = stugrade_352111.stu_id";
         else if (grade.equals("math"))
           sqlquery =
               "SELECT stugrade_352111.stu_id,stuinf_352111.name,math,stugrade_352111.class " +
               "FROM stugrade_352111,stuinf_352111 " +
               "where math between ? and ? and stuinf_352111.stu_id = stugrade_352111.stu_id";
         else if (grade.equals("english"))
           sqlquery =
               "SELECT stugrade_352111.stu_id,stuinf_352111.name,english,stugrade_352111.class " +
               "FROM stugrade_352111,stuinf_352111 " +
               "where english between ? and ? and stuinf_352111.stu_id = stugrade_352111.stu_id";
         else if (grade.equals("composite"))
           sqlquery =
               "SELECT stugrade_352111.stu_id,stuinf_352111.name,composite,stugrade_352111.class " +
               "FROM stugrade_352111,stuinf_352111 " +
               "where composite between ? and ? and stuinf_352111.stu_id = stugrade_352111.stu_id";
         else {
           sqlquery =
               "SELECT stugrade_352111.stu_id,stuinf_352111.name,total,stugrade_352111.class " +
               "FROM stugrade_352111,stuinf_352111 " +
               "where total between ? and ? and stuinf_352111.stu_id = stugrade_352111.stu_id";
         }
       }
       else if (aclass.equals("352112")) {
         if (grade.equals("chinese"))
           sqlquery =
               "SELECT stugrade_352112.stu_id,stuinf_352112.name,chinese,stugrade_352112.class " +
               "FROM stugrade_352112,stuinf_352112 " +
               "where chinese between ? and ? and stuinf_352112.stu_id = stugrade_352112.stu_id";
         else if (grade.equals("math"))
           sqlquery =
               "SELECT stugrade_352112.stu_id,stuinf_352112.name,math,stugrade_352112.class " +
               "FROM stugrade_352112,stuinf_352112 " +
               "where math between ? and ? and stuinf_352112.stu_id = stugrade_352112.stu_id";
         else if (grade.equals("english"))
           sqlquery =
               "SELECT stugrade_352112.stu_id,stuinf_352112.name,english,stugrade_352112.class " +
               "FROM stugrade_352112,stuinf_352112 " +
               "where english between ? and ? and stuinf_352112.stu_id = stugrade_352112.stu_id";
         else if (grade.equals("composite"))
           sqlquery =
               "SELECT stugrade_352112.stu_id,stuinf_352112.name,composite,stugrade_352112.class " +