dbconnmanager.java

一个简单的JavaSe的入门

       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_A2241.stu_id,stuinf_A2241.name,chinese,stugrade_A2241.class " +
             "FROM stugrade_A2241,stuinf_A2241 " +
             "where chinese between ? and ? and stuinf_A2241.stu_id = stugrade_A2241.stu_id " +
             "UNION SELECT stugrade_A2242.stu_id,stuinf_A2242.name,chinese,stuinf_A2242.class " +
             "FROM stugrade_A2242,stuinf_A2242 " +
             "where chinese between ? and ? and stuinf_A2242.stu_id = stugrade_A2242.stu_id " +
             "UNION SELECT stugrade_A2243.stu_id,stuinf_A2243.name,chinese,stuinf_A2243.class " +
             "FROM stugrade_A2243,stuinf_A2243 where chinese " +
             "between ? and ? and stuinf_A2243.stu_id = stugrade_A2243.stu_id";
       else if (grade.equals("math"))
         sqlquery =
             "SELECT stugrade_A2241.stu_id,stuinf_A2241.name,math,stugrade_A2241.class " +
             "FROM stugrade_A2241,stuinf_A2241 " +
             "where math between ? and ? and stuinf_A2241.stu_id = stugrade_A2241.stu_id " +
             "UNION SELECT stugrade_A2242.stu_id,stuinf_A2242.name,math,stuinf_A2242.class " +
             "FROM stugrade_A2242,stuinf_A2242 " +
             "where math between ? and ? and stuinf_A2242.stu_id = stugrade_A2242.stu_id " +
             "UNION SELECT stugrade_A2243.stu_id,stuinf_A2243.name,math,stuinf_A2243.class " +
             "FROM stugrade_A2243,stuinf_A2243 where math " +
             "between ? and ? and stuinf_A2243.stu_id = stugrade_A2243.stu_id";
       else if (grade.equals("english"))
         sqlquery =
             "SELECT stugrade_A2241.stu_id,stuinf_A2241.name,english,stugrade_A2241.class " +
             "FROM stugrade_A2241,stuinf_A2241 " +
             "where english between ? and ? and stuinf_A2241.stu_id = stugrade_A2241.stu_id " +
             "UNION SELECT stugrade_A2242.stu_id,stuinf_A2242.name,english,stuinf_A2242.class " +
             "FROM stugrade_A2242,stuinf_A2242 " +
             "where english between ? and ? and stuinf_A2242.stu_id = stugrade_A2242.stu_id " +
             "UNION SELECT stugrade_A2243.stu_id,stuinf_A2243.name,english,stuinf_A2243.class " +
             "FROM stugrade_A2243,stuinf_A2243 where english " +
             "between ? and ? and stuinf_A2243.stu_id = stugrade_A2243.stu_id";
       else if (grade.equals("composite"))
         sqlquery =
             "SELECT stugrade_A2241.stu_id,stuinf_A2241.name,composite,stugrade_A2241.class " +
             "FROM stugrade_A2241,stuinf_A2241 " +
             "where composite between ? and ? and stuinf_A2241.stu_id = stugrade_A2241.stu_id " +
             "UNION SELECT stugrade_A2242.stu_id,stuinf_A2242.name,composite,stuinf_A2242.class " +
             "FROM stugrade_A2242,stuinf_A2242 " +
             "where composite between ? and ? and stuinf_A2242.stu_id = stugrade_A2242.stu_id " +
             "UNION SELECT stugrade_A2243.stu_id,stuinf_A2243.name,composite,stuinf_A2243.class " +
             "FROM stugrade_A2243,stuinf_A2243 where composite " +
             "between ? and ? and stuinf_A2243.stu_id = stugrade_A2243.stu_id";
       else {
         sqlquery =
             "SELECT stugrade_A2241.stu_id,stuinf_A2241.name,total,stugrade_A2241.class " +
             "FROM stugrade_A2241,stuinf_A2241 " +
             "where total between ? and ? and stuinf_A2241.stu_id = stugrade_A2241.stu_id " +
             "UNION SELECT stugrade_A2242.stu_id,stuinf_A2242.name,total,stuinf_A2242.class " +
             "FROM stugrade_A2242,stuinf_A2242 " +
             "where total between ? and ? and stuinf_A2242.stu_id = stugrade_A2242.stu_id " +
             "UNION SELECT stugrade_A2243.stu_id,stuinf_A2243.name,total,stuinf_A2243.class " +
             "FROM stugrade_A2243,stuinf_A2243 where total " +
             "between ? and ? and stuinf_A2243.stu_id = stugrade_A2243.stu_id";
       }
     }
     if (!aclass.equals("")) { //班级成绩统计
       if (aclass.equals("A2241")) {
         if (grade.equals("chinese"))
           sqlquery =
               "SELECT stugrade_A2241.stu_id,stuinf_A2241.name,chinese,stugrade_A2241.class " +
               "FROM stugrade_A2241,stuinf_A2241 " +
               "where chinese between ? and ? and stuinf_A2241.stu_id = stugrade_A2241.stu_id";
         else if (grade.equals("math"))
           sqlquery =
               "SELECT stugrade_A2241.stu_id,stuinf_A2241.name,math,stugrade_A2241.class " +
               "FROM stugrade_A2241,stuinf_A2241 " +
               "where math between ? and ? and stuinf_A2241.stu_id = stugrade_A2241.stu_id";
         else if (grade.equals("english"))
           sqlquery =
               "SELECT stugrade_A2241.stu_id,stuinf_A2241.name,english,stugrade_A2241.class " +
               "FROM stugrade_A2241,stuinf_A2241 " +
               "where english between ? and ? and stuinf_A2241.stu_id = stugrade_A2241.stu_id";
         else if (grade.equals("composite"))
           sqlquery =
               "SELECT stugrade_A2241.stu_id,stuinf_A2241.name,composite,stugrade_A2241.class " +
               "FROM stugrade_A2241,stuinf_A2241 " +
               "where composite between ? and ? and stuinf_A2241.stu_id = stugrade_A2241.stu_id";
         else {
           sqlquery =
               "SELECT stugrade_A2241.stu_id,stuinf_A2241.name,total,stugrade_A2241.class " +
               "FROM stugrade_A2241,stuinf_A2241 " +
               "where total between ? and ? and stuinf_A2241.stu_id = stugrade_A2241.stu_id";
         }
       }
       else if (aclass.equals("A2242")) {
         if (grade.equals("chinese"))
           sqlquery =
               "SELECT stugrade_A2242.stu_id,stuinf_A2242.name,chinese,stugrade_A2242.class " +
               "FROM stugrade_A2242,stuinf_A2242 " +
               "where chinese between ? and ? and stuinf_A2242.stu_id = stugrade_A2242.stu_id";
         else if (grade.equals("math"))
           sqlquery =
               "SELECT stugrade_A2242.stu_id,stuinf_A2242.name,math,stugrade_A2242.class " +
               "FROM stugrade_A2242,stuinf_A2242 " +
               "where math between ? and ? and stuinf_A2242.stu_id = stugrade_A2242.stu_id";
         else if (grade.equals("english"))
           sqlquery =
               "SELECT stugrade_A2242.stu_id,stuinf_A2242.name,english,stugrade_A2242.class " +
               "FROM stugrade_A2242,stuinf_A2242 " +
               "where english between ? and ? and stuinf_A2242.stu_id = stugrade_A2242.stu_id";
         else if (grade.equals("composite"))
           sqlquery =
               "SELECT stugrade_A2242.stu_id,stuinf_A2242.name,composite,stugrade_A2242.class " +
               "FROM stugrade_A2242,stuinf_A2242 "+
               "where composite between ? and ? and stuinf_A2242.stu_id = stugrade_A2242.stu_id";
         else {
           sqlquery =
               "SELECT stugrade_A2242.stu_id,stuinf_A2242.name,total,stugrade_A2242.class " +
               "FROM stugrade_A2242,stuinf_A2242 " +
               "where total between ? and ? and stuinf_A2242.stu_id = stugrade_A2242.stu_id";
         }
       }
       else if (aclass.equals("A2243")) {
         if (grade.equals("chinese"))
           sqlquery =
               "SELECT stugrade_A2243.stu_id,stuinf_A2243.name,chinese,stugrade_A2243.class " +
               "FROM stugrade_A2243,stuinf_A2243 " +
               "where chinese between ? and ? and stuinf_A2243.stu_id = stugrade_A2243.stu_id";
         else if (grade.equals("math"))
           sqlquery =
               "SELECT stugrade_A2243.stu_id,stuinf_A2243.name,math,stugrade_A2243.class " +
               "FROM stugrade_A2243,stuinf_A2243 " +
               "where math between ? and ? and stuinf_A2243.stu_id = stugrade_A2243.stu_id";
         else if (grade.equals("english"))
           sqlquery =
               "SELECT stugrade_A2243.stu_id,stuinf_A2243.name,english,stugrade_A2243.class " +
               "FROM stugrade_A2243,stuinf_A2243 " +
               "where english between ? and ? and stuinf_A2243.stu_id = stugrade_A2243.stu_id";
         else if (grade.equals("composite"))
           sqlquery =
               "SELECT stugrade_A2243.stu_id,stuinf_A2243.name,composite,stugrade_A2243.class " +
               "FROM stugrade_A2243,stuinf_A2243 " +
               "where composite between ? and ? and stuinf_A2243.stu_id = stugrade_A2243.stu_id";
         else {
           sqlquery =
               "SELECT stugrade_A2243.stu_id,stuinf_A2243.name,total,stugrade_A2243.class " +
               "FROM stugrade_A2243,stuinf_A2243 " +
               "where total between ? and ? and stuinf_A2243.stu_id = stugrade_A2243.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_A2241 " +
             "where sex = '男' " +
             "UNION SELECT stu_id,name,sex,class FROM stuinf_A2242 "+
             "where sex = '男' " +
             "UNION SELECT stu_id,name,sex,class FROM stuinf_A2243 "+
             "where sex = '男'";
       else if (sumstr.equals("女"))
         sqlquery =
             "SELECT stu_id,name,sex,class FROM stuinf_A2241 " +
             "where sex = '女' " +
             "UNION SELECT stu_id,name,sex,class FROM stuinf_A2242 "+
             "where sex = '女' " +
             "UNION SELECT stu_id,name,sex,class FROM stuinf_A2243 "+
             "where sex = '女'";
       else if (sumstr.equals("民族"))
         sqlquery =
             "SELECT stu_id,name,ethnic,class FROM stuinf_A2241 " +
             "where ethnic LIKE '"+aethnic+"' "+
             " UNION SELECT stu_id,name,ethnic,class FROM stuinf_A2242 "+
             "where ethnic LIKE '"+aethnic+"' " +
             "UNION SELECT stu_id,name,ethnic,class FROM stuinf_A2243 "+
             "where ethnic LIKE '"+aethnic+"'";
       else if (sumstr.equals("出生日期"))
         sqlquery =
             "SELECT stu_id,name,birthday,class FROM stuinf_A2241 " +
             "where birthday between "+range1+" and "+range2+
             " order by birthday ASC "+
             " UNION SELECT stu_id,name,birthday,class FROM stuinf_A2242 "+
             "where birthday between "+range1+" and "+range2+
             " order by birthday ASC "+
             " UNION SELECT stu_id,name,birthday,class FROM stuinf_A2243 "+
             "where birthday between "+range1+ " and "+range2+" order by birthday ASC";
     }
     if (!aclass.equals("")) { //班级成绩统计
       if (aclass.equals("A2241")) {
         if (sumstr.equals("男"))
           sqlquery =
               "SELECT stu_id,name,sex,class FROM stuinf_A2241 " +
               "where sex = '男' ";
         else if (sumstr.equals("女"))
           sqlquery =
               "SELECT stu_id,name,sex,class FROM stuinf_A2241 " +
               "where sex = '女' ";
         else if (sumstr.equals("民族"))
           sqlquery =
               "SELECT stu_id,name,ethnic,class FROM stuinf_A2241 " +
               "where ethnic LIKE '"+aethnic+"'";
         else if (sumstr.equals("出生日期"))
           sqlquery =
               "SELECT stu_id,name,birthday,class FROM stuinf_A2241 " +
               "where birthday between " +range1+ " and "+range2+" order by birthday ASC";
       }
       else if (aclass.equals("A2242")) {
         if (sumstr.equals("男"))
           sqlquery =
               "SELECT stu_id,name,sex,class FROM stuinf_A2242 " +
               "where sex = '男' ";
         else if (sumstr.equals("女"))
           sqlquery =
               "SELECT stu_id,name,sex,class FROM stuinf_A2242 " +
               "where sex = '女' ";
         else if (sumstr.equals("民族"))
           sqlquery =
               "SELECT stu_id,name,ethnic,class FROM stuinf_A2242 " +
               "where ethnic LIKE '"+aethnic+"'";
         else if (sumstr.equals("出生日期"))
           sqlquery =
               "SELECT stu_id,name,birthday,class FROM stuinf_A2242 " +
               "where birthday between "+range1+" and "+range2+
               " order by birthday ASC";
       }
       else if (aclass.equals("A2243")) {
         if (sumstr.equals("男"))
           sqlquery =
               "SELECT stu_id,name,sex,class FROM stuinf_A2242 " +
               "where sex = '男' ";
         else if (sumstr.equals("女"))
           sqlquery =
               "SELECT stu_id,name,sex,class FROM stuinf_A2242 " +
               "where sex = '女' ";
         else if (sumstr.equals("民族"))
           sqlquery =
               "SELECT stu_id,name,ethnic,class FROM stuinf_A2242 " +
               "where ethnic LIKE '"+aethnic+"'";
         else if (sumstr.equals("出生日期"))
           sqlquery =
               "SELECT stu_id,name,birthday,class FROM stuinf_A2242 " +
               "where birthday between "+range1+" and "+range2+
               " order by birthday ASC";
       }
     }
     con = getConnection("Access");
     ps = con.prepareStatement(sqlquery);
     rs = ps.executeQuery();
     while (rs.next()) {
       resultdate.add(rs.getString("stu_id"));
       resultdate.add(rs.getString("name"));
       resultdate.addElement(rs.getString(3));
       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 boolean checkClass(String aclass)
 {
   boolean checkflag =  true;
   if(!aclass.equals("A2241")&&!aclass.equals("A2242")&&!aclass.equals("A2243"))
     checkflag = false;
   else checkflag = true;
   return checkflag;
 }

 //检验输入的学号是否合法
 public boolean checkStuid(String userid) {
   boolean checkflag = true;
   Pattern pattern;
   pattern = Pattern.compile("[0-9]");
   for (int i = 0; i < userid.length(); i++) {
     String str = userid.substring(i, i + 1);
     Matcher matcher = pattern.matcher(str);
     if (!matcher.matches()) {
       checkflag = false;
       break;
     }
     else checkflag = true;
   }
 return checkflag;
}

  /*关闭所有连接*/
  public synchronized void closeConns() {
    Enumeration allPools = connPools.elements();
    while (allPools.hasMoreElements()) {
      DBConnPool pool = (DBConnPool) allPools.nextElement();
      pool.closeConn();
    }
  }

  /*创建连接池*/
  private void createPools() {
    for (int i = 0; i < poolnames.size(); i++) {
      String poolname = poolnames.elementAt(i).toString();
      String drivername = drivernames.elementAt(i).toString();
      String dbid = dbids.elementAt(i).toString();
      String username = usernames.elementAt(i).toString();
      String passwd = passwds.elementAt(i).toString();
      int maxconn = 0;
      try {
        maxconn = Integer.parseInt(maxconns.elementAt(i).toString());
      }
      catch (NumberFormatException e) {
        e.printStackTrace();
      }
      DBConnPool pool = new DBConnPool(poolname, drivername, dbid, username,
                                       passwd, maxconn);
      connPools.put(poolname, pool);
    }
  }
}