Java的JDBC数据库连接池实现方法

最新推荐文章于 2022-05-06 15:44:56 发布

liyinlong

最新推荐文章于 2022-05-06 15:44:56 发布

阅读量310

点赞数

文章标签：数据库连接池 jdbc java wrapper null interface

本文链接：https://blog.csdn.net/liyinlong/article/details/6479929

版权

Java的JDBC数据库连接池实现方法

虽然J2EE程序员一般都有现成的应用服务器所带的JDBC数据库连接池，不过对于开发一般的Java Application、 Applet或者JSP、velocity时，我们可用的JDBC数据库连接池并不多，并且一般性能都不好。

Java程序员都很羡慕Windows ADO ，只需要new Connection 就可以直接从数据库连接池中返回Connection。并且 ADO Connection 是线程安全的，多个线程可以共用一个Connection，所以ASP程序一般都把getConnection 放在 Global.asa 文件中，在 IIS 启动时建立数据库连接。ADO 的Connection 和Result 都有很好的缓冲，并且很容易使用。

其实我们可以自己写一个JDBC数据库连接池。

写JDBC connection pool 的注意事项有：

1. 有一个简单的函数从连接池中得到一个 Connection。

2. close 函数必须将connection 放回数据库连接池。

3. 当数据库连接池中没有空闲的connection，数据库连接池必须能够自动增加connection 个数。

4. 当数据库连接池中的connection 个数在某一个特别的时间变得很大，但是以后很长时间只用其中一小部分，应该可以自动将多余的connection 关闭掉。

5. 如果可能，应该提供debug 信息报告没有关闭的new Connection 。

