在执行数据库SQL语句时，我们先要进行数据连接；而每次创建新的数据库的连接要消耗大量的资源，这样，大家就想出了数据库连接池技术。它的原理是，在运行过程中，同时打开着一定数量的数据库连接，形成数据连接池，当需要用到数据连接时，就从中取出一个连接，完成某些SQL操作后，系统自动回收，以供其它用户（或进程）调用。
连接池和连接池管理类
DBConnectionManager.java
//以下为数据库连接池管理类的代码清单，为了保证在整个应用中的数据库连接池管理类的实例只有一个，因此构造函数是私有的，通过静态函数getInstance（）的到这个唯一的管理类实例。
package com.range0505;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.util.Enumeration;
import java.util.Properties;
import java.util.StringTokenizer;
import java.util.Vector;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
/**
* used to manage DBConnectionPool
* and the number of this class's instances is limited to be only one
* so the constructor is private
* getInstance() function is used to get the only instance of this class
*
* @author range0505
* Aug 29, 2008 3:32:28 PM
*
* any question, please contact 'http://blog.csdn.net/range0505'
*/
public class DBConnectionManager {
/**
* log
*/
private static final Log log = LogFactory.getLog(DBConnectionManager.class);
/**
* the very only DBConnectionManager instance
*/
private static DBConnectionManager instance;
/**
* how many process get the DBConnectionManager instance and use the connectionPool
*/
private static int clients = 0;
/**
* db driver
*/
private Vector<Driver> drivers;
/**
* connection pool
*/
private DBConnectionPool pool;
/**
* the props read from the configuration file which are used to create db connection
*/
private Properties props;
/**
* the constructor
* for that there must be only one DBConnectionManager instance, so the constructor is private
*/
private DBConnectionManager() {
init();
}
/**
* get DBConnectionManager instance
*
* @return DBConnectionManager instance
*/
public synchronized static DBConnectionManager getInstance() {
if(instance == null) {
instance = new DBConnectionManager();
}
++clients;
return instance;
}
/**
* init DBConnectionManager
* load db driver and create pool
*/
private void init() {
InputStream is = this.getClass().getResourceAsStream(
"db.properties");
try {
props = new Properties();
props.load(is);
} catch (Exception e) {
log.error("Cannot load db.properties", e);
}
loadDriver();
createPool();
}
/**
* load db driver
*/
private void loadDriver() {
drivers = new Vector<Driver>();
String driverClasses = props.getProperty("driver");
StringTokenizer st = new StringTokenizer(driverClasses);
while (st.hasMoreElements()) {
String driverClassName = st.nextToken().trim();
try {
Driver driver = (Driver)Class.forName(driverClassName).newInstance();
DriverManager.registerDriver(driver);
drivers.addElement(driver);
} catch (Exception ex) {
ex.printStackTrace();
}
}
}
/**
* create db connectionPool
*
*/
private void createPool() {
int minConn = Integer.parseInt(props.getProperty("minConnection"));
int maxConn = Integer.parseInt(props.getProperty("maxConnection"));
pool = new DBConnectionPool(props.getProperty("username"),
props.getProperty("password"), props.getProperty("connectionURL"),
minConn, maxConn);
}
/**
* get a dbconnection from pool
* @return db connection
*/
public Connection getConnection() {
int timeout = Integer.parseInt(props.getProperty("timeout"));
int retry = Integer.parseInt(props.getProperty("retry"));
Connection conn = null;
if(pool != null) {
conn = pool.getConnection();
}
if(conn == null) {
for(int i = 0; i < retry; i++) {
conn = getConnection(timeout);
if(conn != null) {
break;
}
}
}
return conn;
}
/**
* if there's too much process to get connection from the pool
* there might not be enough connections
* so wait some time to reget it
*/
private Connection getConnection(long timeout) {
Connection conn = null;
log.info("sleeping for " + timeout + " miniseconds to wait for an aviable connection");
try {
Thread.sleep(timeout);
} catch (InterruptedException e) {
e.printStackTrace();
}
conn = pool.getConnection();
return conn;
}
/**
* return connection to the pool
* @param conn - connection about to return
*/
public void freeConnection(Connection conn) {
if(pool != null) {
pool.freeConnection(conn);
}
}
/**
* release the pool
* if there's still some process using the db connection, wait
*/
public void release() {
if(--clients != 0) {
return;
}
log.info("release DBConnectionPool and driver");
if(pool != null) {
pool.release();
Enumeration allDrivers = drivers.elements();
while (allDrivers.hasMoreElements()) {
Driver driver = (Driver)allDrivers.nextElement();
try {
DriverManager.deregisterDriver(driver);
} catch (Exception ex) {
System.out.println("Can not deregister driver "
+ driver.getClass().getName());
}
}
}
}
}
DBConnectionPool.java
//以下为数据库连接池类，它的很多方法，如获得连接，都是用synchronized关键字修饰的，是为了给函数加一个锁，使得两个进程不能同时运行这个函数，保证了数据库连接池相关数据的正确性。
构造函数中，首先创建min个连接，放入连接池。如果连接池里这些连接都用光了，那么再创建新的连接，最多到max个。min和max都可以在配置文件db.properties中进行设置。
package com.range0505;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
/**
* DBConnectionPool
*
* @author range0505
* Aug 29, 2008 3:32:24 PM
*
* any question, please contact 'http://blog.csdn.net/range0505'
*/
public class DBConnectionPool {
/**
* log
*/
private static final Log log = LogFactory.getLog(DBConnectionPool.class);
/**
* count how many connections are in use
*/
private int inUsed = 0;
/**
* the container used to store the free connections
*/
private ArrayList<Connection> freeConnections = new ArrayList<Connection>();
/**
* db username
*/
private String username;
/**
* db password
*/
private String password;
/**
* db url
*/
private String url;
/**
* the min connection number permitted
*/
private int minConn;
/**
* the max connection number premitted
*/
private int maxConn;
/**
* constructor
* store the db params
* create minConn connections
*
* @param driver
* @param username
* @param password
* @param url
* @param minConn
* @param maxConn
*/
public DBConnectionPool(String username, String password, String url,
int minConn, int maxConn) {
this.username = username;
this.password = password;
this.url = url;
this.minConn = minConn;
this.maxConn = maxConn;
for(int i = 0; i < this.minConn; i++) {
freeConnections.add(newConnection());
}
}
/**
* getConnection
*
* @return a Connection got from pool
*/
public synchronized Connection getConnection() {
Connection conn = null;
if(freeConnections.size() > 0) {
conn = freeConnections.remove(0);
if(conn == null) {
conn = getConnection();
}
} else {
conn = newConnection();
}
if(maxConn == 0 || maxConn < inUsed) {
conn = null;
log.error("there's no aviable connection in DBConnectionPool");
}
if(conn != null) {
++inUsed;
log.debug("get a connection(" + conn + ") from DBConnectionPool, now there's " + inUsed + " connection(s) in use");
}
return conn;
}
/**
* freeConnection
*
* @param conn - the connection about to return to the connection pool
*/
public synchronized void freeConnection(Connection conn) {
freeConnections.add(conn);
--inUsed;
log.debug("free a connection, now there's " + inUsed + " connection(s) in use");
}
/**
* newConnection
*
* @return the newly created connection
*/
private Connection newConnection() {
Connection conn = null;
try {
conn = DriverManager.getConnection(url, username, password);
log.debug("create a new connection");
} catch(SQLException e) {
log.error("new connection failed", e);
}
return conn;
}
/**
* release the connection pool
*/
public synchronized void release() {
Iterator<Connection> allConns = freeConnections.iterator();
while(allConns.hasNext()) {
Connection conn = allConns.next();
try {
conn.close();
} catch(SQLException e) {
log.error("release DBConntionPool failed", e);
}
}
freeConnections.clear();
inUsed = 0;
}
}
db.properties
driver=com.mysql.jdbc.Driver
username=test
password=test
connectionURL=jdbc:mysql://localhost:3306/test
minConnection=10
maxConnection=40
timeout=100
retry=5
测试
Test.java
import java.util.ArrayList;
public class Test {
private static int size = 100;
public static void main(String[] args) {
ArrayList<TestThread> threads = new ArrayList<TestThread>();
for(int i = 0; i < size; i++) {
threads.add(new TestThread());
}
for(int i = 0; i < size; i++) {
threads.get(i).start();
}
}
}
TestThread.java
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import com.range.dbconnection.DBConnectionManager;
public class TestThread extends Thread {
private static final Log log = LogFactory.getLog(TestThread.class);
public void run(){
DBConnectionManager dbConnectionManager = DBConnectionManager.getInstance();
Connection conn = dbConnectionManager.getConnection();
Statement stat = null;
ResultSet rs = null;
try {
stat = conn.createStatement();
rs = stat.executeQuery("select * from user");
while(rs.next()) {
log.debug(rs.getString("fullname"));
}
} catch(SQLException e) {
log.error("query failed", e);
} finally {
try {
if(rs != null) {
rs.close();
}
stat.close();
} catch(SQLException e) {
log.error("close rs or stat failed", e);
}
dbConnectionManager.freeConnection(conn);
}
dbConnectionManager.release();
}
}
以上为测试程序，采用多线程进行测试，可以从打印信息中看到当前连接池中的数据库连接的使用情况。当使用数达到配置文件中设置的40后，将会等待空闲连接，重试次数也在配置文件中可以设置。
在我的机器上连接使用数先从1增到40，经过一段时间的等待，数字持续在40，最后降为0.
Test.java中的‘private static int size = 100;’，这里可以设置一共有多少线程，当数字大到一定程度，程序会抛异常，但是我相信在实际应用中100已经足够。
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/range0505/archive/2008/09/12/2916405.aspx
