自己实现一个数据库连接池

        尽管很多Web服务器厂商已经在服务器内嵌入了数据池的实现,比如Tomcat的DBCP数据库连接池.不过由于其内部的机制开发者并不熟悉.从而出现了Bug不知道该如何去解决. 

       一直以来我也不喜欢用第三方的开发包,所以就自己参考着一本书实现了一个数据库连接池.并且可扩充性也可以.更重要的是自己写的数据库连接池.即便出现了Bug也比较容易找出来.

      下面是数据库连接池的代码,提供了几个接口供程序调用.

import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Date;
import java.util.Enumeration;
import java.util.Hashtable;
import java.util.Properties;
import java.util.StringTokenizer;
import java.util.Vector;

public class DBConnectionManager {
 static private DBConnectionManager instance;
 static private int clients;
 private PrintWriter log;
 private Vector drivers = new Vector();
 private Hashtable pools = new Hashtable();
 
 static synchronized public DBConnectionManager getInstance() {
  if(instance == null){
   instance = new DBConnectionManager();
  }
  clients++;
  return instance;
 }
 
 private DBConnectionManager(){
  init();
 }
 
 
 private void init(){
  System.out.println("init 1");
  InputStream is = getClass().getResourceAsStream("db.properties");
  System.out.println("init 2");
  Properties dbProps = new Properties();
  try{
   dbProps.load(is);
  }catch(Exception e){
   System.err.println("不能读取属性文件."+"请确保db.properties在CLASSPATH指定的路径中");
   return;
  }
  
  String logFile = dbProps.getProperty("logfile","DBConnectionManager.log");
  System.out.println("the logFile is :"+logFile);
  try{
   log = new PrintWriter(new FileWriter(logFile,true),true);
  }catch(IOException e){
   System.err.println("无法打开日志文件:"+logFile);
   log = new PrintWriter(System.err);
  }
  System.out.println("init 3");
  loadDrviers(dbProps);
  createPools(dbProps);
 }
 
 private void createPools(Properties props){
  Enumeration propNames = props.propertyNames();
  while(propNames.hasMoreElements()){
   String name = (String)propNames.nextElement();
   if(name.endsWith(".url")){
    String poolName = name.substring(0,name.lastIndexOf("."));
    String url = props.getProperty(poolName+".url");
    if(url == null){
     log("没有为连接池"+poolName+"指定URL");
     continue;
    }
    
    String user = props.getProperty(poolName+".user");
    String password = props.getProperty(poolName+".password");
    String maxconn = props.getProperty(poolName+".maxconn","0");
    int max;
    try{
     max = Integer.valueOf(maxconn).intValue();
    }catch(NumberFormatException e){
     log("错误的最大连接数限制:"+maxconn+".连接池:"+poolName);
     max = 0;
    }
    
    DBConnectionPool pool = new DBConnectionPool(poolName,url,user,password,max);
    pools.put(poolName,pool);
    log("成功创建连接池"+poolName);
   }
  }
 }
 
 private void loadDrviers(Properties props){
  String driverClasses = props.getProperty("drivers");
  StringTokenizer st = new StringTokenizer(driverClasses);
  while(st.hasMoreElements()){
   String driverClassName = st.nextToken().trim();
   try{
    Driver driver = (Driver)Class.forName(driverClassName).newInstance();
    DriverManager.registerDriver(driver);
    drivers.addElement(driver);
    log("成功注册JDBC驱动程序"+driverClassName);
   }catch(Exception e){
    log("无法注册JDBC驱动程序:"+driverClassName+",错误:"+e);
   }
  }
 }
 
 
 public void freeConnection(String name,Connection con){
  DBConnectionPool pool = (DBConnectionPool)pools.get(name);
  if(pool != null){
   pool.freeConnection(con);
  }
 }
 
 public Connection getConnection(String name){
  DBConnectionPool pool = (DBConnectionPool)pools.get(name);
  if(pool != null){
   return pool.getConnection();
  }
  return null;
 }
 
 public Connection getConnection(String name,long time){
  DBConnectionPool pool = (DBConnectionPool)pools.get(name);
  if(pool != null){
   return pool.getConnection(time);
  }
  return null;
 }
 
 private void log(String msg){
  log.println(new Date()+":"+msg);
 }
 
 
 
