1、連接池模型

　　本文討論的連接池包括一個(gè)連接池類（DBConnectionPool）和一個(gè)連接池管理類（DBConnetionPoolManager）和一個(gè)配置文件操作類(ParseDSConfig)。連接池類是對某一數(shù)據(jù)庫所有連接的“緩沖池”，主要實(shí)現(xiàn)以下功能：①從連接池獲取或創(chuàng)建可用連接；②使用完畢之后，把連接返還給連接池；③在系統(tǒng)關(guān)閉前，斷開所有連接并釋放連接占用的系統(tǒng)資源；④還能夠處理無效連接（原來登記為可用的連接，由于某種原因不再可用，如超時(shí)，通訊問題），并能夠限制連接池中的連接總數(shù)不低于某個(gè)預(yù)定值和不超過某個(gè)預(yù)定值。(5)當(dāng)多數(shù)據(jù)庫時(shí),且數(shù)據(jù)庫是動態(tài)增加的話,將會加到配置文件中。

　　連接池管理類是連接池類的外覆類（wrapper）,符合單例模式，即系統(tǒng)中只能有一個(gè)連接池管理類的實(shí)例。其主要用于對多個(gè)連接池對象的管理，具有以下功能：①裝載并注冊特定數(shù)據(jù)庫的JDBC驅(qū)動程序；②根據(jù)屬性文件給定的信息，創(chuàng)建連接池對象；③為方便管理多個(gè)連接池對象，為每一個(gè)連接池對象取一個(gè)名字，實(shí)現(xiàn)連接池名字與其實(shí)例之間的映射；④跟蹤客戶使用連接情況，以便需要是關(guān)閉連接釋放資源。連接池管理類的引入主要是為了方便對多個(gè)連接池的使用和管理，如系統(tǒng)需要連接不同的數(shù)據(jù)庫，或連接相同的數(shù)據(jù)庫但由于安全性問題，需要不同的用戶使用不同的名稱和密碼。
???????? 2、連接池實(shí)現(xiàn)(經(jīng)過本人改版,可以適用多數(shù)據(jù)庫類型的應(yīng)用以及一種數(shù)據(jù)庫類型多個(gè)數(shù)據(jù)庫且數(shù)據(jù)? 庫的數(shù)量可以動態(tài)增加的應(yīng)用程序)
???????? 1),DBConnectionPool.java?? 數(shù)據(jù)庫連接池類
???????? 2),DBConnectionManager?.java?? 數(shù)據(jù)庫管理類
???????? 3),DSConfigBean?.java??????????????? 單個(gè)數(shù)據(jù)庫連接信息Bean
???????? 4),ParseDSConfig.java??????????????? 操作多(這個(gè)'多'包括不同的數(shù)據(jù)庫和同一種數(shù)據(jù)庫有多個(gè)數(shù)據(jù)庫)
??????????????????????????????????????????????????????????? 數(shù)據(jù) 配置文件xml
???????? 5),ds.config.xml?????????????????????????? 數(shù)據(jù)庫配置文件xml
???????? 原代碼如下:?
??????? DBConnectionPool.java??
??????? ----------------------------------------------------------
????? /**
?*?數(shù)據(jù)庫連接池類
?*/
package com.chunkyo.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.Timer;

