连接池是非常好的想法，应用很普遍。自己写一个数据库连接池，并不像想象中那样困难。一般系统对连接池的功能不会有太多要求，使用自己的连接池未必是个坏主意。下面以Oracle为例，但是对Teradata和Greenplum也是可行的。另外我还实现了连接有效性检查（checkConn）和恢复连接(resetConn)的方法。本例编程采用的是JRE1.4.2环境（别忘了准备访问数据库的jar包）。有任何问题请随时留言，欢迎探讨。
在Oracle内创建测试数据：
1. drop table
2. create table
3. field_id varchar2(3),
4. field_content varchar2(60),
5. date default
6. );
7.
8. insert into my_table(field_id,field_content) values('001','this is first record');
9. insert into my_table(field_id,field_content) values('002','this is second record');
10. insert into my_table(field_id,field_content) values('003','this is third record');
11. commit;DBPool.java：
1. package
2.
3. import
4. import
5. import
6. import
7. import
8.
9. public class
10. private
11. private
12. private
13. private
14. private int connCount = 3;//连接数
15. private Connection[] connections;//保存数据库连接
16. private String[] connStatus;// 已连可用Y 已连不可用N 未连接X
17. private Date[] lastQueryTime;//时间戳
18.
19. public
20. this.connCount=poolConfiguration.getConnCount();
21. this.cls=poolConfiguration.getCls();
22. this.url=poolConfiguration.getUrl();
23. this.usr=poolConfiguration.getUsr();
24. this.pss=poolConfiguration.getPss();
25. this.connections=new Connection[this.connCount];
26. this.connStatus=new String[this.connCount];
27. for(int i=0;i<this.connCount;i++){
28. this.connStatus[i]="X";//初始化全部未连接
29. }
30. this.lastQueryTime = new Date[this.connCount];
31. }
32.
33. public
34. this.cls=cls;
35. this.url=url;
36. this.usr=usr;
37. this.pss=pss;
38. this.connections=new Connection[this.connCount];
39. this.connStatus=new String[this.connCount];
40. for(int i=0;i<this.connCount;i++){
41. this.connStatus[i]="X";//初始化全部未连接
42. }
43. this.lastQueryTime = new Date[this.connCount];
44. }
45.
46. public void
47. if(connCount<1){
48. "请正确设置连接池窗口个数");
49. else{
50. try{
51. this.cls);//register class
52. catch(ClassNotFoundException e){
53. System.out.println(e.getMessage());
54. catch(Exception e){
55. //other exceptions
56. }
57.
58. for(int i=0;i<this.connCount;i++){
59. try{
60. this.connections[i]=DriverManager.getConnection(this.url, this.usr, this.pss);
61. this.connStatus[i]="Y";
62. catch(SQLException e){
63. System.out.println(e.getMessage());
64. catch(Exception e){
65. //other exceptions
66. }
67. }
68. "initPool is ready...");
69. //end if
70. }
71.
72. public void
73. for(int i=0;i<this.connCount;i++){
74. try{
75. this.connections[i].commit();
76. this.connections[i].close();
77. this.connStatus[i]="X";
78. this.lastQueryTime[i]=null;
79. catch(Exception e){
80. try{
81. this.connections[i].close();
82. this.connStatus[i]="X";
83. this.lastQueryTime[i]=null;
84. catch(Exception e1){
85. //just for catch
86. }
87. }
88. }
89. "freePool is over ...");
90. }
91.
92. public DBPoolConnection getPoolConn() throws
93. new
94. poolConnection.connNbr=getConnNbr();
95. if(poolConnection.connNbr==-1){
96. throw new DBPoolIsFullException("连接池已满");
97. else{
98. poolConnection.conn=getConn(poolConnection.connNbr);
99. }
100. return
101. }
102.
103. public void
104. if(poolConnection==null){
105. "poolConnection==null，不需要释放");
106. else{
107. freeConn(poolConnection.connNbr);
108. }
109. }
110.
111. public void
112. for(int i=0;i<this.connStatus.length;i++){
113. "");
114. this.connStatus[i].toString());
115. if(this.lastQueryTime[i]==null){
116. "-[null] ");
117. else{
118. "-["+this.lastQueryTime[i].toString()+"] ");
119. }
120. }
121. "");
122. }
123.
124. public
125. return this.cls;
126. }
127.
128. public
129. return this.url;
130. }
131.
132. public
133. return this.usr;
134. }
135.
136. int
137. int iConn=-1;
138. for(int i=0;i<this.connCount;i++){
139. if(this.connStatus[i].equals("Y")){
140. this.lastQueryTime[i]=new
141. this.connStatus[i]="N";
142. iConn=i;
143. break;
144. }
145. }
146. return
147. }
148.
149. int
150. return this.connections[i];
151. }
152.
153. void
154. try{
155. this.connections[poolConnection.connNbr].close();
156. catch(SQLException e){
157. System.out.println(e.getMessage());
158. }
159. }
160.
161. boolean
162. null;
163. "";
164. boolean checkResult=true;
165.
166. //检查连接是否有效
167. try{
168. "select * from dual";
169. this.connections[poolConnection.connNbr].createStatement();
170. //execute sql
171. stmt.close();
172. "checkConn:checkMessage:execute sql success";
173. System.out.println(checkMessage);
174. catch(Exception e){
175. checkMessage = e.getMessage();
176. //other exceptions
177. if(checkMessage==null){
178. "e.getMessage() is null";
179. System.out.println(checkMessage);
180. }
181. //采取激进重连的策略，尽量避免业务中断
182. if (checkMessage.indexOf("ORA-00942")>=0){
183. true;//不需要重连
184. else if(checkMessage.indexOf("does not exist")>=0){
185. true;//不需要重连
186. else if(checkMessage.indexOf("Syntax error")>=0){
187. true;//不需要重连
188. else{
189. false;//需要重连
190. }
191. }
192. return
193. }
194.
195. boolean
196. boolean result=false;//默认不需要重建连接
197.
198. if(poolConnection==null){
199. "poolConnection==null，不知道您想重设哪个连接");
200. else if(poolConnection.connNbr==-1){
201. "poolConnection.connNbr==-1，不知道您想重设哪个连接");
202. else{
203. if(checkConn(poolConnection)==true){
204. "连接有效，不需要重设");
205. else{
206. //重设连接
207. try{
208. this.cls);//register class
209. catch(ClassNotFoundException e){
210. System.out.println(e.getMessage());
211. catch(Exception e){
212. //other exceptions
213. }
214. try{
215. this.connections[poolConnection.connNbr]=DriverManager.getConnection(this.url, this.usr, this.pss);
216. this.connStatus[poolConnection.connNbr]="Y";
217. "连接已重建");
218. true;//告知调用者连接已重建
219. catch(SQLException e){
220. System.out.println(e.getMessage());
221. catch(Exception e){
222. //other exceptions
223. }
224. }
225. }
226. return
227. }
228.
229. void freeConn(int
230. try{
231. if(i==-1){
232. "i=-1，不需要释放");
233. else{
234. this.connections[i].commit();
235. }
236. catch(SQLException e){
237. System.out.println(e.getMessage());
238. catch(Exception e){
239. //other exceptions
240. }
241. this.connStatus[i]="Y";
242. }
243. }DBPoolConfiguration.java
1. package
2.
3. public class
4. private
5. private
6. private
7. private
8. private int connCount;//连接数
9.
10. public
11. return
12. }
13. public void
14. this.cls = cls;
15. }
16. public
17. return
18. }
19. public void
20. this.url = url;
21. }
22. public
23. return
24. }
25. public void
26. this.usr = usr;
27. }
28. public
29. return
30. }
31. public void
32. this.pss = pss;
33. }
34. public int
35. return
36. }
37. public void setConnCount(int
38. this.connCount = connCount;
39. }
40. }DBPoolConnection.java:
1. package
2.
3. import
4.
5. public class
6. public int connNbr=-1;
7. public Connection conn=null;
8. DBPoolConnection(){
9. this.connNbr=-1;
10. this.conn = null;
11. }
12. }DBPoolIsFullException.java
1. package
2.
3. public class DBPoolIsFullException extends
4. static final long
5. DBPoolIsFullException(String message){
6. super(message);
7. }
8. }Test.java
1. package
2.
3. import
4. import
5. import
6. import
7. import
8. import
9.
10. public class
11. static String rpad(String str,int
12. String s = str;
13. if(s==null){
14. "";
15. }
16. while(s.getBytes().length<len){
17. " ";
18. }
19. return
20. }
21.
22. public static void
23. //初始化
24. "",url="",usr="",pss="",sql="";
25. null;
26. null;
27. "|";//分隔符
28. int connCount=2;//最大连接数
29.
30. //连接Oracle 配置
31. "oracle.jdbc.driver.OracleDriver";
32. "jdbc:oracle:thin:@localhost:1521:myoradb";
33. "abc";
34. "123";
35.
36. "select t.field_id,t.field_content,to_char(t.record_create_date,'YYYYMMDD') day from my_table t";
37.
38. new
39. poolConfiguration.setCls(cls);
40. poolConfiguration.setUrl(url);
41. poolConfiguration.setUsr(usr);
42. poolConfiguration.setPss(pss);
43. poolConfiguration.setConnCount(connCount);
44.
45. new
46. myPool.initPool();
47.
48.
49. "");
50. "after init Pool");
51. myPool.printPoolStatus();
52. "");
53.
54. null;
55.
56. try{
57. c1 = myPool.getPoolConn();
58.
59. "");
60. "after getPoolConn");
61. myPool.printPoolStatus();
62. "");
63.
64. stmt = c1.conn.createStatement();
65. rs = stmt.executeQuery(sql);
66.
67. ResultSetMetaData md = rs.getMetaData();
68.
69. "";
70. int
71. for(int i=1;i<=colCount;i++){
72. if(recordHead.equals("")){
73. recordHead += rpad(md.getColumnName(i),md.getColumnDisplaySize(i));
74. else{
75. recordHead+= gapStr + rpad(md.getColumnName(i),md.getColumnDisplaySize(i));
76. }
77. }
78. //打印表头
79.
80. while(rs.next()){
81. "";
82. for(int i=1;i<=colCount;i++){
83. int
84. if(tmp.equals("")){
85. tmp += rpad(rs.getString(i),colSize);
86. else{
87. tmp += gapStr + rpad(rs.getString(i),colSize);
88. }
89. }
90. //打印数据
91. }
92.
93. //释放资源但是不关闭连接
94. myPool.freePoolConn(c1);
95.
96. "");
97. "after freePoolConn");
98. myPool.printPoolStatus();
99. "");
100. catch(Exception e){
101. System.out.println(e.getMessage());
102. }
103.
104. myPool.freePool();
105.
106. //end of main
107. }下面是Test.java的执行结果：
1. initPool is
2.
3. after
4. Y-[null]
5. Y-[null]
6.
7.
8. after
9. N-[Wed Mar 20 14:46:31 GMT 2013]
10. Y-[null]
11.
12. FIELD_ID|FIELD_CONTENT |DAY
13. 001 |this is first
14. 002 |this is second
15. 003 |this is
16.
17. after
18. Y-[Wed Mar 20 14:46:31 GMT 2013]
19. Y-[null]
20.
21. freePool is感谢您的耐心读到此处，我相信接下去的文字会更有价值。
保持连接池简单性的几个设计思想（不一定正确）：
１）在系统中连接池不应作为一个独立的模块，最好是作为某模块的底层实现。这样可以将超时管理、请求队列、确保资源释放、数据分页（方言不可避免了）等功能剥离出去。
２）固定连接数比动态连接数容易实现，简单的代码更易于维护。
本例有待完善之处（供参考）：
１）查询空闲连接要遍历数组，这样当池中的连接数很大时可能会有问题（具体没测过）
２）可以添加连接池阻塞的功能，有时候后台数据库需要重启，或者想重建连接池以提供更多的并发连接数？
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
