数据库连接池管理-优快云博客

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

Xml代码
<?xml version="1.0" encoding="UTF-8"?>
<pools>
<pool>

<name>one</name>

<username>name1</username>

<password>pswd1</password>

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

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

}

dbcp数据库连接池