/**
?* @author chenyanlin
?*
?*/
public class DBConnectionPool implements TimerListener {

?private Connection con=null;
?private int inUsed=0;??? //使用的連接數(shù)
?private ArrayList freeConnections = new ArrayList();//容器，空閑連接
?private int minConn;???? //最小連接數(shù)
?private int maxConn;???? //最大連接
?private String name;???? //連接池名字
?private String password; //密碼
?private String url;????? //數(shù)據(jù)庫連接地址
?private String driver;?? //驅(qū)動
?private String user;???? //用戶名
?public Timer timer;????? //定時(shí)
?/**
? *
? */
?public DBConnectionPool() {
??// TODO Auto-generated constructor stub
?}
?/**
? * 創(chuàng)建連接池
? * @param driver
? * @param name
? * @param URL
? * @param user
? * @param password
? * @param maxConn
? */
?public DBConnectionPool(String name, String driver,String URL, String user, String password, int maxConn)
?{
??this.name=name;
??this.driver=driver;
??this.url=URL;
??this.user=user;
??this.password=password;
??this.maxConn=maxConn;
?}
?/**
? * 用完，釋放連接
? * @param con
? */
?public synchronized void freeConnection(Connection con)
?{
??this.freeConnections.add(con);//添加到空閑連接的末尾
??this.inUsed--;
?}
?/**
? * timeout? 根據(jù)timeout得到連接
? * @param timeout
? * @return
? */
?public synchronized Connection getConnection(long timeout)
?{
??Connection con=null;
??if(this.freeConnections.size()>0)
??{
???con=(Connection)this.freeConnections.get(0);
???if(con==null)con=getConnection(timeout); //繼續(xù)獲得連接
??}
??else
??{
???con=newConnection(); //新建連接
??}
??if(this.maxConn==0||this.maxConn<this.inUsed)
??{
???con=null;//達(dá)到最大連接數(shù)，暫時(shí)不能獲得連接了。
??}
??if(con!=null)
??{
???this.inUsed++;
??}
??return con;
?}
?/**
? *
? * 從連接池里得到連接
? * @return
? */
?public synchronized Connection getConnection()
?{
??Connection con=null;
??if(this.freeConnections.size()>0)
??{
???con=(Connection)this.freeConnections.get(0);
???this.freeConnections.remove(0);//如果連接分配出去了，就從空閑連接里刪除
???if(con==null)con=getConnection(); //繼續(xù)獲得連接
??}
??else
??{
???con=newConnection(); //新建連接
??}
??if(this.maxConn==0||this.maxConn<this.inUsed)
??{
???con=null;//等待 超過最大連接時(shí)
??}
??if(con!=null)
??{
???this.inUsed++;
???System.out.println("得到　"+this.name+"　的連接，現(xiàn)有"+inUsed+"個(gè)連接在使用!");
??}
??return con;
?}
?/**
? *釋放全部連接
? *
? */
?public synchronized void release()
?{
??Iterator allConns=this.freeConnections.iterator();
??while(allConns.hasNext())
??{
???Connection con=(Connection)allConns.next();
???try
???{
????con.close();
???}
???catch(SQLException e)
???{
????e.printStackTrace();
???}
???
??}
??this.freeConnections.clear();
???
?}
?/**
? * 創(chuàng)建新連接
? * @return
? */
?private Connection newConnection()
?{
??try {
???Class.forName(driver);
???con=DriverManager.getConnection(url, user, password);
??} catch (ClassNotFoundException e) {
???// TODO Auto-generated catch block
???e.printStackTrace();
???System.out.println("sorry can't find db driver!");
??} catch (SQLException e1) {
???// TODO Auto-generated catch block
???e1.printStackTrace();
???System.out.println("sorry can't create Connection!");
??}
??return con;
??
?}
?/**
? * 定時(shí)處理函數(shù)
? */
?public synchronized void TimerEvent()
?{
???? //暫時(shí)還沒有實(shí)現(xiàn)以后會加上的
?}

?/**
? * @param args
? */
?public static void main(String[] args) {
??// TODO Auto-generated method stub

?}
?/**
? * @return the driver
? */
?public String getDriver() {
??return driver;
?}
?/**
? * @param driver the driver to set
? */
?public void setDriver(String driver) {
??this.driver = driver;
?}
?/**
? * @return the maxConn
? */
?public int getMaxConn() {
??return maxConn;
?}
?/**
? * @param maxConn the maxConn to set
? */
?public void setMaxConn(int maxConn) {
??this.maxConn = maxConn;
?}
?/**
? * @return the minConn
? */
?public int getMinConn() {
??return minConn;
?}
?/**
? * @param minConn the minConn to set
? */
?public void setMinConn(int minConn) {
??this.minConn = minConn;
?}
?/**
? * @return the name
? */
?public String getName() {
??return name;
?}
?/**
? * @param name the name to set
? */
?public void setName(String name) {
??this.name = name;
?}
?/**
? * @return the password
? */
?public String getPassword() {
??return password;
?}
?/**
? * @param password the password to set
? */
?public void setPassword(String password) {
??this.password = password;
?}
?/**
? * @return the url
? */
?public String getUrl() {
??return url;
?}
?/**
? * @param url the url to set
? */
?public void setUrl(String url) {
??this.url = url;
?}
?/**
? * @return the user
? */
?public String getUser() {
??return user;
?}
?/**
? * @param user the user to set
? */
?public void setUser(String user) {
??this.user = user;
?}

}

