关于连接池的笔记

最近因各种各样的原因.都要用上DBConnectionManager连接池.

可惜的是找了整个互联网都找不到一个可以通过的例子.

以下是完整的可调用与可使用的例子.

文件序列如下

DBConnectionManager.java

dbop.java

db.properties

test.jsp

test2.jsp

以下为DBConnectionManager.java 源码

package com.china3cts;
import java.sql.*;
import java.io.*;
import java.util.*;
public class DBConnectionManager {

  private static int clients = 0;
  private static DBConnectionManager instance;
  private Vector drivers = new Vector();
  private PrintWriter log;
  private Hashtable pools = new Hashtable();

  public DBConnectionManager() {
    init();
  }
  private void log(String msg) {
    log.println(new java.util.Date() + ": " + msg);
  }

  /**
  * 将文本信息与异常写入日志文件
  */
  private void log(Throwable e, String msg) {
    log.println(new java.util.Date() + ": " + msg);
    e.printStackTrace(log);
  }

  public static synchronized  DBConnectionManager getInstance() {
    if (instance == null) {
      instance = new DBConnectionManager();
    }
    clients++;
    return instance;
  }
  private void init() {
    InputStream is = getClass().getResourceAsStream("db.properties");
    Properties dbProps = new Properties();
    try {
      dbProps.load(is);
    }
    catch (Exception e) {
        System.err.println("Can not read the properties file. " +
        "Make sure db.properties is in the CLASSPATH");
        return;
    }
 
    loadDrivers(dbProps);
    createPools(dbProps);
  }
  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;
  }
  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());
      }
    }
  }

/*
drivers=sun.jdbc.odbc.JdbcOdbcDriver jdbc.idbDriver

logfile=D://user//src//java//DBConnectionManager//log.txt

idb.url=jdbc:idb:c://local//javawebserver1.1//db//db.prp

idb.maxconn=2

access.url=jdbc:odbc:demo

access.user=demo

access.password=demopw

*/

  private void loadDrivers(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);
        //System.err.println("Registered JDBC driver " + driverClassName);
      }
      catch (Exception e) {
        System.err.println("Can not register JDBC driver: " + driverClassName + ", Exception: " + e.toString());
      }
    }
  }
  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) {
          System.err.println("No URL specified for " + poolName);
          continue;
        }
        String user = props.getProperty(poolName + ".user");
        String password = props.getProperty(poolName + ".password");
        String maxconn = props.getProperty(poolName + ".maxconn", "0");
  //System.err.println("Url: " + url+" ,User:"+user+",password:"+password+",maxconn:"+maxconn+"...");
        int max;
        try {
          max = Integer.valueOf(maxconn).intValue();
        }
        catch (NumberFormatException e) {
          System.err.println("Invalid maxconn value " + maxconn + " for " +   poolName);
          max = 0;
        }
        DBConnectionPool pool =
          new DBConnectionPool(poolName, url, user, password, max);
        pools.put(poolName, pool);
       // System.err.println("Initialized pool " + poolName);
      }
    }
  }

  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;

    /**
    * 创建新的连接池
    *
    * @param name 连接池名字
    * @param URL 数据库的JDBC URL
    * @param user 数据库帐号,或 null
    * @param password 密码,或 null
    * @param maxConn 此连接池允许建立的最大连接数
    */
    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;
    }

  /**
  * 将不再使用的连接返回给连接池
  *
  * @param con 客户程序释放的连接
  */
    public synchronized void freeConnection(Connection con) {
    // 将指定连接加入到向量末尾
      freeConnections.addElement(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;
    }

  /**
  * 从连接池获取可用连接.可以指定客户程序能够等待的最长时间
  * 参见前一个getConnection()方法.
  *
  * @param timeout 以毫秒计的等待时间限制
  */
    public synchronized Connection getConnection(long timeout)
    {
      long startTime = new java.util.Date().getTime();
      Connection con;
      while ((con = getConnection()) == null)
      {
        try {
          wait(timeout);
        }
        catch (InterruptedException e) {}
        if ((new java.util.Date().getTime() - startTime) >= timeout)
        {
        // wait()返回的原因是超时
          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(e, "无法关闭连接池" + name+"中的连接");
        }
      }
      freeConnections.removeAllElements();
    }

  /**
  * 创建新的连接
  */
    private Connection newConnection()
    {
      Connection con = null;
      try {
        if (user == null) {
          con = DriverManager.getConnection(URL);
        }
        else {
          con = DriverManager.getConnection(URL, user, password);
        }
        //System.err.println("连接池" + name+"创建一个新的连接");
      }
      catch (SQLException e) {
        log(e, "无法创建下列URL的连接: " + URL);
        return null;
      }
      return con;
    }
  }
}

 

