Sample Applications

Samples for Connection Pooling and Distributed Transactions

This section includes samples of JDBC 2.0 extension features for data sources, connection pooling, connection caching, and distributed transactions (XA), as follows:

Data Source without JNDI--DataSource.java

This example shows how to use JDBC 2.0 data sources without JNDI. For general information about data sources, including how to use them with or without JNDI, see "Data Sources".

/**
 * A Simple DataSource sample without using JNDI.
 */

// You need to import the java.sql package to use JDBC
import java.sql.*;
import javax.sql.*;
import oracle.jdbc.driver.*;
import oracle.jdbc.pool.OracleDataSource;

public class DataSource
{
  public static void main (String args [])
    throws SQLException
  {
    // Create a OracleDataSource instance explicitly
    OracleDataSource ods = new OracleDataSource();

    // Set the user name, password, driver type and network protocol
    ods.setUser("scott");
    ods.setPassword("tiger");
    ods.setDriverType("oci8");
    ods.setNetworkProtocol("ipc");

    // Retrieve a connection
    Connection conn = ods.getConnection();
    getUserName(conn);
    // Close the connection
    conn.close();
    conn = null;
  }

  static void getUserName(Connection conn)
       throws SQLException
  {
    // Create a Statement
    Statement stmt = conn.createStatement ();

    // Select the ENAME column from the EMP table
    ResultSet rset = stmt.executeQuery ("select USER from dual");

    // Iterate through the result and print the employee names
    while (rset.next ())
      System.out.println ("User name is " + rset.getString (1));

    // Close the RseultSet
    rset.close();
    rset =  null;

    // Close the Statement
    stmt.close();
    stmt = null;
  }
}

Data Source with JNDI--DataSourceJNDI.java

This example shows how to use JDBC 2.0 data sources with JNDI. For general information about data sources, including how to use them with or without JNDI, see "Data Sources".

This class includes do_bind() and do_lookup() methods for JNDI functionality, as well as a getUserName() method.

/**
 * A Simple DataSource sample with JNDI.
 * This is tested using File System based reference 
 * implementation of JNDI SPI driver from JavaSoft.
 * You need to download fscontext1_2beta2.zip from
 * JavaSoft site.
 * Include providerutil.jar & fscontext.jar extracted
 * from the above ZIP in the classpath. 
 * Create a directory /tmp/JNDI/jdbc
 */

// You need to import the java.sql package to use JDBC
import java.sql.*;
import javax.sql.*;
import oracle.jdbc.driver.*;
import oracle.jdbc.pool.OracleDataSource;
import javax.naming.*;
import javax.naming.spi.*;
import java.util.Hashtable;

public class DataSourceJNDI
{
  public static void main (String args [])
    throws SQLException, NamingException
  {
    // Initialize the Context
    Context ctx = null;
    try {
      Hashtable env = new Hashtable (5);
      env.put (Context.INITIAL_CONTEXT_FACTORY,
               "com.sun.jndi.fscontext.RefFSContextFactory");
      env.put (Context.PROVIDER_URL, "file:/tmp/JNDI");
      ctx = new InitialContext(env);
    } catch (NamingException ne)
    {
      ne.printStackTrace();
    }

    do_bind(ctx, "jdbc/sampledb");
    do_lookup(ctx, "jdbc/sampledb");

  }

  static void do_bind (Context ctx, String ln)
    throws SQLException, NamingException
  {
    // Create a OracleDataSource instance explicitly
    OracleDataSource ods = new OracleDataSource();

    // Set the user name, password, driver type and network protocol
    ods.setUser("scott");
    ods.setPassword("tiger");
    ods.setDriverType("oci8");
    ods.setNetworkProtocol("ipc");

    // Bind it 
    System.out.println ("Doing a bind with the logical name : " + ln);
    ctx.bind (ln,ods);
  }

  static void do_lookup (Context ctx, String ln)
    throws SQLException, NamingException
  {

    System.out.println ("Doing a lookup with the logical name : " + ln);
    OracleDataSource ods = (OracleDataSource) ctx.lookup (ln);

    // Retrieve a connection
    Connection conn = ods.getConnection();
    getUserName(conn);
    // Close the connection
    conn.close();
    conn = null;
  }

