Share this page 

Connect to Oracle using a connection poolTag(s): JDBC


The Oracle thin jdbc driver (v9.2) makes it very easy to use a connection pool, it's all built in the OracleDataSource with the implicit connection cache mechanism.

An application turns the implicit connection cache on by invoking OracleDataSource.setConnectionCachingEnabled(true). After implicit caching is turned on, the first connection request to the OracleDataSource transparently creates a connection cache.

After you have turned connection caching on, whenever you retrieve a connection through an OracleDataSource.getConnection(), the JDBC drivers check to see if a connection is available in the cache.The getConnection() method checks if there are any free physical connections in the cache that match the specified criteria. If a match is found, a logical connection is returned wrapping the physical connection. If no physical connection match is found, a new physical connection is created, wrapped in a logical connection, and returned.

A Connection Pool is a cache of database connections maintained in memory so that the connections can be reused when the database receives future requests for data. Connection pools are used to enhance the performance of executing commands on a database. Various parameters such as number of minimum connections, maximum connections and idle connections can be set to make sure the connection pool works well according to the environment it is deployed to work in.

In this example, we have a program with several threads. Each thread makes a connection but the login process is done only once.

First a static class to manage the connection pool. 

import oracle.jdbc.pool.OracleDataSource;
import oracle.jdbc.pool.OracleConnectionCacheManager;

import java.util.Properties;
import java.sql.*;

public class JDBCUtils {
    private final  static String CACHE_NAME = "MYCACHE";
    private  static OracleDataSource ods = null;

        static {
            System.out.println("OracleDataSource Initialization");
            try {
                ods = new OracleDataSource();
                ods.setURL("jdbc:oracle:thin:@//server.local:1521/prod");
                ods.setUser("scott");
                ods.setPassword("tiger");
                // caching parms
                ods.setConnectionCachingEnabled(true);
                ods.setConnectionCacheName(CACHE_NAME);
                Properties cacheProps = new Properties();
                cacheProps.setProperty("MinLimit", "1");
                cacheProps.setProperty("MaxLimit", "4");
                cacheProps.setProperty("InitialLimit", "1");
                cacheProps.setProperty("ConnectionWaitTimeout", "5");
                cacheProps.setProperty("ValidateConnection", "true");

                ods.setConnectionCacheProperties(cacheProps);

            }
            catch (SQLException e) {
                e.printStackTrace();
            }
        }

    /**
     * private constructor for static class
     */
    private JDBCUtils() { }

    public static Connection getConnection() throws SQLException {
      return getConnection("env. unspecified");
    }


    public static Connection getConnection(String env)
       throws SQLException
    {
      System.out.println("Request connection for " + env);
      if (ods == null) {
          throw new SQLException("OracleDataSource is null.");
      }
      return ods.getConnection();
    }

    public static void closePooledConnections() throws SQLException{
      if (ods != null ) {
          ods.close();
      }
    }

    public static void listCacheInfos() throws SQLException{
      OracleConnectionCacheManager occm =
          OracleConnectionCacheManager.getConnectionCacheManagerInstance();
      System.out.println
          (occm.getNumberOfAvailableConnections(CACHE_NAME)
              + " connections are available in cache " + CACHE_NAME);
      System.out.println
          (occm.getNumberOfActiveConnections(CACHE_NAME)
              + " connections are active");

    }
 }
then the working thread 
import java.sql.*;

public class TestDBOraclePool3Thread implements Runnable {

    private int noThread = 0;

    TestDBOraclePool3Thread(int n) {
        noThread = n;
    }

    public void run() {
        System.out.println("Starting Thread " + noThread);
        while (true) {
            try {
                Connection conn =
                   JDBCUtils.getConnection("env " + noThread);

                conn.setAutoCommit(false);
                Statement stmt = conn.createStatement();
                ResultSet rset =
                   stmt.executeQuery("select BANNER from SYS.V_$VERSION");
                while (rset.next())
                   System.out.println (rset.getString(1));
                stmt.close();
                System.out.println ("Ok.");
                JDBCUtils.listCacheInfos();
                conn.close();

            }
            catch (SQLException e) {
                e.printStatckTrace()
            }
            finally {
                System.out.println ("Sleep... " + noThread);
                try {
                    Thread.sleep(1000);
                }
                catch(Exception e) { }
            }
        }
    }

}
and finally, the main class 
import java.net.URL;
import java.sql.*;

public class TestDBOraclePool3 {

    public static void main(String[] args) throws SQLException {
        new Thread( new TestDBOraclePool3Thread(1)).start();
        new Thread( new TestDBOraclePool3Thread(2)).start();
        new Thread( new TestDBOraclePool3Thread(3)).start();
        new Thread( new TestDBOraclePool3Thread(4)).start();
        new Thread( new TestDBOraclePool3Thread(5)).start();
        new Thread( new TestDBOraclePool3Thread(6)).start();
        new Thread( new TestDBOraclePool3Thread(7)).start();
    }
}