数据库连接池

 1 package db;
 2 
 3 import java.sql.Connection;
 4 import java.util.concurrent.atomic.AtomicBoolean;
 5 
 6 /**
 7  * 封装的连接
 8  * @author Linkwork, [email protected]
 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, [email protected]
 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      * @return
103      * @throws Exception
104      */
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 Exception
160      */
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     @Override
193     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 index
257      * @return
258      * @throws Exception
259      */
260     private DBConnection createDBConnection(int index) throws Exception {
261         return new DBConnection(index, this.createConnection(), false);
262     }
263     
264     /**
265      * 创建连接
266      * @return
267      * @throws Exception
268      */
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 dbconnection
277      * @return
278      */
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 dbconnection
287      * @return
288      * @throws SQLException
289      */
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, [email protected]
10  * @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 pool
26      */
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 pool
49      */
50     public static void mockConcurrent(DBConnectionPool pool) {
51         for (int index = 0; index < 10; ++ index) {
52             final int value = 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 092
11 dbconnection index=2, insert=5, time=2014-11-29 11:20:47 142
12 【dbconnection-1】idle timeout.
13 【dbconnection-2】idle timeout.
14 dbconnection index=2, insert=1, time=2014-11-29 11:20:47 907
15 dbconnection index=1, insert=0, time=2014-11-29 11:20:47 929
16 dbconnection index=3, insert=3, time=2014-11-29 11:20:47 929
17 dbconnection index=2, insert=4, time=2014-11-29 11:20:47 948
18 dbconnection index=1, insert=5, time=2014-11-29 11:20:47 982
19 dbconnection index=3, insert=8, time=2014-11-29 11:20:47 982
20 has closed all!