/*
 * @author  Rajesh
 * @version 1.0
 *
 * Development Environment        :  Oracle9i JDeveloper

 * Name of the Application        :  BatchingAndREFCursorSample.sqlj
 * Creation/Modification History  :
 *
 *    Rajesh       08-Oct-2001      Created
 *    Venky        05-Mar-2003      Certified on Linux platform
 *

 * Overview of Application        :
 *
 * This sample demonstrates how to read data from a REFCursor returned by a PLSQL
 * procedure in SQLJ. It also demonstrates how to implement batch updating in
 * SQLJ. Batch Updating is a feature by which the performance of an application
 * can be increased. This is achieved by queueing the database queries as a
 * batch and executing them together thereby reducing the round trip to the

 * database.
 *
 * Two important methods of interest in this class are :
 *
 *  1. populateProducts() - This method calls a PLSQL stored procedure which
 *                          returns a REF Cursor and stores the resultant data
 *                          in an iterator.
 *

 *  2. batchUpdate()      - This method shows how to set an execution context
 *                          for doing a batch update and executes 'UPDATE'
 *                          statements in a batch.
 *
 * The user interface for this sample is handled in the file
 * BatchingAndREFCursorFrame.java.
 *
 */
package oracle.otnsamples.sqlj.batchingandrefcursor;


// SQLJ related imports
import sqlj.runtime.ExecutionContext;
import oracle.sqlj.runtime.Oracle;

// SQLException related import
import java.sql.SQLException;

// Utility class import
import java.util.ArrayList;

import java.util.Enumeration;

public class BatchingAndREFCursorSample {

 // Iterator which holds the data returned as part of the calling
 // the PLSQL stored procedure.
 #sql public static iterator ProdIter(int PRODUCT_ID,String PRODUCT_NAME,  int QUANTITY_ON_HAND, int WAREHOUSE_ID);

 private ProdIter iterator              = null;  // Iterator type object
 private BatchingAndREFCursorFrame gui  = null;  // Handler for the GUI class


/**
 *  This method calls the PLSQL stored procedure 'GETPRODUCTDETAILS' and stores
 *  the result in a named iterator. Then the iterator is read and the JTable is
 *  populated with the product information.
 */
 private void populateProducts(){

    try{
      gui.putStatus("Loading product names. Please Wait...");

      // IMP : This statement calls the PLSQL stored procedure 'GETPRODUCTDETAILS'
      //       stores the result in iterator, which is an object of ProdIter.

      #sql { call GETPRODUCTDETAILS(:OUT iterator) };

      // Loop through the iterator, obtain individual values and add to the
      // GUI JTable. The set of statements within the while loop demonstrates
      // how to access the data from an iterator.
      while (iterator.next()){
        int pId       = iterator.PRODUCT_ID();       // Product Id
        String name   = iterator.PRODUCT_NAME();     // Product Name
        int qty       = iterator.QUANTITY_ON_HAND(); // Quantity on hand
        int wId       = iterator.WAREHOUSE_ID();     // Warehouse Id
        gui.addToJTable(name,  pId, qty, wId);       // Add to the table
      }
      iterator.close();                               // Close the iterator

      gui.putStatus("Please choose a Product ");
    }catch(Exception ex){                              //Trap SQL Errors
      gui.putStatus(ex.toString());
    }finally{
      try{
        if(iterator != null)
          iterator.close();                           // Close the iterator
      }catch(SQLException ex){
        gui.putStatus("Error closing the iterator : " + ex.toString());
      }
    }
 }

/**

 *  This method creates a batch of 'UPDATE' queries, creates an execution
 *  context, sets it to batching mode and then updates the database as a batch.
 *  This gives a significant improvement in the performance as the network round
 *  trip is reduced.
 */
 private void batchUpdate(){
   try{

     // Create an execution context and set it to batching mode
     ExecutionContext ec1 = new ExecutionContext();
     ec1.setBatching(true);

     // Loop through the number of times the user has updated different product
     // quantities and put them on batch. This updated information about product
     // quantity is stored in the variable dataForBatchUpdate, a Hashtable which

     // keeps track of the changes made by the user which will be used for
     // batch update and reset once the update is done.

     Enumeration enum =  gui.dataForBatchUpdate.elements();
     while(enum.hasMoreElements()){
        // Update the product details retrieved from the ArrayList
        ArrayList temp       = (ArrayList)enum.nextElement();
        Integer wareHouseId = (Integer)temp.get(2);
        // Get the individual values from the created ArrayList.
        int product_id       = ((Integer)temp.get(0)).intValue();
        int quantity_on_hand = ((Integer)temp.get(1)).intValue();
        int warehouse_id     = wareHouseId.intValue();

        // Create the query for updating individual product details.
        #sql [ec1] {update inventories set quantity_on_hand = :quantity_on_hand where product_id = :product_id and warehouse_id = :warehouse_id };


        // Remove the elements from the Hashtable
        gui.dataForBatchUpdate.remove(product_id+""+wareHouseId);
     }

     // The statement below execute the above statements in a batch and
     // commits the transaction.
     #sql [ec1] { COMMIT };

     // Clear the table and refresh the table to reflect the updated data.
     gui.tableModel.clearTable();
     this.populateProducts();
     gui.repaint();

    }catch(SQLException ex){       // Trap SQL Errors
      gui.putStatus("Exception while updating the database : " + ex.toString());
    }
  }