  static void getUserName(Connection conn)
       throws SQLException
  {
    // Create a Statement
    Statement stmt = conn.createStatement ();

    // Select the ENAME column from the EMP table
    ResultSet rset = stmt.executeQuery ("select USER from dual");

    // Iterate through the result and print the employee names
    while (rset.next ())
      System.out.println ("User name is " + rset.getString (1));

    // Close the RseultSet
    rset.close();
    rset =  null;

    // Close the Statement
    stmt.close();
    stmt = null;
  }
}

Pooled Connection--PooledConnection.java

This is a simple example of how to use JDBC 2.0 pooled connection functionality. For general information about connection pooling, see "Connection Pooling".

/*
 * A simple Pooled Connection Sample
 */

import java.sql.*;
import javax.sql.*;
import oracle.jdbc.driver.*;
import oracle.jdbc.pool.*;

class PooledConnection1
{
  public static void main (String args [])
       throws SQLException
  {

    // Create a OracleConnectionPoolDataSource instance
    OracleConnectionPoolDataSource ocpds =
                               new OracleConnectionPoolDataSource();

    // Set connection parameters
    ocpds.setURL("jdbc:oracle:oci8:@");
    ocpds.setUser("scott");
    ocpds.setPassword("tiger");

    // Create a pooled connection
    PooledConnection pc  = ocpds.getPooledConnection();

    // Get a Logical connection
    Connection conn = pc.getConnection();

    // Create a Statement
    Statement stmt = conn.createStatement ();

    // Select the ENAME column from the EMP table
    ResultSet rset = stmt.executeQuery ("select ENAME from EMP");

    // Iterate through the result and print the employee names
    while (rset.next ())
      System.out.println (rset.getString (1));

    // Close the RseultSet
    rset.close();
    rset = null;

    // Close the Statement
    stmt.close();
    stmt = null;

    // Close the logical connection
    conn.close();
    conn = null;

    // Close the pooled connection
    pc.close();
    pc = null;
  }
}

Oracle Connection Cache (dynamic)--CCache1.java

This is the first of two examples of connection caching using the Oracle sample implementation available with class OracleConnectionCacheImpl.

This example uses the dynamic scheme for situations where the maximum number of pooled connections has already been reached--new pooled connection instances are created as needed, but each one is automatically closed and freed as soon as the JDBC application is done using the logical connection instance that the pooled connection instance provided.

For information about connection caching in general and Oracle's sample implementation in particular, see "Connection Caching".

/**
 *  JDBC 2.0 Spec doesn't mandate that JDBC vendors implement a 
 *  Connection Cache. However, we implemented a basic one with two
 *  schemes as an example.
 *  A Sample demo to illustrate DYNAMIC_SCHEME of OracleConnectionCacheImpl.
 *  Dynamic Scheme :  This is the default scheme. New connections could be 
 *  created beyond the Max limit upon request but closed and freed when the 
 *  logical connections are closed. When all the connections are active and 
 *  busy, requests for new connections willend up creating new physical 
 *  connections. But these physical connections are closed when the
 *  corresponding logical connections are closed. A typical grow and shrink 
 *  scheme. 
 */

import java.sql.*;
import javax.sql.*;
import oracle.jdbc.driver.*;
import oracle.jdbc.pool.*;

