connectionpool.java

Java代码实现的数据库链接池

package crawer.store.db;

import java.util.*;
import java.sql.*;

import org.apache.log4j.Logger;

import crawer.store.DBStore;

public class ConnectionPool  {
	// Number of initial connections to make.
	private int m_InitialConnectionCount = 20;

	// A list of available connections for use.
	private Vector<Connection> m_AvailableConnections = new Vector<Connection>();

	// A list of connections being used currently.
	private Vector<Connection> m_UsedConnections = new Vector<Connection>();
	
	private static ConnectionPool instance;
	
	//同步的对象,用于通知等待线程
	private Integer lockObject = new Integer(100);
	
	//等待获得连接的最大时间(3000秒)
	private final int  waitTime = 3000000 ;
	
	//等待时主动try的次数 200次,其实没那么惨的
	private final int tryTimes = 200; 
	
	protected static Logger log = Logger.getLogger(DBStore.class);
	
	/**
	 * 注意，多次getInstance()，只有第一次是有效的,以后的大小会被忽略，
	 * 如果要重新设置poolsize,调用setPoolSize(int), @see setPoolSize(int size);
	 * @return
	 */	
	public static ConnectionPool getInstance( ){
		if(instance==null){
			try {
				instance = new ConnectionPool(20);
			} catch (SQLException e) {				
				e.printStackTrace();
			}
		}
		return instance;
	}
	
	
	public static ConnectionPool getInstance(int num){
		if(instance==null){
			try {
				instance = new ConnectionPool(num);				
			} catch (SQLException e) {				
				e.printStackTrace();
			}
		}
		return instance;
	}
	
	//动态设置poolSize
	public synchronized void setPoolSize(int size)  throws SQLException{
		if(size>this.m_InitialConnectionCount)
		for(int i = this.m_InitialConnectionCount; i<size; i++)
			m_AvailableConnections.addElement(DBInfo.getConnection());
		this.m_InitialConnectionCount = size;
		
	}
	

	// Constructor
	private ConnectionPool(int num) throws SQLException {
		this.m_InitialConnectionCount = num;
		for (int cnt = 0; cnt < m_InitialConnectionCount; cnt++) {
			// Add a new connection to the available list.
			m_AvailableConnections.addElement(DBInfo.getConnection());
		}
	}

	public  Connection getConnection() throws SQLException, InterruptedException {
		Connection newConnxn = null;
		if (m_AvailableConnections.size() == 0) {
			//开始漫长的等待
			int waitNum = tryTimes;
			int time = waitTime/tryTimes;
			while(waitNum>0){
				synchronized(lockObject){
					lockObject.wait(time);
				}if(m_AvailableConnections.size()>0){
					newConnxn= allocateConn();
					return  newConnxn;
				} 
				waitNum--;
			}
			if(waitNum==0){
				log.info(getCurrentTime()+":  get Connection Time out ");				
			}			
			
		} else {
			// Connections exist !
			// Get a connection object
			newConnxn = allocateConn();
		}

		// Either way, we should have a connection object now.
		return newConnxn;
	}
	
	private synchronized Connection allocateConn(){		
		Connection newConnxn = m_AvailableConnections.lastElement();		
		m_AvailableConnections.removeElement(newConnxn);
		m_UsedConnections.addElement(newConnxn);
		return newConnxn;
	}

	public synchronized void checkin(Connection c) {
		if (c != null) {
			// Remove from used list.
			m_UsedConnections.removeElement(c);
			// Add to the available list
			m_AvailableConnections.addElement(c);
			synchronized(lockObject){
				lockObject.notify();
			}
		}
	}

	public int availableCount() {
		return m_AvailableConnections.size();
	}
	
	
	private String getCurrentTime(){
		Calendar cal = Calendar.getInstance();
		long mills = cal.getTimeInMillis();
		String time = Long.toString(mills);
		return time;
	}
	
	
	/**************************Connection Pool Unit Test ****************/
	
	public static void main(String args[]){
		ConnectionPool pool = ConnectionPool.getInstance(20);
		Connection []connArr = new Connection[7];
		
        for(int i=0; i<connArr.length;i++)
        {
            try {
				connArr[i] = pool.getConnection();
			} catch (SQLException e) {			
				e.printStackTrace();
			} catch (InterruptedException e) {				
				e.printStackTrace();
			}
            System.out.println("Checking out..." + connArr[i]);
            queryTest(pool,connArr[i]);
            System.out.println("Available Connections ... " + pool.availableCount());
        }		
        
        System.out.println(pool.availableCount());
        
	}
	
	private static void queryTest(ConnectionPool pool,Connection conn){
		if(conn == null)
			return;
		try{
		Statement stmt = conn.createStatement();
		String sql = " select * from guys";
		ResultSet rs = stmt.executeQuery(sql);
		while(rs.next()){
			System.out.println(rs.getInt("id") +" "+rs.getString("url"));
		}
		}catch(Exception e){
			e.printStackTrace();
		}finally {
			pool.checkin(conn);
		}
		
	}
	



}