dbconnmanager.java

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

      }
      else if (aclass.equals("352112")) {
        sqlInsert = "insert into stuinf_352112(name, stu_id, class, birthday, sex, nativeplace, ethnic ) values(?,?,?,?,?,?,?)";
      }
      else if (aclass.equals("352113")) {
    	  sqlInsert = "insert into stuinf_352113(name, stu_id, class, birthday, sex, nativeplace, ethnic ) values(?,?,?,?,?,?,?)";
      }
      else if (aclass.equals("352114")) {
        sqlInsert = "insert into stuinf_352114(name, stu_id, class, birthday, sex, nativeplace, ethnic ) values(?,?,?,?,?,?,?)";
      }
      else if (aclass.equals("352115")) {
        sqlInsert = "insert into stuinf_352115(name, stu_id, class, birthday, sex, nativeplace, ethnic ) values(?,?,?,?,?,?,?)";
      }
      else if (aclass.equals("352116")) {
    	  sqlInsert = "insert into stuinf_352116(name, stu_id, class, birthday, sex, nativeplace, ethnic ) values(?,?,?,?,?,?,?)";
      }
      con = getConnection("Access");
      ps = con.prepareStatement(sqlInsert);
      ps.setString(1, aname);
      ps.setInt(2, astu_id);
      ps.setString(3, aclass);
      ps.setDate(4, abirthday);
      ps.setString(5, asex);
      ps.setString(6, anativeplace);
      ps.setString(7, aethnic);
      ps.executeUpdate();
      addflag = true;
    }
    catch (SQLException e) {
      e.printStackTrace();
    }
    finally {
      if (ps != null)try {
        ps.close();
      }
      catch (SQLException ignore) {}
      if (con != null)try {
        con.close();
      }
      catch (SQLException ignore) {}
      finally {
        releaseConnection("Access", con);
      }
    }
    return addflag;
  }

  //添加学生成绩
  public boolean addGrade(String aclass, int astu_id, float atotal, float amath,
                          float achinese,
                          float aenglish, float acomposite) {
    Connection con = null;
    PreparedStatement ps = null;
    boolean addflag = false;
    String sqlInsert = null;
    try {
      if (aclass.equals("352111")) {
        sqlInsert = "insert into stugrade_352111(stu_id, total, chinese, math, english, composite, class) values(?,?,?,?,?,?,?)";
      }
      else if (aclass.equals("352112")) {
        sqlInsert = "insert into stugrade_352112(stu_id, total, chinese, math, english, composite, class) values(?,?,?,?,?,?,?)";
      }
      else if (aclass.equals("352113")) {
    	  sqlInsert = "insert into stugrade_352113(stu_id, total, chinese, math, english, composite, class) values(?,?,?,?,?,?,?)";
      }
      else if (aclass.equals("352114")) {
        sqlInsert = "insert into stugrade_352114(stu_id, total, chinese, math, english, composite, class) values(?,?,?,?,?,?,?)";
      }
      else if (aclass.equals("352115")) {
        sqlInsert = "insert into stugrade_352115(stu_id, total, chinese, math, english, composite, class) values(?,?,?,?,?,?,?)";
      }
      else if (aclass.equals("352116")) {
    	  sqlInsert = "insert into stugrade_352116(stu_id, total, chinese, math, english, composite, class) values(?,?,?,?,?,?,?)";
      }
      con = getConnection("Access");
      ps = con.prepareStatement(sqlInsert);
      ps.setInt(1, astu_id);
      ps.setString(7, aclass);
      ps.setFloat(2, atotal);
      ps.setFloat(3, achinese);
      ps.setFloat(4, amath);
      ps.setFloat(5, aenglish);
      ps.setFloat(6, acomposite);
      ps.executeUpdate();
      addflag = true;
    }
    catch (SQLException e) {
      e.printStackTrace();
    }
    finally {
      if (ps != null)try {
        ps.close();
      }
      catch (SQLException ignore) {}
      if (con != null)try {
        con.close();
      }
      catch (SQLException ignore) {}
      finally {
        releaseConnection("Access", con);
      }
    }
    return addflag;
  }

  //修改学生基本信息
  public boolean updateUser(String aname, int astuid, String aclass,
                            String asex, Date abirthday, String anativeplace,
                            String aethnic) {
    Connection conn = null;
    boolean updflag = false;
    ResultSet rs = null;
    try {
      conn = getConnection("Access");
      //声明可更新的结果集
      Statement stat = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                            ResultSet.CONCUR_UPDATABLE);
      if (aclass.equals("352111")) {
        rs = stat.executeQuery("select * from stuinf_352111 where stu_id = " +
                               astuid);
      }
      else if (aclass.equals("352112")) {
        rs = stat.executeQuery("select * from stuinf_352112 where stu_id = " +
                               astuid);
      }
      else if (aclass.equals("352113")) {
    	  rs = stat.executeQuery("select * from stuinf_352113 where stu_id = " +     
                               astuid);
      }
      else if (aclass.equals("352114")) {
          rs = stat.executeQuery("select * from stuinf_352114 where stu_id = " +
                  astuid);
      }
      else if (aclass.equals("352115")) {
    	  rs = stat.executeQuery("select * from stuinf_352115 where stu_id = " +
	                  astuid);
      }
      else if (aclass.equals("352116")) {
    	  rs = stat.executeQuery("select * from stuinf_352116 where stu_id = " +     
	                  astuid);
      }
      while (rs.next()) {
        if (!aname.equals("")) {
          rs.updateString("name", aname);
          rs.updateRow();
        }
        if (!aclass.equals("")) {
          rs.updateString("class", aclass);
          rs.updateRow();
        }
        if (!asex.equals("")) {
          rs.updateString("sex", asex);
          rs.updateRow();
        }
        if (!anativeplace.equals("")) {
          rs.updateString("nativeplace", anativeplace);
          rs.updateRow();
        }
        if (!aethnic.equals("")) {
          rs.updateString("ethnic", aethnic);
          rs.updateRow();
        }
        if (abirthday != null) {
          rs.updateDate("birthday", abirthday);
          rs.updateRow();
        }

      }
      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 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 if (aclass.equals("352113")) {
    	  rs = stat.executeQuery("select * from stugrade_352113 where stu_id = " +
          astu_id);
      }
      else if (aclass.equals("352114")) {
          rs = stat.executeQuery("select * from stugrade_352114 where stu_id = " +
                  astu_id);
      }
      else if (aclass.equals("352115")) {
    	  rs = stat.executeQuery("select * from stugrade_352115 where stu_id = " +
                  astu_id);
      }
      else if (aclass.equals("352116")) {
    	  rs = stat.executeQuery("select * from stugrade_352116 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 " +
              "UNION SELECT stu_id,chinese,class FROM stugrade_352114 " +
              "UNION SELECT stu_id,chinese,class FROM stugrade_352115 " +
              "UNION SELECT stu_id,chinese,class FROM stugrade_352116 " +