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

尽管很多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("<H1>职工数据</H1>");
while(rs.next()){
out.println("<BR>");
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数据库):

以下是最终的执行结果: