對于數據庫數據不停的讀取和更改，頻繁的建立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);
}
}
}