print Printer View e-mail E-mail this page Bookmark Bookmark 
/**
 * @author  ukulkarn.in
 * @version 1.0

 *
 * Development Environment        :  JDeveloper 2.0
 * Name of the Application        :  sqljSTCollectionSample.sqlj
 * Creation/Modification History  :
 *
 *    ukulkarn.in       20-Feb-1999      Created

 *
 * Overview of Application        :
 *
 * This sample illustrates the accessing of collection datatypes (VARRAYs and
 * Nested Tables), and also Object REFs using SQLJ and Strongly Typed Custom
 * Java Classes.
 *

 * Collections allow the user to store an array of values in a database row,
 * while Object REFs allow users to point to a row in a Object Table. The
 * combination of collections and Object Refs is one way to implement a
 * master-detail relationship.
 *
 * In this we use a collection of object REFs to define a master-detail
 * relation between Countries and Cities. The sample shows the list of all
 * countries and when the user selects a country, the collection of Object REFs

 * is used to retrieve the list of cities in that country, which are then displayed
 * in another table.
 *
 * This sample uses custom java classes to retrieve the collections and object
 * REFs. This is a Strongly typed representation of the database object types.
 *
 * Database object types can also be represented in SQLJ using Oracle JDBC driver
 * extension classes, STRUCT, ARRAY and REF. This is a weakly typed representation
 * retrieval using these classes is illustrated in the "Weakly Typed Access of

 * Collections and Object REFs" sample
 *
 * The GUI for this sample is handled in sqljSTCollectionFrame.java
 *
 */

// SQLJ Runtime Classes
import sqlj.runtime.*;
import sqlj.runtime.ref.*;


import java.sql.*; // Package for JDBC classes

// Packages for Oracle Extensions
import oracle.jdbc.driver.*;
import oracle.sql.*;

// Iterator Declaration for representing Country Records
#sql iterator CountryIter ( String Name, String Geography, String Currency );

public class sqljSTCollectionSample {


   DefaultContext m_ctx; // Database Connection Context
   sqljSTCollectionFrame m_GUI; // The GUI handler for this sample

   static cityRefs l_cityList = null;

  /**
  *  Constructor. Instantiates GUI.
  **/
  public sqljSTCollectionSample() {
    m_GUI = new sqljSTCollectionFrame(this);  // Instantiate GUI
    m_GUI.setVisible(true);
  }


  /**
  *  Main entry point for the class. Instantiates the application class and
  *  sets up the database connection. Also populates the Countries Jtable
  *  with all the Countries Records.
  **/
  public static void main(String[] args) {
    sqljSTCollectionSample collSamp = new sqljSTCollectionSample();// Instantiate class
    collSamp.dbConnection();          // Connect to database
    if (collSamp.m_ctx != null) {
       collSamp.populateCountries(); // Populates the Countries JTable
    }
  }


  /**
  *  Dispatches the GUI events to the appropriate method, which performs
  *  the required JDBC operations. This method is invoked when event occurs
  *  in the GUI (like table Selection, Button clicks etc.). This method
  *  is invoked from the setupListeners method of sqljCollectionFrame.java
  **/
  public void dispatchEvent (String p_eventName) {

    //Dispatch Event
    if (p_eventName.equals("SELECT CITIES")) {
       int l_selectedRow = m_GUI.m_table.getSelectedRow();
       String l_name      = (String) m_GUI.m_tableModel.getValueAt(l_selectedRow,0);
       displayCitiesData(l_name);

    } else if (p_eventName.equals("EXIT"))
       exitApplication();
  }

