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        :  SavepointSample.java
 * Creation/Modification History  :

 *
 *    Anupama Majety      02-Apr-2002      Created
 *    Srikanth Mohan      27-Apr-2002      Certified on Linux
 *
 */

package oracle.otnsamples.oracle9ijdbc.savepoint;


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


// Oracle extensions
import oracle.jdbc.pool.OracleDataSource;

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

// Java Utility Classes
import java.util.Properties;

import java.util.ResourceBundle;
import java.util.Enumeration;

import java.util.Hashtable;
import java.util.ArrayList;

/**
 * This sample illustrates all the transaction related features like creating a
 * savepoint, rollback and committing of a transaction through JDBC.
 */

public class SavepointSample
{
  // Connection instance
  private Connection conn=null;

  // GUI handling
  private SavepointFrame savepointFrame;

  // Tells if the transaction has started or not
  private boolean isTransactionStarted = false;


  // Stores all the savepoint objects created until now.
  private Hashtable savepointsCreated = new Hashtable();

  // Tells if the transfer between account has started yet or not.
  private boolean isTransferStarted = false;

  // Save the savepoint created during the transfer of accounts.
  Savepoint firstSavepoint = null;

  /**
   * Constructor. Initializes the savepointFrame
   */

  public SavepointSample() {
    savepointFrame = new SavepointFrame(this);
  }

  /**
   * Main entry point for the class. Connects to the database and populates the
   * Accounts JTable with rows from the Account_Master table.
   *
   * @param arg Command line arguments
   */
  public static void main(String arg[]){
    // Instantiate the class
    SavepointSample savepointSample = new SavepointSample();


    // Connect to database
    savepointSample.dbConnection();

    // If connected the actions to be performed.
    if(savepointSample.conn != null){

      // Retrieve the data present in Account_Master table and display it
      // in the panel.
      savepointSample.displayAllAccounts();
      savepointSample.savepointFrame.putStatus(
        "Select an account to be updated and click on either Update or Transfer"
        + " to start the transaction.");
    }

  }

  /**
   * Dispatches the appropriate methods to handle the events generated
   * in the GUI. The methods do the required JDBC operations.
   *
   * @param eventName Contains the action to be performed next
   */
  public void dispatchEvent (String eventName) {

    // Displays the required information in UpdateFrame
    if (eventName.equals("SHOW UPDATE")){
      // Show the account details
      this.showAccountDetails();


    } else if (eventName.equals("UPDATE ACCOUNT")){
      // Update the selected account information with the information entered
      this.updateAccountDetails();

    } else if (eventName.equals("TRANSFER ACCOUNT")){
      // If the transaction of transferring account has started.
      if(isTransferStarted){
        this.addAmtToSecondAccount();
      } else {    // If a fresh transfer has started.
        isTransferStarted = false;

        // Transfer between accounts
        this.tranferAmount();
      }
    }  else if (eventName.equals("TRANSFER CANCELLED")){
      // If the transaction of transferring account has started.

        this.transferCancelled();

    } else if (eventName.equals("ROLLBACK")){
      // Rollback the transaction up to the point selected.
      this.transactionRollback();

    } else if (eventName.equals("SET SAVEPOINT")){
      // Create a savepoint until this point
      this.createSavepoint();

    } else if (eventName.equals("COMPLETE ROLLBACK")){
      // Rollback the complete transaction
      this.completeRollback();

    } else if (eventName.equals("COMMIT")){
      // Commit the transaction
      this.commitTransaction();


    } else if (eventName.equals("EXIT")){
      // Performs clean up and closes the application
      this.exitApplication();
    }
  }

