Oracle8i JDBC Developer's Guide and Reference Release 3 (8.1.7) Part Number A83724-01 |
|
This section includes samples of JDBC 2.0 extension features for data sources, connection pooling, connection caching, and distributed transactions (XA), as follows:
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; } }
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; } }
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; } }
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()); } }
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()); } }
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; } }
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. |
|