  /**
  *  Creates a database connection object using SQLJ. Please substitute the
  *  database connection parameters with appropriate values in
  *  ConnectionParams.java
  **/
  public void dbConnection(){

    try{
      // Load the Oracle JDBC Driver and register it.
      DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());


      m_GUI.putStatus("Trying to connect to the Database");

      // Form the database connect string(TNSNAMES entry) as a name-value pair
      // using the connection parameters as specified in ConnectionParams.java
      String l_dbConnectString =
          "(DESCRIPTION=(ADDRESS=(HOST="+ConnectionParams.s_hostName+")"+
          "(PROTOCOL=tcp)(PORT="+ConnectionParams.s_portNumber+"))"+
          "(CONNECT_DATA=(SID="+ConnectionParams.s_databaseSID+")))";

      // To Connect to the  Oracle database, use an instance of  the
      // DefaultContext class. The syntax of the DefaultContext constructor is
      // DefaultContext(databaseURL,username,password, boolean auto-commit);
      // The databaseURL syntax is as follows:
      //      "jdbc:oracle:<driver>:@<db connection string>"
      // <driver>, can be 'thin' or 'oci8'
      // <db connect string>, is a Net8 name-value, denoting the TNSNAMES entry

      m_ctx = new DefaultContext(
                  "jdbc:oracle:thin:@"+l_dbConnectString,
                  ConnectionParams.s_userName,
                  ConnectionParams.s_password,
                  false);

      // Set the above connection context as the default context for this
      // application
      DefaultContext.setDefaultContext(m_ctx);

      m_GUI.putStatus("Connected to "+ConnectionParams.s_databaseSID+ " as "+
              ConnectionParams.s_userName);
    } catch(Exception ex){ //Trap SQL errors
      m_GUI.putStatus(
             "Error in Connecting to the Database "+'\n'+ex.toString());
    }
  }


  /**
  * Method to populate rows from the countries table
  **/
  public void populateCountries() {
     m_GUI.putStatus("Populating Countries ...");
     m_GUI.m_tableModel.clearTable();
     try {
        // Iterator Variable to hold Country Records
        CountryIter l_countryIter = null;

        // Embedded SQL : This SQLJ Call selects Name, Geography, Currency From
        // Obj_Table_Countries Table. These countries records are fetched into
        // an iterator variable 'm_countryIter'.
       #sql l_countryIter = { Select Name, Geography, Currency
                                      From Obj_Table_Countries ORDER BY NAME};*/

       // Loop through the iterator and retrieve all rows

       while (l_countryIter.next()) {
          //Retrieve column values for this row
          String l_name = l_countryIter.Name();
          String l_geography = l_countryIter.Geography();
          String l_currency = l_countryIter.Currency();

          // Update the countries JTable
          m_GUI.addToCountryJTable(l_name, l_geography, l_currency);
       }
       l_countryIter.close();  // Close the iterator
       m_GUI.putStatus("Connected to database and populated all countries");
       m_GUI.appendStatus("Please choose a country");
     } catch (SQLException ex) { // Trap SQL Errors
       m_GUI.putStatus(" Error in Querying countries table: ");
       m_GUI.appendStatus(" "+ex.toString());
     }

  }


  /**
  * Method to retrieve the city records for the selected country, using the
  * collection of Object REFs held in CITY_LIST column
  */
  public void displayCitiesData(String p_country){

    try {
      m_GUI.putStatus("Retrieving cities for the selected country ...");

      // clear the Cities Table
      m_GUI.m_tableModel1.clearTable();

      // Fetch the collection of Object References into a Custom Java Object.
      // Note that CITY_LIST Column holds the collection of Object References pointing City Objects

     #sql  {Select CITY_LIST  INTO :l_cityList FROM OBJ_TABLE_COUNTRIES WHERE NAME = :p_country};

      // Get the array of REFS into a custom Java class, representing the
      // REF to OBJ_TABLE_CITIES table rows.
      objTypeCityRef[] l_cityRefs = l_cityList.getArray();


      // Display all the cities
      for (int i = 0;i<l_cityRefs.length;i= i+1) {

         // Resolve the city reference and fetch the city object into the
         // custom java class
         objTypeCity l_cityObject = (objTypeCity) l_cityRefs[i].getValue();

         // Using the methods defined in the Custom Java Class 'objTypeCity', fecth
         // the city details.
         // Note : Methods getName(), getProvince(), getCityCode() etc in
         // objTypeCity class are generated by JPublisher automatically. This
         // makes the code much easier and less error prone.

         String l_name = l_cityObject.getName().toString();
         String l_state = l_cityObject.getProvince().toString();
         String l_cityCode = l_cityObject.getCityCode().toString();

         // Add a city Row to JTable
         m_GUI.addToCityJTable(l_name, l_state, l_cityCode);
        }

      m_GUI.putStatus("Retrieving cities for the selected country ...Done");
   }catch (SQLException ex) { // Trap SQL Errors
     m_GUI.putStatus(" Error in Querying: ");
     m_GUI.appendStatus(" "+ex.toString());
   }
  }

  /**
  *  Close the database Connection and exit the application
  **/
  public void exitApplication() {
   try {
       m_GUI.putStatus("Closing the connection....please wait.....");
       if(m_ctx != null)
          m_ctx.close(); //Close the m_ctx object.
   } catch(SQLException ex){ //Trap SQL Errors
      m_GUI.putStatus(ex.toString());
   }
   System.exit(0); //Exit the application
  }

}
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