dbwrapper.java

j2ee 数据连接池的源码

package com.ap.framework.dbw;

import java.sql.*;
import java.util.List;
import java.util.ArrayList;

import com.ap.framework.dbw.dbc.CommDataSourcePara;
import com.ap.framework.dbw.dbc.DataSourceLocation;
import com.ap.framework.bean.BeanFactory;
import com.ap.framework.util.BasicParaConfig;
import org.apache.commons.dbcp.*;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

public class DbWrapper {
	
	private final Log logger = LogFactory.getLog(getClass());
	
	private Connection conn;
	private int totalElements = 0;
	
	private DbWrapper() {
		conn = null;
		totalElements = 0;
	}
    
    public static DbWrapper newInstance()  {
        return new DbWrapper();
    }
    
    /**
     * query tolal
     */
    public List executeQuery(String sql, ArrayList vector) throws Exception
    {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
    	
        try {
            pstmt = conn.prepareStatement(sql);
            if (vector != null) {
                for (int k = 0; k < vector.size(); k++)
                    pstmt.setObject(k + 1, vector.get(k));
            }
            rs = pstmt.executeQuery();

            ArrayList vList = new ArrayList();
            ResultSetMetaData aData = rs.getMetaData();
            int column = aData.getColumnCount();
            //column--;

            while (rs.next()) {
                Object[] objs = new Object[column];
                for (int k=0;k<column;k++)  {
                    objs[k] = rs.getObject(k+1);
                }
                vList.add(objs);
            }

           return vList;

        }
        catch (SQLException e) {
            throw new Exception("executeQuery()");
        }
        finally {
            if (rs != null) rs.close();
            if (pstmt != null) pstmt.close();
        }

    }

    public int getRecordCount()  {
        return this.totalElements;
    }
    
    //sum(record)
    private int setRecordeCount(String sql, ArrayList vector) throws Exception
    {
        int result = 0;
        PreparedStatement bps = null;
        ResultSet brs = null;
        try
        {
            if (sql.toUpperCase().lastIndexOf("ORDER BY") > 0)
                sql = sql.substring(0, sql.toUpperCase().lastIndexOf("ORDER BY"));
            sql = "select count(*) from (" + sql + " )";

            bps = conn.prepareStatement(sql);

            if (vector != null)
                for (int k=0; k<vector.size(); k++)  {
                    bps.setObject(k + 1, vector.get(k));
                }

            brs = bps.executeQuery();

            while (brs.next()) {
                result = Integer.parseInt(brs.getObject(1)+"");
            }
            this.totalElements = result;
            return result;
        }
        catch(SQLException e)  {
            throw new Exception("setRecordeCount()");
        }
        finally {
            if (brs!=null) brs.close();
            if (bps!=null) bps.close();
        }
    }
    
    public List executeQuery(String sql, int pageNo, int pageSize, ArrayList vector) throws Exception
    {
    	return executeQuery(sql, pageNo, pageNo+1, pageSize, vector);
    }
    
    /**
     * page query, Export Excel
     */
    public List executeQuery(String sql, int beginPageNo, int endPageNo, 
    		                           int pageSize, ArrayList vector) throws Exception
    {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
    	
        try {
            int recordCount = setRecordeCount(sql,vector);
            if (recordCount<=0) return new ArrayList();
            
            int fmR = (beginPageNo-1) * pageSize;
            int toR = (endPageNo-1) * pageSize;
            sql = "select * from  ( select  x.*, rownum b from ( " + sql + " ) x  ) where  b <= " + String.valueOf(toR) + " and  b > " + String.valueOf(fmR);
            pstmt = conn.prepareStatement(sql);
            if (vector != null) {
                for (int k = 0; k < vector.size(); k++)
                    pstmt.setObject(k + 1, vector.get(k));
            }
            rs = pstmt.executeQuery();

            ArrayList vList = new ArrayList();
            ResultSetMetaData aData = rs.getMetaData();
            int column = aData.getColumnCount();
            column--;

            while (rs.next()) {
                Object[] objs = new Object[column];
                for (int k=0;k<column;k++)  {
                    objs[k] = rs.getObject(k+1);
                }
                vList.add(objs);
            }

           return vList;

        }
        catch (SQLException e)  {
            throw new Exception("executeQuery()");
        }
        finally  {
            if (rs != null) rs.close();
            if (pstmt != null) pstmt.close();
        }

    } 
    
