Sample Illustrating Transaction Toggling in JDBC

Table Of Contents 

• Overview of the Sample Application
• Notations used
• Required Software
• Application Set-up and Configuration
• Deploying and Running the Application 
• Using Oracle9i JDeveloper 
• Using ANT
• Description of Sample Files

This sample illustrates the usage of the JDBC 3.0 feature, sharing connection between local and global transactions (transaction toggling) in Oracle JDBC. The JDBC 3.0 specification defines that a Connection object may operate in either local or global mode, and allows it to switch between these two modes.

At any point a connection can be in one of three valid modes : NO_TXN, LOCAL_TXN, or GLOBAL_TXN. NO_TXN indicates that no transaction is actively using this Connection; LOCAL_TXN indicates that a local transaction that requires explicit commit (i.e., with auto-commit off/disabled) is actively using this Connection; and GLOBAL_TXN indicates that a global transaction is actively using this Connection. Such mode names as well as the transitions among the modes are transparent to applications.With the help of a small Shopping Cart application this sample illustrates how to toggle between local and global transactions.

In this scenario the items in the Shopping Cart can be shipped from two places, either from local database present in San Francisco or from the global database present in Washington. The orders which are to be shipped in and around San Francisco are stored in the local database and the orders which are to be shipped outside San Francisco are stored in the global database. When a user logs in, he is in LOCAL_TXN mode. When he places a order which has to be shipped to a place outside San Francisco then the details of the products present in the order is stored in local database using the connection which is in LOCAL_TXN mode. Then the connection switches to GLOBAL_TXN mode and using two-phase commit stores the shipping details in the global database using global connection and changes the status of the order in local database. When the two-phase commit process is ended the connection mode switches back to NO_TXN mode.

The role of a user id can be either a retailer or a customer. If the role of the user id is a retailer then all the orders placed in the database till now can be viewed. If the admin selects to view the shipping details of a particular order then depending on the shipping location the details are retrieved either from local/global database and shown. If its is a customer then the products are displayed and he/she can place orders.

Using this application, user can carry out following operations :

• Add products to his Shopping Cart.
• Updates the items in the Shopping Cart.
• Checkout the Shopping Cart.
• Check the orders placed in the database till now and its shipping details.

Here is the code usage for initializing the connection cache. You can find more details of the code in TransTogglingSampleBean.java file under src\oracle\otnsamples\oracle9ijdbc\transactiontoggling folder. Look into Description of Sample Files section for folder and file details.

...
   public void placeOrder(HttpServletRequest request) throws SQLException, Exception{
    Connection conn = null;
     
     .....



    // Get the connection object for local database.
    // During instantiation by default a connection will be on NO_TXN mode.
    conn = xconnLocal.getConnection();


    // Insert both, the details of the order and details of each product
    // in local database.
    // As a DML operation is performed the transaction mode switched to
    // LOCAL_TXN mode
    this.insertOrderInformation(conn, shippedFrom);


    // Commit the transaction. Now as no transaction is actively using the
    // connection the transaction mode is switched to NO_TXN mode.
    conn.commit();



    // If the city selected is San Francisco then connect to local database.
    if(shippedFrom.equals("San Francisco")){
      try {
        // By disabling the Auto-commit mode the connection is
        // switches from NO_TXN mode to LOCAL_TXN mode.
        conn.setAutoCommit(false);

        // Insert the shipping details in local database.
        this.insertShippingInformation(conn, city, request);

        // If the above operation is performed successfully then update the
        // Shipping status
        this.updateShippingStatus(conn);


        // Commit the transaction
        conn.commit();

      } catch(Exception excep){
          // In case an exception is raised due to some error then Rollback
          // the whole transaction.
          conn.rollback();
      }
    }else if(shippedFrom.equals("Washington")){
      // In case a city other than San Francisco is selected,
      // call doGlobalUpdate to perform the action.
      this.doGlobalUpdate(conn, request);

    }
    // Perform cleanup
    conn.close();
  }

...

This following notations are used through out this document

Notation	Description
<OC4J_HOME>	Folder where OC4J is installed.
<SAMPLE_HOME>	Folder where the TransTogglingSample will be unzipped.
<JAVA_HOME>	Folder where JAVA is installed.
<ANT_HOME>	Folder where the ANT is installed.

Required Software

Back To Top

• Oracle9i JDeveloper ( Note: Oracle9i JDeveloper is Oracle's Visual Java Development Tool and can be downloaded from here )
  or ANT tool for building the Enterprise Application aRchive (EAR) file( ANT Version 1.5 or above can be downloaded from here ) and JDK1.2.x or above(Can be downloaded from here).
• Oracle9i Database or higher running SQL*Net TCP/IP listener. This can be downloaded from here .
• Oracle9i JDBC Driver. The JDBC driver is available at <ORACLE_HOME>/jdbc/lib Or <OC4J_HOME>/jdbc/lib. 
  

• Unjar the provided TransTogglingSample.jar using the following command 
  

  > jar xvf TransTogglingSample.jar

   

  Note: You will find jar.exe in <JAVA_HOME>\bin. Ensure <JAVA_HOME>\bin is present in your system path. 
  For setting up environment variables in different platforms, please refer environment set up readme document.
  This creates a folder TransTogglingSample with all the source files.
  

• To create the tables and sequences used by this application, run SQL*Plus, connect to your Oracle9i database and execute the following scripts as shown below.
  
  

  SQL> @<SAMPLE_HOME>\database\TransTogglingSampleLocal.sql

  Enter the system users password and host string of the database to which you are connecting. The same host string must be specified as tnsEntryName in ConnectionLocal.properties. This script creates a user called local and creates the required tables in it.

  Only the table Shipping_Details is required in the global database. It is created by executing the TransTogglingSampleGlobal.sql file in the following way:

  SQL> @<SAMPLE_HOME>\database\TransTogglingSampleGlobal.sql

  This script creates a user called global and creates the required tables in it. Enter the system users password and host string of the database to which you are connecting. The same host string must be specified as tnsEntryName in ConnectionGlobal.properties.

• Edit ConnectionGlobal.properties & ConnectionLocal.properties in <SAMPLE_HOME>\TransTogglingSample\config directory in your favorite editor. Change the HostName, Port, SID, UserName and Password to connect to your own database.

This section describes the steps required in deploying and running this application inside embedded OC4J using Oracle9i JDeveloper.

• Open Oracle9iJDeveloper and use File/Open option to select the TransTogglingSample.jws from the TransTogglingSample directory.
• Next, select Project/Make TransTogglingSample.jpr from main menu.
• Now, select Run/Run TransTogglingSample.jpr from main menu which opens up the browser and runs the TransTogglingSample.