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