-------------------------------------------
?DBConnectionManager?.java
------------------------------------------
/**
?*?數(shù)據(jù)庫連接池管理類
?*/
package com.chunkyo.db;

import java.sql.Connection;
import java.util.ArrayList;
import java.util.Enumeration;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.Properties;
import java.util.Vector;

import com.chunkyo.db.ParseDSConfig;
import com.chunkyo.db.DSConfigBean;
import com.chunkyo.db.DBConnectionPool;

/**
?* @author chenyanlin
?*
?*/
public class DBConnectionManager {

?static private DBConnectionManager instance;//唯一數(shù)據(jù)庫連接池管理實(shí)例類
?static private int clients;???????????????? //客戶連接數(shù)
?private Vector drivers? = new Vector();//驅(qū)動信息
?private Hashtable pools=new Hashtable();//連接池
?
?/**
? * 實(shí)例化管理類
? */
?public DBConnectionManager() {
??// TODO Auto-generated constructor stub
??this.init();
?}
?/**
? * 得到唯一實(shí)例管理類
? * @return
? */
?static synchronized public DBConnectionManager getInstance()
?{
??if(instance==null)
??{
???instance=new DBConnectionManager();
??}
??return instance;
??
?}
?/**
? * 釋放連接
? * @param name
? * @param con
? */
?public void freeConnection(String name, Connection con)
?{
??DBConnectionPool pool=(DBConnectionPool)pools.get(name);//根據(jù)關(guān)鍵名字得到連接池
??if(pool!=null)
??pool.freeConnection(con);//釋放連接
?}
?/**
? * 得到一個(gè)連接根據(jù)連接池的名字name
? * @param name
? * @return
? */
?public Connection getConnection(String name)
?{
??DBConnectionPool pool=null;
??Connection con=null;
??pool=(DBConnectionPool)pools.get(name);//從名字中獲取連接池
??con=pool.getConnection();//從選定的連接池中獲得連接
??if(con!=null)
??System.out.println("得到連接。。。");
??return con;
?}
?/**
? * 得到一個(gè)連接，根據(jù)連接池的名字和等待時(shí)間
? * @param name
? * @param time
? * @return
? */
?public Connection getConnection(String name, long timeout)
?{
??DBConnectionPool pool=null;
??Connection con=null;
??pool=(DBConnectionPool)pools.get(name);//從名字中獲取連接池
??con=pool.getConnection(timeout);//從選定的連接池中獲得連接
??System.out.println("得到連接。。。");
??return con;
?}
?/**
? * 釋放所有連接
? */
?public synchronized void release()
?{
??Enumeration allpools=pools.elements();
??while(allpools.hasMoreElements())
??{
???DBConnectionPool pool=(DBConnectionPool)allpools.nextElement();
???if(pool!=null)pool.release();
??}
??pools.clear();
?}

?/**
? * 創(chuàng)建連接池
? * @param props
? */
?private void createPools(DSConfigBean dsb)
?{
??DBConnectionPool dbpool=new DBConnectionPool();
??dbpool.setName(dsb.getName());
??dbpool.setDriver(dsb.getDriver());
??dbpool.setUrl(dsb.getUrl());
??dbpool.setUser(dsb.getUsername());
??dbpool.setPassword(dsb.getPassword());
??dbpool.setMaxConn(dsb.getMaxconn());
??System.out.println("ioio:"+dsb.getMaxconn());
??pools.put(dsb.getName(), dbpool);
?}
?/**
? * 初始化連接池的參數(shù)
? */
?private void init()
?{
??//加載驅(qū)動程序
??this.loadDrivers();
??//創(chuàng)建連接池
??Iterator alldriver=drivers.iterator();
??while(alldriver.hasNext())
??{
???this.createPools((DSConfigBean)alldriver.next());
???System.out.println("創(chuàng)建連接池。。。");
???
??}
??System.out.println("創(chuàng)建連接池完畢。。。");
?}

?/**
? * 加載驅(qū)動程序
? * @param props
? */
?private void loadDrivers()
?{
??ParseDSConfig pd=new ParseDSConfig();
?//讀取數(shù)據(jù)庫配置文件
??drivers=pd.readConfigInfo("ds.config.xml");
??System.out.println("加載驅(qū)動程序。。。");
?}
?/**
? * @param args
? */
?public static void main(String[] args) {
??// TODO Auto-generated method stub

?}

}
----------------------------------------
DSConfigBean.java
----------------------------------------
/**
?*?配置文件Bean類
?*/
package com.chunkyo.db;

