dbfactory.java

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

package com.cn.db.oracle;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Hashtable;
import java.util.Vector;

import oracle.jdbc.pool.OracleConnectionCacheImpl;
import oracle.jdbc.pool.OracleConnectionPoolDataSource;

import com.cn.res.LoadFileOption;

/**
 * <p>Title: 数据库访问类 </p>
 * <p>Description: 建立数据库ORACLE连接，执行SQL命令</p>
 * <p>Copyright:fish Copyright (c)2005</p>
 * <p>Company: MyChoice.cn</p>
 * @author fish  [e-mail]
 * @version 1.0 
 */
public class DbFactory {   
    private static String db_url;
    private static String db_user;
    private static String db_pwd;
    private static int min_connections;
    private static int max_connections;
    private static int max_trytimes; 
    private static OracleConnectionCacheImpl occi; 
    private static OracleConnectionPoolDataSource ocpds; 

    //private static Logger logger = MyLogger.getMyLogger();
    
    private static Connection connection=null;

    /**
     * 加载默认?? 创建连接??
     */
    static{
    	LoadFileOption res= new LoadFileOption();
        db_url = res.getPropsValue("db_url");
        db_user = res.getPropsValue("db_user");
        db_pwd = res.getPropsValue("db_pwd");
        min_connections = Integer.parseInt(res.getPropsValue("min_connections"));
        max_connections = Integer.parseInt(res.getPropsValue("max_connections"));
        max_trytimes = Integer.parseInt(res.getPropsValue("max_trytimes"));
        int iTryTimes = 0;
        //logger.info("Creating Connection Pool......");
        while(iTryTimes < max_trytimes){
            iTryTimes++;
            try{
                ocpds = new OracleConnectionPoolDataSource();
                ocpds.setURL(db_url);
                ocpds.setUser(db_user);
                ocpds.setPassword(db_pwd);
                occi = new OracleConnectionCacheImpl(ocpds);
                occi.setMinLimit(min_connections);
                occi.setMaxLimit(max_connections);
                occi.setCacheScheme(OracleConnectionCacheImpl.FIXED_WAIT_SCHEME);
                //logger.info("trytime: "+iTryTimes+"- activesize: "+occi.getActiveSize()+"- cachesize:"+occi.getCacheSize());
                //logger.info("Connection Pool Created??");
                break;
            }catch(Exception ex){
                //logger.error("Connection Pool is not Created",ex);
            }
        }
    }

    /**
     * 创建数据库连??
     * @return 连接
     */
    public static synchronized Connection getConnection() {
        try{
            return occi.getConnection();
        }catch(Exception ex){
            //logger.error("get connection error! "+ex);
            return null;
        }
    }

    /**
     * 执行SQL语句
     * @param sql
     * @return 成功与否
     */
    public static boolean execute(String sql){
        //logger.debug("execute : "+sql);
        Connection cnn = null;
        Statement stmt = null;
        try{
            cnn = getConnection();
            stmt = cnn.createStatement();
            stmt.execute(sql);
            return true;
        }catch(Exception ex){
            //logger.error("Excute SQL failed! sql = "+sql,ex);
            return false;
        }finally{
            try{cnn.rollback();}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){}
        }
    }

    /**
     * 将纪录集存放入hashtable数组返回
     * @param sql
     * @return Hashtable[]
     */
    public static Hashtable[] getHashQuery(String sql){
        //logger.debug("getHashQuery : "+sql);
        Vector vetRet=new Vector();
        String fieldName;
        String fieldValue;
        Connection cnn = null;
        Statement stmt = null;
        ResultSet rst = null;
        try{
            cnn = 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));
                    fieldValue = rst.getString(fieldName)==null?"":rst.getString(fieldName).toString();
                    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){
           // logger.error("Query and put ResultSet into hashtabel[] failed ! sql = "+sql,ex);
            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){}
        }
    }


    /**
     * 批量执行SQL语句的事务处??
     * @param sql
     * @return 成功与否
     */
    public static boolean batchExcute(String[] sql){
        Connection cnn = null;
        PreparedStatement pstmt = null;
        try{
            cnn = getConnection();
            cnn.setAutoCommit(false);
            for(int i=0;i<sql.length;i++) {
                //logger.debug("batch execute sql "+i+" : "+sql[i]);
                pstmt = cnn.prepareStatement(sql[i]);
                pstmt.executeUpdate();
            }
            cnn.commit();
            cnn.setAutoCommit(true);
            return true;
        }catch(Exception ex) {
            try{cnn.rollback();cnn.setAutoCommit(true);}catch(Exception e){}
            //logger.error("Batch execute SQL failed! ",ex);
            return false;
        }finally{
            try{if(pstmt != null) pstmt.close();pstmt = null; }catch(Exception ex){}
            try{if(cnn != null) cnn.setAutoCommit(true); cnn.close();cnn = null; }catch(Exception ex){}
        }
    }

    /**
     * 释放连接??
     */
    public static void destroy() {
       try{
           if (occi != null)
               occi.close();
           occi = null;
       }catch (Exception ex) {
           //logger.error(ex);
       }
    }

    protected static void setUp() {
    }

    protected static void tearDown() {
    }

    public static void closeAll(){
        try {
           occi.close();
        }
        catch (SQLException ex) {
        }
    }
    
    
    
	/**
	 *调用存储过程,无返回???
	 *@param procName 存储过程字传
	 *@throws SQLException 调用时需要抛出改异常
	 *@since 0.1
	 */	
	 
    public static CallableStatement CallProc(String procName) throws SQLException
	{
	    String callProc="{Call "+procName.trim()+"}";
	    try{ 
		    connection = getConnection();
		    return connection.prepareCall(callProc);
	    	}catch(Exception ex){
	    		//logger.error("CallableStatement failed! ",ex);
	    		try{if(connection != null) connection.close();connection = null; }catch(Exception ex11){}
	    		return null;
	    	}finally{
	            
	        }
	
	}
	
	/**
	 *调用存储过程,获得系统返回
	 *@param procName 存储过程字传
	 *@throws SQLException 调用时需要抛出改异常
	 *@since 0.1 
	 */		
	public static CallableStatement callProcRet(String procName) throws SQLException 
	{
		String callProc="{?=Call "+procName.trim()+"}";
		try{ 
		    connection = getConnection();
		    return connection.prepareCall(callProc);
	    	}catch(Exception ex){
	    		//logger.error("CallableStatement failed!! ",ex);
	    		 try{if(connection != null)  connection.close();connection = null; }catch(Exception ex1){}
	    		return null;
	    	}finally{
	           
	        }
	}
	
	 public static void closeConnection(){
	 	try{if(connection != null)  connection.close();connection = null; }catch(Exception ex1){}
    }
    
	
	
}