    public int executeUpdate(String sql, ArrayList vector) throws Exception
    {
        PreparedStatement pstmt = null;
        try  {
            pstmt = conn.prepareStatement(sql.toString());
            if (vector != null) {
                for (int k = 0; k < vector.size(); k++)
                    pstmt.setObject(k + 1, vector.get(k));
            } 
            return pstmt.executeUpdate();
        }
        catch (Exception e) {
            throw new Exception(sql,e);
        }
        finally {
            if (pstmt != null) pstmt.close();
        }

    }

	public void openConnection() throws Exception {
	    Connection conn = null;
    	try  {
    		//which database connect manner
            BasicParaConfig paraConfig = (BasicParaConfig)BeanFactory.getBean("basicapp_config");
    		String connManner = paraConfig.getWhichDbConnect();
            if (connManner.equals("commdatasource")) {
            	CommDataSourcePara dsl = (CommDataSourcePara)BeanFactory.getBean("commdatasource");
        		String driverClass = dsl.getDriverClass();
        		String url = dsl.getUrl();
        		String userName = dsl.getUserName();
        		String passWord = dsl.getPassWord();
        		 
        		Class.forName(driverClass).newInstance();
        		conn = DriverManager.getConnection(url,userName,passWord);          	 
            }
            if (connManner.equals("dbcpdatasource")) { 
         	    BasicDataSource bdc = (BasicDataSource)BeanFactory.getBean("dbcpdatasource");
         		conn = bdc.getConnection();           	 
            }
            if (connManner.equals("espooldatasource")) { //especial pool
         	    DataSourceLocation bdc = (DataSourceLocation)BeanFactory.getBean("espooldatasource");
         		conn = bdc.getDataSource().getConnection();       	 
            }
        }
    	catch (Exception e)  {
    		logger.error(e);
    		throw new Exception(e);
    	}	
    	this.conn = conn; 
    	conn.setAutoCommit(false); //none auto commit
	}
	
	public void setConnection(Connection conn) {
		this.conn = conn;
	}
	
    public Connection getConnection() {
        try {
        } catch (Exception e) {
        	logger.error(e);
        }
        return this.conn;
    }
    
    public void closeConnection() {
        try {
            if (conn != null) conn.close();
        } catch (SQLException e) {
        	logger.error(e);
        }
    }
    
    public void beginTransaction() {
        try { 
        	conn.setAutoCommit(false);
        }
        catch (SQLException e) {
        	logger.error(e);
        }
    }
    
    public void commit() {
        try { 
            conn.commit();
        }
        catch (SQLException e) {
        	logger.error(e);
        }
    }

    public void rollback() {
        try {
            conn.rollback();
        } catch (SQLException e) {
        	logger.error(e);
        }
    }
    
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub

		try {
			DbWrapper dbw = new DbWrapper();
			dbw.openConnection();
            Connection conn = dbw.getConnection();
            if (conn!=null) {
				System.out.println("open");
				
				ArrayList vector = new ArrayList();
				
				List list = dbw.executeQuery("select CODE from SELF_TERMINAL",vector);
				System.out.println("list:"+list.size()); 
				conn.close();		
            }



			
			//BeanFactory.destroy();
			
			//BasicDataSource ds = (BasicDataSource)BeanFactory.getBean("dataSource"); 
			//System.out.println("a:"+ds.getMaxActive()+":"+ds.getMaxIdle()+":"+ds.getMaxOpenPreparedStatements());
			//ds.close();  
			
			//System.out.println("a1:"+ds.getMaxActive()+":"+ds.getMaxIdle()+":"+ds.getNumActive());
			
			//BasicDataSource ds = new BasicDataSource();
			//ds.setDriverClassName("oracle.jdbc.driver.OracleDriver");
			//ds.setUsername("ubss_db7");
			//ds.setPassword("ubss_db7");
			//ds.setUrl("jdbc:oracle:thin:@192.168.0.25:1521:test3");
			
//			Connection conn = ds.getConnection(); 
//			System.out.println("a:"+ds.getMaxActive()+":"+ds.getMaxIdle());
//			dbw.conn = conn;
//			Vector vector = new Vector();
//			List list = dbw.executeQuery("select * from campaign_plan",vector);
//			System.out.println("b:"+ds.getMaxActive()+":"+ds.getMaxIdle());
//			System.out.println("list:"+list.size()); 
//			conn.close();
//			System.out.println("c:"+ds.getMaxActive()+":"+ds.getMaxIdle());
//			ds.close();
			
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		finally {

		}



	}

}