 public void log(Throwable e,String msg){
  log.println(new Date()+":"+msg);
  e.printStackTrace();
 }
 
 
 public synchronized void release(){
  if(--clients != 0){
   return;
  }
  
  Enumeration allPools = pools.elements();
  while(allPools.hasMoreElements()){
   DBConnectionPool pool = (DBConnectionPool)allPools.nextElement();
   pool.release();
  }
  
  Enumeration allDrivers = drivers.elements();
  while(allDrivers.hasMoreElements()){
   Driver driver = (Driver)allDrivers.nextElement();
   try{
    DriverManager.deregisterDriver(driver);
    log("撤消JDBC驱动程序"+driver.getClass().getName()+"的注册");
   }catch(SQLException e){
    log(e,"无法撤消下列JDBC驱动程序的注册:"+driver.getClass().getName());
   }
  }
 }
 
 
 public class DBConnectionPool {
  private int checkedOut;
  private Vector freeConnections = new Vector();
  private int maxConn;
  private String name;
  private String password;
  private String URL;
  private String user;
  
  public DBConnectionPool(String name,String URL,String user,String password,int maxConn){
   this.name = name;
   this.URL = URL;
   this.user = user;
   this.password = password;
   this.maxConn = maxConn;
  }
  
  public synchronized void freeConnection(Connection con){
   freeConnections.add(con);
   checkedOut--;
   notifyAll();
  }
  
  public synchronized Connection getConnection(){
   Connection con = null;
   if(freeConnections.size() > 0){
    con = (Connection)freeConnections.firstElement();
    freeConnections.removeElementAt(0);
    try{
     if(con.isClosed()){
      log("从连接池"+name+"删除一个无效连接");
      con = getConnection();
     }
    }catch(SQLException e){
     log("从连接池"+name+"删除一个无效连接");
     con = getConnection();
    }
   }else if(maxConn == 0||checkedOut < maxConn){
    con = newConnection();
   }
   if(con != null){
    checkedOut++;
   }
   return con;
  }
  
  public synchronized Connection getConnection(long timeout){
   long startTime = new Date().getTime();
   Connection con;
   while((con = getConnection()) == null){
    try{
     wait(timeout);
    }catch(InterruptedException e){}
    
    if((new Date().getTime() - startTime) >= timeout){
     return null;
    }
   }
   return con;
  }
  
  public synchronized void release(){
   Enumeration allConnections = freeConnections.elements();
   while(allConnections.hasMoreElements()){
    Connection con = (Connection)allConnections.nextElement();
    try{
     con.close();
     log("无法关闭连接池"+name+"中的连接");
    }catch(SQLException e){
     log("无法关闭连接池"+name+"中的连接");
    }
   }
   freeConnections.removeAllElements();
  }
  
  private Connection newConnection(){
   Connection con = null;
   try{
    if(user == null){
     con = DriverManager.getConnection(URL);
    }else{
     con = DriverManager.getConnection(URL,user,password);
    }
    log("连接池"+name+"创建一个新的连接");
   }catch(SQLException e){
    log("无法创建下列URL的连接:"+URL);
    return null;
   }
   return con;
  }
 }
 
}

 

    以下是一个测试用的Servlet例子:

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class TestServlet extends HttpServlet {
 
 private DBConnectionManager connMgr;
 
 public void destroy() {
  connMgr.release();
  super.destroy();
 }

 public void service(HttpServletRequest request, HttpServletResponse response)
   throws ServletException, IOException {

  response.setContentType("text/html;charset=gb2312");
  PrintWriter out = response.getWriter();
  Connection con = connMgr.getConnection("mysql");
  if(con == null){
   out.println("不能获取数据库连接.");
   return;
  }
  ResultSet rs = null;
  ResultSetMetaData md = null;
  Statement stmt = null;
  try{
   stmt = con.createStatement();
   rs = stmt.executeQuery("SELECT * FROM EMPLOYEE");
   md = rs.getMetaData();
   out.println("

职工数据

");
   while(rs.next()){
    out.println("
");
    for(int i = 1;i < md.getColumnCount();i++){
     out.print(rs.getString(i)+",");
    }
   }
   stmt.close();
   rs.close();
  }catch(SQLException e){
   e.printStackTrace();
  }
  
  connMgr.freeConnection("mysql",con);
 }

 public void init(ServletConfig conf) throws ServletException {
  super.init(conf);
  System.out.println("servelt init start");
  connMgr = DBConnectionManager.getInstance();
  System.out.println("servelt init over");
 }

}

   下面是数据库连接池的配置文件:

drivers=com.mysql.jdbc.Driver
logfile=d://log//log.txt
mysql.maxconn=2
mysql.url=jdbc:mysql://localhost/test?user=root&password=sunfeng&useUnicode=true&characterEncoding=gb2312
mysql.user=root
mysql.password=sunfeng

以下是数据库里面的数据(我用的是MySQL数据库):

以下是最终的执行结果:

Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=1394711