/**
*?Title:?ConnectPool.java
*?Description:?連接池管理器
*?Copyright:?Copyright?(c)?2002/12/25
*?Company:
*?Author?:
*?Version?2.0
*/

import ?java.io. * ;
import ?java.sql. * ;
import ?java.util. * ;
import ?java.util.Date;

/**
*?管理類DBConnectionManager支持對一個或多個由屬性文件定義的數據庫連接
*?池的訪問.客戶程序可以調用getInstance()方法訪問本類的唯一實例.
*/
public ? class ?ConnectPool?{
???? static ? public ?ConnectPool?instance;? // ?唯一實例
???? static ? public ? int ?clients;
???? public ?Vector?drivers? = ? new ?Vector();? // 驅動
???? public ?PrintWriter?log;
???? public ?Hashtable?pools? = ? new ?Hashtable();? // 連接

???? /**
????*?返回唯一實例.如果是第一次調用此方法,則創建實例
????*
????*? @return ?DBConnectionManager?唯一實例
???? */
???? static ? synchronized ? public ?ConnectPool?getInstance()?{
???????? if ?(instance? == ? null )?{

????????}
????????clients ++ ;
???????? return ?instance;
????}

???? /**
????*?建構函數私有以防止其它對象創建本類實例
???? */
???? public ?ConnectPool()?{
????????init();
????}

???? /**
????*?將連接對象返回給由名字指定的連接池
????*
????*? @param ?name?在屬性文件中定義的連接池名字
????*? @param ?con?連接對象
???? */
???? public ? void ?freeConnection(String?name,?Connection?con)?{
????????DBConnectionPool?pool? = ?(DBConnectionPool)?pools.get(name);
???????? if ?(pool? != ? null )?{
????????????pool.freeConnection(con);
????????}
???????? else ?{
????????????System.out.println( " pool?==null " );
????????}
????????clients -- ;
????}

???? /**
????*?獲得一個可用的(空閑的)連接.如果沒有可用連接,且已有連接數小于最大連接數
????*?限制,則創建并返回新連接
????*
????*? @param ?name?在屬性文件中定義的連接池名字
????*? @return ?Connection?可用連接或null
???? */
???? public ?Connection?getConnection(String?name)?{
????????DBConnectionPool?pool? = ?(DBConnectionPool)?pools.get(name);
???????? if ?(pool? != ? null )?{
???????????? // return?pool.getConnection();
???????????? return ?pool.returnConnection();
????????}
???????? return ? null ;
????}

???? /**
????*?獲得一個可用連接.若沒有可用連接,且已有連接數小于最大連接數限制,
????*?則創建并返回新連接.否則,在指定的時間內等待其它線程釋放連接.
????*
????*? @param ?name?連接池名字
????*? @param ?time?以毫秒計的等待時間
????*? @return ?Connection?可用連接或null
???? */
???? public ?Connection?getConnection(String?name,? long ?time)?{
????????DBConnectionPool?pool? = ?(DBConnectionPool)?pools.get(name);
???????? if ?(pool? != ? null )?{
???????? return ?pool.getConnection(time);
????????}
???????? return ? null ;
????}

???? /**
????*?關閉所有連接,撤銷驅動程序的注冊
???? */
???? public ? synchronized ? void ?release()?{
???????? // ?等待直到最后一個客戶程序調用
???????? if ?( -- clients? != ? 0 )?{
???????? return ;
????????}

????????Enumeration?allPools? = ?pools.elements();
???????? while ?(allPools.hasMoreElements()){
????????????DBConnectionPool?pool? = ?(DBConnectionPool)?allPools.nextElement();
????????????pool.release();
????????}
????????Enumeration?allDrivers? = ?drivers.elements();
???????? while ?(allDrivers.hasMoreElements())?{
????????????Driver?driver? = ?(Driver)?allDrivers.nextElement();
???????????? try ?{
????????????????DriverManager.deregisterDriver(driver);
????????????????log( " 撤銷JDBC驅動程序? " ? + ?driver.getClass().getName() + " 的注冊 " );
????????????}
???????????? catch ?(SQLException?e)?{
????????????????log(e,? " 無法撤銷下列JDBC驅動程序的注冊:? " ? + ?driver.getClass().getName());
????????????}
????????}
????}

???? /**
????*?根據指定屬性創建連接池實例.
????*
????*? @param ?props?連接池屬性
???? */
???? private ? void ?createPools(Properties?props){
????????Enumeration?propNames? = ?props.propertyNames();
???????? while ?(propNames.hasMoreElements()){
????????????String?name? = ?(String)?propNames.nextElement();
???????????? if ?(name.endsWith( " .url " ))?{
????????????????String?poolName? = ?name.substring( 0 ,?name.lastIndexOf( " . " ));
????????????????String?url? = ?props.getProperty(poolName? + ? " .url " );
???????????????? if ?(url? == ? null )?{
????????????????????log( " 沒有為連接池 " ? + ?poolName? + ? " 指定URL " );
???????????????????? continue ;
????????????????}
????????????????String?user? = ?props.getProperty(poolName? + ? " .user " );
????????????????String?password? = ?props.getProperty(poolName? + ? " .password " );
????????????????String?maxconn? = ?props.getProperty(poolName? + ? " .maxconn " ,? " 0 " );
???????????????? int ?max;
???????????????? try {
????????????????????max? = ?Integer.valueOf(maxconn).intValue();
????????????????}
???????????????? catch ?(NumberFormatException?e){
????????????????????log( " 錯誤的最大連接數限制:? " ? + ?maxconn? + ? " ?.連接池:? " ? + ?poolName);
????????????????????max? = ? 0 ;
????????????????}
????????????????DBConnectionPool?pool? = ? new ?DBConnectionPool(poolName,?url,?user,?password,?max);
????????????????pools.put(poolName,?pool);
????????????????log( " 成功創建連接池 " ? + ?poolName);
????????????}
????????}
????}

???? /**
????*?讀取屬性完成初始化
???? */
???? private ? void ?init(){
???????? try {
????????????Properties?p? = ? new ?Properties();
????????????String?configs? = ?System.getProperty( " user.dir " ) + " \conf\db.properties " ;

????????????System.out.println( " configs?file?local?at? " + configs);
????????????FileInputStream?is? = ? new ?FileInputStream(configs);
????????????Properties?dbProps? = ? new ?Properties();
???????????? try {
????????????????dbProps.load(is);
????????????}
???????????? catch ?(Exception?e){
????????????????System.err.println( " 不能讀取屬性文件.? " ? + " 請確保db.properties在CLASSPATH指定的路徑中 " );
???????????????? return ;
????????????}
????????????String?logFile? = ?dbProps.getProperty( " logfile " ,? " DBConnectionManager.log " );
???????????? try {
????????????????log? = ? new ?PrintWriter( new ?FileWriter(logFile,? true ),? true );
????????????}
???????????? catch ?(IOException?e){
????????????????System.err.println( " 無法打開日志文件:? " ? + ?logFile);
????????????????log? = ? new ?PrintWriter(System.err);
????????????}
????????????loadDrivers(dbProps);
????????????createPools(dbProps);?
????????} catch (Exception?e){}
????}

???? /**
????171?*?裝載和注冊所有JDBC驅動程序
????172?*
????173?*? @param ?props?屬性
????174? */
???? private ? void ?loadDrivers(Properties?props){
????????String?driverClasses? = ?props.getProperty( " drivers " );
????????StringTokenizer?st? = ? new ?StringTokenizer(driverClasses);
???????? while ?(st.hasMoreElements()){
????????????String?driverClassName? = ?st.nextToken().trim();
???????????? try {
????????????????Driver?driver? = ?(Driver)
????????????????Class.forName(driverClassName).newInstance();
????????????????DriverManager.registerDriver(driver);
????????????????drivers.addElement(driver);
????????????????System.out.println(driverClassName);
????????????????log( " 成功注冊JDBC驅動程序 " ? + ?driverClassName);
????????????}
???????????? catch ?(Exception?e){
????????????????log( " 無法注冊JDBC驅動程序:? " ? +
????????????????driverClassName? + ? " ,?錯誤:? " ? + ?e);
????????????}
????????}
????}

???? /**
????*?將文本信息寫入日志文件
???? */
???? private ? void ?log(String?msg){
????????log.println( new ?Date()? + ? " :? " ? + ?msg);
????}

???? /**
????*?將文本信息與異常寫入日志文件
???? */
???? private ? void ?log(Throwable?e,?String?msg){
????????log.println( new ?Date()? + ? " :? " ? + ?msg);
????????e.printStackTrace(log);
????}
}

