連接池是非常好的想法，應用很普遍。自己寫一個數據庫連接池，並不像想象中那樣困難。一般系統對連接池的功能不會有太多要求，使用自己的連接池未必是個壞主意。下面以Oracle為例，但是對Teradata和Greenplum也是可行的。另外我還實現了連接有效性檢查（checkConn）和恢復連接(resetConn)的方法。本例編程采用的是JRE1.4.2環境（別忘了准備訪問數據庫的jar包）。有任何問題請隨時留言，歡迎探討。
在Oracle內創建測試數據：
drop table my_table;
create table my_table(
field_id varchar2(3),
field_content varchar2(60),
record_create_date date default sysdate
);
insert into my_table(field_id,field_content) values('001','this is first record');
insert into my_table(field_id,field_content) values('002','this is second record');
insert into my_table(field_id,field_content) values('003','this is third record');
commit;
DBPool.java：
package dataWebService;
import java.sql.DriverManager;
import java.util.Date;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
public class DBPool{
private String cls;
private String url;
private String usr;
private String pss;
private int connCount = 3;//連接數
private Connection[] connections;//保存數據庫連接
private String[] connStatus;// 已連可用Y 已連不可用N 未連接X
private Date[] lastQueryTime;//時間戳
public DBPool(DBPoolConfiguration poolConfiguration){
this.connCount=poolConfiguration.getConnCount();
this.cls=poolConfiguration.getCls();
this.url=poolConfiguration.getUrl();
this.usr=poolConfiguration.getUsr();
this.pss=poolConfiguration.getPss();
this.connections=new Connection[this.connCount];
this.connStatus=new String[this.connCount];
for(int i=0;i<this.connCount;i++){
this.connStatus[i]="X";//初始化全部未連接
}
this.lastQueryTime = new Date[this.connCount];
}
public DBPool(String cls,String url,String usr,String pss){
this.cls=cls;
this.url=url;
this.usr=usr;
this.pss=pss;
this.connections=new Connection[this.connCount];
this.connStatus=new String[this.connCount];
for(int i=0;i<this.connCount;i++){
this.connStatus[i]="X";//初始化全部未連接
}
this.lastQueryTime = new Date[this.connCount];
}
public void initPool(){
if(connCount<1){
System.out.println("請正確設置連接池窗口個數");
}else{
try{
Class.forName(this.cls);//register class
}catch(ClassNotFoundException e){
System.out.println(e.getMessage());
}catch(Exception e){
System.out.println(e.getMessage());//other exceptions
}
for(int i=0;i<this.connCount;i++){
try{
this.connections[i]=DriverManager.getConnection(this.url, this.usr, this.pss);
this.connStatus[i]="Y";
}catch(SQLException e){
System.out.println(e.getMessage());
}catch(Exception e){
System.out.println(e.getMessage());//other exceptions
}
}
System.out.println("initPool is ready...");
}//end if
}
public void freePool(){
for(int i=0;i<this.connCount;i++){
try{
this.connections[i].commit();
this.connections[i].close();
this.connStatus[i]="X";
this.lastQueryTime[i]=null;
}catch(Exception e){
try{
this.connections[i].close();
this.connStatus[i]="X";
this.lastQueryTime[i]=null;
}catch(Exception e1){
System.out.println(e1.getMessage());//just for catch
}
}
}
System.out.println("freePool is over ...");
}
public DBPoolConnection getPoolConn() throws DBPoolIsFullException{
DBPoolConnection poolConnection = new DBPoolConnection();
poolConnection.connNbr=getConnNbr();
if(poolConnection.connNbr==-1){
throw new DBPoolIsFullException("連接池已滿");
}else{
poolConnection.conn=getConn(poolConnection.connNbr);
}
return poolConnection;
}
public void freePoolConn(DBPoolConnection poolConnection){
if(poolConnection==null){
System.out.println("poolConnection==null，不需要釋放");
}else{
freeConn(poolConnection.connNbr);
}
}
public void printPoolStatus(){
for(int i=0;i<this.connStatus.length;i++){
System.out.println("");
System.out.print(this.connStatus[i].toString());
if(this.lastQueryTime[i]==null){
System.out.print("-[null] ");
}else{
System.out.print("-["+this.lastQueryTime[i].toString()+"] ");
}
}
System.out.println("");
}
public String getCls(){
return this.cls;
}
public String getUrl(){
return this.url;
}
public String getUsr(){
return this.usr;
}
int getConnNbr(){
int iConn=-1;
for(int i=0;i<this.connCount;i++){
if(this.connStatus[i].equals("Y")){
this.lastQueryTime[i]=new Date();
this.connStatus[i]="N";
iConn=i;
break;
}
}
return iConn;
}
Connection getConn(int i){
return this.connections[i];
}
void closeConnForTest(DBPoolConnection poolConnection){
try{
this.connections[poolConnection.connNbr].close();
}catch(SQLException e){
System.out.println(e.getMessage());
}
}
boolean checkConn(DBPoolConnection poolConnection){
Statement stmt=null;
String checkMessage="";
boolean checkResult=true;
//檢查連接是否有效
try{
String sql = "select * from dual";
stmt = this.connections[poolConnection.connNbr].createStatement();
stmt.executeQuery(sql);//execute sql
stmt.close();
checkMessage = "checkConn:checkMessage:execute sql success";
System.out.println(checkMessage);
}catch(Exception e){
checkMessage = e.getMessage();
System.out.println(e.getMessage());//other exceptions
if(checkMessage==null){
checkMessage="e.getMessage() is null";
System.out.println(checkMessage);
}
//采取激進重連的策略，盡量避免業務中斷
if (checkMessage.indexOf("ORA-00942")>=0){
checkResult=true;//不需要重連
}else if(checkMessage.indexOf("does not exist")>=0){
checkResult=true;//不需要重連
}else if(checkMessage.indexOf("Syntax error")>=0){
checkResult=true;//不需要重連
}else{
checkResult=false;//需要重連
}
}
return checkResult;
}
boolean resetConn(DBPoolConnection poolConnection){
boolean result=false;//默認不需要重建連接
if(poolConnection==null){
System.out.println("poolConnection==null，不知道您想重設哪個連接");
}else if(poolConnection.connNbr==-1){
System.out.println("poolConnection.connNbr==-1，不知道您想重設哪個連接");
}else{
if(checkConn(poolConnection)==true){
System.out.println("連接有效，不需要重設");
}else{
//重設連接
try{
Class.forName(this.cls);//register class
}catch(ClassNotFoundException e){
System.out.println(e.getMessage());
}catch(Exception e){
System.out.println(e.getMessage());//other exceptions
}
try{
this.connections[poolConnection.connNbr]=DriverManager.getConnection(this.url, this.usr, this.pss);
this.connStatus[poolConnection.connNbr]="Y";
System.out.println(poolConnection.connNbr+"連接已重建");
result = true;//告知調用者連接已重建
}catch(SQLException e){
System.out.println(e.getMessage());
}catch(Exception e){
System.out.println(e.getMessage());//other exceptions
}
}
}
return result;
}
void freeConn(int i){
try{
if(i==-1){
System.out.println("i=-1，不需要釋放");
}else{
this.connections[i].commit();
}
}catch(SQLException e){
System.out.println(e.getMessage());
}catch(Exception e){
System.out.println(e.getMessage());//other exceptions
}
this.connStatus[i]="Y";
}
}
DBPoolConfiguration.java
package dataWebService;
public class DBPoolConfiguration {
private String cls;
private String url;
private String usr;
private String pss;
private int connCount;//連接數
public String getCls() {
return cls;
}
public void setCls(String cls) {
this.cls = cls;
}
public String getUrl() {
return url;
}
public void setUrl(String url) {
this.url = url;
}
public String getUsr() {
return usr;
}
public void setUsr(String usr) {
this.usr = usr;
}
public String getPss() {
return pss;
}
public void setPss(String pss) {
this.pss = pss;
}
public int getConnCount() {
return connCount;
}
public void setConnCount(int connCount) {
this.connCount = connCount;
}
}
DBPoolConnection.java:
package dataWebService;
import java.sql.Connection;
public class DBPoolConnection{
public int connNbr=-1;
public Connection conn=null;
DBPoolConnection(){
this.connNbr=-1;
this.conn = null;
}
}
DBPoolIsFullException.java
package dataWebService;
public class DBPoolIsFullException extends Exception{
static final long serialVersionUID=1L;
DBPoolIsFullException(String message){
super(message);
}
}
Test.java
package myAction;
import dataWebService.DBPool;
import dataWebService.DBPoolConnection;
import dataWebService.DBPoolConfiguration;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
public class Test {
static String rpad(String str,int len){
String s = str;
if(s==null){
s="";
}
while(s.getBytes().length<len){
s += " ";
}
return s;
}
public static void main(String[] args) {
//初始化
String cls ="",url="",usr="",pss="",sql="";
Statement stmt=null;
ResultSet rs=null;
String gapStr="|";//分隔符
int connCount=2;//最大連接數
//連接Oracle 配置
cls = "oracle.jdbc.driver.OracleDriver";
url = "jdbc:oracle:thin:@localhost:1521:myoradb";
usr = "abc";
pss = "123";
sql = "select t.field_id,t.field_content,to_char(t.record_create_date,'YYYYMMDD') day from my_table t";
DBPoolConfiguration poolConfiguration=new DBPoolConfiguration();
poolConfiguration.setCls(cls);
poolConfiguration.setUrl(url);
poolConfiguration.setUsr(usr);
poolConfiguration.setPss(pss);
poolConfiguration.setConnCount(connCount);
DBPool myPool = new DBPool(poolConfiguration);
myPool.initPool();
System.out.println("");
System.out.print("after init Pool");
myPool.printPoolStatus();
System.out.println("");
DBPoolConnection c1=null;
try{
c1 = myPool.getPoolConn();
System.out.println("");
System.out.print("after getPoolConn");
myPool.printPoolStatus();
System.out.println("");
stmt = c1.conn.createStatement();
rs = stmt.executeQuery(sql);
ResultSetMetaData md = rs.getMetaData();
String recordHead = "";
int colCount = md.getColumnCount();
for(int i=1;i<=colCount;i++){
if(recordHead.equals("")){
recordHead += rpad(md.getColumnName(i),md.getColumnDisplaySize(i));
}else{
recordHead+= gapStr + rpad(md.getColumnName(i),md.getColumnDisplaySize(i));
}
}
System.out.println(recordHead);//打印表頭
while(rs.next()){
String tmp = "";
for(int i=1;i<=colCount;i++){
int colSize = md.getColumnDisplaySize(i)>md.getColumnName(i).length()?md.getColumnDisplaySize(i):md.getColumnName(i).length();
if(tmp.equals("")){
tmp += rpad(rs.getString(i),colSize);
}else{
tmp += gapStr + rpad(rs.getString(i),colSize);
}
}
System.out.println(tmp);//打印數據
}
stmt.close();//釋放資源但是不關閉連接
myPool.freePoolConn(c1);
System.out.println("");
System.out.print("after freePoolConn");
myPool.printPoolStatus();
System.out.println("");
}catch(Exception e){
System.out.println(e.getMessage());
}
myPool.freePool();
}//end of main
}
下面是Test.java的執行結果：
initPool is ready...
after init Pool
Y-[null]
Y-[null]
after getPoolConn
N-[Wed Mar 20 14:46:31 GMT 2013]
Y-[null]
FIELD_ID|FIELD_CONTENT |DAY
001 |this is first record |20130320
002 |this is second record |20130320
003 |this is third record |20130320
after freePoolConn
Y-[Wed Mar 20 14:46:31 GMT 2013]
Y-[null]
freePool is over ...
感謝您的耐心讀到此處，我相信接下去的文字會更有價值。
保持連接池簡單性的幾個設計思想（不一定正確）：
１）在系統中連接池不應作為一個獨立的模塊，最好是作為某模塊的底層實現。這樣可以將超時管理、請求隊列、確保資源釋放、數據分頁（方言不可避免了）等功能剝離出去。
２）固定連接數比動態連接數容易實現，簡單的代碼更易於維護。
本例有待完善之處（供參考）：
１）查詢空閒連接要遍歷數組，這樣當池中的連接數很大時可能會有問題（具體沒測過）
２）可以添加連接池阻塞的功能，有時候後台數據庫需要重啟，或者想重建連接池以提供更多的並發連接數？
