自己造轮子系列（一）------数据库连接池的实现

数据库连接池就是事先将连接创建好，程序用到的时候给予，不用的时候回收。连接池的数量也可以动态拓展，但是有个上限maxPoolSize，同时也有个下限minPoolSize。

我们定义两个数组A,B。A数组放置空闲的连接，B数组放置正在使用的连接。

A,B两个数组的中连接数总和为currentPoolSize。其中currentPoolSize>=minPoolSize&&currentPoolSize<=maxPoolSize。

       假设应用程序App此时向连接池申请一个连接。会遇到如下几种情形：

1.     A数组中有闲置连接，向程序返回一个数据库连接，同时将此连接移到数组B中维护。

2.     A中无可用连接。当前A、B中的连接总数currentPoolSize<maxPoolSize。此时可以新建连接。currentPoolSize自加，B中添加一个刚刚新建的连接。

3.     A中无可用连接。当前A、B中的连接总数currentPoolSize>=maxPoolSize.此时应用程序应该等待。直到其他程序释放连接。或者正在使用的连接失效，从而被移除连接池，此时可以新建连接返回。

我们同时需要启动另外一个线程，定时检测哪些连接长时间闲置，如果currentPoolSize> minPoolSize的时候，我们就可以把该连接回收。这样的机制，就可以维持连接池的连接池维持在minPoolSize与maxPoolSize之间。

因为currentPoolSize与maxPoolSize大小关系会发生变化，所以我们要启动一个checkEqual线程去检测这两个变量之间的关系，这个线程在获取连接wait()时启动。

API介绍

 算法实现

package com.acvoice.connection;import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.Callable;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.Future;
import java.util.concurrent.ScheduledExecutorService;
import java.util.concurrent.TimeUnit;/*** @author IT民工* @time 2016.7.18* jdbc连接池*/
public class JdbcConnectionPool implements ConnectionPool {private ConnectionProperties properties;volatile private  int  currentPoolSize;private   List<ComConnection> pool; //可用连接池private   List<Connection> usedPoll;//已用连接池private  ExecutorService  exc = Executors.newCachedThreadPool();private static   ConnectionPool connectionPool = null;/*** 初始化连接池* @param pro* @throws ClassNotFoundException* @throws SQLException*/private JdbcConnectionPool(ConnectionProperties pro) throws ClassNotFoundException, SQLException{this.properties = pro;pool = new ArrayList<ComConnection>(properties.getInitPoolSize());usedPoll = new ArrayList<Connection>();Class.forName(properties.getDriverClass());for(int i = 0;i<properties.getInitPoolSize();i++){Connection con =  getConnectionByManager();pool.add(new ComConnection(con, 0));}checkOut();} /*** @param pro* @return 单例模式获取连接池，注意同步* @throws ClassNotFoundException* @throws SQLException*/synchronized public static ConnectionPool getConnnectionPool(ConnectionProperties pro) throws ClassNotFoundException, SQLException{if(connectionPool ==null){connectionPool = new JdbcConnectionPool(pro);} return connectionPool;}/*** 当前可以获取连接时即currentPoolSize<maxPoolSize* @return* @throws InterruptedException* @throws ExecutionException*/synchronized public Connection getConect() throws InterruptedException, ExecutionException{Connection con = null;if(pool.size()==0){//当前可用为0，并且还可以拓展Callable<Connection> c = new Callable<Connection>() {public Connection call() throws Exception {return getConnectionByManager();}	};for(int i = 0;i<3;i++){//尝试三次，获取，如果三次都没有获取到，就跑出异常Future<Connection> future= exc.submit(c);con = future.get();if(con!=null){usedPoll.add(con);currentPoolSize ++;return con;}}throw new RuntimeException("尝试了最大次数，并没有获得链接");}else{//不为0，可以从空闲列表中获取con = pool.get(pool.size()-1).getConnection();usedPoll.add(con);pool.remove(pool.size()-1);return con;}}/*** @return* @throws SQLException* 从连接池manger中获取*/private Connection getConnectionByManager() throws SQLException{Connection connection = DriverManager.getConnection(properties.getConnectionUrl(),properties.getUsername(),properties.getPassword());return connection;}/* (non-Javadoc)* @see com.acvoice.ConnectionPool#getConnection()*/synchronized public  Connection  getConnection(){Connection con = null;try {if(currentPoolSize>=properties.getMaxPoolSize()&&pool.size()==0){checkEquel();//启动一个现成去检测currentPoolSize与MaxPoolSize的关系wait();//等待，可以拓展con = getConect();}else{con = getConect();}} catch (InterruptedException e) {e.printStackTrace();}catch (ExecutionException e) {e.printStackTrace();}return con;}	/* (non-Javadoc)* @see com.acvoice.ConnectionPool#realse(java.sql.Connection)*/synchronized public void realse(Connection connection ){pool.add(new ComConnection(connection, 0));//回收可用，并且重新计时usedPoll.remove(connection);//把已用连接池中的该连接的引用移除掉notify();}/*** 每隔1分钟维护列表，把空闲时间比较长的连接释放掉，把不可用的连接也释放掉*/synchronized private void  checkOut(){Runnable r = new Runnable() {public void run() {try {for(int i=usedPoll.size()-1;i>=0;i--){try {//检测连接是否可用if(usedPoll.get(i).isValid(100)){}else{usedPoll.remove(i);currentPoolSize--;}} catch (SQLException e) {e.printStackTrace();}}for(int i=pool.size()-1;i>=0;i--){try {//检测连接是否可用if(pool.get(i).getConnection().isValid(100)){}else{pool.remove(i);currentPoolSize--;}} catch (SQLException e) {e.printStackTrace();}}for(int i =pool.size()-1;i>=0;i--){//长时间空闲的释放掉ComConnection com = pool.get(i);if(pool.get(i).getCount()>properties.getDispoilTime()&¤tPoolSize>properties.getMinPoolSize()){	try {com.getConnection().close();pool.remove(i);com =null;currentPoolSize--;} catch (SQLException e) {e.printStackTrace();}}else{com.setCount(com.getCount()+1);}}} finally{}}};ScheduledExecutorService set = Executors.newScheduledThreadPool(1);//定时检测set.scheduleWithFixedDelay(r, 1, 1, TimeUnit.MINUTES);}/*** 改线程检测currentPoolSize与maxPoolSize的关系，如果关系发生变化，就唤醒等待线程*/synchronized private void checkEquel(){Runnable r = new Runnable() {public void run() {while(currentPoolSize>=properties.getMaxPoolSize()){}notify();}};exc.execute(r);}/*** @author zhao* 该内部类用处存储当前Connection与空闲时间* count为计时几次。*/private class ComConnection implements Comparable<ComConnection>{public Connection getConnection() {return connection;}public ComConnection(Connection connection, Integer count) {super();this.connection = connection;this.count = count;}public Integer getCount() {return count;}public void setCount(Integer count) {this.count = count;}Connection connection;Integer count;public int compareTo(ComConnection o) {if(o.getCount()>count)return -1;return 1;}}synchronized public void destroy() {for(int i=pool.size();i>=0;i--){try {pool.get(i).getConnection().close();pool.remove(i);} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}for(int i=usedPoll.size();i>=0;i--){try {usedPoll.get(i).close();usedPoll.remove(i);} catch (SQLException e) {e.printStackTrace();}}
}
}