Edzy_Java

??????? 雖然 J2EE 程序員一般都有現(xiàn)成的應(yīng)用服務(wù)器所帶的JDBC 數(shù)據(jù)庫連接池，不過對(duì)于開發(fā)一般的 Java Application 、 Applet 或者 JSP、velocity 時(shí)，我們可用的JDBC 數(shù)據(jù)庫連接池并不多，并且一般性能都不好。
??????? Java 程序員都很羨慕 Windows ADO ，只需要 new Connection 就可以直接從數(shù)據(jù)庫連接池中返回 Connection。并且 ADO Connection 是線程安全的，多個(gè)線程可以共用一個(gè) Connection， 所以 ASP 程序一般都把 getConnection 放在 Global.asa 文件中，在 IIS 啟動(dòng)時(shí)建立數(shù)據(jù)庫連接。ADO 的 Connection 和 Result 都有很好的緩沖，并且很容易使用。

??????? 其實(shí)我們可以自己寫一個(gè)JDBC 數(shù)據(jù)庫連接池。寫 JDBC connection pool 的注意事項(xiàng)有：

1. 有一個(gè)簡(jiǎn)單的函數(shù)從連接池中得到一個(gè) Connection。
2. close 函數(shù)必須將 connection 放回 數(shù)據(jù)庫連接池。
3. 當(dāng)數(shù)據(jù)庫連接池中沒有空閑的 connection， 數(shù)據(jù)庫連接池必須能夠自動(dòng)增加 connection 個(gè)數(shù)。
4. 當(dāng)數(shù)據(jù)庫連接池中的 connection 個(gè)數(shù)在某一個(gè)特別的時(shí)間變得很大，但是以后很長(zhǎng)時(shí)間只用其中一小部分，應(yīng)該可以自動(dòng)將多余的 connection 關(guān)閉掉。
5. 如果可能，應(yīng)該提供debug 信息報(bào)告沒有關(guān)閉的 new Connection 。

??????? 如果要 new Connection 就可以直接從數(shù)據(jù)庫連接池中返回 Connection， 可以這樣寫( Mediator pattern ) (以下代碼中使用了中文全角空格)：

public class EasyConnection implements java.sql.Connection{
private Connection m_delegate = null;
public EasyConnection(){
m_delegate = getConnectionFromPool();
}
public void close(){
putConnectionBackToPool(m_delegate);
}
public PreparedStatement prepareStatement(String sql) throws SQLException{
m_delegate.prepareStatement(sql);
}
//...... other method
}

??????? 看來并不難。不過不建議這種寫法，因?yàn)閼?yīng)該盡量避免使用 Java Interface, 關(guān)于 Java Interface 的缺點(diǎn)我另外再寫文章討論。大家關(guān)注的是 Connection Pool 的實(shí)現(xiàn)方法。下面給出一種實(shí)現(xiàn)方法。

package connectionpool;

import java.sql.*;
import java.lang.reflect.*;
import java.util.*;
import java.io.*;

