数据库连接池(JavaBean)

package dbpool;

import java.sql.*;
import java.util.*;

/*连接池类.能够根据要求创建新连接,直到最大连接数为止.*/
public class DBConnPool
{
 //实际使用中的连接数
 private int inUse=0;
 //空闲连接
 private Vector connections = new Vector();
 //连接池名
 private String poolname;
 //数据库标识
 private String dbid;
 //驱动程序名
 private String drivername;
 //数据库账号
 private String username;
 //数据库密码
 private String passwd;
 //最大连接数
 private int maxconn;

 public DBConnPool(String poolname, String drivername, String dbid, String username, String passwd, int maxconn)
 {
  this.poolname = poolname;
  this.dbid = dbid;
  this.drivername = drivername;
  this.username = username;
  this.passwd = passwd;
  this.maxconn = maxconn;
 }

 /*将连接返回给连接池*/
 public synchronized void releaseConnection(Connection con)
 {
  // 将指定连接加入到向量末尾
  connections.addElement(con);
  //连接数减一
  inUse--;
 }

 /*从连接池得到一个连接*/
 public synchronized Connection getConnection()
 {
  Connection con = null;
  if (connections.size() > 0)
  {
   // 获取连接列表中获得第一个连接
   con = (Connection) connections.elementAt(0);
   connections.removeElementAt(0);
   //如果此连接已关闭，则继续获取
   try
   {
    if (con.isClosed())
     con = getConnection();
   }
   catch (Exception ex)
   {
    ex.printStackTrace();
   }
  }
  //如果实际使用的连接小于最大连接数，就新创建一个连接
  else if (maxconn == 0 || inUse < maxconn)
  {
   con = newConnection();
  }
  if (con != null)
  {
   //连接数增一
   inUse++;
  }
  //返回一个连接
  return con;
 }

 /*创建新的连接*/
 private Connection newConnection()
 {
  Connection con = null;
  try
  {
   //加载驱动程序
   Class.forName(drivername);
   //建立连接
   con = DriverManager.getConnection(dbid, username, passwd);
  }
  catch (Exception e)
  {
   e.printStackTrace();
   return null;
  }
  //返回该连接
  return con;
 }

 /*关闭所有连接*/
 public synchronized void closeConn()
 {
  Enumeration allConnections = connections.elements();
  while (allConnections.hasMoreElements())
  {
   Connection con = (Connection) allConnections.nextElement();
   try
   {
    con.close();
   }
   catch (SQLException e)
   {
    e.printStackTrace();
   }
  }
  connections.removeAllElements();
 }
}

---------------------------------------------------------------------------------------------------------------------------------------------

package dbpool;

import java.sql.*;
import java.util.*;

/*连接池管理类,可以管理多个数据库连接池*/
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()
 {
  //添加mysql数据库的连接信息
  poolnames.addElement("mysql");
  drivernames.addElement("com.mysql.jdbc.Driver");
  dbids.addElement("jdbc:mysql://127.0.0.1:3306/testmysql?useUnicode=true&characterEncoding=UTF8");
  usernames.addElement("root");
  passwds.addElement("");
  maxconns.addElement("1000");

  //添加sqlserver数据库的连接信息
  poolnames.addElement("sqlserver");
  drivernames.addElement("com.microsoft.jdbc.sqlserver.SQLServerDriver");
  dbids.addElement("jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=mydb");
  usernames.addElement("sa");
  passwds.addElement("");
  maxconns.addElement("10");
  
  //添加oracle数据库的连接信息
  poolnames.addElement("oracle");
  drivernames.addElement("oracle.jdbc.driver.OracleDriver");
  dbids.addElement("jdbc:oracle:thin:@localhost:1521:mydb");
  usernames.addElement("root");
  passwds.addElement("");
  maxconns.addElement("10");
  
  //添加db2数据库的连接信息
  poolnames.addElement("db2");
  drivernames.addElement("com.ibm.db2.jdbc.app.DB2Driver");
  dbids.addElement("jdbc:db2://localhost:5000/mydb");
  usernames.addElement("admin");
  passwds.addElement("");
  maxconns.addElement("10");

  //添加access数据库的连接信息
  poolnames.addElement("access");
  drivernames.addElement("sun.jdbc.odbc.JdbcOdbcDriver");
  dbids.addElement("jdbc:odbc:TestData");
  usernames.addElement("");
  passwds.addElement("");
  maxconns.addElement("5");

  //创建连接池
  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();
  return null;
 }

 /*关闭所有连接*/
 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);
  }
 }
}
---------------------------------------------------------------------------------------------------------------------------------------------

package dbpool;

import java.util.*;
import java.sql.*;
import java.io.*;

public class ExecuteSQL
{
 private static Connection con = null;
 private ResultSet rs = null;
 private Statement stmt = null;
 private static DBConnManager connManager;
 
 public ExecuteSQL()
 {
  connManager = new DBConnManager();

 }
 //return query Resultret
 public ResultSet executeQuery(String sql)
 {
  try
  {
   con = getConnection();
   stmt = con.createStatement();
   rs=stmt.executeQuery(sql);
  }
  catch(SQLException e)
  {
   System.err.println(e.getMessage());
  }
  return rs;
 }

 public int executeUpdate(String sql)
 {
  int result = 0;
  try
  {
   con = getConnection();
   stmt = con.createStatement();
   result = stmt.executeUpdate(sql);
  }
  catch(SQLException e)
  {
   System.err.println(e.getMessage());
  }
  connManager.releaseConnection("mysql",con);
  return result;

 }
 
 public static Connection getConnection()
 {
 
  try
  {
   con = connManager.getConnection("mysql");
  }
  catch(Exception e)
  {
   System.err.println(e);
  }
  if (con == null)
  {
   System.out.print("不能连接数据库!");
  }
  return con;
 }

 public void closeDB()
 {
  try
  {
   if (rs!=null)
   {
    rs.close();
   }
   if(stmt!=null)
   {
    stmt.close();
   }
   connManager.releaseConnection("mysql",con);
  }
  catch(SQLException e)
  {
   System.err.println(e.getMessage());
  }
 }
 public void destory()
 {
  connManager.closeConns();
 }
 public static String TransUTF8(String chi)
 {
  String result="";
  try
  {
   result=new String(chi.getBytes("ISO8859_1"),"UTF-8");
  }
  catch (UnsupportedEncodingException e)
  {
   System.out.println(e.toString());
  }
  return result;
 }
 public static String TransISO(String chi)
 {
  String result="";
  try
  {
   result = new String(chi.getBytes("UTF-8"),"ISO8859_1");
  }
  catch (UnsupportedEncodingException e)
  {
   System.out.println(e.toString());
  }
  return result;
 }
  
}