  /**
   * 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.
   */
  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
   * Connection.properties file
   */
  public void dbConnection() {
    try {
      savepointFrame.putStatus("Trying to connect to the Database");

      // Load the properties file to get the connection information
      Properties prop = this.loadParams("Connection");


      // Create a OracleDataSource instance
      OracleDataSource ods = new OracleDataSource();

      // Configure the Datasource with proper values of TNSEntry, User Name etc
      this.configureDataSource(ods, prop);

      // Create a connection  object
      conn = ods.getConnection();

      // Sets the connection's auto-commit mode to false so that the transaction
      // is not implicitly committed. The auto-commit mode should be false
      // if we want to create savepoints in our transaction.
      conn.setAutoCommit(false);

    } catch(SQLException ex) { // Handle SQL errors
        System.out.println(
                     "Error in Connecting to the Database "+'\n'+ex.toString());
    } catch(IOException ex) { // Handle SQL errors
        System.out.println(
                     "Error in reading the properties file "+'\n'+ex.toString());

    }
  }

  /**
   * Populates the JTable with the data present in Account_Master table and
   * enables the frame for action.
   */
  private void displayAllAccounts() {
    // Create the Statement object
    Statement stmt = null;
    try {
      // Get the statement object from the Connection
      stmt = conn.createStatement();

      // Execute the Statement
      ResultSet rs = stmt.executeQuery("SELECT Account_Number, "
                      + "INITCAP(Customer_Name), Balance FROM Account_Master "
                      + "ORDER BY Account_Number");

      // Add each record retrieved from database into the Accounts table.
      while(rs.next()){

        savepointFrame.addToAccountTable(rs.getString(1), rs.getString(2),
                                    rs.getString(3));
      }

      // Enable the savepointFrame for Updation
      savepointFrame.enableFrame();
    } catch(Exception excep){   // Handle errors
      savepointFrame.putStatus("Error while retrieving Account details : "
                                                + excep.toString());
    } finally {
      try {
        // Close the Statement object
        if(stmt != null) stmt.close();
      } catch(Exception excep) {
        savepointFrame.putStatus("Error while closing Statement. : "
                                                + excep.toString());
      }
    }
  }

  /**
   * This method retrieves the details of the Account selected and displays them

   * in the update Frame
   */
  private void showAccountDetails() {
    // Get the selected Account details from the JTable
    ArrayList accountDetailsArr = savepointFrame.getSelectedAccountDetails();

    // Retrieve its balance details
    float previousBalance = this.getAccountBalance(
            Integer.parseInt(accountDetailsArr.get(0).toString()));
    try {
      // Display the retrieved details in the UpdateAccount frame
      savepointFrame.frmUpdate.displayAccountDetails(// Account Number
                                     accountDetailsArr.get(0).toString(),
                                     // Customer Name
                                     accountDetailsArr.get(1).toString(),
                                     // previous Balance
                                     previousBalance
                                     );

    } catch(Exception excep){   // Handle errors
      savepointFrame.putStatus("Error while displaying Account Details : "
                                                    + excep.toString());
    }

  }

  /**
   * Updates the details of the account entered in the form into the database.
   */
  private void updateAccountDetails(){
    PreparedStatement pstmt=null;
    try {
      // Find the type of transaction done if the amount is credited or debited.
      String transaction = savepointFrame.getTransactionType();

      // Create a preparedstatement object and cast it
      pstmt = conn.prepareStatement(" UPDATE Account_Master"
                                   +  " SET Balance = ?"
                                   +  " WHERE Account_Number = ? ");

      // Get the previous balance
      float balance = savepointFrame.getBalance();

      // Update the Amount details according to the transaction type.
      // Add or subtract the amount entered from the previous balance if the
      //  transaction type is 'Credited' or 'Debited'
      if(transaction.equals(savepointFrame.frmUpdate.transactionTypes[0])){
        balance += savepointFrame.getAmount();

      }else if(transaction.equals(savepointFrame.frmUpdate.transactionTypes[1])){
        balance -= savepointFrame.getAmount();
      }

      // Set the balance to the amount obtained after crediting/debiting the
      // amount entered.
      pstmt.setFloat(1, balance);

      // The Account number of the account whose balance is updated is retrieved.
      pstmt.setInt(2, savepointFrame.getAccountNumber());

      // Execute the statement
      int i = pstmt.executeUpdate();

      // Remove the data currently present and fills the table
      // with the data from database
      this.refreshAccountsTable();

      // Enables Transaction buttons depending on if it is the first transaction or not.
      this.enableTransactionBtns();

      savepointFrame.putStatus("Account with id "+savepointFrame.getAccountNumber()
                                  + " is updated.");

    } catch(Exception excep){   // Handle errors

      savepointFrame.putStatus("Error while updating account details: "
                                                  + excep.toString());
    } finally {
      try {
        // Close the PreparedStatement object
        if(pstmt != null) pstmt.close();
      } catch(Exception excep) {
        savepointFrame.putStatus("Error while closing Prepared Statement. : "
                                                + excep.toString());
      }
    }
  }

