print Printer View e-mail E-mail this page Bookmark Bookmark 
/*
 * @author  Anupama Majety

 * @version 1.0
 *
 * Development Environment        :  Oracle9i JDeveloper
 * Name of the Application        :  TransTogglingSampleBean.java
 * Creation/Modification History  :
 *

 *    Anupama Majety      01-Jun-2002      Created
 *    Srikanth Mohan      24-Feb-2003      Certified on Linux
 *
 */

// Package name
package oracle.otnsamples.oracle9ijdbc.transactiontoggling;


// JDBC imports
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.PreparedStatement;


// Servlet imports
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;

// Distributed transaction support
import javax.sql.XAConnection;

// JNDI imports
import javax.naming.NamingException;


// JTA imports
import javax.transaction.xa.Xid;
import javax.transaction.xa.XAResource;
import javax.transaction.xa.XAException;

//Package for using Streams
import java.io.IOException;

// Oracle extensions

import oracle.jdbc.xa.client.OracleXADataSource;
import oracle.jdbc.xa.OracleXid;

// Java Utility Classes
import java.util.Properties;
import java.util.ResourceBundle;
import java.util.Hashtable;
import java.util.Date;
import java.util.Enumeration;
import java.util.ArrayList;


/**
 * This class illustrates the feature of sharing of a connection between
 * local and global transaction introduced in JDBC 3.0 The same connection object
 * can operate in either local or global mode.
 */
public class TransTogglingSampleBean {

  /**
   * Distributed transaction connections
   */
  private XAConnection xconnLocal = null;
  private XAConnection xconnGlobal = null;


  /**
   * OracleXADataSource extends the OracleConnectionPoolDataSource, has the
   * APIs for Distributed Transaction support
   */
   OracleXADataSource oxdsLocal = null;
   OracleXADataSource oxdsGlobal  = null;

  /**
   * Holds session object.
   */
  private HttpSession session = null;


  /**
   * This method reads a properties file which is passed as
   * the parameter to it and load it into a java Properties
   * object and returns it.
   *
   * @param     file             Name of the file that has to be loaded.
   * @exception IOException      In case of unexpected Exceptions
   */
  public static Properties loadParams( String file ) throws IOException {
    // Loads a ResourceBundle and creates Properties from it
    Properties prop = new Properties();
    ResourceBundle bundle = ResourceBundle.getBundle( file );
    Enumeration enum = bundle.getKeys();
    String key = null;

    while( enum.hasMoreElements() ) {
      key = (String)enum.nextElement();
      prop.put( key, bundle.getObject( key ) );
    }
    return prop;
  }

  /**
   * Creates a database connection object using DataSource object. Please
   * substitute the database connection parameters with appropriate values in
   * ConnectionLocal.properties and ConnectionGlobal.properties file
   *
   * @exception NamingException   Exceptions raised when lookup fails or
   *                              In case of JNDI error
   * @exception Exception         in case of unexpected errors.

   */
  public void initDataSource() throws NamingException, Exception{

    // Load the properties file to get the connection information
    Properties localConnProp = this.loadParams("ConnectionLocal");
    Properties globalConnProp = this.loadParams("ConnectionGlobal");

    /* This code is required only when using Oracle9i JDBC driver v9.0.1.
       If the JDBC driver version is 9.2.X or higher, this code can be removed.*/
    java.sql.DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
    Connection conn = java.sql.DriverManager.getConnection(
        "jdbc:oracle:thin:@" +
        (String)localConnProp.get("HostName") + ":" +
        (String)localConnProp.get("Port") + ":" +
        (String)localConnProp.get("SID"),
        (String)localConnProp.get("UserName"),

        (String)localConnProp.get("Password"));

    conn.close();

    /* End of code */

    // Create a OracleDataSource instance
    oxdsLocal = new OracleXADataSource();
    oxdsGlobal = new OracleXADataSource();

    // Configure the Datasource with proper values of TNSEntry, User Name etc
    this.configureDataSource(oxdsLocal, localConnProp);
    this.configureDataSource(oxdsGlobal, globalConnProp);


    // Set the NativeXA property true, to support distributed transactions in
    // Oracle8i releases prior to 8.1.6.

    oxdsLocal.setNativeXA(true);
    oxdsGlobal.setNativeXA(true);

  }