class CCache1
{
  public static void main (String args [])
       throws SQLException
  {
    OracleConnectionCacheImpl ods = new OracleConnectionCacheImpl();
    ods.setURL("jdbc:oracle:oci8:@");
    ods.setUser("scott");
    ods.setPassword("tiger");

     // Set the Max Limit
    ods.setMaxLimit (3);

    Connection conn1 = null;
    conn1 = ods.getConnection();
    if (conn1 != null)
      System.out.println("Connection 1 " + " Succeeded!");
    else
      System.out.println("Connection 1 " + " Failed !!!");

    Connection conn2 = null;
    conn2 = ods.getConnection();
    if (conn2 != null)
      System.out.println("Connection 2 " + " Succeeded!");
    else
      System.out.println("Connection 2 " + " Failed !!!");

    Connection conn3 = null;
    conn3 = ods.getConnection();
    if (conn3 != null)
      System.out.println("Connection 3 " + " Succeeded!");
    else
      System.out.println("Connection 3 " + " Failed !!!");

    Connection conn4 = null;
    conn4 = ods.getConnection();
    if (conn4 != null)
      System.out.println("Connection 4 " + " Succeeded!");
    else
      System.out.println("Connection 4 " + " Failed !!!");

    Connection conn5 = null;
    conn5 = ods.getConnection();
    if (conn5 != null)
      System.out.println("Connection 5 " + " Succeeded!");
    else
      System.out.println("Connection 5 " + " Failed !!!");


    System.out.println("Active size : " + ods.getActiveSize());
    System.out.println("Cache Size is " + ods.getCacheSize());

    // Close 3 logical Connections
    conn1.close();
    conn2.close();
    conn3.close();

    System.out.println("Active size : " + ods.getActiveSize());
    System.out.println("Cache Size is " + ods.getCacheSize());

    // close the Data Source
    ods.close();

    System.out.println("Active size : " + ods.getActiveSize());
    System.out.println("Cache Size is " + ods.getCacheSize());
  }
}

Oracle Connection Cache ("fixed with no wait")--CCache2.java

This is the second of two examples of connection caching using the Oracle sample implementation available with class OracleConnectionCacheImpl.

This example uses the "fixed with no wait" scheme for situations where the maximum number of pooled connections has already been reached--a null is returned when a connection is requested.

For information about connection caching in general and Oracle's sample implementation in particular, see "Connection Caching".

/**
 *  JDBC 2.0 Spec doesn't mandate that JDBC vendors implement a
 *  Connection Cache. However, we implemented a basic one with 2
 *  schemes as an Example.
 *  A Sample demo to illustrate FIXED_RETURN_NULL_SCHEME of 
 *  OracleConnectionCacheImpl.
 *  Fixed with NoWait :  At no instance there will be more active 
 *  connections than the Maximum limit. Request for new connections 
 *  beyond the max limit will return null. 
 */

// You need to import the java.sql package to use JDBC
import java.sql.*;
import javax.sql.*;
import oracle.jdbc.driver.*;
import oracle.jdbc.pool.*;

public class CCache2 {

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

    // Create a OracleConnectionPoolDataSource as an factory
    // of PooledConnections for the Cache to create.
    OracleConnectionPoolDataSource ocpds =
                               new OracleConnectionPoolDataSource();
    ocpds.setURL("jdbc:oracle:oci8:@");
    ocpds.setUser("scott");
    ocpds.setPassword("tiger");

    // Associate it with the Cache
    OracleConnectionCacheImpl ods = new OracleConnectionCacheImpl(ocpds);

    // Set the Max Limit
    ods.setMaxLimit (3);

    // Set the Scheme
    ods.setCacheScheme (OracleConnectionCacheImpl.FIXED_RETURN_NULL_SCHEME);

    Connection conn = null;
    for (int i=0; i < 5; ++i )
    {
      conn = ods.getConnection();
      if (conn != null)
        System.out.println("Connection " + i + " Succeeded!");
      else
        System.out.println("Connection " + i + " Failed !!!");
    }

    System.out.println("Active size : " + ods.getActiveSize());
    System.out.println("Cache Size is " + ods.getCacheSize());

    // close the Data Source
    ods.close();

    System.out.println("Active size : " + ods.getActiveSize());
    System.out.println("Cache Size is " + ods.getCacheSize());

  }
}

XA with Suspend and Resume--XA2.java

This sample shows how to suspend and resume a transaction. It uses standard XA resource functionality to suspend and resume the transaction, but includes comments about how to use the Oracle extension suspend() and resume() methods as an alternative.

This class includes a createXid() method to form transaction IDs for purposes of this example.

For general information about distributed transactions and XA functionality, see Chapter 16, "Distributed Transactions".