  /**
   * Transfers the amount entered from the account selected to the account entered.
   * This method creates a savepoint and then transfers the amount entered
   * from the account selected. If the balance of the account is less than the
   * amount entered then an exception is raised and the transaction is rolled back.
   * If no exception is raised then the amount is transferred to the account entered.
   */
   private void tranferAmount(){
    PreparedStatement pstmt=null;
    try {
      // Retrieve the account number from which the amount has to be
      // transferred and the amount to be transferred from the transferAccountFrame.
      int fromAccountNo = savepointFrame.frmTransfer.getFromAccountNo();

      float amount      = savepointFrame.frmTransfer.getAmountTransfered();

      // This method helps to creates an named savepoint in the
      // current transaction and returns the new Savepoint object that
      // represents it. The syntax of this method is
      // Savepoint setSavepoint(String savepointName)
      // This method raises SQLException if the Connection is in auto commit mode.
      firstSavepoint = conn.setSavepoint("beforeTransfer");
      try{
        // Create a prepared statement object and cast it
        pstmt = conn.prepareStatement(" UPDATE Account_Master"
                                     + " SET Balance = Balance - ?"
                                     + " WHERE Account_Number = ? ");

        // Update the database with the value of balance thus obtained.
        pstmt.setFloat(1, amount);

        // The selected Account Number is retrieved
        pstmt.setInt(2, fromAccountNo);

        // Execute the statement
        int i = pstmt.executeUpdate();
      }catch(Exception exception){
        // This method  is similar to
        // the Rollback command in SQL. The syntax of this method is
        // void rollback(Savepoint savepointName)
        //          where savepointName specifies the point up to which the

        // transaction has to be rolled back.
        conn.rollback(firstSavepoint);
        return;
      }

      // If the amount is successfully deducted then call the method
      // to add the amount specified in the account entered.
      this.addAmtToSecondAccount();

    } catch(Exception excep){   // Handle errors
      savepointFrame.putStatus("Error while transferring amt from first account : "
                                                  + excep.toString());
    } finally {
      try {
        // Close the PreparedStatement object
        if(pstmt != null) pstmt.close();
      } catch(Exception excep) {
        savepointFrame.putStatus("Error while closing Prepared Statement. : "
                                                + excep.toString());
      }
    }
  }

