数据库连接池详细说明
首先建立个池子，里面放这我们需要的链接，党我们需要链接的时候从池子里面取，取的时候先判断是否有空闲的，有就拿来用，否则检查是否全用了，如果没有全用，则新建，否则等待或者异常抛出。
假设我们要链接不同的数据库，把相关的配置写在一个xml文件，格式如下：
ds.config.xml
<ds-config>
<pool>
<type>mysql</type>
<name>test</name>
<driver>com.mysql.jdbc.Driver</driver>
<url>jdbc:mysql://localhost:3306/test</url>
<username>root</username>
<password>123456</password>
<maxconn>100</maxconn>
</pool>
<pool>
<type>mysql</type>
<name>user2</name>
<driver>com.mysql.jdbc.Driver</driver>
<url>jdbc:mysql://localhost:3306/test</url>
<username>root</username>
<password>123456</password>
<maxconn>10</maxconn>
</pool>
</ds-config>
然后我们建立个javabean来对应这个xml，
DSConfigBean.java
package com.cgogo.dbPool;
public class DSConfigBean {
private String type = ""; // 数据库类型
private String name = ""; // 连接池名字
private String driver = ""; // 数据库驱动
private String url = ""; // 数据库url
private String username = ""; // 用户名
private String password = ""; // 密码
private int maxconn = 0; // 最大连接数
public DSConfigBean() {
// TODO Auto-generated constructor stub
}
相关的set和get省略
}
接下来需要建立个池的类，来建立和释放链接
DBConnectionPool.java
package com.cgogo.dbPool;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.Timer;
public class DBConnectionPool {
private Connection con = null;
private int inUsed = 0; // 使用的连接数
private ArrayList freeConnections = new ArrayList();// 容器，空闲连接
private int minConn; // 最小连接数
private int maxConn; // 最大连接
private String name; // 连接池名字
private String password; // 密码
private String url; // 数据库连接地址
private String driver; // 驱动
private String user; // 用户名
public Timer timer; // 定时
省略set和get
public DBConnectionPool() {
}
public DBConnectionPool(String name, String driver, String URL,
String user, String password, int maxConn) {
this.name = name;
this.driver = driver;
this.url = URL;
this.user = user;
this.password = password;
this.maxConn = maxConn;
}
//用完，释放连接
public synchronized void freeConnection(Connection con) {
this.freeConnections.add(con);// 添加到空闲连接的末尾
this.inUsed--;
}
// timeout 根据timeout得到连接
public synchronized Connection getConnection(long timeout) {
Connection con = null;
if (this.freeConnections.size() > 0) {
con = (Connection) this.freeConnections.get(0);
if (con == null)
con = getConnection(timeout); // 继续获得连接
} else {
con = newConnection(); // 新建连接
}
if (this.maxConn == 0 || this.maxConn < this.inUsed) {
con = null;// 达到最大连接数，暂时不能获得连接了。
}
if (con != null) {
this.inUsed++;
}
return con;
}
// 从连接池里得到连接
public synchronized Connection getConnection() {
Connection con = null;
if (this.freeConnections.size() > 0) {
con = (Connection) this.freeConnections.get(0);
this.freeConnections.remove(0);// 如果连接分配出去了，就从空闲连接里删除
if (con == null)
con = getConnection(); // 继续获得连接
} else {
con = newConnection(); // 新建连接
}
if (this.maxConn == 0 || this.maxConn < this.inUsed) {
con = null;// 等待 超过最大连接时
}
if (con != null) {
this.inUsed++;
System.out.println("得到 " + this.name + " 的连接，现有" + inUsed
+ "个连接在使用!");
}
return con;
}
// 释放全部连接
public synchronized void release() {
Iterator allConns = this.freeConnections.iterator();
while (allConns.hasNext()) {
Connection con = (Connection) allConns.next();
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
this.freeConnections.clear();
}
//创建新连接
private Connection newConnection() {
try {
Class.forName(driver);
con = DriverManager.getConnection(url, user, password);
} catch (ClassNotFoundException e) {
e.printStackTrace();
System.out.println("sorry can't find db driver!");
} catch (SQLException e1) {
e1.printStackTrace();
System.out.println("sorry can't create Connection!");
}
return con;
}
}
接下来有个管理连接池的类
DBConnectionManager.java
package com.cgogo.dbPool;
import java.sql.Connection;
import java.util.Enumeration;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.Vector;
public class DBConnectionManager {
static private DBConnectionManager instance;// 唯一数据库连接池管理实例类
static private int clients; // 客户连接数
private Vector drivers = new Vector();// 驱动信息
private Hashtable pools = new Hashtable();// 连接池
// 实例化管理类
public DBConnectionManager() {
this.init();
}
// 得到唯一实例管理类
static synchronized public DBConnectionManager getInstance() {
if (instance == null) {
instance = new DBConnectionManager();
}
return instance;
}
// 释放连接
public void freeConnection(String name, Connection con) {
DBConnectionPool pool = (DBConnectionPool) pools.get(name);// 根据关键名字得到连接池
if (pool != null)
pool.freeConnection(con);// 释放连接
}
//得到一个连接根据连接池的名字name
public Connection getConnection(String name) {
DBConnectionPool pool = null;
Connection con = null;
pool = (DBConnectionPool) pools.get(name);// 从名字中获取连接池
con = pool.getConnection();// 从选定的连接池中获得连接
if (con != null)
System.out.println("得到连接。。。");
return con;
}
// 得到一个连接，根据连接池的名字和等待时间
public Connection getConnection(String name, long timeout) {
DBConnectionPool pool = null;
Connection con = null;
pool = (DBConnectionPool) pools.get(name);// 从名字中获取连接池
con = pool.getConnection(timeout);// 从选定的连接池中获得连接
System.out.println("得到连接。。。");
return con;
}
// 释放所有连接
public synchronized void release() {
Enumeration allpools = pools.elements();
while (allpools.hasMoreElements()) {
DBConnectionPool pool = (DBConnectionPool) allpools.nextElement();
if (pool != null)
pool.release();
}
pools.clear();
}
// 创建连接池
private void createPools(DSConfigBean dsb) {
DBConnectionPool dbpool = new DBConnectionPool();
dbpool.setName(dsb.getName());
dbpool.setDriver(dsb.getDriver());
dbpool.setUrl(dsb.getUrl());
dbpool.setUser(dsb.getUsername());
dbpool.setPassword(dsb.getPassword());
dbpool.setMaxConn(dsb.getMaxconn());
System.out.println("ioio:" + dsb.getMaxconn());
pools.put(dsb.getName(), dbpool);
}
//初始化连接池的参数
private void init() {
this.loadDrivers();// 加载驱动程序
Iterator alldriver = drivers.iterator();// 创建连接池
while (alldriver.hasNext()) {
this.createPools((DSConfigBean) alldriver.next());
System.out.println("创建连接池。。。");
}
System.out.println("创建连接池完毕。。。");
}
//加载驱动程序
private void loadDrivers() {
ParseDSConfig pd = new ParseDSConfig();
drivers = pd.readConfigInfo("ds.config.xml");// 读取数据库配置文件
System.out.println("加载驱动程序。。。");
}
}
对xml文件解析类ParseDSConfig.java
package com.cgogo.dbPool;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;
import java.util.Vector;
import java.util.Iterator;
import org.jdom.Document;
import org.jdom.Element;
import org.jdom.JDOMException;
import org.jdom.input.SAXBuilder;
import org.jdom.output.Format;
import org.jdom.output.XMLOutputter;
public class ParseDSConfig {
public ParseDSConfig() {
}
//读取xml配置文件
public Vector readConfigInfo(String path) {
String rpath = this.getClass().getResource("").getPath().substring(1)
+ path;
Vector dsConfig = null;
FileInputStream fi = null;
try {
fi = new FileInputStream(rpath);// 读取路径文件
dsConfig = new Vector();
SAXBuilder sb = new SAXBuilder();
Document doc = sb.build(fi);
Element root = doc.getRootElement();
List pools = root.getChildren();
Element pool = null;
Iterator allPool = pools.iterator();
while (allPool.hasNext()) {
pool = (Element) allPool.next();
DSConfigBean dscBean = new DSConfigBean();
dscBean.setType(pool.getChild("type").getText());
dscBean.setName(pool.getChild("name").getText());
System.out.println(dscBean.getName());
dscBean.setDriver(pool.getChild("driver").getText());
dscBean.setUrl(pool.getChild("url").getText());
dscBean.setUsername(pool.getChild("username").getText());
dscBean.setPassword(pool.getChild("password").getText());
dscBean.setMaxconn(Integer.parseInt(pool.getChild("maxconn")
.getText()));
dsConfig.add(dscBean);
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (JDOMException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
finally {
try {
fi.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return dsConfig;
}
}
把ds.config.xml放置包名下，可以写个测试类
TestPool.java
package com.cgogo.dbPool;
import java.sql.*;
public class TestPool {
public static void main(String[] args) {
DBConnectionManager connectionMan = DBConnectionManager.getInstance();
String name = "test";// 从上下文得到你要访问的数据库的名字
Connection con = null;
// 操作
try {
con = connectionMan.getConnection(name);
Statement stmt = con.createStatement();
String sql = "select * from test";
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
System.out.println(rs.getString("name"));
System.out.println(rs.getString("password"));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
connectionMan.freeConnection(name, con);// 释放，但并未断开连接
}
try {
con = connectionMan.getConnection(name);
Statement stmt = con.createStatement();
String sql = "select * from test";
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
System.out.println(rs.getString("name"));
System.out.println(rs.getString("password"));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
connectionMan.freeConnection(name, con);
}
}
}
控制台输出结果
Test
User2
加载驱动程序。。。
ioio:100
创建连接池。。。
ioio:1000
创建连接池。。。
创建连接池完毕。。。
得到 test 的连接，现有1个连接在使用!
得到连接。。。
tangshun
tangshun
得到 test 的连接，现有1个连接在使用!
得到连接。。。
tangshun
tangshun