/*
    A simple XA demo with suspend and resume. Opens 2 global
    transactions each of one branch. Does some DML on the first one
    and suspends it and does some DML on the 2nd one and resumes the
    first one and commits. Basically, to illustrate interleaving
    of global transactions.
    Need a java enabled 8.1.6 database to run this demo.
 */

// You need to import the java.sql package to use JDBC
import java.sql.*;
import javax.sql.*;
import oracle.jdbc.driver.*;
import oracle.jdbc.pool.*;
import oracle.jdbc.xa.OracleXid;
import oracle.jdbc.xa.OracleXAException;
import oracle.jdbc.xa.client.*;
import javax.transaction.xa.*;

class XA2
{
  public static void main (String args [])
       throws SQLException
  {

    try
    {
        DriverManager.registerDriver(new OracleDriver());

        // You can put a database name after the @ sign in the connection URL.
        Connection conn =
          DriverManager.getConnection ("jdbc:oracle:oci8:@", "scott", "tiger");

        // Prepare a statement to create the table
        Statement stmt = conn.createStatement ();

        try
        {
          // Drop the test table
          stmt.execute ("drop table my_table");
        }
        catch (SQLException e)
        {
          // Ignore an error here
        }

        try
        {   
          // Create a test table
          stmt.execute ("create table my_table (col1 int)");
        }
        catch (SQLException e)
        {
          // Ignore an error here too
        }

        try
        {
          // Drop the test table
          stmt.execute ("drop table my_tab");
        }
        catch (SQLException e)
        {
          // Ignore an error here
        }

        try
        {   
          // Create a test table
          stmt.execute ("create table my_tab (col1 int)");
        }
        catch (SQLException e)
        {
          // Ignore an error here too
        }

        // Create a XADataSource instance
        OracleXADataSource oxds = new OracleXADataSource();
        oxds.setURL("jdbc:oracle:oci8:@");
        oxds.setUser("scott");
        oxds.setPassword("tiger");
    
        // get a XA connection
        XAConnection pc  = oxds.getXAConnection();
        // Get a logical connection
        Connection conn1 = pc.getConnection();
       
        // Get XA resource handle
        XAResource oxar = pc.getXAResource();
        Xid xid1 = createXid(111,111);
    
        // Start a transaction branch
        oxar.start (xid1, XAResource.TMNOFLAGS);
    
        // Create a Statement
        Statement stmt1 = conn1.createStatement ();
    
        // Do some DML
        stmt1.executeUpdate ("insert into my_table values (2727)");
    
        // Suspend the first global transaction
        // ((OracleXAResource)oxar).suspend (xid1); or
        oxar.end (xid1, XAResource.TMSUSPEND);

        Xid xid2 = createXid(222,222);
        oxar.start (xid2, XAResource.TMNOFLAGS);
        Statement stmt2 = conn1.createStatement ();
        stmt2.executeUpdate ("insert into my_tab values (7272)");
        oxar.commit (xid2, true);
        stmt2.close();
        stmt2 = null;

        // Close the Statement
        stmt1.close();
        stmt1 = null;

        // Resume the first global transaction
        // ((OracleXAResource)oxar).resume (xid1); or
        oxar.start (xid1, XAResource.TMRESUME);

        // End the branch
        oxar.end(xid1, XAResource.TMSUCCESS);
    
        // Do a 1 phase commit
        oxar.commit (xid1, true);

        // Close the connection
        conn1.close();   
        conn1 = null;

        // close the XA connection
        pc.close();
        pc = null;

        ResultSet rset = stmt.executeQuery ("select col1 from my_table");
        while (rset.next())
          System.out.println("Col1 is " + rset.getInt(1));
  
        rset.close();
        rset = null;

        rset = stmt.executeQuery ("select col1 from my_tab");
        while (rset.next())
          System.out.println("Col1 is " + rset.getString(1));
  
        rset.close();
        rset = null;

        stmt.close();
        stmt = null;

        conn.close();
        conn = null;

    } catch (SQLException sqe)
    {
      sqe.printStackTrace();
    } catch (XAException xae)
    {
      if (xae instanceof OracleXAException)
      {
        System.out.println("XA error is " +
                          ((OracleXAException)xae).getXAError());
        System.out.println("SQL error is " +
                          ((OracleXAException)xae).getOracleError());
      }
      xae.printStackTrace();
    }
  }

