dbcp 连接数据库

import org.apache.commons.pool.ObjectPool;
import org.apache.commons.pool.impl.GenericObjectPool;
import org.apache.commons.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.PoolingDriver;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.DriverManagerConnectionFactory;
import java.sql.*;

public class DBConn {

    private Connection conn = null;
    private Statement stmt = null;
    private static String sqlJdbc = "";
    private static String sqlUser = "";
    private static String sqlPwd = "";
    private static Class driverClass=null;
    private static ObjectPool connectionPool=null;

    public DBConn() {
        if("".equals(sqlJdbc)){
          sqlJdbc = "jdbc:mysql://192.168.1.117:3306/test";
          //sqlserver: jdbc:microsoft:sqlserver://IP;DatabaseName=lordeDAS

          //oracle:    jdbc:oracle:thin:@ip:1521:myordb
        }
        if("".equals(sqlUser)){
          sqlUser = "root";
        }
        if("".equals(sqlPwd)){
          sqlPwd = "";
        }
        initDataSource();
    }
    
    public synchronized static void initDataSource(){
       if(driverClass==null){
       try {
    	   //oracle:    oracle.jdbc.driver.OracleDriver 
            //sqlserver: com.microsoft.jdbc.sqlserver.SQLServerDriver
           driverClass=Class.forName("org.gjt.mm.mysql.Driver");
         } catch (ClassNotFoundException e) {
             e.printStackTrace();
         }
       }

       if(connectionPool==null){
        try {
            setupDriver(sqlJdbc);
        } catch (Exception e) {
            e.printStackTrace();
        }
       }

        // Display some pool statistics
        try {
            printDriverStats();
        } catch (Exception e) {
            e.printStackTrace();
        }    
    }
    
    
    //获取一个连接
    private Connection getDbPollConnection(){
        try {
            conn = DriverManager.getConnection("jdbc:apache:commons:dbcp:auxdbpoll");
        } catch(SQLException e) {
            e.printStackTrace();
        }
        return conn;
     }
   
    // 获得记录集
     public ResultSet executeQuery(String SQL) {
           try {
               stmt=getDbPollConnection().createStatement();
              ResultSet rs = stmt.executeQuery(SQL);
              return rs;
           } catch (Exception e) {
               e.printStackTrace();
               return null;
           }
       }
     
     // 执行SQL
     public boolean execute(String SQL) {
         try {
             stmt=getDbPollConnection().createStatement();
             stmt.execute(SQL);
             return true;
         } catch (Exception e) {
             e.printStackTrace();
             return false;
         }finally{
           close();  
         }
     }
 
     // 执行SQL  
     public boolean executeUpdate(String SQL) {
         try {
             stmt=getDbPollConnection().createStatement();
             stmt.executeUpdate(SQL);
             return true;
         } catch (Exception e) {
             e.printStackTrace();
             return false;
         }finally{
           close();  
         }
     }
     
     public void close(){
        if(stmt!=null){
         try{
              stmt.close();
              stmt=null;
         }catch(Exception e){
               System.out.println(e);    
         }
        }
        if(conn!=null){
           try{
             conn.close();
             conn=null;
           }catch(Exception e){
             System.out.println(e);  
           }  
        }
     }
    
    public static void printDriverStats() throws Exception {
        PoolingDriver driver = (PoolingDriver) DriverManager.getDriver("jdbc:apache:commons:dbcp:");
        ObjectPool connectionPool = driver.getConnectionPool("auxdbpoll");
        System.out.println("活动的连接: " + connectionPool.getNumActive());
        System.out.println("空闲的连接: " + connectionPool.getNumIdle());
    }
    
    public static void shutdownDriver() throws Exception {
        PoolingDriver driver = (PoolingDriver) DriverManager.getDriver("jdbc:apache:commons:dbcp:");
        driver.closePool("auxdbpoll");
    }
    
    public static void setupDriver(String connectURI) throws Exception {
     System.out.println(connectURI);
        connectionPool = new GenericObjectPool(null);
        ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(connectURI,sqlUser,sqlPwd);
        PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(connectionFactory,connectionPool,null,null,false,true);
        Class.forName("org.apache.commons.dbcp.PoolingDriver");
        PoolingDriver driver = (PoolingDriver) DriverManager.getDriver("jdbc:apache:commons:dbcp:");
        driver.registerPool("auxdbpoll",connectionPool);
    }
    
    //测试
    public void testQuery(){
     String sql="select * from table_user";
        try {
     ResultSet rs=executeQuery(sql);
     while(rs.next()){
     System.out.println(rs.getString("user"));  
     }
   } catch (SQLException e) {
     // TODO Auto-generated catch block
     e.printStackTrace();
   }finally{
     close();  
   }
    }

    public static void main(String[] args){
     DBConn dbConn=new DBConn();
     dbConn.testQuery();
     
    }
}