/**
?* @author chenyanlin
?*
?*/
public class DSConfigBean {

?private String type???? =""; //數(shù)據(jù)庫類型
?private String name???? =""; //連接池名字
?private String driver?? =""; //數(shù)據(jù)庫驅(qū)動
?private String url????? =""; //數(shù)據(jù)庫url
?private String username =""; //用戶名
?private String password =""; //密碼
?private int maxconn? =0; //最大連接數(shù)
?/**
? *
? */
?public DSConfigBean() {
??// TODO Auto-generated constructor stub
?}

?/**
? * @param args
? */
?public static void main(String[] args) {
??// TODO Auto-generated method stub

?}

?/**
? * @return the driver
? */
?public String getDriver() {
??return driver;
?}

?/**
? * @param driver the driver to set
? */
?public void setDriver(String driver) {
??this.driver = driver;
?}

?/**
? * @return the maxconn
? */
?public int getMaxconn() {
??return maxconn;
?}

?/**
? * @param maxconn the maxconn to set
? */
?public void setMaxconn(int maxconn) {
??this.maxconn = maxconn;
?}

?/**
? * @return the name
? */
?public String getName() {
??return name;
?}

?/**
? * @param name the name to set
? */
?public void setName(String name) {
??this.name = name;
?}

?/**
? * @return the password
? */
?public String getPassword() {
??return password;
?}

?/**
? * @param password the password to set
? */
?public void setPassword(String password) {
??this.password = password;
?}

?/**
? * @return the type
? */
?public String getType() {
??return type;
?}

?/**
? * @param type the type to set
? */
?public void setType(String type) {
??this.type = type;
?}

?/**
? * @return the url
? */
?public String getUrl() {
??return url;
?}

?/**
? * @param url the url to set
? */
?public void setUrl(String url) {
??this.url = url;
?}

?/**
? * @return the username
? */
?public String getUsername() {
??return username;
?}

?/**
? * @param username the username to set
? */
?public void setUsername(String username) {
??this.username = username;
?}

}
-----------------------------------------------------
ParseDSConfig.java
-----------------------------------------------------
/**
?*?操作配置文件類 讀? 寫 修改 刪除等操作
?*/
package com.chunkyo.db;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import java.util.Vector;
import java.util.Iterator;

import org.jdom.Document;
import org.jdom.Element;
import org.jdom.JDOMException;
import org.jdom.input.SAXBuilder;
import org.jdom.output.Format;
import org.jdom.output.XMLOutputter;