  static Xid createXid(int gd, int bd)
    throws XAException
  {
    byte[] gid = new byte[1]; gid[0]= (byte) gd;
    byte[] bid = new byte[1]; bid[0]= (byte) bd;
    byte[] gtrid = new byte[64];
    byte[] bqual = new byte[64];
    System.arraycopy (gid, 0, gtrid, 0, 1);
    System.arraycopy (bid, 0, bqual, 0, 1);
    Xid xid = new OracleXid(0x1234, gtrid, bqual);
    return xid;
  }
}

XA with Two-Phase Commit Operation--XA4.java

This example shows basic two-phase COMMIT functionality for a distributed transaction.

This class includes a createXid() method to form transaction IDs for purposes of this example. It also includes doSomeWork1() and doSomeWork2() methods to perform SQL operations.

For general information about distributed transactions and XA functionality, see Chapter 16, "Distributed Transactions".

/*
    A simple 2 phase XA demo. Both the branches talk to different RMS
    Need 2 java enabled 8.1.6 databases to run this demo.
      -> start-1
      -> start-2
      -> Do some DML on 1
      -> Do some DML on 2
      -> end 1
      -> end 2
      -> prepare-1
      -> prepare-2
      -> commit-1
      -> commit-2
    Please change the URL2 before running this.
 */

// You need to import the java.sql package to use JDBC
import java.sql.*;
import javax.sql.*;
import oracle.jdbc.driver.*;
import oracle.jdbc.pool.*;
import oracle.jdbc.xa.OracleXid;
import oracle.jdbc.xa.OracleXAException;
import oracle.jdbc.xa.client.*;
import javax.transaction.xa.*;