/**
*?此內部類定義了一個連接池.它能夠根據要求創建新連接,直到預定的最
*?大連接數為止.在返回連接給客戶程序之前,它能夠驗證連接的有效性.
*/
class ?DBConnectionPool{
???? // private?int?checkedOut;
???? private ?Vector?freeConnections? = ? new ?Vector();
???? private ? int ?maxConn;
???? private ?String?name;
???? private ?String?password;
???? private ?String?URL;
???? private ?String?user;
????
???? /**
????*?創建新的連接池
????*
????*? @param ?name?連接池名字
????*? @param ?URL?數據庫的JDBC?URL
????*? @param ?user?數據庫帳號,或?null
????*? @param ?password?密碼,或?null
????*? @param ?maxConn?此連接池允許建立的最大連接數
???? */
???? public ?DBConnectionPool(String?name,?String?URL,?String?user,?String?password, int ?maxConn){
???????? this .name? = ?name;
???????? this .URL? = ?URL;
???????? this .user? = ?user;
???????? this .password? = ?password;
???????? this .maxConn? = ?maxConn;
????}

???? /**
????*?將不再使用的連接返回給連接池
????*? @param ?con?客戶程序釋放的連接
???? */
???? public ? synchronized ? void ?freeConnection(Connection?con)?{
???????? // ?將指定連接加入到向量末尾
???????? try {
???????????? if (con.isClosed()){
????????????????System.out.println( " before?freeConnection?con?is?closed " );
????????????}
????????????freeConnections.addElement(con);
????????????Connection?contest? = ?(Connection)?freeConnections.lastElement();
???????????? if (contest.isClosed()){
????????????????System.out.println( " after?freeConnection?contest?is?closed " );
????????????}
????????????notifyAll();
????????}
???????? catch (SQLException?e){
????????????System.out.println(e);
????????}
????}
????
???? /**
????*?從連接池獲得一個可用連接.如沒有空閑的連接且當前連接數小于最大連接
????*?數限制,則創建新連接.如原來登記為可用的連接不再有效,則從向量刪除之,
????*?然后遞歸調用自己以嘗試新的可用連接.
???? */
???? public ? synchronized ?Connection?getConnection(){
????????Connection?con? = ? null ;
???????? if ?(freeConnections.size()? > ? 0 ){
???????????? // ?獲取向量中第一個可用連接
????????????con? = ?(Connection)?freeConnections.firstElement();
????????????freeConnections.removeElementAt( 0 );
???????????? try ?{
???????????????? if ?(con.isClosed()){
????????????????????log( " 從連接池 " ? + ?name + " 刪除一個無效連接 " );
????????????????????System.out.println( " 從連接池 " ? + ?name + " 刪除一個無效連接 " );
???????????????????? // ?遞歸調用自己,嘗試再次獲取可用連接
????????????????????con? = ?getConnection();
????????????????}
????????????}
???????????? catch ?(SQLException?e){
????????????????log( " 從連接池 " ? + ?name + " 刪除一個無效連接時錯誤 " );
????????????????System.out.println( " 從連接池 " ? + ?name + " 刪除一個無效連接出錯 " );
???????????????? // ?遞歸調用自己,嘗試再次獲取可用連接
????????????????con? = ?getConnection();
????????????}
???????????? if (freeConnections.size() > maxConn){
????????????????System.out.println( " ?刪除一個溢出連接? " );
????????????????releaseOne();
????????????}
????????}
???????? else ? if ((maxConn? == ? 0 ) || (freeConnections.size() < maxConn)){
????????????con? = ?newConnection();
????????}
???????? return ?con;
????}

???? public ? synchronized ?Connection?returnConnection(){
????????Connection?con? = ? null ;
???????? // 如果閑置小于最大連接,返回一個新連接
???????? if (freeConnections.size() < maxConn){
????????????con? = ?newConnection();
????????}
???????? // 如果閑置大于最大連接，返回一個可用的舊連接
???????? else ? if (freeConnections.size() >= maxConn){
????????????con? = ?(Connection)?freeConnections.firstElement();
????????????System.out.println( " ?[a?連接池可用連接數?]?:? " + " [? " + freeConnections.size() + " ?] " );
????????????freeConnections.removeElementAt( 0 );
????????????System.out.println( " ?[b?連接池可用連接數?]?:? " + " [? " + freeConnections.size() + " ?] " );
???????????? try {
???????????????? if ?(con.isClosed()){
????????????????????log( " 從連接池 " ? + ?name + " 刪除一個無效連接 " );
????????????????????System.out.println( " 從連接池 " ? + ?name + " 刪除一個無效連接 " );
????????????????????returnConnection();
????????????????}
????????????}
???????????? catch ?(SQLException?e){
????????????????log( " 從連接池 " ? + ?name + " 刪除一個無效連接時錯誤 " );
????????????????System.out.println( " 從連接池 " ? + ?name + " 刪除一個無效連接出錯 " );
????????????????returnConnection();
????????????}
????????}
???????? return ?con;
????}

???? /**
????*?從連接池獲取可用連接.可以指定客戶程序能夠等待的最長時間
????*?參見前一個getConnection()方法.
????*
????*? @param ?timeout?以毫秒計的等待時間限制
???? */
???? public ? synchronized ?Connection?getConnection( long ?timeout){
???????? long ?startTime? = ? new ?Date().getTime();
????????Connection?con;
???????? while ?((con? = ?getConnection())? == ? null ){
???????????? try {
????????????????wait(timeout);
????????????}
???????????? catch ?(InterruptedException?e)?{}
???????????? if ?(( new ?Date().getTime()? - ?startTime)? >= ?timeout)?{
???????????????? // ?wait()返回的原因是超時
???????????????? return ? null ;
????????????}
????????}
???????? return ?con;
????}

???? /**
????*?關閉所有連接
???? */
???? public ? synchronized ? void ?release(){
????????Enumeration?allConnections? = ?freeConnections.elements();
???????? while ?(allConnections.hasMoreElements()){
????????????Connection?con? = ?(Connection)?allConnections.nextElement();
???????????? try ?{
????????????????con.close();
????????????????log( " 關閉連接池 " ? + ?name + " 中的一個連接 " );
????????????}
???????????? catch ?(SQLException?e){
????????????????log(e,? " 無法關閉連接池 " ? + ?name + " 中的連接 " );
????????????}
????????}
????????freeConnections.removeAllElements();
????}

???? /**
????*?關閉一個連接
???? */
???? public ? synchronized ? void ?releaseOne(){
???????? if (freeConnections.firstElement() != null ){?
????????????Connection?con? = ?(Connection)?freeConnections.firstElement();
???????????? try ?{
????????????????con.close();
????????????????System.out.println( " 關閉連接池 " ? + ?name + " 中的一個連接 " );
????????????????log( " 關閉連接池 " ? + ?name + " 中的一個連接 " );
????????????}
???????????? catch ?(SQLException?e){
????????????????System.out.println( " 無法關閉連接池 " ? + ?name + " 中的一個連接 " );
????????????????log(e,? " 無法關閉連接池 " ? + ?name + " 中的連接 " );
????????????}
????????}
???????? else {
????????????System.out.println( " releaseOne()?bug. " );
????????}
????}

???? /**
????*?創建新的連接
???? */
???? private ?Connection?newConnection(){
????????Connection?con? = ? null ;
???????? try {
???????????? if ?(user? == ? null )?{
????????????????con? = ?DriverManager.getConnection(URL);
????????????}
???????????? else {
????????????????con? = ?DriverManager.getConnection(URL,?user,?password);
????????????}
????????????log( " 連接池 " ? + ?name + " 創建一個新的連接 " );
????????}
???????? catch ?(SQLException?e)?{
????????????log(e,? " 無法創建下列URL的連接:? " ? + ?URL);
???????????? return ? null ;
????????}
???????? return ?con;
????????}
????}
}

