print Printer View e-mail E-mail this page Bookmark Bookmark 
/*
 * @author  Anupama
 * @version 1.0
 *
 * Development Environment        :  Oracle JDeveloper 10g
 * Name of the Application        :  PlsqlTablesSample.java
 * Creation/Modification History  :
 *
 *    Anupama      05-Nov-2001      Created
 *    Srikanth     10-Feb-2003      Certified on Linux
 *    Shrinivas    25-Jul-2003      Certified using thin driver
 */
 
package oracle.otnsamples.jdbc;

// JDBC imports
import java.sql.Connection;
import java.sql.Types;
import java.sql.SQLException;

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

// Java Utility Classes
import java.util.Properties;
import java.util.Vector;
import java.util.ResourceBundle;
import java.util.Enumeration;

import java.io.IOException;

/** Overview of the Application        :
 *
 *    This sample illustrates the use of PL/SQL Index-by Tables support for
 *  Scalar Types . Index-By Tables are one of the collections
 *  provided by PL/SQL to store arrays, lists, trees. They also help to move
 *  columns of data into and out of database tables or between client-side
 *  applications and stored subprograms. The use of Index-By Tables as IN and 
 *  OUT parameters of a function is also illustrated here.
 *
 *  Some of the new JDBC functions which support the Index-By Tables are :
 *
 *    i)  setPlsqlIndexTable(int, Object, int, int, int, int) :  Used to bind a
 *        PL/SQL index-by table parameter in the IN parameter mode with the array
 *        of the object sent. This method is defined in OraclePreparedStatement
 *        and OracleCallableStatement classes.
 *
 *   ii)  registerIndexTableOutParameter(int, int, int, int)  :
 *        Used to register a PL/SQL Index-By table in OUT parameter mode.
 *
 *  iii)  getPlsqlIndexTable(int)  :  This is a method defined in
 *        OracleCallableStatement to access the PL/SQL Index-By table bound as
 *        OUT parameter. It returns index-by table elements using
 *        JDBC default mappings. The return value is a Java array. The elements
 *        of this array are of the default Java type corresponding to the SQL
 *        type of the elements.
 *
 *   iv)  getPlsqlIndexTable(int, Class)  :  This is similar to the above
 *        method except that it returns index-by table elements using
 *        Java default mappings. The second parameter argument specifies a
 *        Java primitive type to which the index-by table elements are to be
 *        converted.
 *
 *  This sample illustrates the above APIs in a City Table managment
 *  application. User can either Add or Delete a City. The details of the city
 *  considered here are city Id, city Name, population of the city, language
 *  spoken by majority of the people in the city. The table in the database is
 *  populated by temporary values present in the array using PL/SQL
 *  Index-By table at a bulk. All the details of the city
 *  are retrieved from the database as a bulk and stored in the PL/SQL Index-By
 *  table.
 *  
 */
public class PlsqlTablesSample {
  // Connection instance
  Connection conn = null;

  // GUI handling
  PlsqlTablesFrame GUI;

  // Temporary variables to store Cities information present in database.
  int[] cityIdArray = null;

  // Default values with which the database is populated.
  String[] cityNameArray =  {"Delhi", "New York", "Tokyo", "Paris","Bangalore"};
  int[] populationArray  =  {200000, 400000, 300000, 100000,500000};
  String[] languageArray =  {"Hindi", "English", "Japanese", "French","Kannada"};

  // Maximum number of records to be sent to the database for batch updates
  int maxTablLen = 100;

  // maximum length of each element in the array being sent.
  int eleMaxLen = 100;

  /**
   *  Constructor. Initializes the GUI
   **/
  public PlsqlTablesSample() {
    GUI = new PlsqlTablesFrame(this);
  }

