DBCP数据库连接池

原创
2010/11/02 16:49
阅读数 1.7K
对于数据库数据不停的读取和更改,频繁的建立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有相对应的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方法  
}


还需要一个操作就是吧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();  
        }  
    }  
  
}


至此,基本工作已经完成,需要链接,只要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);  
        }  
    }  
  
}  

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

}

展开阅读全文
打赏
0
7 收藏
分享
加载中
这个例子很不错啊,很好
2012/11/15 17:47
回复
举报
更多评论
打赏
1 评论
7 收藏
0
分享
返回顶部
顶部