儘管很多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