public class SimpleConnetionPool {
?private static LinkedList<Object> m_notUsedConnection = new LinkedList<Object>();
?private static HashSet<Object> m_usedUsedConnection = new HashSet<Object>();
?private static String m_url = "jdbc:microsoft:sqlserver://127.0.0.1:1433;DatabaseName=campusblog";
?private static String m_user = "sa";
?private static String m_password = "sa";
?static final boolean DEBUG = true;
?static private long m_lastClearClosedConnection = System.currentTimeMillis();
?public static long CHECK_CLOSED_CONNECTION_TIME = 4 * 60 * 60 * 1000; //4 hours

?static {
??initDriver();
??}

?private SimpleConnetionPool() {?
?}

?private static void initDriver() {
??Driver driver = null;
??//load mysql driver
??try {
???driver = (Driver) Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
???installDriver(driver);
??}
??catch (Exception e) {}
? ////////////////////////////////////////////////////////////////////////////////////////////////////////////////選擇使用
??//load postgresql driver
??//try {
???//driver = (Driver) Class.forName("org.postgresql.Driver").newInstance();
???//installDriver(driver);
??//}
??//catch (Exception e){}
////////////////////////////////////////////////////////////////////////////////////////////////////////////////選擇使用

?}

?public static void installDriver(Driver driver) {
??try {
???DriverManager.registerDriver(driver);
??}
??catch (Exception e) {
???e.printStackTrace();
??}
?}

?public static synchronized Connection getConnection() {
??clearClosedConnection();
??while (m_notUsedConnection.size() > 0) {
???try{
????ConnectionWrapper wrapper = (ConnectionWrapper) m_notUsedConnection.removeFirst();
????if (wrapper.connection.isClosed()) {
?????continue;
????}
????m_usedUsedConnection.add(wrapper);
????if(DEBUG){
?????wrapper.debugInfo = new Throwable("Connection initial statement");
????}
???return wrapper.connection;
???}catch (Exception e){}
??}
??int newCount = getIncreasingConnectionCount();
??LinkedList<Object> list = new LinkedList<Object>();
??ConnectionWrapper wrapper = null;
??for (int i = 0; i < newCount; i++) {
???wrapper = getNewConnection();
???if (wrapper != null) {
????list.add(wrapper);
???}
??}
??if (list.size() == 0) {
???return null;
??}
??wrapper = (ConnectionWrapper) list.removeFirst();
??m_usedUsedConnection.add(wrapper);
??m_notUsedConnection.addAll(list);
??list.clear();
??return wrapper.connection;
?}
?
?private static ConnectionWrapper getNewConnection() {
??try{
???Connection con = DriverManager.getConnection(m_url, m_user, m_password);
???ConnectionWrapper wrapper = new ConnectionWrapper(con);
???return wrapper;
??}
??catch (Exception e) {
???e.printStackTrace();
??}
??return null;
?}
?
?static synchronized void pushConnectionBackToPool(ConnectionWrapper con) {
??boolean exist = m_usedUsedConnection.remove(con);
??if (exist) {
???m_notUsedConnection.addLast(con);
??}
?}
?
?public static int close() {
??int count = 0;
??Iterator iterator = m_notUsedConnection.iterator();
??while (iterator.hasNext()) {
???try{
????((ConnectionWrapper) iterator.next()).close();
????count++;
???}
???catch (Exception e) {}
??}
??m_notUsedConnection.clear();
??iterator = m_usedUsedConnection.iterator();
??while (iterator.hasNext()) {
???try{
????ConnectionWrapper wrapper = (ConnectionWrapper) iterator.next();
????wrapper.close();
????if (DEBUG) {
?????wrapper.debugInfo.printStackTrace();
????}
????count++;
???}catch (Exception e){}
??}
??m_usedUsedConnection.clear();
??return count;
?}
?
?private static void clearClosedConnection() {
??long time = System.currentTimeMillis();
??//sometimes user change system time,just return
??if (time < m_lastClearClosedConnection) {
???time = m_lastClearClosedConnection;
???return;
??}
??//no need check very often
??if (time - m_lastClearClosedConnection < CHECK_CLOSED_CONNECTION_TIME) {
???return;
???}
??m_lastClearClosedConnection = time;
??//begin check
??Iterator iterator = m_notUsedConnection.iterator();
??while (iterator.hasNext()) {
???ConnectionWrapper wrapper = (ConnectionWrapper) iterator.next();
???try {
????if (wrapper.connection.isClosed()) {
?????iterator.remove();
????}
????} catch (Exception e) {
?????iterator.remove();
?????if (DEBUG) {
??????System.out.println("connection is closed, this connection initial StackTrace");
??????wrapper.debugInfo.printStackTrace();
?????}
????}
???}
??//make connection pool size smaller if too big
??int decrease = getDecreasingConnectionCount();
??if (m_notUsedConnection.size() < decrease) {
???return;
???}
??while (decrease-- > 0) {
???ConnectionWrapper wrapper = (ConnectionWrapper) m_notUsedConnection.removeFirst();
???try {
????wrapper.connection.close();
????} catch (Exception e){}
???}
??}
?
?/**
? * * get increasing connection count, not just add 1 connection
? * * @return count
? * */
?public static int getIncreasingConnectionCount(){
??int count = 1;
??int current = getConnectionCount();
??count = current / 4;
??if (count < 1) {
???count = 1;
???}
??return count;
?}
?
?/**
? * * get decreasing connection count, not just remove 1 connection
? * * @return count
? * */
?public static int getDecreasingConnectionCount(){
??//int count = 0;
??int current = getConnectionCount();
??if (current < 10){
???return 0;
??}
??return current / 3;
?}
?
?public synchronized static void printDebugMsg(){
??printDebugMsg(System.out);
?}
?
?public synchronized static void printDebugMsg(PrintStream out){
??if (DEBUG == false) {
???return;
??}
??StringBuffer msg = new StringBuffer();
??msg.append("debug message in " + SimpleConnetionPool.class.getName());
??msg.append("\r\n");
??msg.append("total count is connection pool: " + getConnectionCount());
??msg.append("\r\n");
??msg.append("not used connection count: " + getNotUsedConnectionCount());
??msg.append("\r\n");
??msg.append("used connection, count: " + getUsedConnectionCount());
??out.println(msg);
??Iterator iterator = m_usedUsedConnection.iterator();
??while (iterator.hasNext()){
???ConnectionWrapper wrapper = (ConnectionWrapper) iterator.next();
???wrapper.debugInfo.printStackTrace(out);
??}
??out.println();
?}
?
?public static synchronized int getNotUsedConnectionCount(){
??return m_notUsedConnection.size();
?}
?
?public static synchronized int getUsedConnectionCount(){
??return m_usedUsedConnection.size();
?}
?
?public static synchronized int getConnectionCount(){
??return m_notUsedConnection.size() + m_usedUsedConnection.size();
?}
}

