Using JDBC and UCP

Starting from 12c release, Oracle Database supports invisible columns. Using this feature, you can add a column to the table in hidden mode and make it visible later. JDBC provides APIs to retrieve information about invisible columns. This program demonstrates how to declare an attribute invisible and retrieve later.

1. Open and examine the InvisibleColumn.java file.

   cat src/InvisibleColumn.java

   $ cat src/InvisibleColumn.java 
   
   import java.sql.*;
   import java.util.Properties;
   import oracle.jdbc.*;
   import oracle.jdbc.pool.*;
   
   public class InvisibleColumn 
   {
    public static void main(String args[]) throws SQLException
     {
       String jdbcURL =
         "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(PORT=1521)(HOST=localhost))(CONNECT_DATA=(SERVICE_NAME=pdb1)))";
       String user = "hr";
       String passwd = "hr";
       OracleDataSource ods = new OracleDataSource();
       ods.setURL(jdbcURL);
       ods.setUser(user);
       ods.setPassword(passwd);
       Connection conn = ods.getConnection();
   
       try
       { 
         createTables(conn);
   

2. Use ant to build and run the build.xml file.

   ant InvisibleColumn

   $ ant InvisibleColumn 
   Buildfile: /u01/oracle/jdbchol/JDBC_Demo/build.xml
   
   InvisibleColumn:
   
   print.properties:
        [echo] InvisibleColumn: Sample for Invisible Columns.
        [echo]    Look at Readme.txt for details
   
   run:
       [javac] Compiling 1 source file to /u01/oracle/jdbchol/JDBC_Demo/classes
        [java] Invisibility information of columns
        [java] column name:A
        [java] column1 value:somedata
        [java] Invisibility:false
        [java] column name:B
        [java] column2 value:1
        [java] Invisibility:true
        [java] column name:A
        [java] column1 value:newdata
        [java] Invisibility:false
        [java] column name:B
        [java] column2 value:2
        [java] Invisibility:true
        [java] 
        [java] getColumns for table with invisible columns
        [java] ColCount: 24
   
   BUILD SUCCESSFUL
   Total time: 4 seconds
   

Observe the output.

Starting from 12c release, Oracle Database supports results of SQL statements executed in a stored procedure to be returned implicitly to the client applications without the need to explicitly use a REF CURSOR. There are different methods to retrieve and process the implicit results returned by PL/SQL procedures or blocks.

1. Open and examine the ImplicitResult.java file.

   cat src/ImplicitResult.java

   $ cat src/ImplicitResult.java
   import java.sql.*;
   import oracle.jdbc.*;
   import oracle.sql.*;
   import java.io.*;
   import oracle.jdbc.pool.*;
   
   public class ImplicitResult 
   {
     public static void main(String args[]) throws SQLException 
     {
       String jdbcURL =
         "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(PORT=1521)(HOST= localhost))(CONNECT_DATA=(SERVICE_NAME=pdb1)))";
       String user = "hr";
       String passwd = "hr";
       OracleDataSource ods = new OracleDataSource();
       ods.setURL(jdbcURL);
       ods.setUser(user);
       ods.setPassword(passwd);
       Connection conn = ods.getConnection();
   
        try
        {
   
   
   

2. Use ant to build and run the build.xml file.

   ant ImplicitResult

   $ ant ImplicitResult
   Buildfile: /u01/oracle/jdbchol/JDBC_Demo/build.xml
   
   ImplicitResult:
   
   print.properties:
        [echo] Implicit Result: Sample for getting the Implicit Results
        [echo]    Look at Readme.txt for details
   
   run:
       [javac] Compiling 1 source file to /u01/oracle/jdbchol/JDBC_Demo/classes
        [java] The return value is: true
        [java] ** ResultSet Implicit Results 1 **
        [java] Ellen
        [java] Sundar
        [java] Mozhe
        [java] David
        [java] Hermann
        [java] Shelli
        [java] Amit
        [java] Elizabeth
        [java] Sarah
        [java] David
        [java] Laura
        [java] Harrison
        [java] Alexis
        [java] Anthony
        [java] Gerald
        [java] Nanette
   

        ...
        [java] 199
        [java] 200
        [java] 201
        [java] 202
        [java] 203
        [java] 204
        [java] 205
        [java] 206
        [java] The return value is: false
   
   BUILD SUCCESSFUL
   Total time: 3 seconds
   

Observe the output.

The Oracle JDBC driver automatically caches the PreparedStatement or the CallableStatement objects upon the invocation of the close() method.

A cache entry is automatically allocated to a new SQL statement if not already in the cache upon the invocation of preparedStatement() and prepareCall() methods. The Oracle JDBC driver transparently searches the cache for a match using the following criteria:

• The SQL string must be identical.
• The statement type must be identical PreparedStatement or CallableStatement.
• The type of scrollable result sets FORWARD-ONLY or SCROLLABLE must be the same.

If a match is found, then the cached statement is returned; otherwise, a new statement is created and returned.

In this program you will examine how implicit statement caching is enabled by invoking ((OracleConnection)conn).setImplicitCachingEnabled(true); on the connection object.

1. Open and examine the ImplStmtCache.java file.

   cat src/ImplStmtCache.java

   $ cat src/ImplStmtCache.java/*
    *   Implicit Statement Caching
    * 
    */ 
   // import the java.sql package 
   import java.sql.*;
   import javax.sql.*;
   import oracle.jdbc.*;
   import java.util.Properties;
   import oracle.jdbc.pool.OracleDataSource;
   
   class ImplStmtCache
   {
    public static void main (String args [])
    throws SQLException
    {
     long  start;
     long end;
     long elapsed;
   
     String name = null;
     OracleConnection conn = null;
     OraclePreparedStatement pstmt = null;        
     OracleDataSource ods = new OracleDataSource(); 
   // Set the URL (user name, and password)

2. Use ant to build and run the build.xml file.

   ant ImplStmtCache

   $ ant ImplStmtCache
   Buildfile: /u01/oracle/jdbchol/JDBC_Demo/build.xml
   
   ImplStmtCache:
   
   print.properties:
        [echo] ImplStmtCache: Sample for Implicit Statement Cache.
        [echo]    Look at Readme.txt for details
   
   run:
       [javac] Compiling 1 source file to /u01/oracle/jdbchol/JDBC_Demo/classes
        [java] PredStmt is select EMPLOYEE_ID, FIRST_NAME from EMPLOYEES
        [java]  Time to prepare and execute NON CACHED query 10 times is: 293
        [java]  Time to prepare and execute CACHED query 10 times is: 10
   
   BUILD SUCCESSFUL
   Total time: 2 seconds
   

Observe the output.

Explicit statement caching is managed by the application, because it requires explicit instructions for caching and searching using a user-defined key. Explicit statement caching is enabled by invoking setExplicitStatementCaching(true)> on the connection object.

Determining whether explicit caching is enabled is achieved by invoking getExplicitStatementCachingEnabled(). This method returns true if explicit caching is enabled. It returns false is explicit caching in not enabled. A cache entry is allocated to a new SQL statement (if not already in the cache) upon the invocation of the createStatement(), prepareStatement(), and prepareCall() methods. It is up to the application to search the cache for a match using the user-defined key (a Java string) specified during the closeWithKey(String) method, using the following:

• getStatementWithKey(String);
• getCallWithKey(String);

If a match is found, then the cached statement with the parameter and metadata defined in the last usage is returned. Otherwise, a null value is returned to the application.

In this program, explicit statement cache is done using getStatementWithKey(String) method.

1. Open and examine the ExplStmtCache.java file.

   cat src/ExplStmtCache.java

   $ cat src/ExplStmtCache.java
   Buildfile: /u01/oracle/jdbchol/JDBC_Demo/build.xml
    *   Explicit Statement Caching
    * 
    */ 
   // import the java.sql package 
   import java.sql.*;
   import javax.sql.*;
   import oracle.jdbc.*;
   import oracle.jdbc.pool.OracleDataSource;
   
   class ExplStmtCache
   {
    public static void main (String args [])
    throws SQLException
    {
     long  start;
     long end;
     long elapsed;
   
     String name = null;
     OracleConnection conn = null;
     OraclePreparedStatement pstmt = null;        
     OracleDataSource ods = new OracleDataSource(); 
     // Set the URL (user name, and password)
   String url = "jdbc:oracle:thin:hr/hr@//localhost:1521/pdb1";
   ods.setURL(url); 

2. Use ant to build and run the build.xml file.

   ant ExplStmtCache

   $ ant ExplStmtCache                                                                                                          Buildfile: /u01/oracle/jdbchol/JDBC_Demo/build.xml
   
   ExplStmtCache:
   
   print.properties:
        [echo] ExplStmtCache: Sample for Explicit Statement Cache.
        [echo]    Look at Readme.txt for details
   
   run:
       [javac] Compiling 1 source file to /u01/oracle/jdbchol/JDBC_Demo/classes
        [java] PredStmt is select EMPLOYEE_ID, FIRST_NAME from EMPLOYEES
        [java]  Time to prepare and execute NON CACHED query 10 times is : 270
        [java]  Time to prepare and execute EXPLICITLY CACHED query 10 times is 8
   
   BUILD SUCCESSFUL
   Total time: 2 seconds

Observe the output.

Starting from Oracle Database 12c Release 1, you can map schema-level objects or collections into generic STRUCT classes, or alternatively, into custom Java classes. So, instead of creating schema-level types that are mapped to PL/SQL package types for binding, you can describe and bind PL/SQL types using only the JDBC APIs. The SQL Data Types that are supported by JDBC Drivers are BFILE, BLOB, CHAR, CLOB, DATE, NCHAR VARYING, NUMBER, RAW, REF, ROWID, UROWID and VARCHAR2.

1. Login to sql into the pdb1 using the hr user.

   sqlplus hr/hr@pdb1;

   $ sqlplus hr/hr@pdb1;
   
   SQL*Plus: Release 12.2.0.1.0 Production on Wed Jul 20 09:21:13 2016
   
   Copyright (c) 1982, 2016, Oracle.  All rights reserved.
   
   Last Successful login time: Wed Jul 20 2016 09:16:32 +00:00
   
   Connected to:
   Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
   
   SQL> 
   

2. Create and execute the below package in sql

   create or replace package TEST_PKG is
   type V_TYP is varray(10) of varchar2(200);
   type R_TYP is record(c1 pls_integer, c2 varchar2(100));
   procedure VARR_PROC(p1 in V_TYP, p2 OUT V_TYP);
   procedure REC_PROC(p1 in R_TYP, p2 OUT R_TYP);
   end;
   /

   
   

3. Create and execute the below package in sql and exit from sql.

   create or replace package body TEST_PKG is
   procedure VARR_PROC(p1 in V_TYP, p2 OUT V_TYP) is
   begin
   p2 := p1;
   end;
   procedure REC_PROC(p1 in R_TYP, p2 OUT R_TYP) is
   begin
   p2 := p1;
   end;
   end;
   /

   exit;

4. Open and examine the PLSQLTypeBindsDemo.java file.

   cat src/PLSQLTypeBindsDemo.java

   $cat src/PLSQLTypeBindsDemo.java
   import java.sql.Array;
   import java.sql.CallableStatement;
   import java.sql.Connection;
   import java.sql.SQLException;
   import java.sql.Struct;
   import java.sql.Types;
   
   import oracle.jdbc.OracleConnection;
   
   public class PLSQLTypeBindsDemo
   {
      public static void main(String[] args) throws SQLException
      {
         System.out.println("begin...");
         Connection conn = null;
         oracle.jdbc.pool.OracleDataSource ods = new oracle.jdbc.pool.OracleDataSource();
         ods.setURL("jdbc:oracle:thin:@localhost:1521/pdb1");
         ods.setUser("hr");
         ods.setPassword("hr");
         // get connection
         conn = ods.getConnection();
         
         PLSQLTypeBindsDemo demo = new PLSQLTypeBindsDemo();
         
         demo.varrayDemo(conn);
         
         demo.recordDemo(conn);
   
         if (conn != null)
            conn.close();
   
         System.out.println("done!");
      }
      ...                             

5. Use ant to build and run the build.xml file.

   ant PLSQLTypeBindsDemo

   $ ant PLSQLTypeBindsDemo
   Buildfile: /u01/oracle/jdbchol/JDBC_Demo/build.xml
   
   PLSQLTypeBindsDemo:
   
   print.properties:
        [echo] PLSQLTypeBindsDemo: Sample for PLSQLTypeBindsDemo.
        [echo]    Look at Readme.txt for details
   
   run:
       [javac] Compiling 1 source file to /u01/oracle/jdbchol/JDBC_Demo/classes
        [java] begin...
        [java] varrayDemo - 
        [java] elem[0] = A, elem[[1] = B
        [java] recordDemo - 
        [java] attr[0] = 12345, attr[[1] = B
        [java] done!
   
   BUILD SUCCESSFUL
   Total time: 3 seconds  

   Observe the output.

This section describes getObject() and setObject()

1. Open and examine the SetObjectDemo.java file.

   cat src/SetObjectDemo.java

   $ cat src/SetObjectDemo.java
   /* Copyright (c) 2015, Oracle and/or its affiliates. All rights reserved.*/
   /*
      DESCRIPTION    
       Latest JDBC 4.2 APIs   
       
       Step 1: Enter the Database details in this file. 
               DB_USER, DB_PASSWORD and DB_URL are required
       Step 2: Run the sample with "ant JDBCLatestSpec42"
     
      NOTES
       Use JDK 1.7 and above
   
      MODIFIED    (MM/DD/YY)
       nbsundar    02/17/15 - Creation 
    */
   
   import java.io.IOException;
   import java.io.InputStream;
   import java.sql.Connection;
   import java.sql.ResultSet;
   import java.sql.SQLException;
   import java.sql.Statement;
   import java.sql.PreparedStatement;
   import java.util.Properties;
   
   import oracle.jdbc.pool.OracleDataSource;
   import oracle.jdbc.OracleConnection;
   import oracle.jdbc.OracleType;
   
   
   public class SetObjectDemo{  
     // The recommended format of a connection URL is the long format with the
     // connection descriptor.
     //final static String DB_URL= "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(HOST=myhost)(PORT=1521)(PROTOCOL=tcp))(CONNECT_DATA=(SERVICE_NAME=myorcldbservicename)))";
     final static String DB_URL= "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(HOST=localhost)(PORT=1521)(PROTOCOL=tcp))(CONNECT_DATA=(SERVICE_NAME=pdb1)))";
     final static String DB_USER = "hr";
     final static String DB_PASSWORD = "hr";
   
    /*
    ...

2. Use ant to build and run the build.xml file.

   ant SetObjectDemo

   $ ant SetObjectDemo
   Buildfile: /u01/oracle/jdbchol/JDBC_Demo/build.xml
   
   SetObjectDemo:
   
   print.properties:
        [echo] SetObjectDemo: Sample for SetObjectDemo
        [echo]    Look at Readme.txt for details
   
   run:
       [javac] Compiling 1 source file to /u01/oracle/jdbchol/JDBC_Demo/classes
        [java] EMPLOYEE_ID  FIRST_NAME   LAST_NAME
        [java] -----------  ----------   ---------
        [java] 100 Steven King 
        [java] 101 Neena Kochhar 
        [java] 102 Lex De Haan 
        [java] 103 Alexander Hunold 
        [java] 104 Bruce Ernst 
        [java] 105 David Austin 
        [java] 106 Valli Pataballa 
        [java] 107 Diana Lorentz 
        [java] 108 Nancy Greenberg 
        [java] 109 Daniel Faviet 
        [java] 110 John Chen 
        [java] 111 Ismael Sciarra 
        [java] 112 Jose Manuel Urman 
        [java] 113 Luis Popp 
        [java] 114 Den Raphaely 
        [java] 115 Alexander Khoo 
        [java] 116 Shelli Baida 
        [java] 117 Sigal Tobias 
        [java] 118 Guy Himuro 
        [java] 119 Karen Colmenares 
   ...
   

   ...
   [java] 205 Shelley Higgins 
        [java] 206 William Gietz 
        [java] 
        [java] ************************************
        [java] Print employee by id = 102
        [java] FIRST_NAME  LAST_NAME
        [java] ---------------------
        [java] Lex Lex 
        [java] 
        [java] ************************************
        [java] Print employee by id = 154
        [java] FIRST_NAME  LAST_NAME
        [java] ---------------------
        [java] Nanette Nanette 
        [java] 
        [java] ************************************
        [java] Print employee by id = 205
        [java] FIRST_NAME  LAST_NAME
        [java] ---------------------
        [java] Shelley Shelley 
   
   BUILD SUCCESSFUL
   Total time: 3 seconds
   
   

Observe the output.

This demo is a stock ticker application that uses the Universal Connection Pool to retrieve stock price information from the database. Using this demo, you may view the Universal Connection Pool properties, change the properties at runtime without shutting down the pool instance, view Universal Connection Statistics etc. To perform this demo execute the following steps:

1. Open a new terminal. Navigate to the u01/oracle/jdbchol/UCP_Demo directory. Execute ls to see the contents inside this directory.

   cd /u01/oracle/jdbchol/UCP_Demo

   $ cd /u01/oracle/jdbchol/UCP_Demo/
   ls
   build.xml  classes  ClientSimulator  DbConfig.properties  src
   

   
   

2. Open and examine the DbConfig.properties file.

   cat DbConfig.properties

   $ cat DbConfig.properties                                                                                                        DB_USER =hr
   DB_PASSWORD =hr
   DB_URL =jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=pdb1)))

   
   

3. Open and examine the UCPDemo.java file.

   cat src/UCPDemo.java

   $ cat src/UCPDemo.java                                                                                                           import java.io.IOException;
   import java.sql.Connection;
   import java.sql.ResultSet;
   import java.sql.SQLException;
   import java.sql.Statement;
   import java.util.Date;
   
   import oracle.ucp.UniversalConnectionPoolException;
   import oracle.ucp.UniversalConnectionPoolStatistics;
   import oracle.ucp.admin.UniversalConnectionPoolManagerImpl;
   import oracle.ucp.jdbc.PoolDataSource;
   import oracle.ucp.jdbc.PoolDataSourceImpl;
   
   /**
    * UCP stock ticker demo web service
    * 
    * @author ydolgov
    *
    */
   public class UCPDemo
   {
     public final static String UCP_POOL = "UCP-pool";
     
     private final PoolDataSource pds = new PoolDataSourceImpl();
   
     /**
      * 
      */
     public UCPDemo()
     {
       super();
     }
   ...
   

   
   

4. Use ant to build and run the build.xml file.

   ant

   $ ant 
   Buildfile: /u01/oracle/jdbchol/UCP_Demo/build.xml
   
   print.properties:
        [echo] UCPDemo: That runs UCPDemo
   
   run:
       [javac] Compiling 1 source file to /u01/oracle/jdbchol/UCP_Demo/classes
       [javac] /u01/oracle/jdbchol/UCP_Demo/build.xml:38: warning: 'includeantruntime' was not set, defaulting to build.sysclasspath=last; set to false for repeatable builds
        [java] preparing stock database, please wait till notified...
        [java] Stock data wrote for Wed Jul 20 11:00:00 UTC 2016
        [java] Stock data wrote for Wed Jul 20 11:15:00 UTC 2016
        [java] Stock data wrote for Wed Jul 20 11:30:00 UTC 2016
        [java] Stock data wrote for Wed Jul 20 11:45:00 UTC 2016
        [java] Stock data wrote for Wed Jul 20 12:00:00 UTC 2016
        [java] Stock data wrote for Wed Jul 20 12:15:00 UTC 2016
        [java] Stock data wrote for Wed Jul 20 12:30:00 UTC 2016
        [java] Stock data wrote for Wed Jul 20 12:45:00 UTC 2016
        [java] Stock data wrote for Wed Jul 20 13:00:00 UTC 2016
        [java] Stock data wrote for Wed Jul 20 13:15:00 UTC 2016
        [java] Stock data wrote for Wed Jul 20 13:30:00 UTC 2016
        [java] Stock data wrote for Wed Jul 20 13:45:00 UTC 2016
        [java] Stock data wrote for Wed Jul 20 14:00:00 UTC 2016
        [java] Stock data wrote for Wed Jul 20 14:15:00 UTC 2016                                                                    ....

        ...
        [java] Stock data wrote for Sat Jul 23 07:00:00 UTC 2016
        [java] Stock data wrote for Sat Jul 23 07:15:00 UTC 2016
        [java] Stock data wrote for Sat Jul 23 07:30:00 UTC 2016
        [java] Stock data wrote for Sat Jul 23 07:45:00 UTC 2016
        [java] Stock data wrote for Sat Jul 23 08:00:00 UTC 2016
        [java] Stock data wrote for Sat Jul 23 08:15:00 UTC 2016
        [java] Stock data wrote for Sat Jul 23 08:30:00 UTC 2016
        [java] Stock data wrote for Sat Jul 23 08:45:00 UTC 2016
        [java] Stock data wrote for Sat Jul 23 09:00:00 UTC 2016
        [java] Stock data wrote for Sat Jul 23 09:15:00 UTC 2016
        [java] Stock data wrote for Sat Jul 23 09:30:00 UTC 2016
        [java] Stock data wrote for Sat Jul 23 09:45:00 UTC 2016
        [java] Stock data wrote for Sat Jul 23 10:00:00 UTC 2016
        [java] Stock data wrote for Sat Jul 23 10:15:00 UTC 2016
        [java] Stock data wrote for Sat Jul 23 10:30:00 UTC 2016
        [java] Stock data wrote for Sat Jul 23 10:45:00 UTC 2016
        [java] ...ready to go!

   Note: Upon startup the UCPDemo will spend a few seconds to store the stock price data onto the database, so wait until the message "...ready to go!" is printed out on the screen.

5. Open Firefox web browser and navigate to the http://localhost:8081 url, which is the home page of the UCP Connection Pool Demo. Explore various links on the site.

   http://localhost:8081

   

   
   

   

   
   

   

   
   

   

   
   

   

   
   

6. Observe the terminal. Notice the status of thread operations.

        ...
        [java] Stock data wrote for Sat Jul 23 10:45:00 UTC 2016
        [java] ...ready to go!
        [java] thread-0 started
        [java] thread-0 finished
        [java] thread-0 started
        [java] thread-0 finished
        [java] thread-0 started
        [java] thread-0 finished
        [java] thread-0 started
        [java] thread-0 finished
        [java] thread-0 started
        [java] thread-0 finished
        [java] thread-0 started
        [java] thread-0 finished
   
   

7. To simulate many stock ticker clients using the service simultaneously you can use the Client Simulator.

   Open a new terminal. Navigate to the u01/oracle/jdbchol/UCP_Demo/ClientSimulator directory. Execute ls to see the contents inside this directory.

   cd /u01/oracle/jdbchol/UCP_Demo/ClientSimulator

8. Open and examine the ClientSimulator.java file.

   cat src/ClientSimulator.java

   $ cat src/ClientSimulator.java
   import java.io.BufferedReader;
   import java.io.IOException;
   import java.io.InputStreamReader;
   import java.net.HttpURLConnection;
   import java.net.MalformedURLException;
   import java.net.URL;
   import java.util.ArrayList;
   import java.util.Collection;
   
   /**
    * Multisession multithreaded stock ticker
    * client simulator
    * 
    * @author ydolgov
    *
    */
   public class ClientSimulator
   {
   
     /**
      * 
      */
     public ClientSimulator()
     {
       super();
     }                                                                                                                           ....
   

   
   

9. Use ant to build and run the build.xml file.

   ant

   $ ant
        [java] polled
        [java] polled
        [java] polled
        [java] polled
        [java] polled
        [java] polled
        [java] polled
        [java] polled
        [java] polled
        [java] polled
        [java] polled
        [java] polled
        [java] client simulator finished
   
   BUILD SUCCESSFUL
   Total time: 9 seconds

   
   

   Note: Observe the terminal. Notice the status of thread operations.

This section describes UCP with Tomcat using new UCP XML config file

1. Open a new terminal and navigate to apache tomcat directory. Execute ls to see the contents inside this directory.

   cd /usr/apache-tomcat-9.0.0.M8/

   $ cd /usr/apache-tomcat-9.0.0.M8/
   $ ls
   bin   lib      logs    RELEASE-NOTES  temp     work
   conf  LICENSE  NOTICE  RUNNING.txt    webapps 

2. Navigate into webapps directory and execute ls to see the contents inside this directory.

   cd webapps

   $ cd webapps
   $ ls
     docs  host-manager  ROOT   examples  manager       

3. Move the UCPConfigXML.war file in to the webapps directory. Execute ls to see the contents inside webapps directory.

   cp /u01/oracle/jdbchol/UCPConfigXML_Demo/UCPConfigXML.war .

4. Navigate to apache-tomcat-9.0.0.M8/conf directory. Execute ls to see the contents inside this directory.

   $ cd /usr/apache-tomcat-9.0.0.M8/conf/
   $ ls
   Catalina             context.xml           logging.properties  tomcat-users.xsd
   catalina.policy      jaspic-providers.xml  server.xml          web.xml
   catalina.properties  jaspic-providers.xsd  tomcat-users.xml
   

5. Edit the server.xmlfile. Modify the port value to 8085. This is done to ensure that the port number is not conflicting with any other open ports.

   vi server.xml

   
   

   

   
   

6. Navigate into the apache-tomcat-9.0.0.M8/bin directory.

   cd /usr/apache-tomcat-9.0.0.M8/bin

7. Start the tomcat and immediately stop the tomcat. This is done so that the UCPConfigXML.war gets deployed.

   ./startup.sh

   ./shutdown.sh

   $ ./startup.sh
   Using CATALINA_BASE:   /usr/apache-tomcat-9.0.0.M8
   Using CATALINA_HOME:   /usr/apache-tomcat-9.0.0.M8
   Using CATALINA_TMPDIR: /usr/apache-tomcat-9.0.0.M8/temp
   Using JRE_HOME:        /usr/java/jdk1.8.0_91
   Using CLASSPATH:       /usr/apache-tomcat-9.0.0.M8/bin/bootstrap.jar:/usr/apache-tomcat-9.0.0.M8/bin/tomcat-juli.jar
   Tomcat started.
   $ ./shutdown.sh
   Using CATALINA_BASE:   /usr/apache-tomcat-9.0.0.M8
   Using CATALINA_HOME:   /usr/apache-tomcat-9.0.0.M8
   Using CATALINA_TMPDIR: /usr/apache-tomcat-9.0.0.M8/temp
   Using JRE_HOME:        /usr/java/jdk1.8.0_91
   Using CLASSPATH:       /usr/apache-tomcat-9.0.0.M8/bin/bootstrap.jar:/usr/apache-tomcat-9.0.0.M8/bin/tomcat-juli.jar
   [oracle@host01 bin]$ 

8. Navigate into the apache-tomcat-9.0.0.M8/webapps directory. Execute ls to see the contents inside this directory. Notice that a new directory UCPConfigXML with the same name as the UCPConfigXML.war file got created with contents in it.

   cd /usr/apache-tomcat-9.0.0.M8/webapps

   $ cd /usr/apache-tomcat-9.0.0.M8/webapps/
   $ ls
   docs      host-manager  ROOT    UCPConfigXML.war 
   examples  manager       UCPConfigXML 
   

9. Navigate into the UCPConfigXML directory. Execute ls to see the contents inside this directory.

   cd UCPConfigXML

   $ cd UCPConfigXML
   $ ls 
   build.xml  dist  META-INF  README.txt  src  WEB-INF

10. Navigate into the META-INF directory. Execute ls to see the contents inside this directory.

    cd META-INF

    $ cd META-INF/
    $ ls
    context_old.xml  InitialConfig_nir.xml  MANIFEST.MF
    context.xml      InitialConfig.xml      war-tracker

11. Create a new file context.xml and edit it.

    vi context.xml

12. Insert the following code in to the context.xml file.

    <Context 
        docBase="UCPTomcat" 
        path="/UCPTomcat"
        reloadable="true" 
        source="org.eclipse.jst.jee.server:UCPTomcat">
    
            <Resource 
                name="tomcat/UCPPool" 
                auth="Container"
                factory="oracle.ucp.jdbc.PoolDataSourceImpl" 
                type="oracle.ucp.jdbc.PoolDataSource"
                description="UCP Pool in Tomcat" 
                connectionFactoryClassName="oracle.jdbc.pool.OracleDataSource"
                dataSourceFromConfiguration="UCPDatasource" />  
    </Context>

13. Execute ls to see the contents inside this directory. Verify that the context.xml file is present.

    ls

    $ ls
    context_old.xml  InitialConfig_nir.xml  MANIFEST.MF
    context.xml      InitialConfig.xml      war-tracker

14. Edit the InitialConfig.xml file.

    vi InitialConfig.xml

15. Modify the url, user, password properties with the values as given below. Save the file.

    url="jdbc:oracle:thin:@(DESCRIPTION=
                                (ADDRESS=(HOST=localhost)(PORT=1521)(PROTOCOL=tcp))
                                (CONNECT_DATA=(SERVICE_NAME=pdb1)))" 
    user="hr"
    password="hr"

    
    

    <?xml version="1.0" encoding="UTF-8"?>
    <ucp-properties>
            <connection-pool 
                    connection-pool-name="ucp_pool1" 
                    connection-factory-class-name="oracle.jdbc.pool.OracleDataSource" 
                    url="jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(PORT=1521)(HOST=localhost))(CONNECT_DATA=(SERVICE_NAME=pdb1)))";
                    user = "hr";
                    passwd = "hr"
                    initial-pool-size="10" 
                    min-pool-size="5"  
                    max-pool-size="20" 
                    validate-connection-on-borrow="true" 
                    sql-for-validate-connection="select 1 from dual"
                    shared = "true" 
            >
            <data-source 
                    data-source-name="UCPDatasource" 
                    description="UCP datasource"
            >
            </data-source>
    </connection-pool>
    </ucp-properties>
    

16. Navigate to apache-tomcat-9.0.0.M8/webapps/UCPConfigXML directory. Execute ls to see the contents inside this directory.

    cd /usr/apache-tomcat-9.0.0.M8/webapps/UCPConfigXML
    ls

17. Edit the build.xml file.

    vi build.xml

18. Modify the TOMCAT_HOME property value and give the current tomcat home path. Save the file.

    <property name="TOMCAT_HOME" value="/usr/apache-tomcat-9.0.0.M8"/>

    <project name="UCPTomcatDemo" default="all">
            <property name="classes" value="WEB-INF/classes" />
            <property name="src" value="src" />
            <property name="dist" value="dist" />
    <property name="TOMCAT_HOME" value="/usr/apache-tomcat-9.0.0.M8"/>
    
      <path id="UCPTomcatDemo_classpath">
        <pathelement location="${classes}"/>
        <fileset dir="${TOMCAT_HOME}/lib" includes="*.jar" />
        <fileset dir="WEB-INF/lib" includes="*.jar" />
    </path>
    
      <target name="displayMessage">
            <echo message="-------------------------------------------------"/>
            <echo message="        BUILDING UCP Tomcat Demo                 "/>
            <echo message="-------------------------------------------------"/>
        </target>
    
    <target name="clean">
    <delete dir="${classes}"/>
    <delete dir="${dist}" />
    </target>
    ....
    
    

19. Navigate to apache-tomcat-9.0.0.M8/bin directory. Execute ls to see the contents inside this directory.

    cd /usr/apache-tomcat-9.0.0.M8/bin

    $ cd /usr/apache-tomcat-9.0.0.M8/bin/
    $ ls
    bootstrap.jar                 daemon.sh         startup.sh
    catalina.bat                  digest.bat        tomcat-juli.jar
    catalina.sh                   digest.sh         tomcat-native.tar.gz
    catalina-tasks.xml            setclasspath.bat  tool-wrapper.bat
    commons-daemon.jar            setclasspath.sh   tool-wrapper.sh
    commons-daemon-native.tar.gz  shutdown.bat      version.bat
    configtest.bat                shutdown.sh       version.sh
    configtest.sh                 startup.bat

20. Edit the catalina.sh file.

    vi catalina.sh

21. Modify the JAVA_OPTS value. Give the path of InitialConfig.xml file.

    JAVA_OPTS="$JAVA_OPTS -Doracle.ucp.jdbc.xmlConfigFile='file:///usr/apache-tomcat-9.0.0.M8/webapps/UCPConfigXML/META-INF/InitialConfig.xml'"

    

    
    

22. Start the tomcat.

    ./startup.sh

    $ ./startup.sh
    Using CATALINA_BASE:   /usr/apache-tomcat-9.0.0.M8
    Using CATALINA_HOME:   /usr/apache-tomcat-9.0.0.M8
    Using CATALINA_TMPDIR: /usr/apache-tomcat-9.0.0.M8/temp
    Using JRE_HOME:        /usr/java/jdk1.8.0_91
    Using CLASSPATH:       /usr/apache-tomcat-9.0.0.M8/bin/bootstrap.jar:/usr/apache-tomcat-9.0.0.M8/bin/tomcat-juli.jar
    Tomcat started.

23. Open Firefox web browser and navigate to the below link. This link will contain the output of the demo.

    http://localhost:8085/UCPConfigXML/DemoServlet

    

    Notice that the output is generated by executing the Java file present inside the UCPConfigXML.

In this section you will:

• Deploy a Tomcat/UCP workload (Java servlets) in Oracle RAC and Active Data Guard (ADG) environments
• Configure Oracle RAC & ADG and UCP for planned maintenance
• Configure Oracle RAC & ADG and UCP for unplanned downtime
• Confgure Oracle RAC & ADG for runtime load balancing

Perform the following operations:

1. Open a new terminal. Navigate to the u01/oracle/jdbchol/UCPTomcat_Demo directory. Execute ls to see the contents inside this directory.

   cd /u01/oracle/jdbchol/UCPTomcat_Demo

2. Copy the UCPTomcat.war file from your working directory to the webapps folder of Tomcat. Open the terminal and run this command:

   cp UCPTomcat.war /usr/apache-tomcat-9.0.0.M8/webapps 
   ls /usr/apache-tomcat-9.0.0.M8/webapps

3. Start the Tomcat server. Tomcat can be started by executing the startup.sh file from the bin folder.

   cd /usr/apache-tomcat-9.0.0.M8/bin
   ./startup.sh

   $ cd /usr/apache-tomcat-9.0.0.M8/bin/
   $ ./startup.sh 
   Using CATALINA_BASE:   /usr/apache-tomcat-9.0.0.M8
   Using CATALINA_HOME:   /usr/apache-tomcat-9.0.0.M8
   Using CATALINA_TMPDIR: /usr/apache-tomcat-9.0.0.M8/temp
   Using JRE_HOME:        /usr/java/jdk1.8.0_91
   Using CLASSPATH:       /usr/apache-tomcat-9.0.0.M8/bin/bootstrap.jar:/usr/apache-tomcat-9.0.0.M8/bin/tomcat-juli.jar
   Tomcat started.
   

   Starting the Tomcat server will automatically unzip the contents of the UCPTomcat.war file and create a folder called UCPTomcat with all the contents.

4. Navigate into the apache-tomcat-9.0.0.M8/webapps directory. Execute ls to see the contents inside this directory. Notice that a new directory UCPTomcat with the same name as the UCPTomcat.war file got created with contents in it. 

   cd /usr/apache-tomcat-9.0.0.M8/webapps
   ls

   $ cd /usr/apache-tomcat-9.0.0.M8/webapps/
   $ ls
   docs      host-manager  ROOT    UCPConfigXML.war UCPTomcat.war
   examples  manager       UCPConfigXML      UCPTomcat
   

5. Open the UCPTomcat folder and edit the context.xml file under the META-INF folder. The context.xml file consists of the UCP properties. Replace the url, user, password and onsConfiguration to point to your own database instance.

   cd UCPTomcat/META-INF/

   $ cd UCPTomcat/META-INF/
   $ ls                                                                                                                        context.xml   MANIFEST.MF     war-tracker                                                                                                                                                                                                               $ vi context.xml
   

<Context docBase="UCPTomcat" path="/UCPTomcat"
   reloadable="true" source="org.eclipse.jst.jee.server:UCPTomcat">
<Resource name="tomcat/UCPPool" auth="Container"
   factory="oracle.ucp.jdbc.PoolDataSourceImpl" 
   type="oracle.ucp.jdbc.PoolDataSource"
   description="UCP Pool in Tomcat" 
   connectionFactoryClassName="oracle.jdbc.replay.OracleDataSourceImpl"
   minPoolSize="2" 
   maxPoolSize="60" 
   initialPoolSize="15" 
   autoCommit="true"
   user="hr"
   password="hr" 
   fastConnectionFailoverEnabled="true"
   url="jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(HOST=localhost)(PORT=1521)(PROTOCOL=tcp))(CONNECT_DATA=(SERVICE_NAME=pdb1)))"
    onsConfiguration="nodes=host01:6250"
/>

<context-param>
        <param-name>oracle.ucp.PlannedDrainingPeriod</param-name>
        <param-value>30</param-value>
</context-param>

Update the startcyclops file in the UCPTomcat folder with the corresponding RAC or Database nodes.

$ vi startcyclops

!/bin/sh
java -Dstore_stats=true -Xbootclasspath/a:$SRCHOME/opmn/lib/ons.jar:. -jar WEB-INF/lib/cyclops.jar -ONS="nodes=host01:6250" -csvstats 5556:host01.example.com:6150

Open the build.xml file present in the UCPTomcat directory and change the value of TOMCAT_HOME.

<project name="UCPTomcatDemo" default="all">
        <property name="classes" value="WEB-INF/classes" />
        <property name="src" value="src" />
        <property name="dist" value="dist" />
<property name="TOMCAT_HOME" value="/usr/apache-tomcat-9.0.0.M8" />

  <path id="UCPTomcatDemo_classpath">
    <pathelement location="${classes}"/>
    <fileset dir="${TOMCAT_HOME}/lib" includes="*.jar" />
    <fileset dir="WEB-INF/lib" includes="*.jar" />
</path>

  <target name="displayMessage">
        <echo message="-------------------------------------------------"/>
        <echo message="        BUILDING UCP Tomcat Demo                 "/>
        <echo message="-------------------------------------------------"/>
    </target>

<target name="clean">
<delete dir="${classes}"/>
<delete dir="${dist}" />
</target>




6. Navigate to the UCPTomcat folder in the webapps directory and run the demo.

   cd /u01/oracle/apache-tomcat-9.0.0.M8/webapps/UCPTomcat/
   

   $ ant
   Buildfile: /usr/apache-tomcat-9.0.0.M8/webapps/UCPTomcat/build.xml
   
   displayMessage:
        [echo] -------------------------------------------------
        [echo]         BUILDING UCP Tomcat Demo                 
        [echo] -------------------------------------------------
   
   clean:
      [delete] Deleting directory /usr/apache-tomcat-9.0.0.M8/webapps/UCPTomcat/WEB-INF/classes
      [delete] Deleting directory /usr/apache-tomcat-9.0.0.M8/webapps/UCPTomcat/dist
   
   init:
       [mkdir] Created dir: /usr/apache-tomcat-9.0.0.M8/webapps/UCPTomcat/WEB-INF/classes
   
   compile:
       [javac] Compiling 3 source files to /usr/apache-tomcat-9.0.0.M8/webapps/UCPTomcat/WEB-INF/classes
   
   displayMessage2:
        [echo] --------------------------------------------------
        [echo]                      CREATING a WAR FILE          
        [echo] --------------------------------------------------
   
   prepare_war:
       [mkdir] Created dir: /usr/apache-tomcat-9.0.0.M8/webapps/UCPTomcat/dist
         [jar] Building jar: /usr/apache-tomcat-9.0.0.M8/webapps/UCPTomcat/dist/UCPTomcat.war
   
   all:
   
   BUILD SUCCESSFUL
   Total time: 1 second

7. Start cyclops to monitor the connections. The startcyclops file is an executable to start the cyclops which will demonstrate how the load balancing is done between two instances or available instances.

   chmod +x startcyclops
   ./startcyclops

   $ ./startcyclops
   16/07/21-06:32:18 ONS: Node Lists
   NodeList default[1] active
   Connection default-host01:6250 status=Connecting alive=false
   Proxy (#1) started

   Open another terminal and run initializeLoad to add more threads and add more load.

   chmod +x initializeLoad
   ./initializeLoad

8. Check if the servlet is up:

   http://localhost:<portnumber>/UCPTomcat/DemoServlet

   Replace the <portnumber> with the port that Tomcat is configured to use on your system.

   

   
   

   Observe the output.

1. Open a new terminal. Navigate to the u01/oracle/jdbchol/TG_Demo directory. Execute ls to see the contents inside this directory.

   cd /u01/oracle/jdbchol/TG_Demo

   $ cd /u01/oracle/jdbchol/TG_Demo/
   $ ls
   build.xml  lib  src  TGDemo_setup.sql

2. Open and examine the TGDemo_setup.sql file.

   cat TGDemo_setup.sql

   $ cat TGDemo_setup.sql
   ---TGDemo_setup.sql
   
   rem Copyright (c) 2013, Oracle and/or its affiliates. All rights reserved.
   
   set echo on
   
   connect sys/Welcome1@pdb1 as sysdba
   
   execute dbms_service.stop_service(service_name => 'pdb1_tg');
   execute dbms_service.delete_service(service_name => 'pdb1_tg');
   
   execute dbms_service.create_service(service_name => 'pdb1_tg', network_name => 'pdb1_tg');
   execute dbms_service.start_service(service_name => 'pdb1_tg');
   
   --rem modify service to enable TG
   
   declare
   params dbms_service.svc_parameter_array;
   begin
   params('commit_outcome'):='true';
   dbms_service.modify_service('pdb1_tg',params);
   end;
   /
   ...
   

   
   

3. Login to Oracle with SYSDBA crerdentials. Execute the TGDemo_setup.sql file. Exit from sql after execution.

   
   

   $ sqlplus / as sysdba
   
   SQL*Plus: Release 12.2.0.1.0 Production on Mon Jul 25 10:44:26 2016
   
   Copyright (c) 1982, 2016, Oracle.  All rights reserved.
   
   
   Connected to:
   Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
   
   SQL> @TGDemo_setup.sql
   SQL> 
   SQL> connect sys/Welcome1@pdb1 as sysdba
   Connected.
   SQL> 
   SQL> execute dbms_service.stop_service(service_name => 'pdb1_tg');
   BEGIN dbms_service.stop_service(service_name => 'pdb1_tg'); END;                                                            ...
   

   
   

4. Open and examine the TGDemo.java file.

   cat src/TGDemo.java

   Output:

   /* Copyright (c) 2013, Oracle and/or its affiliates. All rights reserved. */
   
   import java.sql.CallableStatement;
   import java.sql.Connection;
   import java.sql.DriverManager;
   import java.sql.SQLException;
   import java.sql.SQLRecoverableException;
   import java.sql.Statement;
   import java.sql.Types;
   
   import oracle.jdbc.LogicalTransactionId;
   import oracle.jdbc.OracleConnection;
   import oracle.jdbc.pool.OracleDataSource;
   
   /**
    * This demo illustrates how application utilizes the Oracle 12c
    * Transaction Guard (TG) feature in Java.
    *
    * It covers two scenarios: with auto-commit mode ON and OFF on the
    * connection used. In each scenario, application does a database
    * update and starts a local transaction; an outage is injected
    * and affects the transaction commit.
    *
    * After outage, application uses the TG APIs to first retrieve
    * the logical transaction id (LTXID) from the original connection,
    * and then obtains the outcome information for that LTXID from
    * the Oracle database server.
    *
    * For more information regarding TG, please refer to Oracle 12c
    * documentation including Oracle JDBC driver's javadoc.
    *
    * @author tzhou
    */
   public class TGDemo {
   
     static final String host = "localhost";
     static final String port = "1521";
     static final String service = "pdb1_tg";
     static final String jdbcURL =
       "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)"+"(HOST="+host+")(PORT="+port+"))(CONNECT_DATA=(SERVICE_NAME="+service+")))";
   
     static final String user = "hr";
     static final String passwd = "hr";
   
     static OracleDataSource ods = null;
   
     public static void main(String[] args) throws Exception {
   
       show("== Demo begins ==");
   
       TGDemo demo = new TGDemo();
       demo.test();
   
       show("\n== Demo completes ==");
     }
   
     void test() throws Exception {
   
       test1(true);  // auto-commit ON
       test1(false); // auto-commit OFF
     }
   
     void setup(OracleConnection conn) throws Exception {
   
       show("\n  Setting up tables");
   
       trySQL(conn, "drop table TGDemo_tab");
       doSQL(conn, "create table TGDemo_tab (c1 number, c2 varchar2(20))");
     }
   
     OracleConnection getOneConnection() throws Exception {
   
       show("\nConnect URL: " + jdbcURL);
   
       if (ods == null) {
         ods = new OracleDataSource();
         ods.setURL(jdbcURL);
         ods.setUser(user);
         ods.setPassword(passwd);
       }
   
       final OracleConnection newConn = (OracleConnection) ods.getConnection();
       show("\n  Obtained a new connection");
   
       return newConn;
     }
   
     void test1(boolean autoCommit) throws Exception {
   
       String testDescription = autoCommit ? "auto-commit ON" : "auto-commit OFF";
       show("\n-- Test case: "+testDescription+" --");
   
       try
       {
         OracleConnection conn = getOneConnection();
         setup(conn);
   
         conn.setAutoCommit(autoCommit);
         show ("\n  Connection.getAutoCommit() = " + conn.getAutoCommit());
   
         Statement stmt = conn.createStatement();
         if (autoCommit)
           injectOutage(conn);
   
         try {
           stmt.execute("insert into TGDemo_tab values(200, 'value200')");
           show("\n  Perform database update");
   
           if (!autoCommit) {
             injectOutage(conn);
   
             show("\n  Committing local transaction");
             conn.commit();
             show("\n  Local transaction is committed");
           }
         } catch (SQLRecoverableException sqlrexc) {
   
           show("\nConnection hit by outage");
   
           LogicalTransactionId ltxid = getLtxid(conn);
           show("  Current LTXID: " + ((ltxid==null) ? "null" : ltxid));
   
           show ("\n  " + checkLtxidOutcome(ltxid));
   
           // Optional: recovery actions by app
         }
   
         show ("\n  Close the connection");
         conn.abort();
         conn.close();
   
       } catch (SQLException e) {
         e.printStackTrace();
       }
     }
   
     LogicalTransactionId getLtxid(OracleConnection oconn)
       throws Exception {
   
       show ("\n  Obtaining LTXID from connection hit by outage");
       LogicalTransactionId ltxid = oconn.getLogicalTransactionId();
   
       return ltxid;
     }
   
     String checkLtxidOutcome(LogicalTransactionId ltxid)
       throws Exception {
   
       OracleConnection newConn = getOneConnection();
       String ret = checkLtxidOutcome(newConn, ltxid);
       newConn.close();
   
       return ret;
     }
   
     String checkLtxidOutcome(
       Connection conn,
       LogicalTransactionId ltxid)
       throws Exception {
   
       String ret = "";
       CallableStatement cstmt = null;
       try {
         String call = "{call get_ltxid_outcome_wrap(?, ?, ?) } ";
         cstmt = conn.prepareCall(call);
         cstmt.setBytes(1, ltxid.getBytes());
         cstmt.registerOutParameter(2, Types.INTEGER);
         cstmt.registerOutParameter(3, Types.INTEGER);
         cstmt.execute();
   
         int committed = cstmt.getInt(2);
         int user_call_completed = cstmt.getInt(3);
         ret = "LTXID OUTCOME: committed="+(committed==1? "true":"false")+
           " user_call_completed="+(user_call_completed==1? "true":"false");
       } catch (SQLException e) {
         ret = e.toString();
       } finally {
         try {
           cstmt.close();
         } catch (SQLException e) { }
       }
   
       return ret;
     }
   
     void injectOutage(OracleConnection conn) throws Exception {
   
       show("\nOutage happens");
   
       Connection sysConn =
         DriverManager.getConnection(jdbcURL, "system", "oracle");
       String sql = "begin DBMS_SERVICE.DISCONNECT_SESSION('"+service+"', 1); end;";
       doSQL(sysConn, sql);
       sysConn.close();
     }
   /*
   
     // Alternative implementation that injects outage from server-side
     void injectOutage(OracleConnection conn) {
   
       show("\nOutage happens");
   
       CallableStatement cstmt = null;
       try {
         String call = "{call reset_tst_outage_tbl } ";
         cstmt = conn.prepareCall(call);
         cstmt.execute();
   
         call = "{call inject_outage_once } ";
         cstmt = conn.prepareCall(call);
         cstmt.execute();
       } catch (SQLException e) {
         // Outage triggers this exception
         // e.printStackTrace();
       } finally {
         try {
           cstmt.close();
         } catch (SQLException e) { }
       }
     }
   
   */
     void trySQL(Connection connection, String sql)
       throws SQLException {
   
       Statement stmt = connection.createStatement ();
       try {
         stmt.execute (sql);
       } catch (SQLException e) {
   
       } finally {
         stmt.close ();
       }
     }
   
     void doSQL(Connection connection, String sql)
       throws SQLException {
   
       Statement stmt = null;
       try {
         stmt = connection.createStatement ();
         stmt.execute (sql);
       } finally {
         if (stmt != null)
           stmt.close();
       }
     }
   
     static void show(String msg) {
       System.out.println(msg);
     }
   }
   

   
   

5. Use ant to build and run the build.xml file.

   ant

   Observe the output.

   /*
   Buildfile: /u01/oracle/jdbchol/TG_Demo/build.xml
   
   print.properties:
        [echo] TGDemo: Sample demonstrating Transaction Guard
        [echo]    Look at Readme.txt for details
   
   init:
      [delete] Deleting directory /u01/oracle/jdbchol/TG_Demo/classes
       [mkdir] Created dir: /u01/oracle/jdbchol/TG_Demo/classes
   
   run:
       [javac] Compiling 1 source file to /u01/oracle/jdbchol/TG_Demo/classes
        [java] == Demo begins ==
        [java] 
        [java] -- Test case: auto-commit ON --
        [java] 
        [java] Connect URL: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=pdb1_tg)))
        [java] 
        [java]   Obtained a new connection
        [java] 
        [java]   Setting up tables
        [java] 
        [java]   Connection.getAutoCommit() = true
        [java] 
        [java] Outage happens
        [java] 
        [java] Connection hit by outage
        [java] 
        [java]   Obtaining LTXID from connection hit by outage
        [java]   Current LTXID: oracle.jdbc.driver.LogicalTransactionId@6f7fd0e6
        [java] 
        [java] Connect URL: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=pdb1_tg)))
        [java] 
        [java]   Obtained a new connection
        [java] 
        [java]   LTXID OUTCOME: committed=false user_call_completed=false
        [java] 
        [java]   Close the connection
        [java] 
        [java] -- Test case: auto-commit OFF --
        [java] 
        [java] Connect URL: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=pdb1_tg)))
        [java] 
        [java]   Obtained a new connection
        [java] 
        [java]   Setting up tables
        [java] 
        [java]   Connection.getAutoCommit() = false
        [java] 
        [java]   Perform database update
        [java] 
        [java] Outage happens
        [java] 
        [java]   Committing local transaction
        [java] 
        [java] Connection hit by outage
        [java] 
        [java]   Obtaining LTXID from connection hit by outage
        [java]   Current LTXID: oracle.jdbc.driver.LogicalTransactionId@2698dc7
        [java] 
        [java] Connect URL: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=pdb1_tg)))
        [java] 
        [java]   Obtained a new connection
        [java] 
        [java]   LTXID OUTCOME: committed=false user_call_completed=false
        [java] 
        [java]   Close the connection
        [java] 
        [java] == Demo completes ==
   
   BUILD SUCCESSFUL
   Total time: 2 seconds
   [oracle@host01 TG_Demo]$ 

To test Application Continuity in Java, you execute two copies of the same application, one using the normal JDBC driver and the other using the 'Replay' JDBC driver with a Replay datasource. Upon database failure, the Replay driver masks the outage to the application with slight additional latency, while the standard JDBC driver throws a SQLException to the application. The database failure in a single database instance is simulated by killing the session's foreground processes.

1. Open a new terminal. Navigate to the u01/oracle/jdbchol/AC_Demo directory. Execute ls to see the contents inside this directory.

   cd /u01/oracle/jdbchol/AC_Demo

   $ cd /u01/oracle/jdbchol/AC_Demo/
   $ ls
   actest.jar                  actest_replay.properties  runnoreplay
   actest_noreplay.properties  actest_setup.sql          runreplay
   

2. Open and examine the actest_setup.sql file.

   cat actest_setup.sql

   $ cat actest_setup.sql 
   
   rem Copyright (c) 2012, 2013, Oracle and/or its affiliates. All rights reserved.
   
   set echo on
   
   connect sys/Welcome1@pdb1 as sysdba
   
   execute dbms_service.stop_service(service_name => 'pdb1_ac');
   execute dbms_service.delete_service(service_name => 'pdb1_ac');
   
   execute dbms_service.create_service(service_name => 'pdb1_ac', network_name => 'pdb1_ac');
   execute dbms_service.start_service(service_name => 'pdb1_ac');
   
   --rem modify service to enable AC
   
   declare
   params dbms_service.svc_parameter_array;
   begin
   params('FAILOVER_TYPE'):='TRANSACTION';
   params('REPLAY_INITIATION_TIMEOUT'):=1800;
   params('RETENTION_TIMEOUT'):=86400;
   params('FAILOVER_DELAY'):=10;
   params('FAILOVER_RETRIES'):=30;
   params('commit_outcome'):='true';
   params('aq_ha_notifications'):='true';
   dbms_service.modify_service('pdb1_ac',params);
   end;
   /
   commit;
   
   connect hr/hr@pdb1;
   
   drop table emp;
   create table emp(
    empno number(4) not null,
    ename varchar2(10),
    job char(9),
    mgr number(4),
    hiredate date,
    sal number(7,2),
    comm number(7,2),
    deptno number(2));
   
   insert into emp values(8839,'KING','PRESIDENT',NULL,'17-NOV-81',50000,NULL,10);
   insert into emp values(8698,'BLAKE','MANAGER',NULL,'17-NOV-81',8000,NULL,10);
   insert into emp values(8782,'CLARK','MANAGER',NULL,'17-NOV-81',8000,NULL,10);
   insert into emp values(8566,'JONES','MANAGER',NULL,'17-NOV-81',8000,NULL,10);
   insert into emp values(8654,'MARTIN','SALESMAN',NULL,'17-NOV-81',7000,NULL,10);
   insert into emp values(8499,'ALLEN','MANAGER',NULL,'17-NOV-81',9000,NULL,10);
   insert into emp values(8844,'TURNER','CLERK',NULL,'17-NOV-81',5000,NULL,10);
   insert into emp values(8900,'JAMES','MANAGER',NULL,'17-NOV-81',9000,NULL,10);
   insert into emp values(8521,'WARD','PRGRMMER',NULL,'17-NOV-81',9000,NULL,10);
   insert into emp values(8902,'FORD','SALESMAN',NULL,'17-NOV-81',7000,NULL,10);
   insert into emp values(8369,'SMITH','PRGRMMER',NULL,'17-NOV-81',8000,NULL,10);
   insert into emp values(8788,'SCOTT','CLERK',NULL,'17-NOV-81',6000,NULL,10);
   insert into emp values(8876,'ADAMS','PRGRMMER',NULL,'17-NOV-81',7000,NULL,10);
   insert into emp values(8934,'MILLER','SALESMAN',NULL,'17-NOV-81',9000,NULL,10);
   
   commit;
   
   disconnect
   
   

3. Login to Oracle. Execute the actest_setup.sql file. Exit from sql after execution.

   sqlplus / as sysdba

   @actest_setup.sql

   exit

   $ sqlplus / as sysdba
                              
   SQL*Plus: Release 12.2.0.1.0 Production on Tue Jul 26 10:22:34 2016
   
   Copyright (c) 1982, 2016, Oracle.  All rights reserved.
   
   Connected to:
   Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
   
   SQL> @actest_setup.sql
   SQL> 
   SQL> connect sys/Welcome1@pdb1 as sysdba
   Connected.
   SQL> 
   SQL> execute dbms_service.stop_service(service_name => 'pdb1_ac');
   BEGIN dbms_service.stop_service(service_name => 'pdb1_ac'); END;
   
   *
   ERROR at line 1:
   ORA-44304: service pdb1_ac does not exist
   ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
   ORA-06512: at "SYS.DBMS_SERVICE_ERR", line 23
   ORA-06512: at "SYS.DBMS_SERVICE", line 519
   ORA-06512: at line 1
   
   
   SQL> execute dbms_service.delete_service(service_name => 'pdb1_ac');
   BEGIN dbms_service.delete_service(service_name => 'pdb1_ac'); END;
   
   *
   ERROR at line 1:
   ORA-44304: service pdb1_ac does not exist
   ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
   ORA-06512: at "SYS.DBMS_SERVICE_ERR", line 23
   ORA-06512: at "SYS.DBMS_SERVICE", line 453
   ORA-06512: at line 1
   
   
   SQL> 
   SQL> execute dbms_service.create_service(service_name => 'pdb1_ac', network_name => 'pdb1_ac');
   
   PL/SQL procedure successfully completed.
   
   SQL> execute dbms_service.start_service(service_name => 'pdb1_ac');
   
   PL/SQL procedure successfully completed.
   
   SQL> 
   SQL> --rem modify service to enable AC
   SQL> 
   SQL> declare
     2  params dbms_service.svc_parameter_array;
     3  begin
     4  params('FAILOVER_TYPE'):='TRANSACTION';
     5  params('REPLAY_INITIATION_TIMEOUT'):=1800;
     6  params('RETENTION_TIMEOUT'):=86400;
     7  params('FAILOVER_DELAY'):=10;
     8  params('FAILOVER_RETRIES'):=30;
     9  params('commit_outcome'):='true';
    10  params('aq_ha_notifications'):='true';
    11  dbms_service.modify_service('pdb1_ac',params);
    12  end;
    13  /
   
   PL/SQL procedure successfully completed.
   
   SQL> commit;
   
   Commit complete.
   
   SQL> 
   SQL> connect hr/hr@pdb1;
   Connected.
   SQL> 
   SQL> drop table emp;
   
   Table dropped.
   
   SQL> create table emp(
     2   empno number(4) not null,
     3   ename varchar2(10),
     4   job char(9),
     5   mgr number(4),
     6   hiredate date,
     7   sal number(7,2),
     8   comm number(7,2),
     9   deptno number(2));
   
   Table created.
   
   SQL> 
   SQL> insert into emp values(8839,'KING','PRESIDENT',NULL,'17-NOV-81',50000,NULL,10);
   
   1 row created.
   
   SQL> insert into emp values(8698,'BLAKE','MANAGER',NULL,'17-NOV-81',8000,NULL,10);
   
   1 row created.
   
   SQL> insert into emp values(8782,'CLARK','MANAGER',NULL,'17-NOV-81',8000,NULL,10);
   
   1 row created.
   
   SQL> insert into emp values(8566,'JONES','MANAGER',NULL,'17-NOV-81',8000,NULL,10);
   
   1 row created.
   
   SQL> insert into emp values(8654,'MARTIN','SALESMAN',NULL,'17-NOV-81',7000,NULL,10);
   
   1 row created.
   
   SQL> insert into emp values(8499,'ALLEN','MANAGER',NULL,'17-NOV-81',9000,NULL,10);
   
   1 row created.
   
   SQL> insert into emp values(8844,'TURNER','CLERK',NULL,'17-NOV-81',5000,NULL,10);
   
   1 row created.
   
   SQL> insert into emp values(8900,'JAMES','MANAGER',NULL,'17-NOV-81',9000,NULL,10);
   
   1 row created.
   
   SQL> insert into emp values(8521,'WARD','PRGRMMER',NULL,'17-NOV-81',9000,NULL,10);
   
   1 row created.
   
   SQL> insert into emp values(8902,'FORD','SALESMAN',NULL,'17-NOV-81',7000,NULL,10);
   
   1 row created.
   
   SQL> insert into emp values(8369,'SMITH','PRGRMMER',NULL,'17-NOV-81',8000,NULL,10);
   
   1 row created.
   
   SQL> insert into emp values(8788,'SCOTT','CLERK',NULL,'17-NOV-81',6000,NULL,10);
   
   1 row created.
   
   SQL> insert into emp values(8876,'ADAMS','PRGRMMER',NULL,'17-NOV-81',7000,NULL,10);
   
   1 row created.
   
   SQL> insert into emp values(8934,'MILLER','SALESMAN',NULL,'17-NOV-81',9000,NULL,10);
   
   1 row created.
   
   SQL> 
   SQL> commit;
   
   Commit complete.
   
   SQL> 

4. Open and examine the actest_replay.properties file.

   cat actest_replay.properties

   
   $ cat actest_replay.properties 
   username=hr
   password=hr
   autoCommit=false
   
   # Use new 12.1 replay datasource
   datasource=oracle.jdbc.replay.OracleDataSourceImpl
   
   url=jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=pdb1_ac)))
   
   # UCP setting:
   ucp_pool_size=2
   ucp_validate_connection_on_borrow=true
   ucp_connection_wait_timeout=20
   
   # Think Time taken to process the results from the database. Time in milliseconds.
   # -1 means no sleep.
   thread_think_time=20
   
   # Number of concurrent threads running in the application
   # UCP is tuned to have MAX and MIN limit set to this
   number_of_threads=6
   
   verbose=true

5. Open and examine the actest_noreplay.properties file.

   cat actest_noreplay.properties

   
   $ cat actest_noreplay.properties 
   username=hr
   password=hr
   autoCommit=false
   
   # Use standard  12.1 datasource no replay
   datasource=oracle.jdbc.pool.OracleDataSource
   #replay_datasource=oracle.jdbc.replay.OracleDataSourceImpl
   
   url=jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=pdb1_ac)))
   
   # UCP setting:
   ucp_pool_size=2
   ucp_validate_connection_on_borrow=true
   ucp_connection_wait_timeout=20
   
   # Think Time taken to process the results from the database. Time in milliseconds.
   # -1 means no sleep.
   thread_think_time=20
   
   # Number of concurrent threads running in the application
   # UCP is tuned to have MAX and MIN limit set to this
   number_of_threads=6
   
   verbose=true

6. Open two terminal windows. Perform the following steps in both the terminals:

   1. In Both the terminals, Navigate to the u01/oracle/jdbchol/AC_Demo directory. Execute ls to see the contents inside this directory.

      cd /u01/oracle/jdbchol/AC_Demo

      $ cd /u01/oracle/jdbchol/AC_Demo/
      $ ls
      actest.jar                  actest_replay.properties  runnoreplay
      actest_noreplay.properties  actest_setup.sql          runreplay
      

   2. In Both the terminals, Setup the ORACLE_HOME environment variable to point to the Oracle database installation directory. Provide the given values when prompted.

      . oraenv

      ORACLE_SID = [orcl] ?  orcl

      $ . oraenv
      ORACLE_SID = [oracle] ? orcl
      The Oracle base has been set to /u01/app/oracle
      

   3. In One of the terminal execute runreplay and in the other terminal execute runnoreplay.

      sh runreplay

      sh runnoreplay

      $ sh runreplay
      ######################################################
      Connecting to jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=pdb1_ac)))
        # of Threads             : 6
        UCP pool size            : 2
        Thread think time        : 20 ms
      ######################################################
      
      2 active connections, avg response time from db 24 ms
      2 active connections, avg response time from db 28 ms
      2 active connections, avg response time from db 21 ms
      2 active connections, avg response time from db 20 ms
      2 active connections, avg response time from db 17 ms
      1 active connections, avg response time from db 17 ms
      2 active connections, avg response time from db 16 ms
      ...

      
      

      $ sh runreplay
      
      ######################################################
      Connecting to jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=pdb1_ac)))
        # of Threads             : 6
        UCP pool size            : 2
        Thread think time        : 20 ms
      ######################################################
      
      2 active connections, avg response time from db 32 ms
      2 active connections, avg response time from db 18 ms
      2 active connections, avg response time from db 18 ms
      2 active connections, avg response time from db 15 ms
      2 active connections, avg response time from db 15 ms
      2 active connections, avg response time from db 16 ms
      ...

      
      

      Note: In the first terminal window, execute the runnoreplay script. In the second window, execute the runreplay script. These scripts are found in the files folder that you extracted to your working directory as part of the prerequisites. Both the scripts perform a series of database operations in an infinite loop. At periodic intervals, they display the number of connections in the connection pool and the average response time for the database operations. Any exceptions encountered are displayed on the terminal's console.
      The runnoreplay script uses the standard JDBC driver while the runreplay script uses the Replay JDBC driver

      Observe the output.

      Note: Please donot close any terminals as it will be required in the "Simulating a Database Failure in the Application" section.

You can simulate database failure in code.

1. Login to Oracle. Execute the following PL/SQL procedure to disconnect all the connected database sessions. This simulates a database failure. Exit from sql after execution.

   sqlplus sys/Welcome1 as sysdba

   execute dbms_service.disconnect_session('pdb1_ac',dbms_service.immediate);

   exit

   $ sqlplus sys/Welcome1 as sysdba
   
   SQL*Plus: Release 12.2.0.1.0 Production on Thu Jul 28 06:26:34 2016
   
   Copyright (c) 1982, 2016, Oracle.  All rights reserved.
   
   
   Connected to:
   Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
   
   SQL> execute dbms_service.disconnect_session('pdb1_ac',dbms_service.immediate);
   
   PL/SQL procedure successfully completed.
   
   SQL> exit

   
   

Observe the output in all the terminals.

Note: Notice that the application running with the standard JDBC driver encounters a SQLException when the database session was disconnected. However, the application using the Replay JDBC driver executes successfully even when its database session has been disconnected. You will notice that there is a slightly larger latency immediately after the session has been has been terminated.

1. Open a new terminal. Navigate to the u01/oracle/jdbchol/SharedPoolTest directory. Execute ls to see the contents inside this directory.

   cd /u01/oracle/jdbchol/SharedPoolTest

   $ cd /u01/oracle/jdbchol/SharedPoolTest/
   $ ls
   build.xml  SharedPoolTest_setup.sql  src
   

   
   

2. Open and examine the SharedPoolTest_setup.sql file.

   cat SharedPoolTest_setup.sql

   $ cat SharedPoolTest_setup.sql 
   ---SharedPoolTest_setup.sql
   
   rem Copyright (c) 2013, Oracle and/or its affiliates. All rights reserved.
   
   set echo on
   connect sys/Welcome1 as sysdba
   set echo on
   drop user c##common_user;
   create user c##common_user identified by c##common_user default tablespace system quota unlimited on system container=all;
   grant connect, resource, unlimited tablespace, create any directory,drop any directory to c##common_user container=all;
   grant create view, create session, create synonym, create database link, create sequence,                                   CREATE TABLE to c##common_user container=all;
   show errors
   disconn
    

   
   

3. Login to Oracle with SYSDBA crerdentials. Execute the SharedPoolTest_setup.sql file. Exit from sql after execution.

   sqlplus / as sysdba

   exit

   $ sqlplus / as sysdba
   
   SQL*Plus: Release 12.2.0.1.0 Production on Thu Jul 28 06:31:30 2016
   
   Copyright (c) 1982, 2016, Oracle.  All rights reserved.
   
   
   Connected to:
   Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
   
   SQL> @SharedPoolTest_setup.sql
   SQL> 
   SQL> connect sys/Welcome1 as sysdba
   Connected.
   
   SQL> 
   SQL> set echo on
   SQL> 
   SQL> drop user c##common_user;
   
   User dropped.
   
   
   SQL> create user c##common_user identified by c##common_user default tablespace system quota unlimited on system container=all;
   
   User created.
   
    
   SQL> grant connect, resource, unlimited tablespace, create any directory,drop any directory to c##common_user container=all;
   
   Grant succeeded.
   
   
   SQL> grant create view, create session, create synonym, create database link, create sequence, CREATE TABLE to c##common_user container=all;
   
   Grant succeeded.
   
   
   SQL> show errors
   No errors.
   SQL> 
   Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
   SQL> 
   
   

   
   

4. Specify the initial configuration of Shared Pools through an XML configuration file.  You can specify the XML configuration file for UCP through the system property oracle.ucp.jdbc.xmlConfigFile. Open and examine the SharedPoolTest.xml file.
   

   cd /u01/oracle/jdbchol/SharedPoolTest/

   cat SharedPooltest.xml
   <?xml version="1.0" encoding="UTF-8"?>
   
   <ucp-properties>
   
   
   <connection-pool
   connection-pool-name="pool1"
   connection-factory-class-name="oracle.jdbc.pool.OracleDataSource"
   url="jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host01)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl)))"
   user="c##common_user" 
   password="c##common_user" 
   initial-pool-size="10"  
   min-pool-size="2" 
   max-pool-size="30" 
   max-connections-per-service="15" 
   shared="true">
   <connection-property value="20000" name="oracle.jdbc.ReadTimeout"/>
   
   <connection-property value="20000" name="oracle.net.OUTBOUND_CONNECT_TIMEOUT"/>
   
   <data-source description="pdb1 data source" service="pdb1" data-source-name="pds1"/>
   
   <data-source description="pdb2 data source" service="pdb2" data-source-name="pds2"/>
   
   </connection-pool>
   
   </ucp-properties>
   
   

5. Open and examine the SharedPoolTest.java file.

   cat src/SharedPoolTest.java

   $ cat src/SharedPoolTest.java
   
   
   /**
    * This sample test demonstrates the functionality of multi-tenant shared pools.
    * With the use of shared pools now it is possible for more than one datasources
    * to share a common pool provided they are connecting to the same database with
    * a single url. To use shared pool functionality, all the datasources
    * accessing shared pools must be defined in xml configuration file along with
    * the pool properties. Similar to shared pools, we can also define non-shared
    * pool configuration in xml. The code example shows how to get a connection from a
    * pool which is defined in xml, pool reconfiguration, datasource reconfiguration,
    * and adding a new datasource to running pool.
    * 
    */
   
   import java.sql.Connection;
   import java.sql.ResultSet;
   import java.sql.SQLException;
   import java.sql.Statement;
   import java.util.Properties;
   
   import oracle.ucp.admin.UniversalConnectionPoolManager;
   import oracle.ucp.admin.UniversalConnectionPoolManagerImpl;
   import oracle.ucp.jdbc.PoolDataSource;
   import oracle.ucp.jdbc.PoolDataSourceFactory;
   
   public class SharedPoolTest {
   
     static String xmlFileURI ="file:/u01/oracle/jdbchol/SharedPoolTest/SharedPoolTest.xml";
     static String poolName = "pool1";
   
     public static void main(String[] args) throws Exception {
       System.out
           .println("----------------- Multi-Tenant shared pool configuration using XML----------------------- ---");
   
       // Java system property to specify the location of xml configuration file
       // which has pool and datasource properties defined in it.
        System.setProperty("oracle.ucp.jdbc.xmlConfigFile", xmlFileURI);
   
       // The xml file used in this code example defines one connection pool with
       // connection-pool-name -"pool1" and two datasources with datasource-name
       // "pds1" and "pds2" which are using this shared pool.
   
       System.out
           .println("\n################ UseCase:1  Initial Pool/DataSource Configuration using XML     ###############");
   
       // Get the datasource instance, named as "pds1" in xml config file
       PoolDataSource pds1 = PoolDataSourceFactory.getPoolDataSource("pds1");
       Connection pds1Conn = pds1.getConnection();
   
       System.out.println("\nTest connection obtained using datasource1 -pds1");
       testConnection(pds1Conn);
       pds1Conn.close();
   
       // Get the datasource instance, named as "pds2" in xml config file
       PoolDataSource pds2 = PoolDataSourceFactory.getPoolDataSource("pds2");
       Connection pds2Conn = pds2.getConnection();
   
       System.out.println("\nTest connection obtained using datasource2 -pds2");
       testConnection(pds2Conn);
       pds2Conn.close();
   
       System.out.println("pds1.getAvailableConnectionsCount() :"
           + pds1.getAvailableConnectionsCount());
       System.out.println("pds1.getBorrowedConnectionsCount() :"
           + pds1.getBorrowedConnectionsCount());
   
       System.out
           .println(" \n\n################ UseCase:2 Pool Reconfiguration    ##########################");
   
       UniversalConnectionPoolManager pool_mgr;
       pool_mgr = UniversalConnectionPoolManagerImpl
           .getUniversalConnectionPoolManager();
   
       System.out.println("\nPool properties before pool reconfiguration");
       printPoolProperties(pds1);
   
       // reconfigure the connection pool to modify the pool properties, new pool
       // properties are passed to the API in form of key value pair.
       pool_mgr.reconfigureConnectionPool(poolName, getPoolReconfigProperties());
   
       System.out.println("\nPool properties after pool reconfiguration");
       printPoolProperties(pds1);
   
       System.out
           .println(" \n\n################ UseCase:3 Existing DataSource Reconfiguration   ##########################");
   
       System.out
           .println("\nDatasource1 - pds1 properties before datasource reconfiguration");
   
       printDataSourceProperties(pds1);
   
       // reconfigure the existing datasource, new properties are passed as
       // key-value pair.
       pds1.reconfigureDataSource(getPDS1ReconfigProperties());
   
       System.out
           .println("\nDatasource1 - pds1 properties after datasource reconfiguration");
   
       printDataSourceProperties(pds1);
   
       System.out
           .println("\nDatasource2 - pds2 properties before datasource reconfiguration");
       printDataSourceProperties(pds2);
   
       // reconfigure the existing datasource, new properties are passed as
       // key-value pair.
       pds2.reconfigureDataSource(getPDS2ReconfigProperties());
   
       System.out
           .println("\nDatasource2 - pds2 properties after datasource reconfiguration");
       printDataSourceProperties(pds2);
   
       System.out
           .println(" \n\n################ UseCase:4 Add new DataSource to Running pool   ##########################");
   
       // adds a new datasource to pool, new datasource properties are passed as
       // key-value pair.
       PoolDataSource pds3 = PoolDataSourceFactory
           .getPoolDataSource(getNewDataSourceReconfigProperties());
       Connection pds3Conn = pds3.getConnection();
   
       System.out.println("\nTest connection obtained using new datasource -pds3");
       testConnection(pds3Conn);
       pds3Conn.close();
   
       System.out.println("\nPrint new datasource - pds3 properties");
       printDataSourceProperties(pds3);
   
       System.out
           .println("\n-------------------------------    End of Test Case -------------------------------------------------");
   
     }
   
     private static Properties getPoolReconfigProperties() {
       Properties properties = new Properties();
       properties.put("initialPoolSize", "12");
       properties.put("maxPoolSize", "40");
       return properties;
     }
   
     private static Properties getPDS1ReconfigProperties() {
       Properties properties = new Properties();
       properties.put("description", "pdb1 datasource reconfigured");
       properties.put("serviceName", "pdb2");
       return properties;
     }
   
     private static Properties getPDS2ReconfigProperties() {
       Properties properties = new Properties();
       properties.put("description", "pdb2 datasource reconfigured");
       properties.put("serviceName", "pdb1");
       return properties;
     }
   
     private static Properties getNewDataSourceReconfigProperties() {
       Properties properties = new Properties();
       properties.put("connectionPoolName", poolName);
       properties.put("dataSourceName", "pds3");
       properties.put("description", "pds3 datasource added at run time");
       properties.put("serviceName", "pdb1");
       return properties;
     }
   
     static void printDataSourceProperties(PoolDataSource pds) throws SQLException {
       System.out.println("DataSource Name = " + pds.getDataSourceName());
       System.out.println("Description = " + pds.getDescription());
       System.out.println("Service Name = " + pds.getServiceName());
     }
   
     static void printPoolProperties(PoolDataSource pds) throws SQLException {
       System.out.println("Max pool Size = " + pds.getMaxPoolSize());
       System.out.println("Initial pool Size = " + pds.getInitialPoolSize());
       System.out.println("Min pool Size = " + pds.getMinPoolSize());
     }
   
     static void testConnection(Connection conn) throws SQLException {
       Statement stmt = null;
       ResultSet rs = null;
       try {
         stmt = conn.createStatement();
         String query = "select sys_context('userenv', 'instance_name'),"
             + "sys_context('userenv', 'server_host'),"
             + "sys_context('userenv', 'service_name'),"
             + "sys_context('userenv', 'db_unique_name')" + ",user" + " from dual";
   
         rs = stmt.executeQuery(query);
   
         if (rs.next()) {
           String serviceName = rs.getString(3);
           String dbName = rs.getString(4);
           String userName = rs.getString(5);
           System.out.println("Connection Db name from sys context=" + dbName);
           System.out.println("Connection Svc name from sys context="
               + serviceName);
           System.out.println("Connection user Name :  " + userName);
         }
       } catch (SQLException sqlexc) {
         throw sqlexc;
       } finally {
         if (rs != null)
           rs.close();
         if (stmt != null)
           stmt.close();
       }
   
     }
   
   }
   
   

   
   

6. Use ant to build and run the build.xml file.

   ant

   $ ant
   Buildfile: /u01/oracle/jdbchol/SharedPoolTest/build.xml
   
   print.properties:
        [echo] Multitenant: Sample demonstrating Multitenant database
        [echo]    Look at Readme.txt for details
   
   init:
      [delete] Deleting directory /u01/oracle/jdbchol/SharedPoolTest/classes
       [mkdir] Created dir: /u01/oracle/jdbchol/SharedPoolTest/classes
   
   run:
       [javac] /u01/oracle/jdbchol/SharedPoolTest/build.xml:32: warning: 'includeantruntime' was not set, defaulting to build.sysclasspath=last; set to false for repeatable builds
       [javac] Compiling 1 source file to /u01/oracle/jdbchol/SharedPoolTest/classes
        [java] ----------------- Multi-Tenant shared pool configuration using XML----------------------- ---
        [java] 
        [java] ################ UseCase:1  Initial Pool/DataSource Configuration using XML     ###############
        [java] 
        [java] Test connection obtained using datasource1 -pds1
        [java] Connection Db name from sys context=orcl
        [java] Connection Svc name from sys context=pdb1
        [java] Connection user Name :  C##COMMON_USER
        [java] 
        [java] Test connection obtained using datasource2 -pds2
        [java] Connection Db name from sys context=orcl
        [java] Connection Svc name from sys context=pdb2
        [java] Connection user Name :  C##COMMON_USER
        [java] pds1.getAvailableConnectionsCount() :12
        [java] pds1.getBorrowedConnectionsCount() :0
        [java]  
        [java] 
        [java] ################ UseCase:2 Pool Reconfiguration    ##########################
        [java] 
        [java] Pool properties before pool reconfiguration
        [java] Max pool Size = 30
        [java] Initial pool Size = 10
        [java] Min pool Size = 2
        [java] 
        [java] Pool properties after pool reconfiguration
        [java] Max pool Size = 40
        [java] Initial pool Size = 12
        [java] Min pool Size = 2
        [java]  
        [java] 
        [java] ################ UseCase:3 Existing DataSource Reconfiguration   ##########################
        [java] 
        [java] Datasource1 - pds1 properties before datasource reconfiguration
        [java] DataSource Name = pds1
        [java] Description = pdb1 data source
        [java] Service Name = pdb1
        [java] 
        [java] Datasource1 - pds1 properties after datasource reconfiguration
        [java] DataSource Name = pds1
        [java] Description = pdb1 datasource reconfigured
        [java] Service Name = pdb2
        [java] 
        [java] Datasource2 - pds2 properties before datasource reconfiguration
        [java] DataSource Name = pds2
        [java] Description = pdb2 data source
        [java] Service Name = pdb2
        [java] 
        [java] Datasource2 - pds2 properties after datasource reconfiguration
        [java] DataSource Name = pds2
        [java] Description = pdb2 datasource reconfigured
        [java] Service Name = pdb1
        [java]  
        [java] 
        [java] ################ UseCase:4 Add new DataSource to Running pool   ##########################
        [java] 
        [java] Test connection obtained using new datasource -pds3
        [java] Connection Db name from sys context=orcl
        [java] Connection Svc name from sys context=pdb1
        [java] Connection user Name :  C##COMMON_USER
        [java] 
        [java] Print new datasource - pds3 properties
        [java] DataSource Name = pds3
        [java] Description = pds3 datasource added at run time
        [java] Service Name = pdb1
        [java] 
        [java] -------------------------------    End of Test Case -------------------------------------------------
   
   BUILD SUCCESSFUL
   Total time: 21 seconds
   

Observe the output.

OracleDataSource (JDBC) and PoolDataSource (UCP) furnish the following APIs for building shard keys. subkey is the key used for partitioning the data. Note that for composite sharding, superShardingKey is created using the same method createShardingKeyBuilder().

OracleShardingKey shardKey =  pds.createShardingKeyBuilder()
                                     .subkey(<email>, OracleType.VARCHAR2) 
                                     .subkey(<custid>, OracleType.NUMBER)
                                     .build();

OracleShardingKey shardKey =  ods.createShardingKeyBuilder() 
                                     .subkey(<email>, OracleType.VARCHAR2) 
                                     .subkey(<custid>, OracleType.NUMBER)
                                     .build();

JDBC Connection with Sharding Key feature creates a JDBC connection to a Shard based on the sharding key.

OracleDataSource ods = new OracleDataSource();
ods.setURL(<DB_URL>);
ods.setUser(<DB_USER>);
ods.setPassword(<DB_PASSWORD>);
Connection connection = ods.createConnectionBuilder()
                                 .shardingKey(<OracleShardingKey>)
                                 .build();

Pool creation feature retreives a connection from UCP based on the Sharding key.

PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource();

pds.setURL(<DB_URL>);
pds.setUser(<DB_USER>);
pds.setPassword(<DB_PASSWORD>);
pds.initialPoolSize(10);
pds.maxPoolSize(20);
pds.minPoolSize(5);

//Connection borrowing
Connection connection = pds.createConnectionBuilder()
                                .shardingKey(<OracleShardingKey>) 
                                .build();

Single shardJDBC can be accessed using Connection String.The connection URL string takes the SHARDING_KEY and the SUPER_SHARDING_KEY as additional attributes.

jdbc:oracle:thin:@(DESCRIPTION=
			(ADDRESS=
				(HOST=bpodb12s)
				(PORT=1571)
				(PROTOCOL=tcp)
			)
			(CONNECT_DATA=
				(SERVICE_NAME=oltp_rw_srvc.cust_sdb.oradbcloud)
				(SHARDING_KEY=test@test.com)
				(SUPER_SHARDING_KEY=gold)
			)
		)

Capability to set the sharding key even after a connection is obtained is also provided.

connection.setShardingKey(<ShardingKey>, <SuperShardingKey>)
connection.setShardingKeyIfValid(<sharding_key>,<super_sharding_key>,timeout);