对于数据库数据不停的读取和更改,频繁的建立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;
    }

}


最后,最关键的类就是管理类了,(需要增加dbcpjar包,同时还需要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, (byte1, wait);
            ConnectionFactory connectionFactory 
= new DriverManagerConnectionFactory(
                    dbJdbc, dbUser, dbPwd);
            PoolableConnectionFactory poolableConnectionFactory 
= new PoolableConnectionFactory(
                    connectionFactory, connectionPool, 
nullnullfalsetrue);
            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);
        }

    }


}