1 package db; 2  3 import java.sql.Connection; 4 import java.util.concurrent.atomic.AtomicBoolean; 5  6 /** 7  * 封装的连接 8  * @author Linkwork, 276247076@qq.com 9  * @since 2014年11月01日10  */11 public class DBConnection {12     13     /**14      * 原生的连接15      */16     private Connection connection = null;17     18     /**19      * 是否空闲20      */21     private AtomicBoolean idle = null;22     23     /**24      * 最近一次的空闲开始时间25      */26     private volatile long idleStart = 0L;27     28     /**29      * 标识30      */31     private int index = -1;32     33     /**34      * 构造函数35      * @param index 标识36      * @param connection 连接37      * @param idle 是否空闲38      */39     public DBConnection(int index, Connection connection, boolean idle) {40         this.index = index;41         this.connection = connection;42         this.idle = new AtomicBoolean(idle);43     }44     45     /**46      * 释放47      */48     public void release() {49         if (this.idle.compareAndSet(false, true)) {50             this.idleStart = System.currentTimeMillis();51         }52     }53 54     public Connection getConnection() {55         return connection;56     }57 58     public AtomicBoolean getIdle() {59         return idle;60     }61     62     public void setConnection(Connection connection) {63         this.connection = connection;64     }65 66     public int getIndex() {67         return index;68     }69 70     public long getIdleStart() {71         return idleStart;72     }73 74     public void setIdleStart(long idleStart) {75         this.idleStart = idleStart;76     }77     78 }

  1 package db;  2   3 import java.sql.Connection;  4 import java.sql.DriverManager;  5 import java.sql.SQLException;  6 import java.util.Vector;  7   8 /**  9  * 数据库连接池 10  * @author Linkwork, 276247076@qq.com 11  * @since 2014年11月01日 12  */ 13 public class DBConnectionPool extends Thread { 14      15     /** 16      * 容量 17      */ 18     private volatile int capacity = 1; 19      20     /** 21      * 驱动 22      */ 23     private String driver = null; 24      25     /** 26      * 地址 27      */ 28     private String url = null; 29      30     /** 31      * 用户名 32      */ 33     private String user = null; 34      35     /** 36      * 密码 37      */ 38     private String password = null; 39      40     /** 41      * 等待的轮询间隔 42      */ 43     private volatile long waitInterval = 50L; 44      45     /** 46      * 等待的超时时间，默认 2分钟 47      */ 48     private volatile long waitTimeout = 120000L; 49      50     /** 51      * 空闲的超时时间，默认 5分钟 52      */ 53     private volatile long idleTimeout = 300000L; 54      55     /** 56      * 连接集 57      */ 58     private Vector<DBConnection> dbconnectionLst = null; 59      60     /** 61      * 是否正在进行关闭 62      */ 63     private volatile boolean closing = false; 64      65     /** 66      * 构造函数 67      * @param capacity 容量 68      * @param driver 驱动 69      * @param url 地址 70      * @param user 用户名 71      * @param password 密码 72      */ 73     public DBConnectionPool( 74             int capacity, 75             String driver, 76             String url, 77             String user, 78             String password) { 79         this.capacity = capacity; 80         this.driver = driver; 81         this.url = url; 82         this.user = user; 83         this.password = password; 84         this.dbconnectionLst = new Vector<DBConnection>(); 85     } 86      87     /** 88      * 获取 DB连接 89      * @return 90      */ 91     public DBConnection getDBConnectionQuietly() { 92         try { 93             return this.getDBConnection(); 94         } catch (Exception e) { 95             e.printStackTrace(); 96         } 97         return null; 98     } 99     100     /**101      * 获取 DB连接102      * @return103      * @throws Exception104      */105     public DBConnection getDBConnection() throws Exception {106         long start = System.currentTimeMillis();107         // 当不是正在进行关闭108         while (! this.closing) {109             // 遍历连接集，获取空闲、可用的连接110             for (DBConnection dbconnection: this.dbconnectionLst) {111                 if (dbconnection.getIdle().compareAndSet(true, false)) {112                     // 若连接未关闭113                     if (! this.isNullOrClose(dbconnection)) {114                         return dbconnection;115                     } else {116                         if (! this.closing) {117                             dbconnection.getIdle().set(true);118                         }119                     }120                 }121             }122             // 若连接的总数未超出容量，则新建连接123             if ((this.dbconnectionLst.size() < this.capacity)124                     && (! this.closing)) {125                 synchronized (this.dbconnectionLst) {126                     DBConnection dbconnection = this.createDBConnection(this.dbconnectionLst.size() + 1);127                     this.dbconnectionLst.add(dbconnection);128                     return dbconnection;129                 }130             } 131             // 遍历连接集，重用空闲、不可用的连接132             for (DBConnection dbconnection: this.dbconnectionLst) {133                 if (dbconnection.getIdle().compareAndSet(true, false)) {134                     // 若连接已关闭135                     if ((null == dbconnection.getConnection())136                             || dbconnection.getConnection().isClosed()) {137                         Connection connection = this.createConnection();138                         dbconnection.setConnection(connection);139                         return dbconnection;140                     } else if (! this.closing) {141                         dbconnection.getIdle().set(true);142                     }143                 }144             }145             // 延迟轮询146             Thread.sleep(this.waitInterval);147             long end = System.currentTimeMillis();148             // 若等待超时149             if (end - start > this.waitTimeout) {150                 throw new Exception("ERROR_WAIT_TIMEOUT");151             }152             start = end;153         } // while (! this.closing) {154         return null;155     }156     157     /**158      * 关闭连接池中的所有连接159      * @throws Exception160      */161     public void close() {162         this.closing = true;163         // 是否已经关闭164         boolean closed = false;165         // 当未关闭完成166         while (! closed) {167             closed = true;168             try {169                 Thread.sleep(this.waitInterval);170             } catch (Exception e) {171                 e.printStackTrace();172                 return;173             }174             // 遍历连接集，关闭所有空闲连接175             for (DBConnection dbconnection: this.dbconnectionLst) {176                 // 若连接空闲，则关闭该连接，并标记未关闭完成177                 if (dbconnection.getIdle().compareAndSet(true, false)) {178                     closed = false;179                     try {180                         if (! this.isNullOrClose(dbconnection)) {181                             dbconnection.getConnection().close();182                         }183                     } catch (Exception e) {184                         e.printStackTrace();185                     }186                 }187             }188         } // while (true) {189         System.out.println("has closed all!");190     }191 192     @Override193     public void run() {194         // 当不是正在进行关闭195         while (! this.closing) {196             try {197                 // 延迟轮询198                 Thread.sleep(this.waitInterval);199                 // 遍历连接集，关闭空闲超时的连接200                 for (DBConnection dbconnection: this.dbconnectionLst) {201                     // 若连接空闲，且空闲超时202                     if (dbconnection.getIdle().get()203                             && this.idleTimeout(dbconnection)204                             && dbconnection.getIdle().compareAndSet(true, false)) {205                         // 若连接空闲超时206                         if (this.idleTimeout(dbconnection)) {207                             dbconnection.setIdleStart(0L);208                             dbconnection.getConnection().close();209                             System.out.println("【dbconnection-" + dbconnection.getIndex() + "】idle timeout.");210                         }211                         if (! this.closing) {212                             dbconnection.getIdle().set(true);213                         }214                     }215                 }216             } catch (Exception e) {217                 e.printStackTrace();218             }219         }220     }221     222     public int getCapacity() {223         return capacity;224     }225 226     public void setCapacity(int capacity) {227         this.capacity = capacity;228     }229 230     public long getWaitTimeout() {231         return waitTimeout;232     }233 234     public void setWaitTimeout(long waitTimeout) {235         this.waitTimeout = waitTimeout;236     }237 238     public long getIdleTimeout() {239         return idleTimeout;240     }241 242     public void setIdleTimeout(long idleTimeout) {243         this.idleTimeout = idleTimeout;244     }245 246     public long getWaitInterval() {247         return waitInterval;248     }249 250     public void setWaitInterval(long waitInterval) {251         this.waitInterval = waitInterval;252     }253 254     /**255      * 创建 DB连接256      * @param index257      * @return258      * @throws Exception259      */260     private DBConnection createDBConnection(int index) throws Exception {261         return new DBConnection(index, this.createConnection(), false);262     }263     264     /**265      * 创建连接266      * @return267      * @throws Exception268      */269     private Connection createConnection() throws Exception {270         Class.forName(this.driver);271         return DriverManager.getConnection(this.url, this.user, this.password);272     }273     274     /**275      * DB连接是否空闲超时276      * @param dbconnection277      * @return278      */279     private boolean idleTimeout(DBConnection dbconnection) {280         return ((dbconnection.getIdleStart() > 0)281                 && (System.currentTimeMillis() - dbconnection.getIdleStart() > this.idleTimeout));282     }283     284     /**285      * DB连接是否为空、或已关闭286      * @param dbconnection287      * @return288      * @throws SQLException289      */290     private boolean isNullOrClose(DBConnection dbconnection) throws SQLException {291         return ((null == dbconnection.getConnection())292                 || dbconnection.getConnection().isClosed());293     }294 295 }

 1 package db; 2  3 import java.sql.PreparedStatement; 4 import java.text.SimpleDateFormat; 5 import java.util.Date; 6  7 /** 8  * 测试：数据库连接池 9  * @author Linkwork, 276247076@qq.com10  * @since 2014年11月0日11  */12 public class DBConnectionPoolTest {13     14     public static void main(String[] args) {15         DBConnectionPool pool = new DBConnectionPool(3, "com.mysql.jdbc.Driver", "jdbc:mysql://localhost:3306/db", "lw2013qq", "omg2056db4qq");16         pool.setWaitTimeout(10000L);17         pool.setIdleTimeout(40L);18         pool.start();19         testPool(pool);20         pool.close();21     }22     23     /**24      * 测试连接池25      * @param pool26      */27     public static void testPool(DBConnectionPool pool) {28         DBConnection dbconnection = pool.getDBConnectionQuietly();29         if (null != dbconnection) {30             try {31                 // 创建表32                 dbconnection.getConnection().prepareStatement("create table if not exists test_pool (tp_value int);").execute();33                 dbconnection.release();34                 mockConcurrent(pool);35                 Thread.sleep(1000);36                 mockConcurrent(pool);37                 Thread.sleep(60);38             } catch (Exception e) {39                 e.printStackTrace();40             } finally {41                 dbconnection.release();42             }43         }44     }45     46     /**47      * 模拟多线程并发访问数据库，并发插入 10行数据48      * @param pool49      */50     public static void mockConcurrent(DBConnectionPool pool) {51         for (int index = 0; index < 10; ++ index) {52             final int value =http://www.mamicode.com/ index;53             Thread thread = new Thread() {54                 public void run() {55                     DBConnection dbconnection = pool.getDBConnectionQuietly();56                     if (null != dbconnection) {57                         try {58                             // 插入数据59                             PreparedStatement statement = dbconnection.getConnection().prepareStatement("insert into test_pool(tp_value) values(?);");60                             statement.setInt(1, value);61                             statement.execute();62                             StringBuffer msg = new StringBuffer();63                             msg.append("dbconnection index=").append(dbconnection.getIndex())64                                 .append(", insert=").append(value)65                                 .append(", time=").append(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss SSS").format(new Date()));66                             System.out.println(msg.toString());67                         } catch (Exception e) {68                             e.printStackTrace();69                         } finally {70                             dbconnection.release();71                         }72                     }73                 }74             };75             thread.start();76         }77     }78 79 }

 1 dbconnection index=1, insert=0, time=2014-11-29 11:20:46 901 2 dbconnection index=2, insert=2, time=2014-11-29 11:20:46 917 3 dbconnection index=3, insert=8, time=2014-11-29 11:20:46 917 4 dbconnection index=2, insert=1, time=2014-11-29 11:20:46 960 5 dbconnection index=1, insert=4, time=2014-11-29 11:20:46 960 6 dbconnection index=3, insert=6, time=2014-11-29 11:20:46 984 7 dbconnection index=2, insert=7, time=2014-11-29 11:20:47 008 8 dbconnection index=1, insert=9, time=2014-11-29 11:20:47 008 9 【dbconnection-3】idle timeout.10 dbconnection index=1, insert=3, time=2014-11-29 11:20:47 09211 dbconnection index=2, insert=5, time=2014-11-29 11:20:47 14212 【dbconnection-1】idle timeout.13 【dbconnection-2】idle timeout.14 dbconnection index=2, insert=1, time=2014-11-29 11:20:47 90715 dbconnection index=1, insert=0, time=2014-11-29 11:20:47 92916 dbconnection index=3, insert=3, time=2014-11-29 11:20:47 92917 dbconnection index=2, insert=4, time=2014-11-29 11:20:47 94818 dbconnection index=1, insert=5, time=2014-11-29 11:20:47 98219 dbconnection index=3, insert=8, time=2014-11-29 11:20:47 98220 has closed all!