DBCP数据库连接池

对于数据库数据不停的读取和更改，频繁的建立Connection是很不划算，建立连接池是很好的选择，在多个数据库数据之间来回倒换，需要为每个数据库建立一个池，本例的目的就是建立一个管理这些池的管理类，达到的目的是：在我们需要一个链接的时候，只要提供需要那个连接池（每个连接池对于一个名字，所有的连接池都装载入一个Map中）的名字，就可以得到链接进行操作，完毕需要关闭，关闭这个链接并不会真正的关闭，在池中他还是存在的。
首先定义个xml文件作为我们需要的连接池的配置文件：如下：config.xml

<?xml version="1.0" encoding="UTF-8"?>
<pools>
    <pool>
        <!-- 连接池的名字 -->
        <name>one</name>
        <!-- 连接数据库的名字 -->
        <username>name1</username>
        <!-- 连接数据库的密码 -->
        <password>pswd1</password>
        <!-- 连接数据库的url -->
        <jdbcurl>jdbc:mysql://IP/DataminInfo</jdbcurl>
        <!-- 连接池的最大容纳链接数目 -->
        <max>10</max>
        <!-- 连接池取不到链接等待时间 -->
        <wait></wait>
        <!-- 连接数据库驱动 -->
        <driver>com.mysql.jdbc.Driver</driver>
    </pool>
    <pool>
        <name>two</name>
        <username>name2</username>
        <password>paswd2</password>
        <jdbcurl>jdbc:mysql://IP/UIC</jdbcurl>
        <max>10</max>
        <wait></wait>
        <driver>com.mysql.jdbc.Driver</driver>
    </pool>
</pools>

作为对象操作，这个xml有相对应的Bean：如下：

BaseConnBean.java

public class BaseConnBean {

    private String name;
    private String username;
    private String password;
    private String jdbcurl;
    private int max;
    private long wait;
    private String driver;
    public String getDriver() {
        return driver;
    }
    public void setDriver(String driver) {
        this.driver = driver;
    }
……//其他set get方法
}

还需要一个操作就是吧xml文件组装成Bean，并把这些bean加到list里面，代码如下

ConfigXml.java

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.jdom.Document;
import org.jdom.Element;
import org.jdom.JDOMException;
import org.jdom.input.SAXBuilder;

import com.cgogo.mymodel.po.BaseConnBean;

public class ConfigXml {
    
    public static void main(String[] args) {
        read("config.xml");
    }
    
    public static List<BaseConnBean> read(){
        return read("config.xml");
    }

    public static List<BaseConnBean> read(String path) {
        String rpath = ConfigXml.class.getResource("").getPath().substring(1)
                + path;
        FileInputStream fi = null;
        List<BaseConnBean> pools=new ArrayList<BaseConnBean>();
        try {
            fi = new FileInputStream(rpath);
            SAXBuilder sb = new SAXBuilder();
            Document doc = sb.build(fi);
            Element root = doc.getRootElement();
            List list=root.getChildren();
            Element pool = null;
            Iterator allPool = list.iterator();
            while(allPool.hasNext()){
                pool=(Element) allPool.next();
                BaseConnBean bcBean=new BaseConnBean();
                bcBean.setName(pool.getChildText("name"));
                
                bcBean.setUsername(pool.getChildText("username"));
                bcBean.setPassword(pool.getChildText("password"));
                bcBean.setJdbcurl(pool.getChildText("jdbcurl"));
                try{
                bcBean.setMax(Integer.parseInt(pool.getChildText("max")));
                }catch(NumberFormatException e){
                    bcBean.setMax(0);
                }
                try{
                bcBean.setWait(Long.parseLong(pool.getChildText("wait")));
                }catch(NumberFormatException e){
                    bcBean.setWait(-1L);
                }
                bcBean.setDriver(pool.getChildText("driver"));
                pools.add(bcBean);
            }
        } catch (FileNotFoundException e) {
            System.out.println("file does not find");
            e.printStackTrace();
        } catch (JDOMException e) {
            System.out.println("jdom exception");
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return pools;
    }
}

最后，最关键的类就是管理类了，（需要增加dbcp的jar包，同时还需要commons-pools）代码如下

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.DriverManagerConnectionFactory;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.PoolingDriver;
import org.apache.commons.pool.ObjectPool;
import org.apache.commons.pool.impl.GenericObjectPool;

import com.cgogo.mymodel.po.BaseConnBean;
import com.cgogo.mymodel.util.ConfigXml;

public class MyDbPool {
    public static void main(String[] args) {

    }

    private static String dbJdbc = null;

    private static String dbUser = null;

    private static String dbPwd = null;

    private static int max;

    private static long wait;

    private static String driver = null;

    private static Class driverClass = null;

    private static ObjectPool connectionPool = null;

    public static Map<String, ObjectPool> map = null;

    public MyDbPool() {
    }

    /** *//**
     * 初始化数据源
     */
    private static synchronized void initDataSource() {
        // 驱动数据源
        if (driverClass == null) {
            try {
                driverClass = Class.forName(driver);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }
        }
    }