  /**
  *   This method adds the amount to be transferred to the account mentioned. The
  * procedure followed here is, first a savepoint is created before adding the
  * amount to the account. If an account with that number doesn't exist then
  * an exception is raised and the transaction is rolled back to the previous

  * savepoint created and the user is asked to enter the account number again.
  * If the update is performed successfully then the savepoint are deleted.
  */
  private void addAmtToSecondAccount(){
    PreparedStatement pstmt = null;
    try {
      int toAccountNo   = savepointFrame.frmTransfer.getToAccountNo();
      float amount      = savepointFrame.frmTransfer.getAmountTransfered();

      Savepoint secondSavepoint = conn.setSavepoint("afterTransfer");
      try{
        // Create a prepared statement object and cast it
        pstmt = conn.prepareStatement( " UPDATE Account_Master"
                                      + " SET Balance = Balance + ?"
                                      + " WHERE Account_Number = ? ");

        // Update the database with the value of balance thus obtained.
        pstmt.setFloat(1, amount);

        // The selected Account Number is retrieved
        pstmt.setInt(2, toAccountNo);

        // Execute the statement
        int i = pstmt.executeUpdate();

        // If this account_number doesn't exist then zero rows are returned.
        if(i == 0){
          throw new SQLException();
        }

      }catch(SQLException sqlException){
        // Rollback to the pervious save point
        conn.rollback(secondSavepoint);

        // This variable as a flag to know the point of transaction.
        isTransferStarted = true;

        // Show the transferAccountFrame again.
        savepointFrame.callTransferFrame(amount);
        return;
      }

      // Remove the data currently present and fills the table
      // with the data from database
      this.refreshAccountsTable();

      // Remove the savepoints
      firstSavepoint  = null;
      secondSavepoint = null;

      // Enables Transaction buttons depending on if it is the first
      // transaction or not.
      this.enableTransactionBtns();

      // Display the status in the status bar.
      savepointFrame.putStatus("Transfer between accounts "
                                + savepointFrame.frmTransfer.getFromAccountNo()
                                + ", " + toAccountNo + " is done.");

    } catch(Exception excep){   // Handle errors

      savepointFrame.putStatus("Error while updating account details: "
                                                  + excep.toString());
    } finally {
      try {
        // Close the PreparedStatement object
        if(pstmt != null) pstmt.close();
      } catch(Exception excep) {
        savepointFrame.putStatus("Error while closing Prepared Statement. : "
                                                + excep.toString());
      }
    }
  }

  /**
  *    This method is called if the Cancel button in the Transfer frame is clicked.
  * If the 'Cancel' button is clicked in between the transfer transaction i.e.
  * when the user is asked to enter the correct account number alone then a
  * rollback should be performed so that the amount debited from the first account
  * is cancelled.
  */
  private void transferCancelled(){
    try{
      // If the transferring the amount has started.
      if(isTransferStarted){
        isTransferStarted = false;

        // Rollback to the point before debiting the amount from the
        // account selected.
        conn.rollback(firstSavepoint);
        firstSavepoint = null;
      }

      savepointFrame.putStatus("Transfer between accounts is cancelled.");
    }catch(Exception excep){   // Handle errors
      savepointFrame.putStatus("Error while canceling transfer: "
                                                  + excep.toString());
    }
  }

  /**
   * This method creates a savepoint until the current point in the transaction,
   * makes an entry of the savepoint id created in LogTable and enables the
   * transaction buttons if they are still disabled.
   */
  private void createSavepoint(){
    try{
      // This method helps to creates an unnamed savepoint in the
      // current transaction and returns the new Savepoint object that
      // represents it. The syntax of this method is
      // Savepoint setSavepoint()
      // This method raises SQLException if the Connection is in auto commit mode.
      Savepoint savepoint = conn.setSavepoint();

      // Store the savepoint created in Hashtable so that it can be retrieved
      // when the transaction has to be rolled back.
      savepointsCreated.put(new Integer(savepoint.getSavepointId()),savepoint);

      // Add the savepoint created in the Log table
      savepointFrame.addToLogTable(savepoint.getSavepointId());

      // After a savepoint is created the Rollback, Rollback completely and
      // commit buttons are enabled if they are still not enabled and their
      // tool tips are set.
      if(!savepointFrame.rollbackButton.isEnabled()){

        savepointFrame.enableTransactionButtons();
      }

      // Update the status in the status bar
      savepointFrame.putStatus(" Savepoint with id "+savepoint.getSavepointId()
                                                    + " is created");
    }catch(Exception excep){
      // Display the exception in Status bar.
      savepointFrame.putStatus("Error while creating a Savepoint: "
                                                + excep.toString());
    }
  }

