数据库连接池

package ruyi.core.conn;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Vector;
import java.util.concurrent.Executors;

import ruyi.core.app.ApplicationContext;
import ruyi.core.thread.CommonThread;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

public class ConnectionPool {
	
	private static ConnectionPool inst;
	
	private Vector<Connection> connPool;
	private Vector<Connection> connFullPool;
	protected final Log log = LogFactory.getLog(this.getClass());
	
	private ApplicationContext applicationContext;
	private String driver;
	private String urlStr;
	private String userName;
	private String userPWD;
	private int count=0;
   
	public int poolType=TYPE_MYSQL;
	public static final int TYPE_MYSQL=1;
	public static final int TYPE_SQLSERVER=2;
	
    Character start='`';
    Character stop='`';
	
	private ConnectionPool(){
		try {
			connPool= new Vector<Connection>();
			connFullPool=new Vector<Connection>();
			applicationContext = ApplicationContext.getInstance();
			driver = applicationContext.getProperty("core.database.driverClassName");
			Class.forName(driver);
			if(driver.contains("sqlserver")){
				poolType=TYPE_SQLSERVER;
				start='[';
				stop=']';
			}
			urlStr = applicationContext.getProperty("core.database.url");
			userName = applicationContext.getProperty("core.database.user"); 
			userPWD = applicationContext.getProperty("core.database.password");
		} catch (Exception e) {
			log.info("database connection failed.",e);
		}
	}
	
	public ConnectionPool(String driver, String urlStr, String userName, String userPWD){
		try {
			connPool= new Vector<Connection>();
			connFullPool=new Vector<Connection>();
			applicationContext = ApplicationContext.getInstance();
			this.driver = driver;
			Class.forName(driver);
			if(driver.contains("sqlserver")){
				poolType=TYPE_SQLSERVER;
				start='[';
				stop=']';
			}
			this.urlStr = urlStr;
			this.userName = userName; 
			this.userPWD = userPWD;
		} catch (Exception e) {
			log.info("Database connection failed.",e);
		}
	}
	
	public static ConnectionPool getInstance(){
		if (inst==null){
			inst=new ConnectionPool();
		}
		return inst;
	}
	
	public DBManager getDBConnection() throws ClassNotFoundException, SQLException{
		return new DBManager(this);
	}
	
	public int getSize(){
		return connPool.size();
	}
	
	private synchronized Connection connect(boolean debug){
		try {
			if(debug)
				log.debug("Connect to DB,urlStr:"+urlStr+"userName:"+userName+"userPWD:"+userPWD);
			Connection con = DriverManager.getConnection(urlStr, userName,userPWD);
			con.setAutoCommit(true);
			//con.setNetworkTimeout(Executors.newFixedThreadPool(1), 3000);
			return con;
		} catch (SQLException e) {
			log.error("Not able to connect the DB and redo the connection.urlStr:"+urlStr+"userName:"+userName+"userPWD:"+userPWD,e);
			try {
				Thread.sleep(1000);
			} catch (InterruptedException e1) {}
			return connect(debug);
		}
	}
	
	public boolean isClosed(Connection result){
		try {
			if(result==null){
				
			}else if(result.isClosed()){ 
				connFullPool.remove(result);
				result=null;
			}else{
				PreparedStatement pst=result.prepareStatement("select 1 from dual");
				pst.execute();
				pst.getResultSet().close();
				pst.close();
			}
		} catch (SQLException e) {
			connFullPool.remove(result);
			result=null;
		}
		return result==null;
	}
	public Connection getConnection(boolean debug){
		Connection result=null;
		while(true){
			synchronized(connPool){
				if(connPool.size()<=0||result!=null)
					break;
				result=connPool.remove(0);
			}
			if(isClosed(result))
				result=null;
		} 
		if(result==null){
			result=connect(debug);
			connFullPool.add(result);
		}
		count++;
		if(debug)
			log.debug("Get connection. The pool size is "+connFullPool.size()+", and the free connections is "+connPool.size()+", and the count is:"+count);	
		return result;
	}
	
	public void putConnection(DBManager dbManager){
		if(dbManager!=null&&dbManager.getConn()!=null){
			connPool.add(dbManager.getConn());
			if(dbManager.isDebug())
				log.debug("Disconnected. The pool size is "+connFullPool.size()+", and the free connections is "+connPool.size());
		}
	}
	public void putConnection(Connection conn){
		connPool.add(conn);
	}
	public int getPoolType() {
		return poolType;
	}

	public void setPoolType(int poolType) {
		this.poolType = poolType;
	}
	
//	public static void main(String[] args) throws Exception
//	{
//		/*--------------------------------------------------
//		 * String name ;
//        DBManager db = new DBManager();                           //测试从sqlserver是否连通
//        ResultSet rs = db.sendQuery("select * from platform.platform.test");
//        ArrayList<String> list = new ArrayList<String>();
//         while(rs.next()){
//    	   name= rs.getString("name");  
//    	   list.add(name);
//       }
//         System.out.println("list size"+ list.size());*/
//		
//	   /*	-------------------------------------------------------
//	    * DBManager db = new DBManager();                        //测试sqlserver返回插入id
//		int res =db.sendInsert("INSERT INTO platform.platform.test VALUES('songwei12');");
//		System.out.println("get return id is "+res);*/
//		
//		
//	   /*---------------------------------
//		* ArrayList<String> list = new ArrayList<String>();      //测试mysql是否连通
//		DBManager db = new DBManager();  
//		ResultSet rs = db.sendQuery("select * from demo");
//		while(rs.next()){
//		list.add(rs.getString("name"));
//		}
//		System.out.println("get data size from mysql :"+list.size());*/
//	}

}