/**
?* @author chenyanlin
?*
?*/
public class ParseDSConfig {

?/**
? * 構(gòu)造函數(shù)
? */
?public ParseDSConfig() {
??// TODO Auto-generated constructor stub
?}
?/**
? * 讀取xml配置文件
? * @param path
? * @return
? */
?public Vector readConfigInfo(String path)
?{
??String rpath=this.getClass().getResource("").getPath().substring(1)+path;
??Vector dsConfig=null;
??FileInputStream fi = null;
??try
??{
???fi=new FileInputStream(rpath);//讀取路徑文件
???dsConfig=new Vector();
???SAXBuilder sb=new SAXBuilder();
???Document doc=sb.build(fi);
???Element root=doc.getRootElement();
???List pools=root.getChildren();
???Element pool=null;
???Iterator allPool=pools.iterator();
???while(allPool.hasNext())
???{
????pool=(Element)allPool.next();
????DSConfigBean dscBean=new DSConfigBean();
????dscBean.setType(pool.getChild("type").getText());
????dscBean.setName(pool.getChild("name").getText());
????System.out.println(dscBean.getName());
????dscBean.setDriver(pool.getChild("driver").getText());
????dscBean.setUrl(pool.getChild("url").getText());
????dscBean.setUsername(pool.getChild("username").getText());
????dscBean.setPassword(pool.getChild("password").getText());
????dscBean.setMaxconn(Integer.parseInt(pool.getChild("maxconn").getText()));
????dsConfig.add(dscBean);
???}
???
??} catch (FileNotFoundException e) {
???// TODO Auto-generated catch block
???e.printStackTrace();
??} catch (JDOMException e) {
???// TODO Auto-generated catch block
???e.printStackTrace();
??} catch (IOException e) {
???// TODO Auto-generated catch block
???e.printStackTrace();
??}
??
??finally
??{
???try {
????fi.close();
???} catch (IOException e) {
????// TODO Auto-generated catch block
????e.printStackTrace();
???}
??}
??
??return dsConfig;
?}

/**
?*修改配置文件 沒時(shí)間寫 過段時(shí)間再貼上去 其實(shí)一樣的
?*/
?public void modifyConfigInfo(String path,DSConfigBean dsb) throws Exception
?{
??String rpath=this.getClass().getResource("").getPath().substring(1)+path;
??FileInputStream fi=null; //讀出
??FileOutputStream fo=null; //寫入
??
?}
/**
?*增加配置文件
?*
?*/
?public void addConfigInfo(String path,DSConfigBean dsb)
?{
??String rpath=this.getClass().getResource("").getPath().substring(1)+path;
??FileInputStream fi=null;
??FileOutputStream fo=null;
??try
??{
???fi=new FileInputStream(rpath);//讀取xml流
???
???SAXBuilder sb=new SAXBuilder();
???
???Document doc=sb.build(fi); //得到xml
???Element root=doc.getRootElement();
???List pools=root.getChildren();//得到xml子樹
???
???Element newpool=new Element("pool"); //創(chuàng)建新連接池
???
???Element pooltype=new Element("type"); //設(shè)置連接池類型
???pooltype.setText(dsb.getType());
???newpool.addContent(pooltype);
???
???Element poolname=new Element("name");//設(shè)置連接池名字
???poolname.setText(dsb.getName());
???newpool.addContent(poolname);
???
???Element pooldriver=new Element("driver"); //設(shè)置連接池驅(qū)動
???pooldriver.addContent(dsb.getDriver());
???newpool.addContent(pooldriver);
???
???Element poolurl=new Element("url");//設(shè)置連接池url
???poolurl.setText(dsb.getUrl());
???newpool.addContent(poolurl);
???
???Element poolusername=new Element("username");//設(shè)置連接池用戶名
???poolusername.setText(dsb.getUsername());
???newpool.addContent(poolusername);
???
???Element poolpassword=new Element("password");//設(shè)置連接池密碼
???poolpassword.setText(dsb.getPassword());
???newpool.addContent(poolpassword);
???
???Element poolmaxconn=new Element("maxconn");//設(shè)置連接池最大連接
???poolmaxconn.setText(String.valueOf(dsb.getMaxconn()));
???newpool.addContent(poolmaxconn);
???pools.add(newpool);//將child添加到root
???Format format = Format.getPrettyFormat();
????? format.setIndent("");
????? format.setEncoding("utf-8");
????? XMLOutputter outp = new XMLOutputter(format);
????? fo = new FileOutputStream(rpath);
????? outp.output(doc, fo);
??} catch (FileNotFoundException e) {
???// TODO Auto-generated catch block
???e.printStackTrace();
??} catch (JDOMException e) {
???// TODO Auto-generated catch block
???e.printStackTrace();
??} catch (IOException e) {
???// TODO Auto-generated catch block
???e.printStackTrace();
??}
??finally
??{
???
??}
?}
?/**
? *刪除配置文件
? */
?public void delConfigInfo(String path,String name)
?{
??String rpath=this.getClass().getResource("").getPath().substring(1)+path;
??FileInputStream fi = null;
??FileOutputStream fo=null;
??try
??{
???fi=new FileInputStream(rpath);//讀取路徑文件
???SAXBuilder sb=new SAXBuilder();
???Document doc=sb.build(fi);
???Element root=doc.getRootElement();
???List pools=root.getChildren();
???Element pool=null;
???Iterator allPool=pools.iterator();
???while(allPool.hasNext())
???{
????pool=(Element)allPool.next();
????if(pool.getChild("name").getText().equals(name))
????{
?????pools.remove(pool);
?????break;
????}
???}
???Format format = Format.getPrettyFormat();
????? format.setIndent("");
????? format.setEncoding("utf-8");
????? XMLOutputter outp = new XMLOutputter(format);
????? fo = new FileOutputStream(rpath);
????? outp.output(doc, fo);

???
??} catch (FileNotFoundException e) {
???// TODO Auto-generated catch block
???e.printStackTrace();
??} catch (JDOMException e) {
???// TODO Auto-generated catch block
???e.printStackTrace();
??} catch (IOException e) {
???// TODO Auto-generated catch block
???e.printStackTrace();
??}
??
??finally
??{
???try {
????fi.close();
???} catch (IOException e) {
????// TODO Auto-generated catch block
????e.printStackTrace();
???}
??}
?}
?/**
? * @param args
? * @throws Exception
? */
?public static void main(String[] args) throws Exception {
??// TODO Auto-generated method stub

??ParseDSConfig pd=new ParseDSConfig();
??String path="ds.config.xml";
??pd.readConfigInfo(path);
??//pd.delConfigInfo(path, "tj012006");
??DSConfigBean dsb=new DSConfigBean();
??dsb.setType("oracle");
??dsb.setName("yyy004");
??dsb.setDriver("org.oracle.jdbc");
??dsb.setUrl("jdbc:oracle://localhost");
??dsb.setUsername("sa");
??dsb.setPassword("");
??dsb.setMaxconn(1000);
??pd.addConfigInfo(path, dsb);
??pd.delConfigInfo(path, "yyy001");
?}

}

