package liyz.test.db;
import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Date;
import java.util.Enumeration;
import java.util.Hashtable;
import java.util.StringTokenizer;
import java.util.Vector;
public class DBConnectionManager {
?static private DBConnectionManager instance; // 唯一實例
?static private int clients;
?private Vector drivers = new Vector();
?private PrintWriter log;
?private Hashtable pools = new Hashtable();
?private static final String DRIVER = "oracle.jdbc.driver.OracleDriver";
?private static final String LOGFILE = "/export/home/OA_Trace/DBConnectionManager1.log";
?private static final String URL = "jdbc:oracle:thin:@10.147.42.21:1521:pms";
?private static final String USER = "pmsdev";
?private static final String PASSWORD = "pmsdev";
?private static final String MAXCONN = "10";
?/**
? * 返回唯一實例.如果是第一次調用此方法,則創建實例
? *
? * @return DBConnectionManager 唯一實例
? */
?static synchronized public DBConnectionManager getInstance() {
??if (instance == null) {
???instance = new DBConnectionManager();
??}
??clients++;
??return instance;
?}
?/**
? * 建構函數私有以防止其它對象創建本類實例
? */
?private DBConnectionManager() {
??init();
?}
?/**
? * 將連接對象返回給由名字指定的連接池
? *
? * @param name
? *??????????? 在屬性文件中定義的連接池名字
? * @param con
? *??????????? 連接對象
? */
?public void freeConnection(String name, Connection con) {
??DBConnectionPool pool = (DBConnectionPool) pools.get(name);
??if (pool != null) {
???pool.freeConnection(con);
??}
?}
?/**
? * 獲得一個可用的(空閑的)連接.如果沒有可用連接,且已有連接數小于最大連接數 限制,則創建并返回新連接
? *
? * @param name
? *??????????? 在屬性文件中定義的連接池名字
? * @return Connection 可用連接或null
? */
?public Connection getConnection(String name) {
??DBConnectionPool pool = (DBConnectionPool) pools.get(name);
??if (pool != null) {
???return pool.getConnection();
??}
??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() {
??String poolName = "idb";
??String url = this.getPact("idb.url");
??if (url == null) {
???log("沒有為連接池" + poolName + "指定URL");
??}
??String user = this.getPact("idb.user");
??String password = this.getPact("idb.password");
??String maxconn = this.getPact("idb.maxconn");
??String maxfreeconn = this.getPact("idb.maxfreeconn");
??int max, maxfree;
??try {
???max = Integer.valueOf(maxconn).intValue();
???maxfree = Integer.valueOf(maxfreeconn).intValue();
??} catch (NumberFormatException e) {
???log("錯誤的連接數限制: maxconn:" + maxconn + " minConn:" + maxfreeconn
?????+ " .連接池: " + poolName);
???max = 0;
???maxfree = 5;
??}
??DBConnectionPool pool = new DBConnectionPool(poolName, url, user,
????password, max, maxfree);
??pools.put(poolName, pool);
??log("成功創建連接池" + poolName);
?}
?/**
? * 讀取屬性完成初始化
? */
?private void init() {
??String logFile = this.getPact("logfile");
??try {
???log = new PrintWriter(new FileWriter(logFile, true), true);
??} catch (IOException e) {
???System.err.println("無法打開日志文件: " + logFile);
???log = new PrintWriter(System.err);
??}
??loadDrivers();
??createPools();
?}
?/**
? * 裝載和注冊所有JDBC驅動程序
? *
? * @param props
? *??????????? 屬性
? */
?private void loadDrivers() {
??String driverClasses = this.getPact("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);
????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 int maxfree;
??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, int maxfree) {
???this.name = name;
???this.URL = URL;
???this.user = user;
???this.password = password;
???this.maxConn = maxConn;
???this.maxfree = maxfree;
??}
??/**
?? * 將不再使用的連接返回給連接池
?? *
?? * @param con
?? *??????????? 客戶程序釋放的連接
?? */
??public synchronized void freeConnection(Connection con) {
???// 將指定連接加入到向量末尾
???if (freeConnections.size() >= maxfree) {
????try {
?????con.close();
?????log("關閉連接池" + name + "中的一個連接");
????} catch (SQLException e) {
?????log(e, "無法關閉連接池" + name + "中的連接");
?????freeConnections.addElement(con);
????}
???} else
????freeConnections.addElement(con);
???checkedOut--;
???notifyAll();
??}
??/**
?? * 從連接池獲得一個可用連接.如沒有空閑的連接且當前連接數小于最大連接 數限制,則創建新連接.如原來登記為可用的連接不再有效,則從向量刪除之,
?? * 然后遞歸調用自己以嘗試新的可用連接.
?? */
??public synchronized Connection getConnection() {
???Connection con = null;
???if (freeConnections.size() > 0) {
????// 獲取向量中第一個可用連接
????con = (Connection) freeConnections.firstElement();
????freeConnections.removeElementAt(0);
????try {
?????if (con.isClosed()) {
??????log("從連接池" + name + "刪除一個無效連接");
??????// 遞歸調用自己,嘗試再次獲取可用連接
??????con = getConnection();
?????}
?????// else log("從連接池" + name+"獲得一個空閑連接連接");
????} catch (SQLException e) {
?????log("從連接池" + name + "刪除一個無效連接");
?????// 遞歸調用自己,嘗試再次獲取可用連接
?????con = getConnection();
????}
???} else if (maxConn == 0 || checkedOut < maxConn) {
????con = newConnection();
???}
???if (con != null) {
????checkedOut++;
???}
???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();
??}
??/**
?? * 創建新的連接
?? */
??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;
??}
?}
?/**
? *
? * 讀取配置文件取
? *
? * @return 變量
? */
?private String getPact(String path) {
??String result = null;
??/*
?? *
?? * 填充代碼 讀取配置文件變量
?? *
?? */
??return result;
?}
?public static void main(String[] args) {
??DBConnectionManager db = DBConnectionManager.getInstance();
??Connection con = null;
??con = db.getConnection("idb");
??// }
?}
}