dbconnmanager.java

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

package stumng;

import java.sql.*;
import java.util.*;
import java.sql.Date;
import java.util.regex.*;

/*连接池管理类,可以管理多个数据库连接池*/
public class DBConnManager {
  //连接池名列表
  private Vector poolnames = new Vector();

  //驱动程序名列表
  private Vector drivernames = new Vector();

  //数据库标识列表
  private Vector dbids = new Vector();

  //用户名列表
  private Vector usernames = new Vector();

  //密码列表
  private Vector passwds = new Vector();

  //最大连接数列表
  private Vector maxconns = new Vector();

  //连接池队列
  private Hashtable connPools = new Hashtable();

  public DBConnManager() {
    //添加Access数据库的连接信息
    poolnames.addElement("Access");
    drivernames.addElement("sun.jdbc.odbc.JdbcOdbcDriver");
    dbids.addElement("jdbc:odbc:stuMngs");
    usernames.addElement("sa");
    passwds.addElement("sa");
    maxconns.addElement("300");

    //创建连接池
    createPools();
  }

  /*将连接返回给由指定的连接池*/
  public void releaseConnection(String name, Connection con) {
    DBConnPool pool = (DBConnPool) connPools.get(name);
    if (pool != null)
      pool.releaseConnection(con);
  }

  /*得到一个指定连接池中的连接*/
  public Connection getConnection(String name) {
    DBConnPool pool = (DBConnPool) connPools.get(name);
    if (pool != null)
      return pool.getConnection();
    else return null;
  }

  /*往Access数据库中插入数据时，进行编码转换*
           public String ISOtoGBK(String iso) {
    String gb;
    try {
      if (iso.equals("")) {
        return "";
      }
      else {
        gb = new String(iso.getBytes("ISO-8859-1"),"GBK");
        return gb;
      }
    }
    catch (Exception e) {
      System.err.print("编码转换错误：" + e.getMessage());
      return "";
    }
           }

   *往Access数据库中取数据时，进行编码转换*
           public String GBKtoISO(String gb) {
    String iso;
    try {
      if (gb.equals("")) {
        return "";
      }
      else {
        iso = new String(gb.getBytes("GBK"), "ISO-8859-1");
        return iso;
      }
    }
    catch (Exception e) {
      System.err.print("编码转换错误：" + e.getMessage());
      return "";
    }
           }*/



  //检查该班是否存在此学生
  public boolean userExist(int userId, String aclass) {
    Connection con = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    boolean occupied = true;
    String sqlquery ="select * from stuinf_352111 where stu_id=? and class ='"+aclass+"'";
    try {
      if (aclass.equals("352111")) {
        sqlquery = "select * from stuinf_352111 where stu_id=?";
      }
      else if (aclass.equals("352112")) {
        sqlquery = "select * from stuinf_352112 where stu_id=?";
      }
      else if (aclass.equals("352113")) {
    	  sqlquery = "select * from stuinf_352113 where stu_id=?";
      }
      else if (aclass.equals("352114")) {
          sqlquery = "select * from stuinf_352114 where stu_id=?";
        }
        else if (aclass.equals("352115")) {
          sqlquery = "select * from stuinf_352115 where stu_id=?";
        }
        else if (aclass.equals("352116")) {
      	  sqlquery = "select * from stuinf_352116 where stu_id=?";
        }      
      else occupied = false;
      con = getConnection("Access");
      ps = con.prepareStatement(sqlquery);
      ps.setInt(1, userId);
      rs = ps.executeQuery();
      if (rs.next() == false )
        occupied = false;
      else occupied = true;
    }
    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 occupied;
  }

