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     public DBConnection(int index, Connection connection, boolean idle) {34         this.index = index;35         this.connection = connection;36         this.idle = new AtomicBoolean(idle);37     }38     39     public void release() {40         if (this.idle.compareAndSet(false, true)) {41             this.idleStart = System.currentTimeMillis();42         }43     }44 45     public Connection getConnection() {46         return connection;47     }48 49     public AtomicBoolean getIdle() {50         return idle;51     }52     53     public void setConnection(Connection connection) {54         this.connection = connection;55     }56 57     public int getIndex() {58         return index;59     }60 61     public long getIdleStart() {62         return idleStart;63     }64 65     public void setIdleStart(long idleStart) {66         this.idleStart = idleStart;67     }68     69 }

  1 package db;  2   3 import java.sql.Connection;  4 import java.sql.DriverManager;  5 import java.util.Vector;  6   7 /**  8  * 数据库连接池  9  * @author Linkwork, 276247076@qq.com 10  * @since 2014年11月01日 11  */ 12 public class DBConnectionPool extends Thread { 13      14     /** 15      * 容量 16      */ 17     private volatile int capacity = 1; 18      19     /** 20      * 驱动 21      */ 22     private String driver = null; 23      24     /** 25      * 地址 26      */ 27     private String url = null; 28      29     /** 30      * 用户名 31      */ 32     private String user = null; 33      34     /** 35      * 密码 36      */ 37     private String password = null; 38      39     /** 40      * 等待的轮询间隔 41      */ 42     private volatile long waitInterval = 50L; 43      44     /** 45      * 等待的超时时间 46      */ 47     private volatile long waitTimeout = 300000L; 48      49     /** 50      * 空闲的超时时间 51      */ 52     private volatile long idleTimeout = 60L; 53      54     /** 55      * 连接集 56      */ 57     private Vector<DBConnection> dbconnectionLst = null; 58      59     /** 60      * 是否关闭 61      */ 62     private volatile boolean closing = false; 63      64     public DBConnectionPool( 65             int capacity, 66             String driver, 67             String url, 68             String user, 69             String password) { 70         this.capacity = capacity; 71         this.driver = driver; 72         this.url = url; 73         this.user = user; 74         this.password = password; 75         this.dbconnectionLst = new Vector<DBConnection>(); 76     } 77      78     public DBConnection getDBConnection() throws Exception { 79         long start = System.currentTimeMillis(); 80         while (! this.closing) { 81             // 遍历连接集，获取空闲、可用的连接 82             for (DBConnection dbconnection: this.dbconnectionLst) { 83                 if (dbconnection.getIdle().compareAndSet(true, false)) { 84                     // 若连接未关闭 85                     if ((null != dbconnection.getConnection()) 86                             && (! dbconnection.getConnection().isClosed())) { 87                         return dbconnection; 88                     } else { 89                         dbconnection.getIdle().set(true); 90                     } 91                 } 92             } 93             // 若连接的总数未超出容量 94             if ((this.dbconnectionLst.size() < this.capacity) 95                     && (! this.closing)) { 96                 synchronized (this.dbconnectionLst) { 97                     DBConnection dbconnection = this.createDBConnection(this.dbconnectionLst.size() + 1); 98                     this.dbconnectionLst.add(dbconnection); 99                     return dbconnection;100                 }101             } 102             // 遍历连接集，重用空闲、不可用的连接103             for (DBConnection dbconnection: this.dbconnectionLst) {104                 if (dbconnection.getIdle().compareAndSet(true, false)) {105                     // 若连接已关闭106                     if ((null == dbconnection.getConnection())107                             || dbconnection.getConnection().isClosed()) {108                         Connection connection = this.createConnection();109                         dbconnection.setConnection(connection);110                         return dbconnection;111                     } else {112                         dbconnection.getIdle().set(true);113                     }114                 }115             }116             // 延迟轮询117             Thread.sleep(this.waitInterval);118             long end = System.currentTimeMillis();119             if (end - start > this.waitTimeout) {120                 throw new Exception("ERROR_WAIT_TIMEOUT");121             }122             start = end;123         } // while (! this.closing) {124         return null;125     }126     127     public void close() throws Exception {128         this.closing = true;129         boolean closed = false;130         while (! closed) {131             closed = true;132             Thread.sleep(this.waitInterval);133             // 遍历连接集，关闭所有连接134             for (DBConnection dbconnection: this.dbconnectionLst) {135                 if (dbconnection.getIdle().compareAndSet(true, false)) {136                     closed = false;137                     if ((null != dbconnection.getConnection())138                             && (! dbconnection.getConnection().isClosed())) {139                         dbconnection.getConnection().close();140                     }141                 }142             }143         } // while (true) {144     }145 146     @Override147     public void run() {148         while (! this.closing) {149             try {150                 Thread.sleep(this.waitInterval);151                 // 遍历连接集，适时关闭空闲连接152                 for (DBConnection dbconnection: this.dbconnectionLst) {153                     if (dbconnection.getIdle().get()154                             && (dbconnection.getIdleStart() > 0)155                             && (System.currentTimeMillis() - dbconnection.getIdleStart() > this.idleTimeout)) {156                         if (dbconnection.getIdle().compareAndSet(true, false)) {157                             if ((dbconnection.getIdleStart() > 0)158                                 && (System.currentTimeMillis() - dbconnection.getIdleStart() > this.idleTimeout)) {159                                 dbconnection.setIdleStart(0L);160                                 dbconnection.getConnection().close();161                                 System.out.println("【dbconnection-" + dbconnection.getIndex() + "】idle timeout.");162                             }163                             dbconnection.getIdle().set(true);164                         }165                     }166                 }167             } catch (Exception e) {168                 e.printStackTrace();169             }170         }171     }172     173     public int getCapacity() {174         return capacity;175     }176 177     public void setCapacity(int capacity) {178         this.capacity = capacity;179     }180 181     public long getWaitTimeout() {182         return waitTimeout;183     }184 185     public void setWaitTimeout(long waitTimeout) {186         this.waitTimeout = waitTimeout;187     }188 189     public long getIdleTimeout() {190         return idleTimeout;191     }192 193     public void setIdleTimeout(long idleTimeout) {194         this.idleTimeout = idleTimeout;195     }196 197     private DBConnection createDBConnection(int index) throws Exception {198         return new DBConnection(index, this.createConnection(), false);199     }200     201     private Connection createConnection() throws Exception {202         Class.forName(this.driver);203         return DriverManager.getConnection(this.url, this.user, this.password);204     }205 206 }

 1 package db; 2  3 import java.sql.PreparedStatement; 4 import java.sql.ResultSet; 5  6 /** 7  * 测试：数据库连接池 8  * @author Linkwork, 276247076@qq.com 9  * @since 2014年11月01日10  */11 public class DBConnectionPoolTest {12     13     public static void main(String[] args) throws Exception {14         DBConnectionPool pool = new DBConnectionPool(3, "com.mysql.jdbc.Driver", "jdbc:mysql://localhost:3306/db", "Linkwork", "276247076");15         pool.start();16         DBConnection dbconnection = pool.getDBConnection();17         if (null != dbconnection) {18             dbconnection.getConnection().prepareStatement("create table if not exists test_pool (tp_value int);").execute();19             dbconnection.release();20         }21         mockConcurrent(pool);22         Thread.sleep(2000);23         mockConcurrent(pool);24         Thread.sleep(50);25         pool.close();26     }27     28     public static void mockConcurrent(DBConnectionPool pool) {29         for (int index = 0; index < 10; ++ index) {30             final int value =http://www.mamicode.com/ index;31             Thread thread = new Thread() {32                 public void run() {33                     DBConnection dbconnection = null;34                     try {35                         dbconnection = pool.getDBConnection();36                     } catch (Exception e) {37                         e.printStackTrace();38                     }39                     if (null != dbconnection) {40                         try {41                             if (Math.random() > 0.5) {42                                 PreparedStatement statement = dbconnection.getConnection().prepareStatement("insert into test_pool(tp_value) values(?);");43                                 statement.setInt(1, value);44                                 statement.execute();45                                 dbconnection.release();46                                 System.out.println("【dbconnection-" + dbconnection.getIndex() + "】insert: " + value);47                             }48                             else {49                                 PreparedStatement statement = dbconnection.getConnection().prepareStatement("select * from test_pool where tp_value = http://www.mamicode.com/?;");50                                 statement.setInt(1, value);51                                 ResultSet rs = statement.executeQuery();52                                 if ((null != rs)53                                         && rs.next()) {54                                     System.out.println("【dbconnection-" + dbconnection.getIndex() + "】query: " + rs.getInt(1));55                                 } else {56                                     System.out.println("【dbconnection-" + dbconnection.getIndex() + "】query: ");57                                 }58                                 dbconnection.release();59                             }60                         } catch (Exception e) {61                             e.printStackTrace();62                         }63                     }64                 }65             };66             thread.start();67         }68     }69 70 }

【dbconnection-1】query: 0【dbconnection-1】query: 8【dbconnection-1】query: 4【dbconnection-2】insert: 2【dbconnection-3】insert: 6【dbconnection-2】insert: 3【dbconnection-1】insert: 1【dbconnection-1】insert: 9【dbconnection-3】insert: 5【dbconnection-2】insert: 7【dbconnection-1】idle timeout.【dbconnection-2】idle timeout.【dbconnection-3】idle timeout.【dbconnection-3】query: 3【dbconnection-1】query: 1【dbconnection-2】query: 0【dbconnection-1】idle timeout.【dbconnection-2】idle timeout.【dbconnection-3】idle timeout.