  /**
   * This method helps to rollback until the savepoint selected from the Log Table,
   * refreshes the Accounts table to display the correct details and removes all
   * the savepoint until the point selected in the log table.
   */
  private void transactionRollback(){
    try{
      // Get the details of the savepoint id selected and store it in ArrayList.
      ArrayList selectedLogDetails = savepointFrame.getSelectedLogDetails();

      // Get the savepoint id from it.
      int selectedSavepointId = Integer.parseInt(selectedLogDetails.get(0).toString());

      // Using the savepoint id selected above get the savepoint object
      // which was got when the Savepoint was created from the hash table.
      Savepoint savepoint = (Savepoint)savepointsCreated.get(new Integer(
                                                    selectedSavepointId));

      // This method undoes all changes after the given Savepoint object was set
      // in the current transaction and releases any database locks currently

      // held by this Connection object. It is similar to
      // the Rollback command in SQL. The syntax of this method is
      // void rollback(Savepoint savepointName)
      //          where savepointName specifies the point up to which the
      // transaction has to be rolled back.
      conn.rollback(savepoint);

      // Remove the data currently present in the Accounts table and fills
      // the table with the data from database.
      this.refreshAccountsTable();

      // Get the selected row.
      int selectedRow =  savepointFrame.logTable.getSelectedRow();

      // Remove all the savepoint entries present after the savepoint selected
      // from the Log Table
      savepointFrame.deleteRowsFromLogTable(selectedRow);

      // Update the status in the status bar
      savepointFrame.putStatus("The transaction is rolled back up to the point selected");

      // Update the transaction label. Change it to Transaction Rolled back
      savepointFrame.lblLog.setText("Transaction Rolledback");
    }catch(Exception excep){
       savepointFrame.putStatus("Error while trying to rollback the transaction: "
                                            + excep.toString());
    }
  }

  /**
   * This method helps to rollback all changes in the current transaction,
   * refreshes the Accounts table to display the correct details and removes all
   * entries in the log table.
   */

  private void completeRollback(){
    try {
      // This method undoes all changes made in the current transaction
      // and releases any database locks currently held by this Connection
      // object. It is similar to the Rollback command in SQL.
      conn.rollback();

      // Remove the data currently present and fills the table
      // with the data from database
      this.refreshAccountsTable();

      // Removes the current transaction details from Accounts and Log table.
      this.removeTransactionDetails("Transaction Rolled back");

      // Update the status in the status bar
      savepointFrame.putStatus("The transaction is rolled back completely");
    }catch (Exception excep){      // Handle errors
      savepointFrame.putStatus(" Error when rolling back the complete transaction: "
                                                   + excep.toString());
    }
  }

  /**
   * This method helps to commit the transaction and remove all details from
   * the log table.
   */
  private void commitTransaction(){
    try{
      // Commit the transaction. Makes all changes made since the previous
      // commit /rollback permanent and releases any database locks currently
      // held by this Connection object.
      conn.commit();

      // Removes the current transaction details from Accounts and Log table.
      this.removeTransactionDetails("Transaction Committed");


      // Update the status in the status bar
      savepointFrame.putStatus("The transaction is committed.");
    }catch(Exception excep){
       savepointFrame.putStatus("Error while committing the transaction: "
                                                    + excep.toString());
    }
  }

  /**
   * This method retrieves the Account balance of a particular account.
   *
   * @param accountNumber Number of the account whose balance should be
   *     retrieved
   * @return The balance of the account is returned
   */
  private float getAccountBalance(int accountNumber){
   PreparedStatement pstmt = null;
    try {
      // Create a Callable statement object and cast it
      pstmt = conn.prepareStatement("SELECT Balance FROM  "
                                      + "Account_Master WHERE Account_Number = ?");

      pstmt.setInt(1,accountNumber);

      // Execute the query
      ResultSet rs = pstmt.executeQuery();

      // Get the balance and return it
      rs.next();
      return (rs.getFloat(1));

    } catch(Exception excep){   // Handle errors
      savepointFrame.putStatus("Error while retrieving Balance details : "
                                                  + excep.toString());
      return -1;

    } finally {
      try {
        // Close the Statement object
        if(pstmt != null) pstmt.close();
      } catch(Exception excep) {
        savepointFrame.putStatus("Error while closing Prepared Statement. : "
                                                + excep.toString());
      }
    }
  }