 /**
  * This method initializes the datasource only when the datasource is null
  * or else it uses previously loaded datasource.
  *
  * @exception SQLException   if unable to connect to the database.
  * @exception Exception      In case of unexpected Exceptions
  */
  public void dbConnection() throws SQLException, Exception{

    if(oxdsLocal == null || oxdsGlobal == null)
       initDataSource();

    if(oxdsLocal != null) {

      xconnLocal = oxdsLocal.getXAConnection();
    }

    // If lookup was success, retrieve a distributed connection from the Datasource
    if(oxdsGlobal != null) {
      xconnGlobal = oxdsGlobal.getXAConnection();
    }

  }

  /**
   * This method is called when an order outside San Francisco is placed.
   * In such case the orders must be shipped from the Branch present in
   * Global database(Washington). The status of the order stored in local
   * database must be changed to 'Shipped' if the shipping information is stored
   * successfully in the global database. The procedure followed here is called
   * Two-phase commit. The same connection which was used to store the
   * details of the order is used as to do a global transaction here.
   *

   * @param     connLocal     Local connection
   * @param     request       HttpServletRequest object
   * @exception SQLException In case of an error in the database.
   * @exception XAException  In cases where functions particular to Global_Txn
                  are called when its connection is in Local_Txn or vice versa.
   * @exception Exception    In case of an unexpected exception
   * @return    The success or failure of this operation.
   */
  private boolean doGlobalUpdate(Connection connLocal, HttpServletRequest request)
       throws SQLException, XAException, Exception {
    Connection connGlobal = null;

    // Transaction Resources used by the resource manager for coordinating
    // transactions
    XAResource xresLocal   = null;
    XAResource xresGlobal  = null;

    Xid xidLocal   = null;
    Xid xidGlobal  = null;


   try {
      // If doCommit is set to TRUE, then transaction can be committed
      // else denotes an unsuccessful transaction
      boolean doCommit = true;

      // Get transaction resources
      xresLocal   = xconnLocal.getXAResource();
      xresGlobal  = xconnGlobal.getXAResource();

      // Get Connection instance
      connGlobal  = xconnGlobal.getConnection();

      // Transaction ID is an unique identifier for a particular transaction branch
      // Create transaction id for each database user.
       xidLocal   = createXid(1);
       xidGlobal  = createXid(2);

      // When start() is invoked on the XAResource, the connection switches from
      // NO_TXN mode to GLOBAL_TXN mode.
      // Here the flags can be either
      //    XAResource.TMNOFLAGS --> Start of new transaction branch

      //    XAResource.TMJOIN    --> Joins a transaction branch specified by xid
      //    XAResource.TMRESUME  --> Resumes a transaction branch specified by xid
      //
      //  note: The commit() or rollback() or setAutoCommit() methods of the
      //        connection instance should not be invoked until the distributed
      //        transaction is either committed or rolled back by the resource managers.
      xresLocal.start(xidLocal,XAResource.TMNOFLAGS);
      xresGlobal.start (xidGlobal ,XAResource.TMNOFLAGS);

      // Insert the shipping details in Global database.
      this.insertShippingInformation(connGlobal,
                                  request.getParameter("OtherCity"), request);

      // Update the shipping status in the Order_Master table.
      this.updateShippingStatus(connLocal);

      // End(xid,flags) disassociates the transaction branch from the distributed
      // transaction. Here the TXM mode switches back to NO_TXN mode.
      // The flags can be either
      //    XAResource.TMSUCCESS --> Transaction branch has succeeded
      //    XAResource.TMFAIL    --> Transaction branch has failed
      //    XAResource.TMSUSPEND --> Suspend the transaction branch specified by xid

      xresLocal.end(xidLocal,XAResource.TMSUCCESS);
      xresGlobal.end (xidGlobal ,XAResource.TMSUCCESS);

      // Prepares the changes performed in the transaction branch specified by xid
      // for commit. Checks the status of the database.
      // Returns an integer value
      //           XAResource.XA_RDONLY --> The transaction has executed only
      //                                    read only operations (SELECT query)
      //           XAResource.XA_OK     --> The transaction has executed
      //                                     updates successfully
      //           no value             --> One of the transaction branch has
      //                                    encountered some error
      int prpLocal   =  xresLocal.prepare(xidLocal);
      int prpGlobal  =  xresGlobal.prepare(xidGlobal);

      // In case all the branches use the same resources (i.e.) the data updated
      // belongs to the same database table, only one of the branches return
      // XA_OK and all the other branches return XA_RDONLY, only one update
      // need to be committed.
      if(!((prpLocal == XAResource.XA_OK) || (prpLocal == XAResource.XA_RDONLY)))
        doCommit = false;

      if(!((prpGlobal == XAResource.XA_OK) || (prpGlobal == XAResource.XA_RDONLY)))

        doCommit = false;

      // If some update has happened in the transaction branch
      if(prpLocal == XAResource.XA_OK) {

        // If prepare has returned a valid value, commit the transaction branch
        if(doCommit)

          // Commit(xid,onePhase) commits the prepared changes to the database
          // If onePhase is set when only one transaction branch participates in
          // the distributed transaction
          xresLocal.commit (xidLocal, false);
        else
          // Rollback changes
          xresGlobal.rollback (xidLocal);
      }

      // If some update has happened in the transaction branch
      if(prpGlobal == XAResource.XA_OK) {

        // If prepare has returned a valid value, commit the transaction branch
        if (doCommit)

          // Commit the transaction branch

          xresGlobal.commit (xidGlobal, false);
        else
          // Rollback changes
          xresGlobal.rollback (xidGlobal);

      }

      // Close the connections
      connGlobal.close();

      // Return true if transaction was committed successfully else return false.
      return(doCommit);

    } catch (Exception excep) {
       // Ending the global transaction, rollback the changes and close the
       // connections incase where exception is raised.
       xresLocal.end(xidLocal,XAResource.TMFAIL);
       xresGlobal.end (xidGlobal ,XAResource.TMFAIL);
       xresGlobal.rollback (xidGlobal);
       xresLocal.rollback(xidLocal);
       connLocal.close();
       connGlobal.close();
       throw excep;
    }
  }