  //检查是否有该学生的成绩记录
  public boolean gradeExist(int userId, String aclass) {
    Connection con = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    boolean occupied = true;
    String sqlquery = null;
    try {
      if (aclass.equals("352111")) {
        sqlquery = "select * from stugrade_352111 where stu_id=?";
      }
      else if (aclass.equals("352112")) {
        sqlquery = "select * from stugrade_352112 where stu_id=?";
      }
      else if (aclass.equals("352113")) {
    	  sqlquery = "select * from stuinf_352113 where stu_id=?";
      }
      else if (aclass.equals("352114")) {
          sqlquery = "select * from stuinf_352114 where stu_id=?";
        }
        else if (aclass.equals("352115")) {
          sqlquery = "select * from stuinf_352115 where stu_id=?";
        }
        else if (aclass.equals("352116")) {
      	  sqlquery = "select * from stuinf_352116 where stu_id=?";
        }     
      con = getConnection("Access");
      ps = con.prepareStatement(sqlquery);
      ps.setInt(1, userId);
      rs = ps.executeQuery();
      if (!rs.next())
        occupied = false;
    }
    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 occupied;
  }

  //删除学生
  //userId为要删除的用户ID
  public boolean removeUser(int userId, String aname, String aclass) {
    Connection con = null;
    PreparedStatement ps = null;
    boolean delflag = false;
    boolean exitflag = false;
    String sqlupdate = null;
    String sqldel = null;
    try {
      if (aclass.equals("352111")) {
        sqlupdate = "delete * from stuinf_352111 where stu_id=? and name = ?";
        exitflag = gradeExist(userId, aclass);
        if (exitflag = true) {
          sqldel = "delete * from stugrade_352111 where stu_id= ?";
        }
      }
      else if (aclass.equals("352112")) {
        sqlupdate = "delete * from stuinf_352112 where stu_id=? and name = ?";
        exitflag = gradeExist(userId, aclass);
        if (exitflag = true) {
          sqldel = "delete * from stugrade_352112 where stu_id= ?";
        }
      }
      else if (aclass.equals("352113")){
        sqlupdate = "delete * from stuinf_352113 where stu_id=? and name = ?";
        exitflag = gradeExist(userId, aclass);
        if (exitflag = true) {
          sqldel = "delete * from stugrade_352113 where stu_id= ?";
        }
      }
      else  if (aclass.equals("352114")) {
          sqlupdate = "delete * from stuinf_352114 where stu_id=? and name = ?";
          exitflag = gradeExist(userId, aclass);
          if (exitflag = true) {
            sqldel = "delete * from stugrade_352114 where stu_id= ?";
          }
        }
        else if (aclass.equals("352115")) {
          sqlupdate = "delete * from stuinf_352115 where stu_id=? and name = ?";
          exitflag = gradeExist(userId, aclass);
          if (exitflag = true) {
            sqldel = "delete * from stugrade_352115 where stu_id= ?";
          }
        }
        else if (aclass.equals("352116")){
          sqlupdate = "delete * from stuinf_352116 where stu_id=? and name = ?";
          exitflag = gradeExist(userId, aclass);
          if (exitflag = true) {
            sqldel = "delete * from stugrade_352116 where stu_id= ?";
          }
        }
      
      con = getConnection("Access");
      ps = con.prepareStatement(sqlupdate);
      ps.setInt(1, userId);
      ps.setString(2, aname);
      ps.executeUpdate();
      exitflag = gradeExist(userId, aclass);
      if (exitflag = true) {
        ps = con.prepareStatement(sqldel);
        ps.setInt(1, userId);
        ps.executeUpdate();
      }
      delflag = 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 delflag;
  }

  //添加基本信息
  //userId为添加的用户ID
  public boolean addUser(String aname, int astu_id, String aclass,
                         Date abirthday, String asex,
                         String anativeplace, String aethnic) {
    Connection con = null;
    PreparedStatement ps = null;
    boolean addflag = false;
    String sqlInsert = null;
    try {
      if (aclass.equals("352111")) {
        sqlInsert = "insert into stuinf_352111(name, stu_id, class, birthday, sex, nativeplace, ethnic ) values(?,?,?,?,?,?,?)";