  /**
   * This method removes all the savepoint details from the hash table, Log table
   * and disables all transaction buttons
   *
   * @param logLbl The text which should be displayed in the Log label.
   */
  private void removeTransactionDetails(String logLbl){
     // Delete all the savepoint details from the hash table.
     savepointsCreated.clear();

    // Remove all entries from Log Jtable
    savepointFrame.clearLogTable();

    // Set the transaction variable to false so that if an item is updated
    // after this then the transaction label is updated.
    isTransactionStarted = false;

    // Update the transaction label. Change it to Transaction Committed
    savepointFrame.lblLog.setText(logLbl);

    // Disable the transaction related buttons
    savepointFrame.disableTransactionbuttons();
  }

  /**
   * This method clears the account table and calls the method which helps to

   * retrieve all details from database and display in the account table.
   */
  private void refreshAccountsTable(){
    // Remove all previous elements existing in the Jtable
    savepointFrame.clearAccountsTable();

    // Get the records from the database again and display as that the
    // table reflects the database at all the time
    this.displayAllAccounts();
  }

  private void enableTransactionBtns(){
    // If this is the first updation performed then show the message 'Transaction
    // Started' as the log label and enable that the transaction has started.
    if(!isTransactionStarted){
      // Change the log label to Transaction Started
      savepointFrame.lblLog.setText("Transaction Started");
      isTransactionStarted = true;

      savepointFrame.completeRollbackButton.setEnabled(true);
      savepointFrame.completeRollbackButton.setToolTipText(
                                 savepointFrame.completeRollbackBtnToolTip);
    }

    // Enable the savepoint button and display its tool tip.
    savepointFrame.savepointButton.setEnabled(true);
    savepointFrame.savepointButton.setToolTipText(
                                          savepointFrame.savepointBtnToolTip);
    // Enable the commit button
    savepointFrame.commitButton.setEnabled(true);
    savepointFrame.commitButton.setToolTipText(savepointFrame.commitBtnToolTip);

  }

   /**
    * This method configures the Datasource with appropriate values of Host Name,
    * User Name, Password etc.
    *

    * Note that the configuration parameters are stored in ConnectionParams.java
    * file.
    *
    * @param ods Datasource object
    */
  private void configureDataSource(OracleDataSource ods, Properties prop) {

    // Driver type, either 'thin' or 'oci8'
    // if its a OCI driver then, the TNS entry name has to be specified
    ods.setDriverType("thin");

    // Database name
    ods.setDatabaseName((String)prop.get("Database"));

    // Host name
    ods.setServerName((String)prop.get("HostName"));

    // Port number
    ods.setPortNumber(Integer.parseInt((String)prop.get("Port")));

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

    // Sets the password
    ods.setPassword((String)prop.get("Password"));
  }

  /**
   * This method performs the clean up action and closes the application
   */
  void exitApplication(){
    // If connected to database
    if (conn != null) {
      try {
        // Rollback all the uncommitted data.
        conn.rollback();

        // Close the connection
        conn.close();

        conn = null;
      }catch (Exception excep){      // Handle errors
        savepointFrame.putStatus(" Error while Closing the connection: "
                      + excep.toString());
      }
    }
    // Close the application
    System.exit(0);
  }
}
E-mail this page
Printer View Printer View
Oracle Is The Information Company About Oracle | Oracle RSS Feeds | Careers | Contact Us | Site Maps | Legal Notices | Terms of Use | Privacy