以下为dbop.java 源码

package com.china3cts;
import java.sql.*;
public class dbop
{
ResultSet rs = null;
public ResultSet execute(String sql)
{
 rs = null;
 try
  {
   DBConnectionManager connMgr;
   connMgr = DBConnectionManager.getInstance();
   Connection conn = connMgr.getConnection("china3cts");
   Statement stmt = conn.createStatement();
   rs = stmt.executeQuery(sql);
  }
 catch(SQLException ex)
  {
   System.err.println("查询出错: " + ex.getMessage());
  }
return rs;
}

public int update(String sql)
{
 int outn=0;
 try
  {
   DBConnectionManager connMgr;
   connMgr = DBConnectionManager.getInstance();
   Connection conn = connMgr.getConnection("china3cts");
   Statement stmt = conn.createStatement();
   outn=stmt.executeUpdate(sql);
  }
 catch(SQLException ex)
  {
   System.err.println("查询出错: " + ex.getMessage());
  }
return outn;
}
}

以下为db.properties 配置文件

drivers=org.gjt.mm.mysql.Driver
logfile=D://Tomcat//webapps//ROOT//logs//dbpool.log
china3cts.url=jdbc:mysql://localhost/china3cts
china3cts.user=root
china3cts.password=
china3cts.maxconn=1000

java文件javac后会生成如下几个文件

DBConnectionManager$DBConnectionPool.class
DBConnectionManager.class
dbop.class

各文件位置存放

class和db.properties请置于

/Web-inf/Classes/com/china3cts 目录下

以下是test.jsp

<%@ page contentType="text/html;charset=GBK"%>
<%@ page language="java" import="java.sql.*" %>
<%@ page language="java" import="com.china3cts.DBConnectionManager" %>
<%
Statement stmt = null;
DBConnectionManager connMgr;
connMgr = DBConnectionManager.getInstance();
Connection conn = connMgr.getConnection("china3cts");
if (conn!=null)
{
 out.print("good");
 String SQL="select id from sometable limit 1";
 stmt = conn.createStatement();
 ResultSet rs = null;
 rs=stmt.executeQuery(SQL);
 if (rs.next())
 {
  out.print(rs.getInt(1));
 }
 else
 {
  out.print("Error!");
 }
}
else
{
 out.print("worse");
}
%>

 

以下是test2.jsp 源码

<%@ page contentType="text/html;charset=GBK"%>
<%@ page language="java" import="java.sql.*" %>
<%@ page language="java" import="com.china3cts.*" %>
<%
ResultSet rs=null;
dbop conn=new dbop();
String SQL="select id from sometable limit 1";
rs=conn.execute(SQL);
if (rs.next())
{
 out.print(rs.getInt(1));
}
else
{
 out.print("Error!");
}

%>

 

注释与说明如下

0:DBConnectionManager为网上大虾所原创,原作者无从考究.

1:test.jsp 是单纯调用连接池测试, 实际应用的时候记得调用release方法清空一下

2:test2.jsp是测试我自己写的数据库操作扩展类,因为我以前是写ASP的.习惯了conn.execute,所以也顺便写个conn.execute 好一点.

3:二个测试程序应该得到同样的结果,只不过第一个程序多了一个good!

4:实际应用中请酌情使用dbop.java,这个是我自己写的.呵呵.

5:程序中或者源码中多次出现的china3cts

其中源码中打包出现的china3cts指的是包名.可以自行修改

而测试程序中出现的china3cts则是配置文件中的键名china3cts

已知bug

1 , 经过无数次测试,已知原DBConnectionManager.java源程序中的log好像无法使用.

我只把其中新获取连接中的log改成system.err.println

2,已有人称此连接池为假的并发连接池,是队列连接池.是真是假,无从判断.也没时间.

如果有大虾知道log如何调试好,麻烦告诉本人,谢谢,小弟弟MSN:info@hkeb.com QQ:3292957

非常感谢.

2006-07-03 于 凌晨4:30  深圳