  /**
   * Creates a unique Transaction Id.
   *
   * @param     branchId    Id of the branch
   * @return    Transaction Id generated is returned
   * @exception XAException In case of an error involved in the transaction.
   */
  private Xid createXid(int branchId) throws XAException {

    // Global Transaction Id will be identical for all the branches under the
    // same distributed transaction. Here it is set to letter 'O'
    byte[] globalId = new byte[1];
    globalId[0]= (byte)'O';

    // Transaction Branch Id is unique for each branch under the same
    // distributed transaction. Set the transaction id as the parameter passed.
    byte[] branchIdArr = new byte[1];
    branchIdArr[0]= (byte)branchId;

    byte[] globalTranId    = new byte[64];
    byte[] branchQualifier = new byte[64];

    // Copy Global Transaction Id and Branch Id into a 64 byte array
    System.arraycopy (globalId, 0, globalTranId, 0, 1);
    System.arraycopy (branchIdArr, 0, branchQualifier, 0, 1);


    // Create the Transaction Id
    // Transaction Id has 3 components
    Xid xid = new OracleXid(0x1234,     // Format identifier
                      globalTranId,     // Global transaction identifier
                   branchQualifier);    // Branch qualifier

    return xid;
  }

  /**
   * This method is invoked when the details of the order made and the products
   * present must be inserted into the database.
   *
   * @param     conn      Connection object
   * @param     city      Place to which the order must be shipped
   * @exception Exception In case of unreported exception
   */
  private void insertOrderInformation(Connection conn, String city)
                throws Exception {

    // Retrieve the orderId for the new order being placed
    int orderId = this.getOrderId(conn);

    PreparedStatement pstmt = null;

    // Insert the details of the order in the Order_Master table

    pstmt = conn.prepareStatement("INSERT INTO Order_Master "
            + "(Order_Id, Order_Date, Customer_Id, Branch_Id, Order_Total) "
            + " VALUES (?, ?, ?, ?,?)");

    // Set the order Id
    pstmt.setInt(1, orderId);

    // Get todays date and set the date
    pstmt.setDate(2, new java.sql.Date(new Date().getTime()));

    pstmt.setString(3, (String)session.getAttribute("UserId"));

    // Retrieve the branch Id of the city selected
    pstmt.setString(4, this.getBranchId(city, conn));

    // Get the amount for the order.
    pstmt.setFloat(5, this.getOrderTotal());

    // Execute
    pstmt.executeUpdate();

    // Close the Prepared Statement
    pstmt.close();

    // Insert the details of each product in the order into the Order_Items table.
    pstmt = conn.prepareStatement("INSERT INTO Order_Items (Order_Id, Product_Id,"
                  +" Quantity_Ordered) VALUES (?, ?, ?)");


    // Set the orderId
    pstmt.setInt(1, orderId);

    ShoppingCartProduct scProduct = null;

    // Retrieve the details of each product from session.
    Hashtable shoppingCartHash = (Hashtable)session.getAttribute("ShoppingCart");

    // Get all the keys stored in the Hashtable in an Enumeration.
    Enumeration enumCart = shoppingCartHash.keys();

    // Iterate through the Hashtable and insert one row for each product retrieved
    // in the table.
    while (enumCart.hasMoreElements()){

      // Get the Product from the Hashtable and store in ShoppingCartProduct object.
      scProduct = (ShoppingCartProduct)shoppingCartHash.get(enumCart.nextElement());

      // Set the product Id
      pstmt.setString(2, scProduct.productID);

      // Set the quantity Ordered.
      pstmt.setInt(3,scProduct.getQuantityOrdered());

      // Execute the preparedStatement.
      pstmt.executeUpdate();
    }

    // Clean up

    pstmt.close();
  }

