Oracle8i JDBC Developer's Guide and Reference Release 3 (8.1.7) Part Number A83724-01 |
Library | Service | Contents | Index

## 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

- Data Source with JNDI--DataSourceJNDI.java

- Pooled Connection--PooledConnection.java

- Oracle Connection Cache (dynamic)--CCache1.java

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

- XA with Suspend and Resume--XA2.java

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

### 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;
      }

    }

| Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
Library | Service | Contents | Index