dbfactory.java

手写的连接池类,实现了数据库的连接池,提高了操作数据库的效率.

/*
 * 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  [e-mail]
     * @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();
			    }
		}
	}
	

}