  /**
   *  This method updates the Shipping status column present in the Order_Master
   *  table if the Shipping details are entered successfully.
   *  This method is in transaction with insertShippingInformation method.
   *  Exception raised in one of them rollsback the changes made by the other.
   *
   * @param conn Connection object
   * @exception Exception In case of an unreported exception
   */
  private void updateShippingStatus(Connection conn) throws Exception {

    // Create the prepared statement.
    PreparedStatement pstmt = conn.prepareStatement(" UPDATE Order_Master SET "
                          + "Order_Status = 'Shipped' where Order_Id = ?");

    // Set Order_Id
    pstmt.setInt(1,((Integer)session.getAttribute("OrderId")).intValue());

    // Execute
    pstmt.executeUpdate();

    // Perform CleanUp
    pstmt.close();
  }

  /**
   * This method inserts the shipping details of the order inside the database.

   * Depending on the connection object sent, the shipping details are inserted
   * either in the local or global database.
   *      This method is in transaction with updateShippingStatus method.
   * Exception raised in one of them rollsback the changes made by the other.
   *
   * @param     conn      Connection object
   * @param     city      Place to which the order must be shipped
   * @param     request   HttpServletRequest object
   * @exception Exception In case of unreported exception
   */
  private void insertShippingInformation(Connection conn, String city,
                      HttpServletRequest request) throws Exception {

    PreparedStatement pstmt = null;

    // Prepare the query
    pstmt = conn.prepareStatement("INSERT INTO Shipping_Details "
              + "(Order_Id, Name, Email_Id, Phone_Number, Address, City, "
            + "Country, Zip_Code) VALUES (?, ?, ?, ?, ?, ?, ?, ?)");

    // Retrieve the shipping details from Request/ Session and set them.
    pstmt.setInt(1, ((Integer)session.getAttribute("OrderId")).intValue());
    pstmt.setString(2, request.getParameter("Name"));
    pstmt.setString(3, request.getParameter("EmailId"));
    pstmt.setString(4, request.getParameter("PhoneNumber"));
    pstmt.setString(5, request.getParameter("Address"));
    pstmt.setString(6, city);
    pstmt.setString(7, request.getParameter("Country"));
    pstmt.setInt(8, Integer.parseInt(request.getParameter("ZipCode")));

    // Execute the prepared Statement.

    pstmt.executeUpdate();

    // Close the prepared statement
    pstmt.close();
  }

  /**
   * This method validates the login and password entered by the user. If the
   * login is correct then the details of the user are returned.
   * @param userId    Id of the user used to login
   * @param password  Password of the user
   * @return Details of the user
   * @exception SQLException if unable to execute the query
   * @exception Exception In case of an unreported exception
   */
  public Hashtable getUserDetails(String userId, String password)
                      throws SQLException, Exception {

    Connection conn         = null;
    PreparedStatement pstmt = null;
    ResultSet rset          = null;

    // Holds the Role and CustomerName of the user
    Hashtable customerDetails = null;

    // Get the connection object from the XAConnection object. Here the
    // connection object is retrieved from the instance of local database.
    conn = xconnLocal.getConnection();

    // Prepare the query
    pstmt = conn.prepareStatement(" SELECT Customer_Name, Role "
                                  + " FROM Customer_Master "

                                  + " WHERE LOWER(TRIM(Customer_Id)) = "
                                  + "LOWER(TRIM(?)) AND LOWER(Password) = LOWER(?)");

    // Set user Id
    pstmt.setString(1, userId);

    // Set password
    pstmt.setString(2, password);

    // Execute the query and retrieve the customerName and password
    rset = pstmt.executeQuery();

    // If customer with this name exists the get this details and store in hashtable.
    if(rset.next()) {
      customerDetails = new Hashtable();
      customerDetails.put("UserName", rset.getString(1));
      customerDetails.put("Role", rset.getString(2));
    }

    // Perform cleanup
    rset.close();
    pstmt.close();
    conn.close();

    // Return the details of the customer
    return(customerDetails);
  }