    /** *//**
     * 连接池启动
     * 
     * @throws Exception
     */
    public static void StartPool(String poolname, String dbJdbc, String dbUser,
            String dbPwd, int max, long wait) {
        // 初始化数据源
        initDataSource();
        // 如果连接池为空
        if (connectionPool != null) {
            ShutdownPool();
        }
        try {
            connectionPool = new GenericObjectPool(null, max, (byte) 1, wait);
            ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(
                    dbJdbc, dbUser, dbPwd);
            PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(
                    connectionFactory, connectionPool, null, null, false, true);
            Class.forName("org.apache.commons.dbcp.PoolingDriver");
            PoolingDriver driver = (PoolingDriver) DriverManager
                    .getDriver("jdbc:apache:commons:dbcp:");
            driver.registerPool(poolname, connectionPool);
            map.put(poolname, connectionPool);
            System.out.println("Create " + poolname
                    + " for Database Connection Succees.");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /** *//**
     * 释放连接池
     */
    public static void ShutdownPool() {
        try {
            PoolingDriver driver = (PoolingDriver) DriverManager
                    .getDriver("jdbc:apache:commons:dbcp:");
            driver.closePool("dbpool");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /** *//**
     * 打印连接池状态
     */
    public static String GetPoolStats(String poolname) {

        StringBuffer stat = new StringBuffer();
        try {
            PoolingDriver driver = (PoolingDriver) DriverManager
                    .getDriver("jdbc:apache:commons:dbcp:");
            ObjectPool connectionPool = driver.getConnectionPool(poolname);

            stat.append("-- Active Connection: ");
            stat.append(connectionPool.getNumActive());
            stat.append(" ,");
            stat.append("Free Connection: ");
            stat.append(connectionPool.getNumIdle());
            stat.append(" . --");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return stat.toString();
    }

    /** *//**
     * 取得连接池中的连接
     * 
     * @return
     */
    public synchronized static Connection getDbConnection(String poolname) {
        Connection conn = null;
        if (map == null) {
            System.out.println("map null");
            map = new HashMap<String, ObjectPool>();
        }
        if (map.get(poolname) == null) {
            init(poolname);// 初始化基本数据
            StartPool(poolname, dbJdbc, dbUser, dbPwd, max, wait);
        }
        try {
            conn = DriverManager.getConnection("jdbc:apache:commons:dbcp:"
                    + poolname);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    private static void init(String poolname) {
        List<BaseConnBean> pools = ConfigXml.read();
        for (BaseConnBean baseConnBean : pools) {
            if (baseConnBean.getName().equals(poolname)) {
                dbJdbc = baseConnBean.getJdbcurl();
                dbUser = baseConnBean.getUsername();
                dbPwd = baseConnBean.getPassword();
                max = baseConnBean.getMax();
                wait = baseConnBean.getWait();
                driver = baseConnBean.getDriver();
            }

        }
    }

    public static void close(Connection c) {
        try {
            if (c != null)
                c.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}

至此，基本工作已经完成，需要链接，只要MyDbPool.getDbConnection("name1")，就可以得到一个链接，注意，用完需要归还下

多线程测试例子

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Test1 extends Thread{

    private String sql;
    private String poolname;
    private Connection c;
    public Test1(String poolname,Connection c,String sql){
        this.poolname=poolname;
        this.c=c;
        this.sql=sql;
    }
    public static void main(String[] args) {
        
        for(int i=0;i<100;i++){
            String sql="select * from table1 limit "+i+",1";

            new Test1("datamininfo",MyDbPool.getDbConnection("datamininfo"),sql).start();
            
            sql="select * from table2 limit "+i+",1";
            new Test1("uic",MyDbPool.getDbConnection("uic"),sql).start();
        }

    }
    
    public void run(){
        try {
            ResultSet rs=c.createStatement().executeQuery(sql);
            while(rs.next()){
                System.out.println(rs.getString(1));
            }
            if(poolname.equals("datamininfo"))
            System.out.println("DataMinInfo:\n"+MyDbPool.GetPoolStats("name1"));
            else
            System.out.println("UIC:\n"+MyDbPool.GetPoolStats("name2"));
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            MyDbPool.close(c);
        }
    }

}

评论

10 楼 knzl2002 2008-08-03  

嗯，学习学习

9 楼 sunsong 2008-07-13  

dbcp是不太稳定，c3p0总体反应好一点，而且参数更多，可配置性更强。

8 楼 zojomotico 2008-07-13  

恩 学习到了

7 楼 soleghost 2008-07-12  

曾经做一个项目的时候，dbcp的数据库的连接池经常断掉，不知道什么原因
后来用c3p0就没事了

6 楼 zingers 2008-07-12  

C3P0。。

5 楼 jwin 2008-07-12  

可以参考spring的AbstractRoutingDataSource，可以多态切换数据库

4 楼 kuangbaoxu 2008-06-29  

3楼的好建议,我没有考虑这一点.多谢

3 楼 cddcdd 2008-06-25  

找夏新的hibernate的书看一下

我看你好像没有关闭连接的处理

这个时候该请出动态代理出来了

2 楼 SeanHe 2008-06-25  

dbcp都实现好的东西，为什么要自己去实现一边呢？

1 楼 AllenZhang 2008-06-25  

close的时候，直接把connection关了？
那不是池很快就干了？