class ConnectionWrapper implements InvocationHandler{
?private final static String CLOSE_METHOD_NAME = "close";
?public Connection connection = null;
?private Connection m_originConnection = null;
?public long lastAccessTime = System.currentTimeMillis();
?Throwable debugInfo = new Throwable("Connection initial statement");
?
?ConnectionWrapper(Connection con){
??Class[] interfaces = {java.sql.Connection.class};
??this.connection = (Connection) Proxy.newProxyInstance(con.getClass().getClassLoader(),interfaces, this);
??m_originConnection = con;
?}
?
?void close() throws SQLException {
??m_originConnection.close();
?}
?
?public Object invoke(Object proxy, Method m, Object[] args) throws Throwable{
??Object obj = null;
??if (CLOSE_METHOD_NAME.equals(m.getName())) {
???SimpleConnetionPool.pushConnectionBackToPool(this);
??}
??else {
???obj = m.invoke(m_originConnection, args);
??}
??lastAccessTime = System.currentTimeMillis();
??return obj;
?}
}

??????? 使用方法

public class TestConnectionPool{
　　public static void main(String[] args) {
　　　　SimpleConnetionPool.setUrl(DBTools.getDatabaseUrl());
　　　　SimpleConnetionPool.setUser(DBTools.getDatabaseUserName());
　　　　SimpleConnetionPool.setPassword(DBTools.getDatabasePassword());

　　　　Connection con = SimpleConnetionPool.getConnection();
　　　　Connection con1 = SimpleConnetionPool.getConnection();
　　　　Connection con2 = SimpleConnetionPool.getConnection();

　　　　//do something with con ...

　　　　try {
　　　　　　con.close();
　　　　} catch (Exception e) {}

　　　　try {
　　　　　　con1.close();
　　　　} catch (Exception e) {}

　　　　try {
　　　　　　con2.close();
　　　　} catch (Exception e) {}

　　　　con = SimpleConnetionPool.getConnection();
　　　　con1 = SimpleConnetionPool.getConnection();
　　　　try {
　　　　　　con1.close();
　　　　} catch (Exception e) {}

　　　　con2 = SimpleConnetionPool.getConnection();
　　　　SimpleConnetionPool.printDebugMsg();

　　}
}