dbconnmanager.java~198~

Java完全自学手册 全部随书代码 作者：马军 王灏

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("wangqiaoyun");
    passwds.addElement("wangqiaoyun");
    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_A2241 where stu_id=? and class ='"+aclass+"'";
    try {
      if (aclass.equals("A2241")) {
        sqlquery = "select * from stuinf_A2241 where stu_id=?";
      }
      else if (aclass.equals("A2242")) {
        sqlquery = "select * from stuinf_A2242 where stu_id=?";
      }
      else if (aclass.equals("A2243")) sqlquery = "select * from stuinf_A2243 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("A2241")) {
        sqlquery = "select * from stugrade_A2241 where stu_id=?";
      }
      else if (aclass.equals("A2242")) {
        sqlquery = "select * from stugrade_A2242 where stu_id=?";
      }
      else sqlquery = "select * from stugrade_A2243 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("A2241")) {
        sqlupdate = "delete * from stuinf_A2241 where stu_id=? and name = ?";
        exitflag = gradeExist(userId, aclass);
        if (exitflag = true) {
          sqldel = "delete * from stugrade_A2241 where stu_id= ?";
        }
      }
      else if (aclass.equals("A2242")) {
        sqlupdate = "delete * from stuinf_A2242 where stu_id=? and name = ?";
        exitflag = gradeExist(userId, aclass);
        if (exitflag = true) {
          sqldel = "delete * from stugrade_A2242 where stu_id= ?";
        }
      }
      else {
        sqlupdate = "delete * from stuinf_A2243 where stu_id=? and name = ?";
        exitflag = gradeExist(userId, aclass);
        if (exitflag = true) {
          sqldel = "delete * from stugrade_A2243 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("A2241")) {
        sqlInsert = "insert into stuinf_A2241(name, stu_id, class, birthday, sex, nativeplace, ethnic ) values(?,?,?,?,?,?,?)";
      }
      else if (aclass.equals("A2242")) {
        sqlInsert = "insert into stuinf_A2242(name, stu_id, class, birthday, sex, nativeplace, ethnic ) values(?,?,?,?,?,?,?)";
      }
      else sqlInsert = "insert into stuinf_A2243(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("A2241")) {
        sqlInsert = "insert into stugrade_A2241(stu_id, total, chinese, math, english, composite, class) values(?,?,?,?,?,?,?)";
      }
      else if (aclass.equals("A2242")) {
        sqlInsert = "insert into stugrade_A2242(stu_id, total, chinese, math, english, composite, class) values(?,?,?,?,?,?,?)";
      }
      else sqlInsert = "insert into stugrade_A2243(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("A2241")) {
        rs = stat.executeQuery("select * from stuinf_A2241 where stu_id = " +
                               astuid);
      }
      else if (aclass.equals("A2242")) {
        rs = stat.executeQuery("select * from stuinf_A2242 where stu_id = " +
                               astuid);
      }
      else rs = stat.executeQuery("select * from stuinf_A2243 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 {