--------------------------------------
ds.config.xml?? 配置文件
--------------------------------------


<ds-config>
<pool>
<type>mysql</type>
<name>user</name>
<driver>com.mysql.jdbc.driver</driver>
<url>jdbc:mysql://localhost:3306/user</url>
<username>sa</username>
<password>123456</password>
<maxconn>100</maxconn>
</pool>
<pool>
<type>mysql</type>
<name>user2</name>
<driver>com.mysql.jdbc.driver</driver>
<url>jdbc:mysql://localhost:3306/user2</url>
<username>sa</username>
<password>1234</password>
<maxconn>10</maxconn>
</pool>
<pool>
<type>sql2000</type>
<name>books</name>
<driver>com.microsoft.sqlserver.driver</driver>
<url>jdbc:sqlserver://localhost:1433/books:databasename=books</url>
<username>sa</username>
<password></password>
<maxconn>100</maxconn>
</pool>
</ds-config>


3. 連接池的使用
? 1。Connection的獲得和釋放
? DBConnectionManager?? connectionMan=DBConnectionManager?.getInstance();//得到唯一實(shí)例
?? //得到連接
?? String name="mysql";//從上下文得到你要訪問的數(shù)據(jù)庫的名字
?? Connection? con=connectionMan.getConnection(name);
? //使用
? 。。。。。。。
? // 使用完畢
?connectionMan.freeConnection(name,con);//釋放，但并未斷開連接
?2。數(shù)據(jù)庫連接的動態(tài)增加和連接池的動態(tài)增加
????? 1。調(diào)用xml操作增加類

??????2。重新實(shí)例華連接池管理池類