连接池是非常好的想法，应用很普遍。自己写一个数据库连接池，并不像想象中那样困难。一般系统对连接池的功能不会有太多要求，使用自己的连接池未必是个坏主意。下面以Oracle为例，但是对Teradata和Greenplum也是可行的。另外我还实现了连接有效性检查（checkConn）和恢复连接(resetConn)的方法。本例编程采用的是JRE1.4.2环境（别忘了准备访问数据库的jar包）。有任何问题请随时留言，欢迎探讨。

在Oracle内创建测试数据：

1. drop table my_table;  
2. create table my_table(  
3.        field_id varchar2(3),  
4.        field_content varchar2(60),  
5.        record_create_date date default sysdate  
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 dataWebService;  
2.  
3. import java.sql.DriverManager;  
4. import java.util.Date;  
5. import java.sql.Connection;  
6. import java.sql.SQLException;  
7. import java.sql.Statement;  
8.  
9. public class DBPool{  
10.     private String cls;  
11.     private String url;  
12.     private String usr;  
13.     private String pss;   
14.     private int connCount = 3;//连接数  
15.     private Connection[] connections;//保存数据库连接  
16.     private String[] connStatus;// 已连可用Y   已连不可用N   未连接X  
17.     private Date[] lastQueryTime;//时间戳  
18.       
19.     public DBPool(DBPoolConfiguration poolConfiguration){  
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 DBPool(String cls,String url,String usr,String pss){  
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 initPool(){  
47.         if(connCount<1){  
48.             System.out.println("请正确设置连接池窗口个数");  
49.         }else{  
50.             try{  
51.                 Class.forName(this.cls);//register class  
52.             }catch(ClassNotFoundException e){  
53.                 System.out.println(e.getMessage());  
54.             }catch(Exception e){  
55.                 System.out.println(e.getMessage());//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.                     System.out.println(e.getMessage());//other exceptions  
66.                 }  
67.             }     
68.             System.out.println("initPool is ready...");  
69.         }//end if  
70.     }  
71.  
72.     public void freePool(){  
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.                     System.out.println(e1.getMessage());//just for catch  
86.                 }  
87.             }             
88.         }  
89.         System.out.println("freePool is over ...");  
90.     }  
91.  
92.     public DBPoolConnection getPoolConn() throws DBPoolIsFullException{  
93.         DBPoolConnection poolConnection = new DBPoolConnection();  
94.         poolConnection.connNbr=getConnNbr();  
95.         if(poolConnection.connNbr==-1){  
96.             throw new DBPoolIsFullException("连接池已满");  
97.         }else{  
98.             poolConnection.conn=getConn(poolConnection.connNbr);  
99.         }  
100.         return poolConnection;  
101.     }  
102.  
103.     public void freePoolConn(DBPoolConnection poolConnection){  
104.         if(poolConnection==null){  
105.             System.out.println("poolConnection==null，不需要释放");  
106.         }else{  
107.             freeConn(poolConnection.connNbr);  
108.         }  
109.     }  
110.       
111.     public void printPoolStatus(){  
112.         for(int i=0;i<this.connStatus.length;i++){  
113.             System.out.println("");  
114.             System.out.print(this.connStatus[i].toString());  
115.             if(this.lastQueryTime[i]==null){  
116.                 System.out.print("-[null] ");  
117.             }else{  
118.                 System.out.print("-["+this.lastQueryTime[i].toString()+"] ");  
119.             }  
120.         }  
121.         System.out.println("");  
122.     }  
123.       
124.     public String getCls(){  
125.         return this.cls;  
126.     }  
127.  
128.     public String getUrl(){  
129.         return this.url;  
130.     }  
131.  
132.     public String getUsr(){  
133.         return this.usr;  
134.     }  
135.  
136.     int getConnNbr(){  
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 Date();  
141.                 this.connStatus[i]="N";  
142.                 iConn=i;  
143.                 break;  
144.             }  
145.         }  
146.         return iConn;  
147.     }  
148.       
149.     Connection getConn(int i){  
150.         return this.connections[i];  
151.     }  
152.           
153.     void closeConnForTest(DBPoolConnection poolConnection){  
154.         try{  
155.             this.connections[poolConnection.connNbr].close();  
156.         }catch(SQLException e){  
157.             System.out.println(e.getMessage());   
158.         }  
159.     }  
160.           
161.     boolean checkConn(DBPoolConnection poolConnection){  
162.         Statement stmt=null;  
163.         String checkMessage="";  
164.         boolean checkResult=true;  
165.           
166.         //检查连接是否有效  
167.         try{  
168.             String sql = "select * from dual";  
169.             stmt = this.connections[poolConnection.connNbr].createStatement();  
170.             stmt.executeQuery(sql);//execute sql  
171.             stmt.close();  
172.             checkMessage = "checkConn:checkMessage:execute sql success";  
173.             System.out.println(checkMessage);  
174.         }catch(Exception e){  
175.             checkMessage = e.getMessage();  
176.             System.out.println(e.getMessage());//other exceptions  
177.             if(checkMessage==null){  
178.                 checkMessage="e.getMessage() is null";  
179.                 System.out.println(checkMessage);  
180.             }  
181.             //采取激进重连的策略，尽量避免业务中断  
182.             if (checkMessage.indexOf("ORA-00942")>=0){  
183.                 checkResult=true;//不需要重连  
184.             }else if(checkMessage.indexOf("does not exist")>=0){  
185.                 checkResult=true;//不需要重连  
186.             }else if(checkMessage.indexOf("Syntax error")>=0){  
187.                 checkResult=true;//不需要重连  
188.             }else{            
189.                 checkResult=false;//需要重连  
190.             }  
191.         }  
192.         return checkResult;  
193.     }  
194.       
195.     boolean resetConn(DBPoolConnection poolConnection){  
196.         boolean result=false;//默认不需要重建连接   
197.           
198.         if(poolConnection==null){  
199.             System.out.println("poolConnection==null，不知道您想重设哪个连接");  
200.         }else if(poolConnection.connNbr==-1){  
201.             System.out.println("poolConnection.connNbr==-1，不知道您想重设哪个连接");  
202.         }else{  
203.             if(checkConn(poolConnection)==true){  
204.                 System.out.println("连接有效，不需要重设");  
205.             }else{  
206.                 //重设连接  
207.                 try{  
208.                     Class.forName(this.cls);//register class  
209.                 }catch(ClassNotFoundException e){  
210.                     System.out.println(e.getMessage());  
211.                 }catch(Exception e){  
212.                     System.out.println(e.getMessage());//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.                     System.out.println(poolConnection.connNbr+"连接已重建");  
218.                     result = true;//告知调用者连接已重建  
219.                 }catch(SQLException e){  
220.                     System.out.println(e.getMessage());  
221.                 }catch(Exception e){  
222.                     System.out.println(e.getMessage());//other exceptions  
223.                 }     
224.             }  
225.         }  
226.         return result;  
227.     }  
228.           
229.     void freeConn(int i){  
230.         try{  
231.             if(i==-1){  
232.                 System.out.println("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.             System.out.println(e.getMessage());//other exceptions  
240.         }  
241.         this.connStatus[i]="Y";  
242.     }  
243. }  

DBPoolConfiguration.java

1. package dataWebService;  
2.  
3. public class DBPoolConfiguration {  
4.     private String cls;  
5.     private String url;  
6.     private String usr;  
7.     private String pss;   
8.     private int connCount;//连接数   
9.       
10.     public String getCls() {  
11.         return cls;  
12.     }  
13.     public void setCls(String cls) {  
14.         this.cls = cls;  
15.     }  
16.     public String getUrl() {  
17.         return url;  
18.     }  
19.     public void setUrl(String url) {  
20.         this.url = url;  
21.     }  
22.     public String getUsr() {  
23.         return usr;  
24.     }  
25.     public void setUsr(String usr) {  
26.         this.usr = usr;  
27.     }  
28.     public String getPss() {  
29.         return pss;  
30.     }  
31.     public void setPss(String pss) {  
32.         this.pss = pss;  
33.     }  
34.     public int getConnCount() {  
35.         return connCount;  
36.     }  
37.     public void setConnCount(int connCount) {  
38.         this.connCount = connCount;  
39.     }  
40. } 

DBPoolConnection.java:

1. package dataWebService;  
2.  
3. import java.sql.Connection;  
4.  
5. public class DBPoolConnection{  
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 dataWebService;  
2.  
3. public class DBPoolIsFullException extends Exception{  
4.     static final long serialVersionUID=1L;  
5.     DBPoolIsFullException(String message){  
6.         super(message);  
7.     }  
8. } 

Test.java

1. package myAction;  
2.  
3. import dataWebService.DBPool;  
4. import dataWebService.DBPoolConnection;  
5. import dataWebService.DBPoolConfiguration;  
6. import java.sql.ResultSet;  
7. import java.sql.ResultSetMetaData;  
8. import java.sql.Statement;  
9.  
10. public class Test {  
11.     static String rpad(String str,int len){  
12.         String s = str;  
13.         if(s==null){  
14.             s="";  
15.         }  
16.         while(s.getBytes().length<len){  
17.             s += " ";  
18.         }  
19.         return s;  
20.     }  
21.       
22.     public static void main(String[] args) {          
23.         //初始化  
24.         String cls ="",url="",usr="",pss="",sql="";  
25.         Statement stmt=null;  
26.         ResultSet rs=null;  
27.         String gapStr="|";//分隔符  
28.         int connCount=2;//最大连接数  
29.  
30.         //连接Oracle  配置  
31.         cls = "oracle.jdbc.driver.OracleDriver";  
32.         url = "jdbc:oracle:thin:@localhost:1521:myoradb";  
33.         usr = "abc";  
34.         pss = "123";  
35.           
36.         sql = "select t.field_id,t.field_content,to_char(t.record_create_date,'YYYYMMDD') day from my_table t";  
37.  
38.         DBPoolConfiguration poolConfiguration=new DBPoolConfiguration();  
39.         poolConfiguration.setCls(cls);  
40.         poolConfiguration.setUrl(url);  
41.         poolConfiguration.setUsr(usr);  
42.         poolConfiguration.setPss(pss);  
43.         poolConfiguration.setConnCount(connCount);  
44.           
45.         DBPool myPool = new DBPool(poolConfiguration);  
46.         myPool.initPool();  
47.  
48.           
49.         System.out.println("");  
50.         System.out.print("after init Pool");  
51.         myPool.printPoolStatus();  
52.         System.out.println("");  
53.           
54.         DBPoolConnection c1=null;  
55.  
56.         try{  
57.             c1 = myPool.getPoolConn();  
58.  
59.             System.out.println("");  
60.             System.out.print("after getPoolConn");  
61.             myPool.printPoolStatus();  
62.             System.out.println("");           
63.               
64.             stmt = c1.conn.createStatement();  
65.             rs = stmt.executeQuery(sql);  
66.  
67.             ResultSetMetaData md = rs.getMetaData();  
68.               
69.             String recordHead = "";  
70.             int colCount = md.getColumnCount();  
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.             System.out.println(recordHead);//打印表头  
79.               
80.             while(rs.next()){  
81.                 String tmp = "";  
82.                 for(int i=1;i<=colCount;i++){  
83.                     int colSize = md.getColumnDisplaySize(i)>md.getColumnName(i).length()?md.getColumnDisplaySize(i):md.getColumnName(i).length();  
84.                     if(tmp.equals("")){  
85.                         tmp += rpad(rs.getString(i),colSize);  
86.                     }else{  
87.                         tmp += gapStr + rpad(rs.getString(i),colSize);  
88.                     }  
89.                 }  
90.                 System.out.println(tmp);//打印数据  
91.             }  
92.               
93.             stmt.close();//释放资源但是不关闭连接                        
94.             myPool.freePoolConn(c1);  
95.               
96.             System.out.println("");  
97.             System.out.print("after freePoolConn");  
98.             myPool.printPoolStatus();  
99.             System.out.println("");           
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 ready...  
2.  
3. after init Pool  
4. Y-[null]   
5. Y-[null]   
6.  
7.  
8. after getPoolConn  
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 record                                        |20130320  
14. 002     |this is second record                                       |20130320  
15. 003     |this is third record                                        |20130320  
16.  
17. after freePoolConn  
18. Y-[Wed Mar 20 14:46:31 GMT 2013]   
19. Y-[null]   
20.  
21. freePool is over ... 

 感谢您的耐心读到此处，我相信接下去的文字会更有价值。

保持连接池简单性的几个设计思想（不一定正确）：

１）在系统中连接池不应作为一个独立的模块，最好是作为某模块的底层实现。这样可以将超时管理、请求队列、确保资源释放、数据分页（方言不可避免了）等功能剥离出去。

２）固定连接数比动态连接数容易实现，简单的代码更易于维护。

本例有待完善之处（供参考）：

１）查询空闲连接要遍历数组，这样当池中的连接数很大时可能会有问题（具体没测过）

２）可以添加连接池阻塞的功能，有时候后台数据库需要重启，或者想重建连接池以提供更多的并发连接数？

 本文转自 hexiaini235 51CTO博客，原文链接：http://blog.51cto.com/idata/1159243，如需转载请自行联系原作者