dbconnmanager.java~192~

Java课程项目—学生管理系统是北航软件学院2005级大三上学期的选修课期末大作业项目

      if (conn != null)try {
        conn.close();
      }
      catch (SQLException ignore) {}
      finally {
        releaseConnection("Access", conn);
      }
    }
    return updflag;
  }

  //修改学生成绩
  public boolean updateGrade(String aclass, int astu_id, String amath,
                             String achinese,
                             String aenglish, String acomposite) {
    Connection conn = null;
    boolean updflag = false;
    ResultSet rs = null;
    float atotal = 0;
    try {
      conn = getConnection("Access");
      //声明可更新的结果集
      Statement stat = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                            ResultSet.CONCUR_UPDATABLE);
      if (aclass.equals("352111")) {
        rs = stat.executeQuery("select * from stugrade_352111 where stu_id = " +
                               astu_id);
      }
      else if (aclass.equals("352112")) {
        rs = stat.executeQuery("select * from stugrade_352112 where stu_id = " +
                               astu_id);
      }
      else rs = stat.executeQuery(
          "select * from stugrade_352113 where stu_id = " +
          astu_id);

      while (rs.next()) {

        if (!achinese.equals("")) {
          float chinese = Float.parseFloat(achinese);
          rs.updateFloat("chinese", chinese);
          atotal = chinese;
          rs.updateRow();
        }
        else atotal = rs.getFloat(3);

        if (!amath.equals("")) {
          float math = Float.parseFloat(amath);
          atotal = atotal + math;
          rs.updateFloat("math", math);
          rs.updateRow();
        }
        else atotal = atotal + rs.getFloat(4);

        if (!aenglish.equals("")) {
          float english = Float.parseFloat(aenglish);
          atotal = atotal + english;
          rs.updateFloat("english", english);
          rs.updateRow();
        }
        else atotal = atotal + rs.getFloat(5);

        if (!acomposite.equals("")) {
          float composite = Float.parseFloat(acomposite);
          atotal = atotal + composite;
          rs.updateFloat("composite", composite);
          rs.updateRow();
        }
        else atotal = atotal + rs.getFloat(6);

        rs.updateFloat("total", atotal);
        rs.updateRow();
        atotal = 0;
      }
      updflag = true;

    }
    catch (SQLException e) {
      e.printStackTrace();
    }
    finally {
      if (rs != null)try {
        rs.close();
      }
      catch (SQLException ignore) {}
      if (conn != null)try {
        conn.close();
      }
      catch (SQLException ignore) {}
      finally {
        releaseConnection("Access", conn);
      }
    }
    return updflag;
  }

  //获取学生排名的详细信息
  public Vector getRank(String aclass, String aschool, String bymath,
                        String bychinese, String byenglish, String bycomposite,
                        String bytotal) {
    Connection con = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    Vector resultdate = new Vector();
    String sqlquery = null;
    String bygrade = null;
    try {
      //全院成绩排名
      if (!aschool.equals("")) {
        if (!bychinese.equals("")) {
          bygrade = "chinese";
          sqlquery = "SELECT stu_id,chinese,class FROM stugrade_352111 " +
              "UNION SELECT stu_id,chinese,class FROM stugrade_352112 " +
              "UNION SELECT stu_id,chinese,class FROM stugrade_352113 " +
              "ORDER BY chinese";
        }
        else if (!bymath.equals("")) {
          bygrade = "math";
          sqlquery = "SELECT stu_id,math,class FROM stugrade_352111 " +
              "UNION SELECT stu_id,math,class FROM stugrade_352112 " +
              "UNION SELECT stu_id,math,class FROM stugrade_352113 " +
              "ORDER BY math";

        }
        else if (!byenglish.equals("")) {
          bygrade = "english";
          sqlquery = "SELECT stu_id,english,class FROM stugrade_352111 " +
              "UNION SELECT stu_id,english,class FROM stugrade_352112 " +
              "UNION SELECT stu_id,english,class FROM stugrade_352113 " +
              "ORDER BY english";

        }
        else if (!bycomposite.equals("")) {
          bygrade = "composite";
          sqlquery = "SELECT stu_id,composite,class FROM stugrade_352111 " +
              "UNION SELECT stu_id,composite,class FROM stugrade_352112 " +
              "UNION SELECT stu_id,composite,class FROM stugrade_352113 " +
              "ORDER BY composite";

        }
        else {
          bygrade = "total";
          sqlquery = "SELECT stu_id,total,class FROM stugrade_352111 " +
              "UNION SELECT stu_id,total,class FROM stugrade_352112 " +
              "UNION SELECT stu_id,total,class FROM stugrade_352113 " +
              "ORDER BY total";

        }
      }
      if (!aclass.equals("")) { //班级成绩排名
        if (aclass.equals("352111")) {
          if (!bychinese.equals("")) {
            bygrade = "chinese";
            sqlquery = "select stu_id, chinese, class from stugrade_352111 " +
                "ORDER BY chinese";
          }
          else if (!bymath.equals("")) {
            bygrade = "math";
            sqlquery = "select stu_id, math, class from stugrade_352111 " +
                "ORDER BY math";
          }
          else if (!byenglish.equals("")) {
            bygrade = "english";
            sqlquery = "select stu_id, english, class from stugrade_352111 " +
                "ORDER BY english";
          }
          else if (!bycomposite.equals("")) {
            bygrade = "composite";
            sqlquery = "select stu_id, composite, class from stugrade_352111 " +
                "ORDER BY composite";
          }
          else {
            bygrade = "total";
            sqlquery = "select stu_id, total, class  from stugrade_352111 " +
                "ORDER BY total";
          }
        }
        else if (aclass.equals("352112")) {
          if (!bychinese.equals("")) {
            bygrade = "chinese";
            sqlquery = "select stu_id, chinese, class from stugrade_352112 " +
                "ORDER BY chinese";
          }
          else if (!bymath.equals("")) {
            bygrade = "math";
            sqlquery = "select stu_id, math, class from stugrade_352112 " +
                "ORDER BY math";
          }
          else if (!byenglish.equals("")) {
            bygrade = "english";
            sqlquery = "select stu_id, english, class from stugrade_352112 " +
                "ORDER BY english";
          }
          else if (!bycomposite.equals("")) {
            bygrade = "composite";
            sqlquery = "select stu_id, composite, class  from stugrade_352112 " +
                "ORDER BY composite";
          }
          else {
            bygrade = "total";
            sqlquery = "select stu_id, total, class  from stugrade_352112 " +
                "ORDER BY total";
          }
        }
        else if (aclass.equals("352113")) {

          if (!bychinese.equals("")) {
            bygrade = "chinese";
            sqlquery = "select stu_id, chinese, class from stugrade_352113 " +
                "ORDER BY chinese";
          }
          else if (!bymath.equals("")) {
            bygrade = "math";
            sqlquery = "select stu_id, math, class from stugrade_352113 " +
                "ORDER BY math";
          }
          else if (!byenglish.equals("")) {
            bygrade = "english";
            sqlquery = "select stu_id, english, class from stugrade_352113 " +
                "ORDER BY english";
          }
          else if (!bycomposite.equals("")) {
            bygrade = "composite";
            sqlquery = "select stu_id, composite, class from stugrade_352113 " +
                "ORDER BY composite";
          }
          else {
            bygrade = "total";
            sqlquery = "select stu_id, total, class from stugrade_352113 " +
                "ORDER BY total";
          }

        }
      }
      con = getConnection("Access");
      ps = con.prepareStatement(sqlquery);
      rs = ps.executeQuery();
      while (rs.next()) {
        resultdate.add(rs.getString("stu_id"));
        resultdate.add(rs.getString(bygrade));
        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 stuinfQuery(int astuid, String aclass, String aname) {
    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, 4);
        if (sstuid.equals("352111") )
          sqlquery = "select * from stuinf_352111 where stu_id ="+astuid;
        else if (sstuid.equals("352112") )
          sqlquery = "select * from stuinf_352112 where stu_id ="+astuid;
        else sqlquery = "select * from stuinf_352113 where stu_id ="+astuid;
      }
      else if (!aname.equals("")) {
        if (!aclass.equals("全院")) {
          if (aclass.equals("352111")) sqlquery =
              "select * from stuinf_352111 where name ='"+aname+"'";
          else if (aclass.equals("352112")) sqlquery =
              "select * from stuinf_352112 where name ='"+aname+"'";
          else if (aclass.equals("352113")) sqlquery =
              "select * from stuinf_352113 where name ='"+aname+"'";
        }
        else sqlquery = "SELECT * FROM stuinf_352111 where name ='"+aname+
            "' UNION SELECT * FROM stuinf_352112 where name ='"+aname+
            "' UNION SELECT * FROM stuinf_352113 where name ='"+aname+"'";
      }
      else if (!aclass.equals("")) {
        if (aclass.equals("352111")) sqlquery =
            "select * from stuinf_352111";
        else if (aclass.equals("352112")) sqlquery =
            "select * from stuinf_352112";
        else if (aclass.equals("352113")) sqlquery =
            "select * from stuinf_352113";
      }
      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, 4);
       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 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 (!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 ";
     }
     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) {}