class XA4
{
  public static void main (String args [])
       throws SQLException
  {

    try
    {
        String URL1 = "jdbc:oracle:oci8:@";
        String URL2 =
                    "jdbc:oracle:thin:@
                    (description=(address=(host=dlsun991)(protocol=tcp)
                    (port=5521))(connect_data=(sid=rdbms2)))";

        DriverManager.registerDriver(new OracleDriver());

        // You can put a database name after the @ sign in the connection URL.
        Connection conna =
          DriverManager.getConnection (URL1, "scott", "tiger");

        // Prepare a statement to create the table
        Statement stmta = conna.createStatement ();

        Connection connb =
          DriverManager.getConnection (URL2, "scott", "tiger");

        // Prepare a statement to create the table
        Statement stmtb = connb.createStatement ();

        try
        {
          // Drop the test table
          stmta.execute ("drop table my_table");
        }
        catch (SQLException e)
        {
          // Ignore an error here
        }

        try
        {   
          // Create a test table
          stmta.execute ("create table my_table (col1 int)");
        }
        catch (SQLException e)
        {
          // Ignore an error here too
        }

        try
        {
          // Drop the test table
          stmtb.execute ("drop table my_tab");
        }
        catch (SQLException e)
        {
          // Ignore an error here
        }

        try
        {   
          // Create a test table
          stmtb.execute ("create table my_tab (col1 char(30))");
        }
        catch (SQLException e)
        {
          // Ignore an error here too
        }

        // Create a XADataSource instance
        OracleXADataSource oxds1 = new OracleXADataSource();
        oxds1.setURL("jdbc:oracle:oci8:@");
        oxds1.setUser("scott");
        oxds1.setPassword("tiger");

        OracleXADataSource oxds2 = new OracleXADataSource();

        oxds2.setURL
              ("jdbc:oracle:thin:@(description=(address=(host=dlsun991)
              (protocol=tcp)(port=5521))(connect_data=(sid=rdbms2)))");
        oxds2.setUser("scott");
        oxds2.setPassword("tiger");
    
        // Get a XA connection to the underlying data source
        XAConnection pc1  = oxds1.getXAConnection();

        // We can use the same data source 
        XAConnection pc2  = oxds2.getXAConnection();

        // Get the Physical Connections
        Connection conn1 = pc1.getConnection();
        Connection conn2 = pc2.getConnection();

        // Get the XA Resources
        XAResource oxar1 = pc1.getXAResource();
        XAResource oxar2 = pc2.getXAResource();

        // Create the Xids With the Same Global Ids
        Xid xid1 = createXid(1);
        Xid xid2 = createXid(2);

        // Start the Resources
        oxar1.start (xid1, XAResource.TMNOFLAGS);
        oxar2.start (xid2, XAResource.TMNOFLAGS);

        // Do  something with conn1 and conn2
        doSomeWork1 (conn1);
        doSomeWork2 (conn2);

        // END both the branches -- THIS IS MUST
        oxar1.end(xid1, XAResource.TMSUCCESS);
        oxar2.end(xid2, XAResource.TMSUCCESS);

        // Prepare the RMs
        int prp1 =  oxar1.prepare (xid1);
        int prp2 =  oxar2.prepare (xid2);

        System.out.println("Return value of prepare 1 is " + prp1);
        System.out.println("Return value of prepare 2 is " + prp2);

        boolean do_commit = true;

        if (!((prp1 == XAResource.XA_OK) || (prp1 == XAResource.XA_RDONLY)))
           do_commit = false;

        if (!((prp2 == XAResource.XA_OK) || (prp2 == XAResource.XA_RDONLY)))
           do_commit = false;

       System.out.println("do_commit is " + do_commit);
        System.out.println("Is oxar1 same as oxar2 ? " + oxar1.isSameRM(oxar2));

        if (prp1 == XAResource.XA_OK)
          if (do_commit)
             oxar1.commit (xid1, false);
          else
             oxar1.rollback (xid1);

        if (prp2 == XAResource.XA_OK)
          if (do_commit)
             oxar2.commit (xid2, false);
          else
             oxar2.rollback (xid2);

         // Close connections
        conn1.close();
        conn1 = null;
        conn2.close();
        conn2 = null;

        pc1.close();
        pc1 = null;
        pc2.close();
        pc2 = null;

        ResultSet rset = stmta.executeQuery ("select col1 from my_table");
        while (rset.next())
          System.out.println("Col1 is " + rset.getInt(1));
  
        rset.close();
        rset = null;

        rset = stmtb.executeQuery ("select col1 from my_tab");
        while (rset.next())
          System.out.println("Col1 is " + rset.getString(1));
  
        rset.close();
        rset = null;

        stmta.close();
        stmta = null;
        stmtb.close();
        stmtb = null;

        conna.close();
        conna = null;
        connb.close();
        connb = null;

    } catch (SQLException sqe)
    {
      sqe.printStackTrace();
    } catch (XAException xae)
    {
      if (xae instanceof OracleXAException) {
        System.out.println("XA Error is " +
                      ((OracleXAException)xae).getXAError());
        System.out.println("SQL Error is " +
                      ((OracleXAException)xae).getOracleError());
      }
    }
  }
  static Xid createXid(int bids)
    throws XAException
  {
    byte[] gid = new byte[1]; gid[0]= (byte) 9;
    byte[] bid = new byte[1]; bid[0]= (byte) bids;
    byte[] gtrid = new byte[64];
    byte[] bqual = new byte[64];
    System.arraycopy (gid, 0, gtrid, 0, 1);
    System.arraycopy (bid, 0, bqual, 0, 1);
    Xid xid = new OracleXid(0x1234, gtrid, bqual);
    return xid;
  }

  private static void doSomeWork1 (Connection conn)
   throws SQLException
  {
    // Create a Statement
    Statement stmt = conn.createStatement ();

    int cnt = stmt.executeUpdate ("insert into my_table values (4321)");

    System.out.println("No of rows Affected " + cnt);

    stmt.close();
    stmt = null;
  }

  private static void doSomeWork2 (Connection conn)
    throws SQLException
  {
    // Create a Statement
    Statement stmt = conn.createStatement ();

    int cnt = stmt.executeUpdate ("insert into my_tab values ('test')");

    System.out.println("No of rows Affected " + cnt);

    stmt.close();
    stmt = null;
  }

}