Database Connection Pool Code Example with Apache DBCP and Oracle 10g

1) Download the Apache Commons DBCP and Commons Pool jars from the link here and add them to the classpath / build path of the Java Application / Project.

2) Add the jboss-j2ee.jar file to the class path/build path of the Java Application/Project. The jboss-j2ee.jar file is present in the $JBOSS_HOME/server/default/lib folder of the JBoss Installation folder.

3) Complete the JDBC example at the link here

4) Create a java class - ConnectionPoolExample.java - in the src folder of the Java Application / Project

package info.icontraining.jdbc;

import java.sql.*;
import javax.sql.*;
import org.apache.commons.pool.*;
import org.apache.commons.pool.impl.*;
import org.apache.commons.dbcp.*;

public class ConnectionPoolExample {
 
   private static GenericObjectPool genericPool = new GenericObjectPool();

   public static void main(String[] args) throws Exception {

      try {
         Class.forName("oracle.jdbc.OracleDriver");
      } catch (ClassNotFoundException e) {
         e.printStackTrace();
      }

      DataSource dataSource = setupDataSource("jdbc:oracle:thin:system/system@localhost:1521:xe");

      Connection conn = null;
      Statement stmt = null;
      ResultSet rs = null;

      try {
         conn = dataSource.getConnection();
         stmt = conn.createStatement();
         rs = stmt.executeQuery("SELECT * FROM DUAL");
            
         while(rs.next()) {
            for(int i=1; i<=rs.getMetaData().getColumnCount(); i++) {
               System.out.println(rs.getString(i));
            }
         }
            
         System.out.println("Active Connections: " + genericPool.getNumActive() + ", Idle Connections: " + genericPool.getNumIdle());
            
      } catch(SQLException e) {
         e.printStackTrace();
      } finally {
         try { if (rs != null) rs.close(); } catch(Exception e) { }
         try { if (stmt != null) stmt.close(); } catch(Exception e) { }
         try { if (conn != null) conn.close(); } catch(Exception e) { }
      }
        
      System.out.println("Active Connections: " + genericPool.getNumActive() + ", Idle Connections: " + genericPool.getNumIdle());
   }

   public static DataSource setupDataSource(String connectURI) throws Exception {
      ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(connectURI,null);
      KeyedObjectPoolFactory kopf =new GenericKeyedObjectPoolFactory(null);

      PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory( connectionFactory, genericPool, kopf, null, false, true);

      for(int i = 0; i < 5; i++) {
         genericPool.addObject();
      }

      System.out.println("Active Connections: " + genericPool.getNumActive() + ", Idle Connections: " + genericPool.getNumIdle());
        
      PoolingDataSource dataSource = new PoolingDataSource(genericPool);
      return dataSource;
   }
}

5) Run the code as a standalone Java Application