  /**
   *   Main entry point for the class. Connects to the database and populates the
   *   City JTable with rows from the Printmedia table.
   *   @param arg 
   */
  public static void main(String arg[]){
    // Instantiate the class
    PlsqlTablesSample plsqlTablesSample=new PlsqlTablesSample();

    // Connect to database
    plsqlTablesSample.dbConnect();

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

      // Populate the database with temporary values
      plsqlTablesSample.populateCityInfo();

      // Display the data in the panel.
      plsqlTablesSample.displayCities();
    }
  }

  /**
   *   Dispatches the appropriate methods to handle the events generated
   *   in the GUI. The methods do the required JDBC opertations.
   *   @param eventName - Event name
   */
  public void dispatchEvent (String eventName) {

    if (eventName.equals("ADD CITY"))
      // Add new City details
      this.addCityDetails();

    else if (eventName.equals("DELETE CITY"))
      // Delete the selected city information
      this.deleteCityDetails();

    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.
   * @param file - Database Configuration file
   * @return - Properties object
   * @throws IOException - Exception thrown while reading the file
   */
  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 dbConnect() {
    try {
      GUI.putStatus("Trying to connect to the Database");

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

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

      this.configureDataSource(ods, prop);

      // Create a connection  object
      conn = ods.getConnection();
 
      // Sets the auto-commit property for the connection to be false.
      conn.setAutoCommit(false);

    } catch (SQLException sqlEx){ // Handle SQL Errors
      GUI.putStatus("SQL Errors = " + sqlEx.toString());
    } catch(Exception excep) { // Handle other errors
      GUI.putStatus(" Couldn't configure Initial Context "
                                          + excep.toString());
    }
  }


  /**
  *   Populates the city_tab table with the values present in the
  *   global variables.
  **/
  public void populateCityInfo()  {
    // CallableStatement instance
    OracleCallableStatement ocstmt=null;

    try{
      // Length of the array being passed to the PL/SQL Index-By table.
      int currentLen  = cityNameArray.length;

      // Create a Callable statement object and cast it
      ocstmt =(OracleCallableStatement)conn.prepareCall(
                                  " begin"+
                                  " cityinfo_pkg.populate_DB(?,?,?);"+
                                  " end;");

      // setPlsqlIndexTable is used to enter data into a PLSQL Index by Table.
      // It is of the form
      // synchronized public void setPlsqlIndexTable
      //        (int paramIndex, Object arrayData, int maxLen, int curLen,
      //        int elemSqlType, int elemMaxLen) throws SQLException
      // arrayData - array of values to be bound to PL/SQL index-by table
      // maxLen -  maximum table length of the index-by table for batch updates
      // curLen -  actual size of the index-by table bind value in arrayData
      // elemSqlType - corresponding index-by table element type in OracleTypes.
      // elemMaxLen - index-table element  maximum length for CHAR, VARCHAR,
      // or RAW. This value is ignored for other types.
      // To set the Index-by Table parameter with the cityNames array
      ocstmt.setPlsqlIndexTable (1, cityNameArray, maxTablLen,
                               currentLen, OracleTypes.VARCHAR, eleMaxLen);

      // Set the Index-by table with the population of the cities.
      ocstmt.setPlsqlIndexTable (2, populationArray, maxTablLen,
                               currentLen, OracleTypes.NUMBER, 0);

      // To set the Index-by Table parameter with language array
      ocstmt.setPlsqlIndexTable (3, languageArray, maxTablLen,
                               currentLen, OracleTypes.VARCHAR, eleMaxLen);

      // Execute the callable statement.
      ocstmt.execute();

      // Commit the inserts
      conn.commit();

    }catch(Exception ex){
      GUI.putStatus(" Couldn't populate data in the Database "+ex.toString());
      ex.printStackTrace();
    }finally {
      try {
        // Close the CallableStatement object
        if(ocstmt != null) ocstmt.close();
      } catch(Exception e) {}
    }
  }

    /**
  *   Populates the JTable with the data present in PL/SQL Index-By table and
  *   enables the frame for action.
  **/
  public void displayCities() {
    OracleCallableStatement ocstmt=null;

    try {
      // Create a Callable statement object and cast it
      ocstmt =(OracleCallableStatement)conn.prepareCall(
                                  " begin"+
                                  " cityinfo_pkg.populate_Index_By_tbl();"+
                                  " cityinfo_pkg.retrieve_All_Cities(?,?,?,?);"+
                                  " end;");

      // Index-By Table can be registered using registerIndexTableOutParameter
      // whose format is
      // synchronized registerIndexTableOutParameter
      //          (int paramIndex, int maxLen, int elemSqlType, int elemMaxLen)
      //          throws SQLException
      // where paramIndex - the parameter position within the callable statement
      // maxLen - maximum number of records to be returned
      // elemSqlType - index_by table element type based on  OracleTypes class
      // eleMaxLen - maximum length of index_by table element for CHAR, VARCHAR
      // or RAW. This value is ignored for other types.
      ocstmt.registerIndexTableOutParameter(1,
                                            maxTablLen,
                                            OracleTypes.INTEGER,
                                            0);

      // Register the out parameter of the index by table for the 2nd parameter
      // It returns an array of city names in the form of string
      ocstmt.registerIndexTableOutParameter(2,
                                            maxTablLen,
                                            OracleTypes.VARCHAR,
                                            eleMaxLen);

      // Register the out parameter of the index by table for the 3rd parameter
      // It contains an array of population corresponding to each city
      ocstmt.registerIndexTableOutParameter(3,
                                            maxTablLen,
                                            OracleTypes.INTEGER,
                                            eleMaxLen);

      // Register the out parameter of the index by table for the 4th parameter
      ocstmt.registerIndexTableOutParameter(4,
                                            maxTablLen,
                                            OracleTypes.VARCHAR,
                                            eleMaxLen);

      // Execute the callable statement.
      ocstmt.execute();

      // Index By Table sent as OUT parameter of a procedure can be retrieved
      // as Java primitive array using
      // synchronized public Object getPlsqlIndexTable
      //         (int paramIndex, Class primitiveType) throws SQLException
      // where paramIndex is the index of the OUT parameter and
      // primitiveType - Class of the OUT parameter datatype  in java
      cityIdArray      = (int[]) ocstmt.getPlsqlIndexTable(1,
                                                        java.lang.Integer.TYPE);

      // Retrieve the array of name of the cities present in Index-by table
      cityNameArray    = (String[]) ocstmt.getPlsqlIndexTable(2);

      // Retrieve the population of the cities present as integer in
      // index-by table
      populationArray  = (int[]) ocstmt.getPlsqlIndexTable(3,
                                                        java.lang.Integer.TYPE);

      // Retrieve the language spoken in the corresponding cities from
      // Index-by table, since the mapping is not specified,
      // uses the default JDBC mapping
      languageArray    = (String[]) ocstmt.getPlsqlIndexTable(4);

      int sizeofTbl=cityIdArray.length;
      for(int i=0;i<sizeofTbl;i++){
        GUI.addToJTable(cityIdArray[i], cityNameArray[i], populationArray[i],
                          languageArray[i]);
      }
      // Enable the GUI for Add or Delete
      GUI.enableFrame();
      GUI.putStatus("  Click ADD / DELETE to add new or delete an "+
                                                  "existing city ");
    } catch(Exception ex){   // Trap errors
      GUI.putStatus("Error while retrieving City details : "+ex.toString());
    } finally {
      try {
        // Close the CallableStatement object
        if(ocstmt != null) ocstmt.close();
      } catch(Exception e) {}
    }
  }

 /**
  *   Deletes the details of the city selected in the Jtable from the
  *   PL/SQL Index-By table and database.
  **/
  public void deleteCityDetails(){
    // CallableStatement instance
    OracleCallableStatement ocstmt=null;
    Vector cityInfoVector = GUI.getSelectedCity();
    try {
      // Create a callable statement object and cast it
      ocstmt =(OracleCallableStatement)conn.prepareCall(
                                  " begin"+
                                  " cityinfo_pkg.delete_city(?);"+
                                  " end;");

      ocstmt.setInt(1,Integer.parseInt((String)cityInfoVector.elementAt(0)));
      ocstmt.execute();
      conn.commit();
      int selectedRow =  GUI.cityTable.getSelectedRow();
      GUI.deleteRow(selectedRow);

    } catch(Exception ex){   // Trap errors
      GUI.putStatus("Error while deleting City details : "+ex.toString());
    } finally {
      try {
        // Close the CallableStatement object
        if(ocstmt != null) ocstmt.close();
      } catch(Exception e) {}
    }
  }

  /**
  *   Adds the details of the city entered into the
  *   PL/SQL Index-By table and database.
  **/
  public void addCityDetails(){

    // CallableStatement instance
    OracleCallableStatement ocstmt=null;
    String cityName = GUI.getCityName();
    int population  = GUI.getPopulation();
    String language = GUI.getLanguage();

    try {

      // Create a callable statement object and cast it.Call the method to add
      // a city and retrieve the data pertaining to all the cities.
      ocstmt =(OracleCallableStatement)conn.prepareCall(
                                  " begin"+
                                  " cityinfo_pkg.add_City(?,?,?,?);"+
                                  " end;");

      ocstmt.setString(1,cityName);
      ocstmt.setInt(2,population);
      ocstmt.setString(3,language);
      ocstmt.registerOutParameter(4,Types.INTEGER);

      // Execute the Callable Statement
      ocstmt.executeUpdate();

      // Retrieve the cityId returned as OUT parameter from the procedure
      int cityId=ocstmt.getInt(4);

      // Add the city details as a row to the Panel.
      GUI.addToJTable(cityId, cityName, population,language);

      // Commit the insert
      conn.commit();

    } catch(Exception ex){   // Trap errors
      GUI.putStatus("Error while adding City details : "+ex.toString());
    } finally {
      try {
        // Close the CallableStatement object
        if(ocstmt != null) ocstmt.close();
      } catch(Exception e) {}
    }
  }

 /**
  * Clears the data present in the table city_Tab.
  **/
  public void clearTblData(){

    // CallableStatement instance
    OracleCallableStatement ocstmt=null;
    try{
      // Create a Callable statement object and cast it
      ocstmt =(OracleCallableStatement)conn.prepareCall(
                                  " begin"+
                                  " cityinfo_pkg.clear_DB();"+
                                  " end;");

      // Execute the callable statement.
      ocstmt.execute();

      // Commit the insert
      conn.commit();

    }catch(Exception ex){
      GUI.putStatus(" Couldn't Delete data in the Database "+ex.toString());
    }finally {
      try {
        // Close the CallableStatement object
        if(ocstmt != null) ocstmt.close();
      } catch(Exception e) {}
    }
  }

   /**
    * This method configures the Data source 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) {

    // Database Host Name
    ods.setServerName(prop.getProperty("HostName"));

     // Set the database SID
    ods.setDatabaseName(prop.getProperty("SID")); 

    // Set database port
    ods.setPortNumber( new Integer( prop.getProperty("Port") ).intValue());

    // Set the driver type
    ods.setDriverType  ("thin");

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

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

  /**
  *  This method performs the clean up action and closes the application
  **/
  void exitApplication(){
    // If connected to database
    if (conn != null) {
      try {
        clearTblData();
        // Close the connection
        conn.close();
        conn = null;
      }
      catch (Exception ex){      // Trap errors
        GUI.putStatus(" Error while Closing the connection: "+ex.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