dbcp数据库连接池

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

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 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

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方法  
} 

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

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;  
    }  
} 

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）代码如下
Java代码
 
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();  
        }  
    }  
 
} 

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")，就可以得到一个链接，注意，用完需要归还下

多线程测试例子
Java代码
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);  
        }  
    }  
 
}