 /**
  *  Creates a database connection in SQLJ through Connection.properties. Please
  *  substitute the  connection parameters with appropriate values in
  *  Connection.properties file.
  */
  private void dbConnection(){
    try{
      gui.putStatus("Trying to connect to the Database");

      // Connects to the database.
      Oracle.connect(BatchingAndREFCursorSample.class,"../../../../Connection.properties");

    } catch(SQLException ex){                      //Trap SQL errors
      gui.putStatus(
              "Error in Connecting to the Database "+'\n'+ex.toString());
      System.out.println("Error connecting to the database : " + ex.toString());
      System.exit(0);

    }
  }

 /**
  * Constructor. Instantiates GUI.
  */
  public BatchingAndREFCursorSample() {
    try {

      gui = new BatchingAndREFCursorFrame(this);  // Instantiate GUI
      gui.setVisible(true);                       // Make the frame visible

    }catch (Exception e) {                        // Trap general errors
      e.printStackTrace();
    }
  }

 /**
  *  Main entry point for the class. Instantiates root class and  sets up the

  *  database connection and populates the Hotels JTable.
  */
  public static void main(String[] args)throws SQLException{

    // Instantiate application class
    BatchingAndREFCursorSample root = new BatchingAndREFCursorSample();

    root.dbConnection();                  // Setup the db connection
    root.gui.tableModel.clearTable();
    root.populateProducts();              // Populate JTable with Hotel Records

  }

/**
  * Dispatches the GUI events to the appropriate method, which perform
  * the required operations. This method is invoked when event occurs
  * in the GUI (like table Selection, Button clicks etc.) and is invoked
  * from the setupListeners section of BatchingAndREFCursorFrame class and
  * UpdateQuantityFrame class.
  */

  public void dispatchEvent(String eventName) {

    // Dispatch Event 'SHOW_UPDATE_FORM'
    if (eventName.equals("SHOW_UPDATE_FORM")){

      // Get the data for the selected row in the JTable and store in a ArrayList.
        ArrayList data = gui.getDataForSelectedRow();

      // Pass on the data to the showUpdateForm method in the GUI handler class
      // which will show the update form for the user to update the quantity on
      // hand for the product chosen. If the user has not selected any row, ask
      // them to select a product to do this operation.
      if(data != null)
        gui.showUpdateForm(data);
      else
        gui.putStatus("Please select a product!!!");
        }

    // Dispatch Event 'UPDATE_QUANTITY'
    if (eventName.equals("UPDATE_QUANTITY")){


      // Check whether the user has entered null value. If a null value is
      // found, report the message back to the user and show the form again.
      // Else store the updated quantity by calling the method
      // 'storeDataForBatchUpdate' which will store the new data in a ArrayList and
      // disposes the frame.
      if((gui.updateFrame.qtyOnHand.getText()).equals("")){
        gui.putStatus("Quantity cannot be empty");
        return;
      }else{
        gui.storeDataForBatchUpdate();
        gui.putStatus(" ");
        gui.updateFrame.setVisible(false);
      }
        }

    // Dispatch Event 'BATCH_UPDATE'
    if (eventName.equals("BATCH_UDPATE")){

      // Check if there is any product information to be updated. If yes, call the
      // method 'batchUpdate'  which will do an batch update and disable
      // the batch update button since there is no data to update. Else mention
      // that there  is no data to update.

      if (gui.dataForBatchUpdate.size() <= 0)
        gui.putStatus("No data to Update!!!");
      else
        this.batchUpdate();
        gui.setDetailsButton.setEnabled(false);
        }

    // Dispatch Event 'EXIT'
    if (eventName.equals("EXIT"))
        exitApplication();              // Exit gracefully
 }

/**
 * This method closes the connection object before exiting this module.
 */
  private void exitApplication(){
    try{
      Oracle.close();
      System.exit(0);
    }catch(Exception ex){
      System.out.println("Exception occured : " + ex.toString());
    }
  }

}