/**
*?Title:?ConnectPool.java
*?Description:?數據庫操作
*?Copyright:?
*?Company:
*?Author?:
*?remark?:?加入指針回滾
*?Version?2.0
*/

import ?java.io. * ;
import ?com.sjky.pool. * ;
import ?java.sql. * ;
import ?java.util. * ;
import ?java.util.Date;
import ?java.net. * ;

public ? class ?PoolMan? extends ?ConnectPool?{

???? private ?ConnectPool?connMgr;
???? private ?Statement?stmt;
???? private ?Connection?con?;
???? private ?ResultSet?rst;

???? /**
????*對象連接初始化
????*? */

???? public ?Connection?getPool(String?name)? throws ?Exception{
???????? try {
????????????connMgr? = ?ConnectPool.getInstance();
????????????con? = ?connMgr.getConnection(name);
????????}
???????? catch (Exception?e){
????????????System.err.println( " 不能創建連接!請嘗試重啟應用服務器 " );
????????}
???????? return ?con;
????}

???? /**
????*同以上方法,加入連接空閑等待時間
????*待用方法
????*? */

???? public ?Connection?getPool_t(String?name,? long ?time)? throws ?Exception?{
???????? try {
????????????connMgr? = ?ConnectPool.getInstance();
????????????con? = ?connMgr.getConnection(name,time);
????????}
???????? catch (Exception?e){
????????????System.err.println( " 不能創建連接! " );
????????}
???????? return ?con;
????}
???? /**
????*執行查詢方法1
????*? */
???? public ?ResultSet?executeQuery(String?SqlStr)? throws ?Exception{
????????ResultSet?result? = ? null ;
???????? try {
????????????stmt? = ?con.createStatement();
????????????result? = ?stmt.executeQuery(SqlStr);
????????????con.commit();
????????}
???????? catch (java.sql.SQLException?e){
???????????? throw ? new ?Exception( " 執行查詢語句出錯 " );
????????}
???????? return ?result;
????}

???? /**
????*執行查詢方法2
????*? */
???? public ?ResultSet?getRst(String?SqlStr)? throws ?Exception?{
???????? // ?ResultSet?result?=?null;
???????? try {
????????????stmt? = ?con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
????????????rst? = ?stmt.executeQuery(SqlStr);
????????????con.commit();
????????}
???????? catch (java.sql.SQLException?e){
???????????? throw ? new ?Exception( " 執行查詢語句出錯 " );
????????}
???????? return ?rst;
????}
???? /**
????*執行更新
????*? */
???? public ? int ?Update(String?SqlStr)? throws ?Exception{
???????? int ?result? = ? - 1 ;
???????? try {
????????????stmt? = ?con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
????????????result? = ?stmt.executeUpdate(SqlStr);
????????????con.commit();
???????????? if (result == 0 )
????????????System.out.println( " 執行delete,update,insert?SQL出錯 " );
????????}
???????? catch (java.sql.SQLException?e)????{
????????????System.err.println( " 執行delete,update,insert?SQL出錯 " );
????????}
???????? return ?result;
????}

???? /**
????*執行事務處理
????*? */
???? public ? boolean ?handleTransaction(Vector?SqlArray)? throws ?Exception?{
???????? boolean ?result? = ? false ;
???????? int ?ArraySize? = ?SqlArray.size();
???????? try {
????????????stmt? = ?con.createStatement();
????????????con.setAutoCommit( false );
????????????System.out.println( " ArraySize?is " ? + ArraySize);
???????????? for ( int ?i = 0 ;i < ArraySize;i ++ ){
????????????????System.out.println( " ?開始執行語句 " + (String)SqlArray.elementAt(i));
????????????????stmt.executeUpdate((String)SqlArray.elementAt(i));
????????????????System.out.println( " ?執行成功 " );
????????????}
????????????con.commit();
????????????con.setAutoCommit( true )?; // 必須
????????????System.out.println( " 事務執行成功 " );
????????????result? = ? true ;
????????}
???????? catch (java.sql.SQLException?e){
???????????? try {
????????????????System.out.println(e.toString());
????????????????System.out.println( " 數據庫操作失敗 " );
????????????????con.rollback();
????????????}
???????????????? catch (java.sql.SQLException?Te){
????????????????System.err.println( " 事務出錯回滾異常 " );
????????????}
????????}
???????? try {
????????????con.setAutoCommit( true );
????????}
???????? catch (java.sql.SQLException?e){
????????????System.err.println( " 設置自動提交失敗 " );
????????}
???????? return ?result;
????}

???? /**
????*釋放連接
????*? */
???? public ? void ?close(String?name)? throws ?Exception?{
???????? try {
???????????? if (stmt != null )
????????????????stmt.close();
???????????? if (con != null ){
????????????????connMgr.freeConnection(name,con);
????????????????System.out.println( " ?[c?正在釋放一個連接?]? " );
????????????}
????????}
???????? catch (java.sql.SQLException?e){
????????????System.err.println( " 釋放連接出錯 " );
????????}
????}
}