如果要new Connection 就可以直接从数据库连接池中返回Connection，可以这样写( Mediator pattern ) (以下代码中使用了中文全角空格)：
1. public class EasyConnection implements java.sql.Connection{
2. private Connection m_delegate = null;
3. public EasyConnection(){
4. m_delegate = getConnectionFromPool();
5. }
6. 　public void close(){
7. putConnectionBackToPool(m_delegate);
8. }
9. public PreparedStatement prepareStatement(String sql) throws SQLException{
10. m_delegate.prepareStatement(sql);
11. }
12. //...... other method
13. }
看来并不难。不过不建议这种写法，因为应该尽量避免使用Java Interface, 关于Java Interface 的缺点我另外再写文章讨论。大家关注的是Connection Pool 的实现方法。下面给出一种实现方法。
1. import java.sql.*;
2. import java.lang.reflect.*;
3. import java.util.*;
4. import java.io.*;
6. public class SimpleConnetionPool {
7. private static LinkedList m_notUsedConnection = new LinkedList();
8. private static HashSet m_usedUsedConnection = new HashSet();
9. private static String m_url = "";
10. private static String m_user = "";
11. private static String m_password = "";
12. static final boolean DEBUG = true;
13. static private long m_lastClearClosedConnection = System.currentTimeMillis();
14. public static long CHECK_CLOSED_CONNECTION_TIME = 4 * 60 * 60 * 1000; //4 hours
16. static {
17. initDriver();
18. }
20. private SimpleConnetionPool() {
21. }
23. private static void initDriver() {
24. Driver driver = null;
25. //load mysql driver
26. try {
27. driver = (Driver) Class.forName("com.mysql.jdbc.Driver").newInstance();
28. installDriver(driver);
29. } catch (Exception e) {
30. }
32. //load postgresql driver
33. try {
34. driver = (Driver) Class.forName("org.postgresql.Driver").newInstance();
35. installDriver(driver);
36. } catch (Exception e) {
37. }
38. }
40. public static void installDriver(Driver driver) {
41. try {
42. DriverManager.registerDriver(driver);
43. } catch (Exception e) {
44. e.printStackTrace();
45. }
46. }
49. public static synchronized Connection getConnection() {
50. clearClosedConnection();
51. while (m_notUsedConnection.size() > 0) {
52. try {
53. ConnectionWrapper wrapper = (ConnectionWrapper) m_notUsedConnection.removeFirst();
54. if (wrapper.connection.isClosed()) {
55. continue;
56. }
57. m_usedUsedConnection.add(wrapper);
58. if (DEBUG) {
59. wrapper.debugInfo = new Throwable("Connection initial statement");
60. }
61. return wrapper.connection;
62. } catch (Exception e) {
63. }
64. }
65. int newCount = getIncreasingConnectionCount();
66. LinkedList list = new LinkedList();
67. ConnectionWrapper wrapper = null;
68. for (int i = 0; i < newCount; i++) {
69. wrapper = getNewConnection();
70. if (wrapper != null) {
71. list.add(wrapper);
72. }
73. }
74. if (list.size() == 0) {
75. return null;
76. }
77. wrapper = (ConnectionWrapper) list.removeFirst();
78. m_usedUsedConnection.add(wrapper);
80. m_notUsedConnection.addAll(list);
81. list.clear();
83. return wrapper.connection;
84. }
86. private static ConnectionWrapper getNewConnection() {
87. try {
88. Connection con = DriverManager.getConnection(m_url, m_user, m_password);
89. ConnectionWrapper wrapper = new ConnectionWrapper(con);
90. return wrapper;
91. } catch (Exception e) {
92. e.printStackTrace();
93. }
94. return null;
95. }
97. static synchronized void pushConnectionBackToPool(ConnectionWrapper con) {
98. boolean exist = m_usedUsedConnection.remove(con);
99. if (exist) {
100. m_notUsedConnection.addLast(con);
101. }
102. }
104. public static int close() {
105. int count = 0;
107. Iterator iterator = m_notUsedConnection.iterator();
108. while (iterator.hasNext()) {
109. try {
110. ( (ConnectionWrapper) iterator.next()).close();
111. count++;
112. } catch (Exception e) {
113. }
114. }
115. m_notUsedConnection.clear();
117. iterator = m_usedUsedConnection.iterator();
118. while (iterator.hasNext()) {
119. try {
120. ConnectionWrapper wrapper = (ConnectionWrapper) iterator.next();
121. wrapper.close();
122. if (DEBUG) {
123. wrapper.debugInfo.printStackTrace();
124. }
125. count++;
126. } catch (Exception e) {
127. }
128. }
129. m_usedUsedConnection.clear();
131. return count;
132. }
134. private static void clearClosedConnection() {
135. long time = System.currentTimeMillis();
136. //sometimes user change system time,just return
137. if (time < m_lastClearClosedConnection) {
138. time = m_lastClearClosedConnection;
139. return;
140. }
141. //no need check very often
142. if (time - m_lastClearClosedConnection < CHECK_CLOSED_CONNECTION_TIME) {
143. return;
144. }
145. m_lastClearClosedConnection = time;
147. //begin check
148. Iterator iterator = m_notUsedConnection.iterator();
149. while (iterator.hasNext()) {
150. ConnectionWrapper wrapper = (ConnectionWrapper) iterator.next();
151. try {
152. if (wrapper.connection.isClosed()) {
153. iterator.remove();
154. }
155. } catch (Exception e) {
156. iterator.remove();
157. if (DEBUG) {
158. System.out.println("connection is closed, this connection initial StackTrace");
159. wrapper.debugInfo.printStackTrace();
160. }
161. }
162. }
164. //make connection pool size smaller if too big
165. int decrease = getDecreasingConnectionCount();
166. if (m_notUsedConnection.size() < decrease) {
167. return;
168. }
170. while (decrease-- > 0) {
171. ConnectionWrapper wrapper = (ConnectionWrapper) m_notUsedConnection.removeFirst();
172. try {
173. wrapper.connection.close();
174. } catch (Exception e) {
175. }
176. }
177. }
179. /**
180. * get increasing connection count, not just add 1 connection
181. * @return count
182. */
183. public static int getIncreasingConnectionCount() {
184. int count = 1;
185. int current = getConnectionCount();
186. count = current / 4;
187. if (count < 1) {
188. count = 1;
189. }
190. return count;
191. }
193. /**
194. * get decreasing connection count, not just remove 1 connection
195. * @return count
196. */
197. public static int getDecreasingConnectionCount() {
198. int count = 0;
199. int current = getConnectionCount();
200. if (current < 10) {
201. return 0;
202. }
203. return current / 3;
204. }
206. public synchronized static void printDebugMsg() {
207. printDebugMsg(System.out);
208. }
210. public synchronized static void printDebugMsg(PrintStream out) {
211. if (DEBUG == false) {
212. return;
213. }
214. StringBuffer msg = new StringBuffer();
215. msg.append("debug message in " + SimpleConnetionPool.class.getName());
216. msg.append("/r/n");
217. msg.append("total count is connection pool: " + getConnectionCount());
218. msg.append("/r/n");
219. msg.append("not used connection count: " + getNotUsedConnectionCount());
220. msg.append("/r/n");
221. msg.append("used connection, count: " + getUsedConnectionCount());
222. out.println(msg);
223. Iterator iterator = m_usedUsedConnection.iterator();
224. while (iterator.hasNext()) {
225. ConnectionWrapper wrapper = (ConnectionWrapper) iterator.next();
226. wrapper.debugInfo.printStackTrace(out);
227. }
228. out.println();
229. }
231. public static synchronized int getNotUsedConnectionCount() {
232. return m_notUsedConnection.size();
233. }
235. public static synchronized int getUsedConnectionCount() {
236. return m_usedUsedConnection.size();
237. }
239. public static synchronized int getConnectionCount() {
240. return m_notUsedConnection.size() + m_usedUsedConnection.size();
241. }
243. public static String getUrl() {
244. return m_url;
245. }
247. public static void setUrl(String url) {
248. if (url == null) {
249. return;
250. }
251. m_url = url.trim();
252. }
254. public static String getUser() {
255. return m_user;
256. }
258. public static void setUser(String user) {
259. if (user == null) {
260. return;
261. }
262. m_user = user.trim();
263. }
265. public static String getPassword() {
266. return m_password;
267. }
269. public static void setPassword(String password) {
270. if (password == null) {
271. return;
272. }
273. m_password = password.trim();
274. }
276. }
278. class ConnectionWrapper implements InvocationHandler {
279. private final static String CLOSE_METHOD_NAME = "close";
280. public Connection connection = null;
281. private Connection m_originConnection = null;
282. public long lastAccessTime = System.currentTimeMillis();
283. Throwable debugInfo = new Throwable("Connection initial statement");
285. ConnectionWrapper(Connection con) {
286. this.connection = (Connection) Proxy.newProxyInstance(
287. con.getClass().getClassLoader(),
288. con.getClass().getInterfaces(), this);
289. m_originConnection = con;
290. }
292. void close() throws SQLException {
293. m_originConnection.close();
294. }
296. public Object invoke(Object proxy, Method m, Object[] args) throws Throwable {
297. Object obj = null;
298. if (CLOSE_METHOD_NAME.equals(m.getName())) {
299. SimpleConnetionPool.pushConnectionBackToPool(this);
300. }
301. else {
302. obj = m.invoke(m_originConnection, args);
303. }
304. lastAccessTime = System.currentTimeMillis();
305. return obj;
306. }
307. }
使用方法
1. public class TestConnectionPool{
2. public static void main(String[] args) {
3. SimpleConnetionPool.setUrl(DBTools.getDatabaseUrl());
4. SimpleConnetionPool.setUser(DBTools.getDatabaseUserName());
5. SimpleConnetionPool.setPassword(DBTools.getDatabasePassword());
7. Connection con = SimpleConnetionPool.getConnection();
8. Connection con1 = SimpleConnetionPool.getConnection();
9. Connection con2 = SimpleConnetionPool.getConnection();
11. //do something with con ...
13. try {
14. con.close();
15. } catch (Exception e) {}
17. try {
18. con1.close();
19. } catch (Exception e) {}
21. try {
22. con2.close();
23. } catch (Exception e) {}
25. con = SimpleConnetionPool.getConnection();
26. con1 = SimpleConnetionPool.getConnection();
27. try {
28. con1.close();
29. } catch (Exception e) {}
31. con2 = SimpleConnetionPool.getConnection();
32. SimpleConnetionPool.printDebugMsg();
34. }
35. }