/*
 * Created on 2005-5-11
 *
 */
package com.cn.db.sqlserver;
import java.sql.*;
import java.util.*;
    /**
     * <p>Title: 數據庫訪問類 </p>
     * <p>Description: 建立數據庫MSSQL連接，執行SQL命令</p>
     * <p>Copyright:fish Copyright (c)2004</p>
     * <p>Company:com.cn</p>
     * @author fish  jerryinside@Gmail.com
     * @version 1.0 
     */
public class DbFactory extends Thread{
	static private Connection conn = null;
	static private String url;
	static private String user;
	static private String password;
	private static ResourceBundle propretites = null;
	
	private static Stack pool=new Stack();//放連接池的堆?棧
	private static int  currentConnections=0;//當前連接的數?目
	private static int  maxConnections=2;//?zuyi  最大連接的數目
	private static long expiryTime=3600000;//過期時間設為1小時
	private static long maxConnectionsAttempts=50;//?最大重試次數?目
	private static long counterConnectionAttempts=0;//已重試次?數目
	private static long connectionWaitTimeout=10*1000;//連接等待時間
	
	//數據庫連接初始化
	static {
		propretites = ResourceBundle.getBundle("fish");
		String jdbcDriver = propretites.getString("DB.classForName");
		  String dbUrl = propretites.getString("DB.dataBase");
		  String dbUserName = propretites.getString("DB.userName");
		  String dbPassword = propretites.getString("DB.passWord");

		try {
			Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver")
					.newInstance();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		url = dbUrl;
		user = dbUserName;
		password = dbPassword;
	}
	
	
//	public static synchronized Connection getConnection() {
//		System.out.println("??始連接數據庫!");
//		try {
//			conn = DriverManager.getConnection(url, user, password);
//			System.out.println("數據庫連接成??");
//		} catch (SQLException e1) {
//			e1.printStackTrace();
//		}
//		return conn;
//	}
	
	
	 public static synchronized DBConnection getConnection() throws Exception//返回??個連??
	  {
	    DBConnection connection=null;//建力??個的DBConnection對象
	    if(pool.empty()&&currentConnections<maxConnections)//如果當前的堆棧為空并且當前的當前連接數小于最大連接數
	    {
	    	//System.out.println("新連?jkjie   接");
	      connection=getNewConnection();//調用getNewConnection方法已獲得一個新連接
	      //System.out.println("新的連接"+currentConnections);
	    }
	    else
	    {
	    	//System.out.println("老連?接");
	      connection=getPooledConnection();//調用getPooledConnection方法以便從堆棧中取出??個連??
	      //System.out.println("彈出連接");
	    }
	    return connection;//返回??個連接對??
	  }
	
	
	
	  private static synchronized DBConnection getPooledConnection() throws Exception//得到連接池的??個連??
	  {
	    DBConnection con=null;
	    for(int i=0;i<10;i++){
	    if(pool.empty())//如果堆棧為空
	    {
	        sleep(50);//休眠500毫秒
	        //wait(3600000);
	        //return popConnection(driver, url, uid, pwd);
	      }
	      else {
	        return popConnection();//返回堆棧中的??個連??
	      }
	    }throw new Exception(url + "連接超時");
	  }
	  
	  private static DBConnection getNewConnection() throws Exception//返回??個新的連??
	  {
	   // Class.forName(driver).newInstance();//裝載??
	    Connection con=DriverManager.getConnection(url, user, password);//獲得連接
	   currentConnections++;//當前連接數加1
	    return new DBConnection(con,url);//返回??個DBConnection對象
	  }

	  private static DBConnection popConnection() throws Exception//彈出??個連??
	  {
	    while(!pool.empty())//如果堆棧不為??
	    {
	      DBConnection con=(DBConnection)pool.pop();//從堆棧中彈出??個連??
	      if (isValid(con)) {//判斷連接是否合法
	        counterConnectionAttempts = 0;//如果合法把連接次數設置為0

	        return con;//返回堆棧中彈出的連接
	      }
	      else {
	        con.close();//如果連接不合??
	        currentConnections--;//當前連接數減1
	        counterConnectionAttempts = 0;//把連接次數設置為0
	        if (pool.empty()) {
		      //  System.out.println("old connect");
	          return getNewConnection();
	        }
	      }
	    }throw new Exception();
	  }

	  private static boolean isExpired(DBConnection con) throws Exception//判斷是否過期
	  {
	 
	    return(System.currentTimeMillis()-con.gettimestamp())>expiryTime;
	  }

	  private static boolean isValid(DBConnection con) throws Exception//判斷是否合法
	  {
//	    System.out.println((con.getConnection()!=null));
//	    System.out.println(!con.getConnection().isClosed());
//	    System.out.println(!isExpired(con));
	  	return(con.getConnection()!=null&&!con.getConnection().isClosed()&&!isExpired(con));
	  }

	  public static synchronized void release(DBConnection con) throws Exception//將連接放回連接池
	  {
	    if(isValid(con))
	    {
	    	pool.push(con);
//	      System.out.println("當前連接??"+currentConnections);
//	      System.out.println("連接池連接數"+pool.size());
	    //notify();
	    }
	    else
	    {
	        con.close();
	       currentConnections--;
//	        System.out.println("當前連接??"+currentConnections);
//	        System.out.println("連接池連接數"+com.choice.common.Connections.pool.size());
	    }
	  }
	  /*protected void finalize() throws Throwable//關閉??有連??
	  {
	    shutdown();
	  }*/

	  protected void shutdown()//關閉??有數據庫的連??
	  {
	    if(pool!=null)
	    {
	      while(!pool.isEmpty())
	      {
	        ((DBConnection)pool.pop()).close();
	      }
	    }
	  }
	

	
	
	//執行?SQL語句，并返回是否成功
	public static boolean execute(String sql) {
		Connection cnn = null;
		DBConnection dbcnn=null;
		Statement stmt = null;
		try {
			dbcnn=getConnection();
			cnn = dbcnn.getConnection();
			stmt = cnn.createStatement();
			stmt.execute(sql);
			return true;
		} catch (Exception ex) {
			return false;
		} finally {
			try {
				if (stmt != null)
					stmt.close();
				stmt = null;
			} catch (Exception ex) {
			}
//			try {
//				if (cnn != null)
//					cnn.close();
//				cnn = null;
//			} catch (Exception ex) {
//			}
			
		    try {
			    //  System.out.println("進棧");
			      release(dbcnn);
			    }
			    catch (Exception ex) {
			      ex.printStackTrace();
			    }
		}
	}
	

	//執行批量SQL
	public static boolean batchExcute(String[] sql){
        for(int i=0;i<sql.length;i++){
          if(!execute(sql[i])){
          	System.out.println(i);
          	return false;
          	}
        }
        return true;
    }
	
	
	
	//執行SQL語句，將結果存入hashtable表中
	public static Hashtable[] getHashQuery(String sql) {
		Connection cnn = null;
		DBConnection dbcnn=null;
		Vector vetRet = new Vector();
		String fieldName;
		String fieldValue;
		Statement stmt = null;
		ResultSet rst = null;
		try {
			dbcnn=getConnection();
			cnn = dbcnn.getConnection();
			stmt = cnn.createStatement();
			rst = stmt.executeQuery(sql);
			ResultSetMetaData resultMeta = rst.getMetaData();
			int columns = resultMeta.getColumnCount();
			Hashtable record = new Hashtable();
			int i = 0;
			int rows = 0;
			while (rst.next()) {
				record.clear();
				for (i = 1; i <= columns; i++) {
					fieldName = new String(resultMeta.getColumnLabel(i));
        			fieldName=fieldName.toUpperCase();
					String temp=rst.getString(fieldName);
					fieldValue = temp == null ? "" :temp;
					record.put(fieldName, fieldValue);
				}
				vetRet.addElement(record.clone());
				rows++;
			}
			if (rows == 0) {
				return null;
			} else {
				Hashtable[] hashRet = new Hashtable[vetRet.size()];
				vetRet.copyInto(hashRet);
				return hashRet;
			}
		} catch (Exception ex) {
			ex.printStackTrace();
			return null;
		} finally {
			try {
				if (rst != null)
					rst.close();
				rst = null;
			} catch (Exception ex) {
			}
			try {
				if (stmt != null)
					stmt.close();
				stmt = null;
			} catch (Exception ex) {
			}
//			try {
//				if (cnn != null)
//					cnn.close();
//				cnn = null;
//			} catch (Exception ex) {
//			}


		    try {
		//	      System.out.println("進棧");
			      release(dbcnn);
			    }
			    catch (Exception ex) {
			      ex.printStackTrace();
			    }
		}
	}
	

}