對(duì)于數(shù)據(jù)庫(kù)數(shù)據(jù)不停的讀取和更改，頻繁的建立Connection是很不劃算，建立連接池是很好的選擇，在多個(gè)數(shù)據(jù)庫(kù)數(shù)據(jù)之間來回倒換，需要為每個(gè)數(shù)據(jù)庫(kù)建立一個(gè)池，本例的目的就是建立一個(gè)管理這些池的管理類，達(dá)到的目的是：在我們需要一個(gè)鏈接的時(shí)候，只要提供需要那個(gè)連接池（每個(gè)連接池對(duì)于一個(gè)名字，所有的連接池都裝載入一個(gè)Map中）的名字，就可以得到鏈接進(jìn)行操作，完畢需要關(guān)閉，關(guān)閉這個(gè)鏈接并不會(huì)真正的關(guān)閉，在池中他還是存在的。

首先定義個(gè)xml文件作為我們需要的連接池的配置文件：如下：config.xml

<?xml version="1.0" encoding="UTF-8"?>
<pools>
    <pool>
        <!-- 連接池的名字 -->
        <name>one</name>
        <!-- 連接數(shù)據(jù)庫(kù)的名字 -->
        <username>name1</username>
        <!-- 連接數(shù)據(jù)庫(kù)的密碼 -->
        <password>pswd1</password>
        <!-- 連接數(shù)據(jù)庫(kù)的url -->
        <jdbcurl>jdbc:mysql://IP/DataminInfo</jdbcurl>
        <!-- 連接池的最大容納鏈接數(shù)目 -->
        <max>10</max>
        <!-- 連接池取不到鏈接等待時(shí)間 -->
        <wait></wait>
        <!-- 連接數(shù)據(jù)庫(kù)驅(qū)動(dòng) -->
        <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>

作為對(duì)象操作，這個(gè)xml有相對(duì)應(yīng)的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方法
}

 

還需要一個(gè)操作就是吧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;
    }
}

最后，最關(guān)鍵的類就是管理類了，（需要增加dbcp的jar包，同時(shí)還需要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() {
    }

    /**
     * 初始化數(shù)據(jù)源
     */
    private static synchronized void initDataSource() {
        // 驅(qū)動(dòng)數(shù)據(jù)源
        if (driverClass == null) {
            try {
                driverClass = Class.forName(driver);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 連接池啟動(dòng)
     * 
     * @throws Exception
     */
    public static void StartPool(String poolname, String dbJdbc, String dbUser,
            String dbPwd, int max, long wait) {
        // 初始化數(shù)據(jù)源
        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();
        }
    }

    /**
     * 打印連接池狀態(tài)
     */
    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);// 初始化基本數(shù)據(jù)
            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();
        }
    }

}

 

至此，基本工作已經(jīng)完成，需要鏈接，只要MyDbPool.getDbConnection("name1")，就可以得到一個(gè)鏈接，注意，用完需要?dú)w還下

多線程測(cè)試?yán)?/span>

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

}