DBCP串連池的最簡單應用(用於Oracle資料庫)

鑒於有人問起DBCP直接用於JDBC串連的問題，我做了一個最簡單的樣本。所有資源來源於網上。它不需要什麼Web容器，就是一簡單的控制台應用。

資源：
http://apache.etoak.com//commons/pool/binaries/commons-pool-1.5.6-bin.zip
http://labs.renren.com/apache-mirror//commons/dbcp/binaries/commons-dbcp-1.4-bin.zip
http://download.java.net/maven/1/javaee/jars/javaee-api-5.jar
當然，還有Oracle jdbc要用的ojdbc14.jar (適用於oracle9i及以上版本)

工程檔案：放到這裡了。http://dl.iteye.com/topics/download/210279f0-f752-37a6-969f-d58ba13cc394

資料庫連接資訊：
jdbc:oracle:thin:scott/tiger@sean-m700:1521:ora92
sean-m700是主機名稱，ora92是oracle資料庫的instance ID. 我手頭的機器上沒有安裝oracle資料庫，用的是很早以前的一個oracle9.2的拷貝，重新安裝執行個體和相應服務得來的。

源碼如下：借化獻佛，源碼也是從網上得來的。(http://svn.apache.org/viewvc/commons/proper/dbcp/trunk/doc/BasicDataSourceExample.java?revision=1100136&view=markup)

/* 
// 
33  // Here's a simple example of how to use the BasicDataSource. 
34  // 
35   
36  // 
37  // Note that this example is very similiar to the PoolingDriver 
38  // example. 
39   
40  // 
41  // To compile this example, you'll want: 
42  //  * commons-pool-1.5.6.jar 
43  //  * commons-dbcp-1.3.jar (JDK 1.4-1.5) or commons-dbcp-1.4 (JDK 1.6+) 
44  //  * j2ee.jar (for the javax.sql classes) 
45  // in your classpath. 
46  // 
47  // To run this example, you'll want: 
48  //  * commons-pool-1.5.6.jar 
49  //  * commons-dbcp-1.3.jar (JDK 1.4-1.5) or commons-dbcp-1.4 (JDK 1.6+) 
50  //  * j2ee.jar (for the javax.sql classes) 
51  //  * the classes for your (underlying) JDBC driver 
52  // in your classpath. 
53  // 
54  // Invoke the class using two arguments: 
55  //  * the connect string for your underlying JDBC driver 
56  //  * the query you'd like to execute 
57  // You'll also want to ensure your underlying JDBC driver 
58  // is registered.  You can use the "jdbc.drivers" 
59  // property to do this. 
60  // 
61  // For example: 
62  //  java -Djdbc.drivers=oracle.jdbc.driver.OracleDriver \ 
63  //      -classpath commons-pool-1.5.6.jar:commons-dbcp-1.4.jar:j2ee.jar:oracle-jdbc.jar:. \ 
64  //      PoolingDataSourceExample 
65  //      "jdbc:oracle:thin:scott/tiger@myhost:1521:mysid" 
66  //      "SELECT * FROM DUAL" 
*/   
/* 
The Oracle connection URL for the thin client-side driver ojdbc14.jar has the following format: 
jdbc:oracle:thin:[user/password]@[host][:port]:SID 
jdbc:oracle:thin:[user/password]@//[host][:port]/SID 
 
  user - The login user name defined in the Oracle server. 
 
  password - The password for the login user. 
 
  host - The host name where Oracle server is running. 
        Default is 127.0.0.1 - the IP address of localhost. 
 
  port - The port number where Oracle is listening for connection. 
        Default is 1521. 
 
  SID  - System ID of the Oracle server database instance. 
        SID is a required value. By default, Oracle Database 10g Express 
        Edition creates one database instance called XE. 
*/   
   
import org.apache.commons.dbcp.BasicDataSource;   
import javax.sql.*;   
import java.sql.*;   
   
public class TestDataSource   
{   
   
    /** 
    * @param args 
    */   
    public static void main(String[] args)   
    {   
        System.out.println("Setting up data source.");   
        String url = "jdbc:oracle:thin:scott/tiger@sean-m700:1521:ora92";   
        DataSource dataSource = setupDataSource(url);   
        System.out.println("Done...");   
   
        // Now, we can use JDBC DataSource as we normally would.   
        //   
        Connection conn = null;   
        Statement stmt = null;   
        ResultSet rset = null;   
   
        try {   
            System.out.println("Creating connection.");   
            conn = dataSource.getConnection();   
            System.out.println("Creating statement.");   
            stmt = conn.createStatement();   
            System.out.println("Executing statement.");   
            rset = stmt.executeQuery("select 1 from DUAL");   
            System.out.println("Results:");   
            int numcols = rset.getMetaData().getColumnCount();   
            while(rset.next()) {   
                for(int i=1;i<=numcols;i++) {   
                    System.out.print("\t" + rset.getString(i));   
                }   
                System.out.println("");   
            }   
        } catch(SQLException e) {   
            e.printStackTrace();   
        } finally {   
            try { if (rset != null) rset.close(); } catch(Exception e) { }   
            try { if (stmt != null) stmt.close(); } catch(Exception e) { }   
            try { if (conn != null) conn.close(); } catch(Exception e) { }   
        }   
    }   
   
    public static DataSource setupDataSource(String connectURI) {   
        BasicDataSource ds = new BasicDataSource();   
        ds.setDriverClassName("oracle.jdbc.driver.OracleDriver");   
        ds.setUsername("scott");   
        ds.setPassword("tiger");   
        ds.setUrl(connectURI);   
        return ds;   
    }   
   
    public static void printDataSourceStats(DataSource ds) {   
        BasicDataSource bds = (BasicDataSource) ds;   
        System.out.println("NumActive: " + bds.getNumActive());   
        System.out.println("NumIdle: " + bds.getNumIdle());   
    }   
   
    public static void shutdownDataSource(DataSource ds) throws SQLException {   
        BasicDataSource bds = (BasicDataSource) ds;   
        bds.close();   
    }   
   
}   

    /*
    //
    33  // Here's a simple example of how to use the BasicDataSource.
    34  //
    35 
    36  //
    37  // Note that this example is very similiar to the PoolingDriver
    38  // example.
    39 
    40  //
    41  // To compile this example, you'll want:
    42  //  * commons-pool-1.5.6.jar
    43  //  * commons-dbcp-1.3.jar (JDK 1.4-1.5) or commons-dbcp-1.4 (JDK 1.6+)
    44  //  * j2ee.jar (for the javax.sql classes)
    45  // in your classpath.
    46  //
    47  // To run this example, you'll want:
    48  //  * commons-pool-1.5.6.jar
    49  //  * commons-dbcp-1.3.jar (JDK 1.4-1.5) or commons-dbcp-1.4 (JDK 1.6+)
    50  //  * j2ee.jar (for the javax.sql classes)
    51  //  * the classes for your (underlying) JDBC driver
    52  // in your classpath.
    53  //
    54  // Invoke the class using two arguments:
    55  //  * the connect string for your underlying JDBC driver
    56  //  * the query you'd like to execute
    57  // You'll also want to ensure your underlying JDBC driver
    58  // is registered.  You can use the "jdbc.drivers"
    59  // property to do this.
    60  //
    61  // For example:
    62  //  java -Djdbc.drivers=oracle.jdbc.driver.OracleDriver \
    63  //      -classpath commons-pool-1.5.6.jar:commons-dbcp-1.4.jar:j2ee.jar:oracle-jdbc.jar:. \
    64  //      PoolingDataSourceExample
    65  //      "jdbc:oracle:thin:scott/tiger@myhost:1521:mysid"
    66  //      "SELECT * FROM DUAL"
    */ 
    /*
    The Oracle connection URL for the thin client-side driver ojdbc14.jar has the following format:
    jdbc:oracle:thin:[user/password]@[host][:port]:SID
    jdbc:oracle:thin:[user/password]@//[host][:port]/SID
   
      user - The login user name defined in the Oracle server.
   
      password - The password for the login user.
   
      host - The host name where Oracle server is running. 
            Default is 127.0.0.1 - the IP address of localhost.
   
      port - The port number where Oracle is listening for connection.
            Default is 1521.
   
      SID  - System ID of the Oracle server database instance. 
            SID is a required value. By default, Oracle Database 10g Express 
            Edition creates one database instance called XE.
    */ 
     
    import org.apache.commons.dbcp.BasicDataSource; 
    import javax.sql.*; 
    import java.sql.*; 
     
    public class TestDataSource 
    { 
     
        /**
        * @param args
        */ 
        public static void main(String[] args) 
        { 
            System.out.println("Setting up data source."); 
            String url = "jdbc:oracle:thin:scott/tiger@sean-m700:1521:ora92"; 
            DataSource dataSource = setupDataSource(url); 
            System.out.println("Done..."); 
     
            // Now, we can use JDBC DataSource as we normally would. 
            // 
            Connection conn = null; 
            Statement stmt = null; 
            ResultSet rset = null; 
     
            try { 
                System.out.println("Creating connection."); 
                conn = dataSource.getConnection(); 
                System.out.println("Creating statement."); 
                stmt = conn.createStatement(); 
                System.out.println("Executing statement."); 
                rset = stmt.executeQuery("select 1 from DUAL"); 
                System.out.println("Results:"); 
                int numcols = rset.getMetaData().getColumnCount(); 
                while(rset.next()) { 
                    for(int i=1;i<=numcols;i++) { 
                        System.out.print("\t" + rset.getString(i)); 
                    } 
                    System.out.println(""); 
                } 
            } catch(SQLException e) { 
                e.printStackTrace(); 
            } finally { 
                try { if (rset != null) rset.close(); } catch(Exception e) { } 
                try { if (stmt != null) stmt.close(); } catch(Exception e) { } 
                try { if (conn != null) conn.close(); } catch(Exception e) { } 
            } 
        } 
     
        public static DataSource setupDataSource(String connectURI) { 
            BasicDataSource ds = new BasicDataSource(); 
            ds.setDriverClassName("oracle.jdbc.driver.OracleDriver"); 
            ds.setUsername("scott"); 
            ds.setPassword("tiger"); 
            ds.setUrl(connectURI); 
            return ds; 
        } 
     
        public static void printDataSourceStats(DataSource ds) { 
            BasicDataSource bds = (BasicDataSource) ds; 
            System.out.println("NumActive: " + bds.getNumActive()); 
            System.out.println("NumIdle: " + bds.getNumIdle()); 
        } 
     
        public static void shutdownDataSource(DataSource ds) throws SQLException { 
            BasicDataSource bds = (BasicDataSource) ds; 
            bds.close(); 
        } 
     
    } 
不過，需要說明的是，DBCP串連池是幾個開源串連池裡最不適合用於生產環境的，經常會出現死串連現象。 而cp30和proxool都是不錯的選擇。DBCP用於測評開發環境，還是比較便利的。