  /**
   * This method helps to retrieve the details all the products present with
   * the local database and return it to be displayed in the JSP.
   * @return Details of the product
   * @exception Exception In case of unreported exception

   */
  public Hashtable getProductDetails() throws Exception {

    Connection conn         = null;
    Statement stmt          = null;
    ResultSet rset          = null;

    // Holds the details of the product.
    Hashtable productDetails = new Hashtable();

    // Get the connection object from the XAConnection object. Here the
    // connection object is retrieved from the instance of local database.
    conn = xconnLocal.getConnection();

    // Construct the query to retrieve the details of the product.
    String query = "SELECT Product_Id, INITCAP(Product_Name), Product_Desc, "
                    + "Price, Category_Desc, Quantity_On_Hand "
                    + "FROM Product_Master ORDER BY LOWER(Product_Name)";

    // Create the statement.
    stmt = conn.createStatement();

    // Execute the query and retrieve the product Details
    rset = stmt.executeQuery(query);

    Product productObj = null;

    // Store the details of the product in hash table.
    while(rset.next()) {
      productObj = new Product(rset.getString(1), rset.getString(2),
                                rset.getString(3),
                                rset.getFloat(4), rset.getString(5),
                                rset.getInt(6));
      productDetails.put(rset.getString(1), productObj);

    }

    // Perform cleanup
    rset.close();
    stmt.close();
    conn.close();

    // Return the product details
    return(productDetails);
  }

  /**
   * This method is called when the shipping details of the Order are given and the
   * order is to be placed. It helps to store the details of the order in the
   * respective tables. The details of the products present in the order is stored
   * in local database. Depending on the city selected the shipping details is
   * stored in  local database or global database. All orders that are to be
   * shipped outside San Francisco are stored in global database and the orders
   * that are to be shipped in San Francisco are stored in local database.
   *
   * @param request HttpServletRequest Object
   * @exception SQLException if unable to execute the query
   * @exception Exception In case of an unreported exception
   */
  public void placeOrder(HttpServletRequest request) throws SQLException, Exception{
    Connection conn = null;

    // Retrieve the session being used.
    session = request.getSession(false);

    // Get the city selected
    String city = request.getParameter("City");

    // Location from which the order must be shipped.
    String shippedFrom = "";


    // The shipped Location is Washington if the order should be shipped to
    // a place outside San Francisco
    if(city.equals("Others")){
      shippedFrom = "Washington";
    } else {
      shippedFrom = city;
    }

    // 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 method retrieves all the orders that are placed in the database.
   *
   * @param     request   HttpServletREquest object
   * @exception Exception In case of unreported exception.
   */
  public void retrieveAllOrders(HttpServletRequest request) throws Exception {
    // Retrieve the session being used.
    session = request.getSession(false);

    Connection conn         = null;
    Statement stmt = null;
    ResultSet rset          = null;

    // Holds the details of order.
    Hashtable orderHash = new Hashtable();

    // Gets connection object from XAConnection. Here the connection

    // object is retrieved from the instance of local database.
    conn = xconnLocal.getConnection();

    // Construct the query
    String query = "SELECT Order_Id, Customer_Id, "
                    + "TO_CHAR(Order_Date,'dd-MON-yyyy'), Order_Total, "
                    + "INITCAP(Branch_Location), Order_Status "
                    + "FROM Order_Master om, Shipping_Branches sb "
                    + "WHERE om.Branch_Id = sb.Branch_Id ORDER BY Order_Id DESC";

    // Create the connection
    stmt = conn.createStatement();

    // Execute the query and retrieve the order details
    rset = stmt.executeQuery(query);

    OrderMasterInfo orderMasterInfo = null;
    int orderId = 0;

    // Retrieve all the details and store in hashtable.
    while(rset.next()) {
      orderId = rset.getInt(1);
      orderMasterInfo = new OrderMasterInfo(orderId, rset.getString(2),
                                rset.getString(3), rset.getFloat(4),
                                rset.getString(5), rset.getString(6));

      orderHash.put(new Integer(orderId), orderMasterInfo);
    }

    // Store all the orders in session.
    session.setAttribute("AllOrders", orderHash);

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


  /**
   *  The shipping details and the details of all the order items present in the
   *  order whose id is sent is retrieved by this method and stored in session.
   *
   * @param     orderId   Id of the order
   * @exception Exception In case of an unreported Exception
   */
  public void retrieveOrderDetails(int orderId) throws Exception {
    Connection conn         = null;
    PreparedStatement pstmt = null;
    ResultSet rset          = null;

    // Remove the shipping details and all order items present in the session so
    // that shipping details of the previous Order are not shown in the absence
    // of shipping details of the current order.
    session.removeAttribute("OrderItems");
    session.removeAttribute("ShippingDetails");

    // Get the connection object from the XAConnection object. Here the
    // connection object is retrieved from the instance of local database.
    conn = xconnLocal.getConnection();

    // Prepare the query
    pstmt = conn.prepareStatement(" SELECT oi.Product_Id, INITCAP(Product_Name),"
                      + " Price, Quantity_Ordered "
                      + "FROM Order_Items oi, Product_Master pm "
                      + "WHERE Order_Id = ? AND "
                      + "oi.Product_Id = pm.Product_Id");

    // Set order Id
    pstmt.setInt(1, orderId);

    // Execute the query and retrieve the order items
    rset = pstmt.executeQuery();

    // Holds details of the order Items
    ArrayList orderItems = new ArrayList();

    // Retrieve the details of the Items and store in ArrayList

    while(rset.next()) {
      orderItems.add(rset.getString(1));
      orderItems.add(rset.getString(2));
      orderItems.add(new Float(rset.getFloat(3)));
      orderItems.add(new Integer(rset.getInt(4)));
    }

    // Store the details of the order items in session.
    session.setAttribute("OrderItems", orderItems);

    // Close the ResultSet and Prepared Statement
    rset.close();
    pstmt.close();

    // Retrieve the details of all the orders from session and get the
    // details of the current selected order.
    Hashtable ordersHash = (Hashtable)session.getAttribute("AllOrders");
    OrderMasterInfo orderObj = (OrderMasterInfo)ordersHash.get(new Integer(orderId));

    // Retrieve the branch location and order status from it.
    String branchLocation = orderObj.branchLocation;
    String orderStatus    = orderObj.orderStatus;

    // You enter the loop only if the product is shipped. So in case where
    // the shipping details are not entered in the database due to some exception,
    // then the shipping details wouldn't have been entered in the database due
    // to the Two-phase commit. So you needn't query the database only.
    if(orderStatus.equalsIgnoreCase("Shipped")) {
      if(branchLocation.equals("Washington")){
        conn.close();

        // Get the connection object from the XAConnection object. Here the
        // connection object is retrieved from the instance of global database.
        conn = xconnGlobal.getConnection();
      }

      // Retrieve the shipping details from the local/global database depending
      // on the connection object passed.
      retrieveShippingDetails(conn, orderId);
    }


    // Perform cleanup
    conn.close();
  }

  /**
   * This method retrieves the shipping details of the order sent from
   * the database it is connected to.
   *
   * @param     conn      Connection object
   * @param     orderId   Id of the order
   * @exception Exception In case of unreported exception
   * @return comments
   */
  public void retrieveShippingDetails(Connection conn, int orderId)
                            throws Exception {
    PreparedStatement pstmt = null;
    ResultSet rset          = null;

    // Prepare the query
    pstmt = conn.prepareStatement(" SELECT INITCAP(Name), Email_Id, Phone_Number,"
                      + " Address, INITCAP(City), INITCAP(Country), Zip_Code "
                      + "FROM Shipping_Details WHERE Order_Id = ?");

    // Set order Id
    pstmt.setInt(1, orderId);

    // Execute the query and retrieve the shipping details
    rset = pstmt.executeQuery();

    ShippingDetails shippingDetails = null;

    // If the shipping details of the order exists the enter the loop and
    // retrieve the details.
    if(rset.next()) {
      shippingDetails = new ShippingDetails(rset.getString(1), rset.getString(2),
                                rset.getString(3),
                                rset.getString(4), rset.getString(5),
                                rset.getString(6), rset.getInt(7));
    }


   // Store the details in session.
   session.setAttribute("ShippingDetails", shippingDetails);

   // Perform cleanup
   rset.close();
   pstmt.close();
  }

  /**
   * This method helps to retrieve the branch id of the city selected.
   *
   * @param     city     City selected
   * @param     conn     Connection object.
   * @return    branch Id of the city
   * @exception Exception In case of an unreported exception
   */
  private String getBranchId(String city, Connection conn) throws Exception{

    // Prepare the query
    PreparedStatement pstmt = conn.prepareStatement(" SELECT Branch_Id,"
                                 + " Shipping_Charge FROM Shipping_Branches "
                                 + " WHERE Branch_Location = ?");

    // Set the city
    pstmt.setString(1,city);

    // Execute the query and retrieve the shipping branch details
    ResultSet rset = pstmt.executeQuery();

    String branchId = "";

    // If a branch exists in this city retrieve the branch id and its
    // shipping charges.
    if(rset.next()) {
      branchId = rset.getString(1);
      session.setAttribute("ShippingCharges",new Float(rset.getFloat(2)));
    }

    // Perform Cleanup
    rset.close();
    pstmt.close();


    // Return branch Id
    return branchId;
  }

  /**
   * This method calculates the total of the order.
   * @return    Amount of products purchased or Order Total
   * @exception Exception In case of an unreported exception
   */
  private float getOrderTotal() throws Exception {
    float orderTotal= 0.0f;
    ShoppingCartProduct scProduct = null;

    // Retrieve the details of the products present in the shopping cart.
    Hashtable shoppingCartHash = (Hashtable)session.getAttribute("ShoppingCart");

    // Get all the keys stored in the Hashtable in an Enumeration.
    Enumeration enumCart = shoppingCartHash.keys();

    // Iterate through the Hashtable
    while (enumCart.hasMoreElements()){
      Object key = enumCart.nextElement();

      // Get the first Product from the Hashtable and display it
      scProduct = (ShoppingCartProduct)shoppingCartHash.get(key);
      orderTotal += scProduct.getTotalPrice();
    }
    return orderTotal;
  }

  /**
   * This method retrieves the next orderId from the sequence.
   *
   * @param conn Connection Object
   * @return    Next order Id
   * @exception Exception   In case of an unreported exception
   */
  private int getOrderId(Connection conn) throws Exception{
    // Create the statement
    Statement stmt = conn.createStatement();

    // Execute the query
    ResultSet rset = stmt.executeQuery("SELECT ORDER_ID_SEQ.NEXTVAL FROM DUAL");


    int orderId = 0;

    // If the sequence exists
    if(rset.next()) {
      orderId = rset.getInt(1);

      // It is stored in session so that it can be displayed in OrderPage
      session.setAttribute("OrderId", new Integer(orderId));
    }

    // Close the statement.
    stmt.close();

    return orderId;
  }

  /**
  * This method configures the Datasource with appropriate values of TNSEntry,
  * User Name and Password.
  * Note that the configuration parameters are stored in ConnectionParamsLocal.java
  * for local user.
  * @param oxads OracleXADataSource object
  */
  private void configureDataSource(OracleXADataSource oxds, Properties prop) {

    // Driver type, either 'thin' or 'oci8'
    // if its a OCI driver then, the TNS entry name has to be specified

    oxds.setDriverType("thin");

    // Sets the database server name
    oxds.setServerName((String)prop.get("HostName"));

    // Sets the database port number
    oxds.setPortNumber(new Integer((String)prop.get("Port")).intValue());

    // Sets the database sid
    oxds.setDatabaseName((String)prop.get("SID"));

    // Sets the user name
    oxds.setUser((String)prop.get("UserName"));

    // Sets the password

    oxds.setPassword((String)prop.get("Password"));

  }

  /**
  *  Closes the connections to database.
  *
  *  @exception SQLException   Raised while closing the connection
  *  @exception Exception   In case of an unreported exception
  */
  public void cleanUp()
    throws SQLException,Exception {
    if(session != null){