print Printer View e-mail E-mail this page Bookmark Bookmark 


/**
 * @author  Jagriti
 * @version 1.0
 *
 * Development Environment        :  Oracle9i JDeveloper
 * Name of the Application        :  MultipleCollectionSample.java

 * Creation/Modification History  :
 *
 *    Jagriti      12-Nov-2001      Created
 *    Elango       06-Nov-2002      certified with JDK 1.4
 *
 * Overview of Application        :
*  Multi-level collection types are collection types whose elements are themselves

*  directly or indirectly another collection type. Oracle 9i JDBC Driver supports
*  accessing/manipulating of multiple level collections.
*
*  This sample demonstrates this feature of Oracle 9i JDBC,
*  The explanation of the sample scenario is as follows:
* -> When this application is run a table with various geographic regions data
*    appears.
* -> The user can select a region and click "View Countries" button. A list of

*    countries corresponding to the region selected appears. The data is retrieved from
*    the COUNTIES_NT Nested Table, which is represented as COUNTRY_LIST column in the
*    GEOGRAPHIC_REGION table.
* -> The user can further select a country and click the "View Cities" button.
*    A list of cities corresponding to the country selected appears. The cities data
*    is retrieved from the Cities_NT Nested Table, which is represented as CITY_LIST
*    column in COUNTRIES_NT Nested table.
*  Hence demonstrating a table containing a nested table and the nested table containing
*  another nested table.

*
*  This java files handles all the logic of the sample. The GUI is handled by
*  GeographicRegionFrame, CountriesFrame, Citiesframe java files. This sample
*  uses custom classes generated by Oracle JPublisher utility, which provides a
*  representation of the collections in the database to the java objects.
*/


 package oracle.otnsamples.oracle9ijdbc.multiplecollection;


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

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


 // utils
 import java.math.BigDecimal;

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

import java.io.IOException;

 public class MultipleCollectionSample {


  // Connection instance
 Connection m_conn = null;

  // GUI handling
 GeographicRegionFrame m_GUI = null;

 /**
 *  Constructor. Initializes the GUI
 **/
 public MultipleCollectionSample()  {
   m_GUI = new GeographicRegionFrame(this);
 }


  /**
  *   Main entry point for the class. Connects to the database and displays the
  *   the list of geographic regions.
  **/
  public static void main(String args[]) {

    // Instantiate the class
    MultipleCollectionSample MCS = new MultipleCollectionSample();

    // Connect to the database
    MCS.dbConnect();

    // If connected, retrieve the geographic regions.
    if(MCS.m_conn != null)

      MCS.displayRegion();
  }

  /**
  *   Dispatches the appropriate methods to handle the events generated
  *   in the GUI. The methods do the required JDBC operations.
  **/
  public void dispatchEvent(String p_event) {

    if(p_event.equals("DISPLAY COUNTRIES"))
      // To display the countries corresponding to the region selected.
      this.displayCountries();
    else if(p_event.equals("DISPLAY CITIES"))
     // To display the cities corresponding to the country selected.
      this.displayCities();

    else if(p_event.equals("EXIT"))
     // Perform cleanup and close 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 dbConnect() {

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


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

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

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

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

      // Sets the database name
      ods.setDatabaseName((String)prop.get("SID"));

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


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

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

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

      m_GUI.putStatus(" Connected to " + prop.get("SID") +
                    " Database as " + prop.get("UserName"));


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

    }

  }


  /**
  *  Displays the list of regions names and region description from geographic_region
  *  database table
  **/
  public void displayRegion() {

     // Statement to query the geographic_region table
    Statement stmt = null;

    // ResultSet to hold the returned names and region description
    ResultSet rset = null;

    String name = null;
    String region_description = null;
    try {

      // Create a statement object
      stmt = m_conn.createStatement();

      // Retrieve name and region description from GEOGRAPHIC_REGION table
      rset = stmt.executeQuery(" SELECT name, region_description FROM geographic_region ");

      // Loop untill all the regions are added to the table
      while(rset.next()) {

        name = rset.getString(1);
        region_description = rset.getString(2);

        // Add data to the region table
        m_GUI.addToJTable(name,region_description);

      }
    } catch(Exception ex) {   // Trap errors
      m_GUI.putStatus(" Error while querying geographic regions :"+ex.toString());
    } finally {
      try {
        // Close the Statement and ResultSet objects

        if(stmt != null) stmt.close();
        if(rset != null) rset.close();
      } catch(Exception ex) { }
    }
}

 /**
  * Method to retrieve the countries data for the selected region from COUNTRY_LIST
  * nested type column of GEOGRAPHIC_REGION table.
  */
  public void displayCountries(){

    // Statement to query the Countries_NT  Nested table
    PreparedStatement pstmt = null;

    // ResultSet to hold the returned countries
    OracleResultSet rset = null;
    try {

      // Get the value of the region selected from the table
      String selectedRegion = m_GUI.getSelectedRegion();


      // prepare a query to fetch countries for the selected region
      // it returns a nested table
      pstmt = m_conn.prepareStatement(
               "SELECT country_list FROM geographic_region WHERE name = ? ");

      pstmt.setString(1,selectedRegion); // Bind the region name

      rset = (OracleResultSet)pstmt.executeQuery();// Execute Query
      m_GUI.m_countriesList.m_tableModel.clearTable();// Clear the table

      // loop through the resultset to fetch the collection of countries
      while ( rset.next() ) {

        // CountriesNt.java is automatically generated by Oracle JPublisher utility, it represents the
        // Nested table type column COUNTRY_LIST of GEOGRAPHIC_REGION table
        CountriesNt countryList = (CountriesNt) rset.getORAData(1,CountriesNt.getORADataFactory());

        // CountriesType.java is automatically generated by Oracle JPublisher utility.
        // Get the array that represent the COUNTRY_LIST Nested Table rows
        CountriesType[] countriesRef = countryList.getArray();

        // loop through the array to retrieve name and capital values and then
        // populate the JTable.

        for (int i = 0;i<countriesRef.length;i= i+1) {
          // getName() and getCapital() are methods of automatically generated methods in
          // java class CountriesType
          String name = countriesRef[i].getName();
          String capital = countriesRef[i].getCapital();

          // Add a country row to the Jtable
          m_GUI.m_countriesList.addToJTable(name, capital);
        }
      }

   }catch (SQLException ex) {
     m_GUI.putStatus(" Error in Querying Countries: "+ex.toString());
   }
   finally {
      try {
        // Close the Prepared Statement and ResultSet object
        if( pstmt != null) pstmt.close();
        if(rset != null) rset.close();
      } catch(Exception ex) { }
    }
   }



  /**
  * Method to retrieve the cities data for the selected country from CITY_LIST
  * nested type column contained in COUNTRY_NT Nested table of GEOGRAPHIC_REGION table.
  */
  public void displayCities(){

   // Statement to query the Cities_NT nested table
    PreparedStatement pstmt = null;

    // ResultSet to hold the returned cities
    OracleResultSet rset = null;

    try {
      // Get the value of the country selected from the table
      String selectedCountry = m_GUI.m_countriesList.getSelectedCountry();

      // prepare a query to fetch cities for a selected country
      // TABLE expression can be used to query any nested table column value.
      // It basically replaces the subquery expression.
      pstmt = m_conn.prepareStatement(
                       " SELECT c.city_list FROM geographic_region g, TABLE(g.country_list) c "+
                       " WHERE c.name = ? ");

      pstmt.setString(1,selectedCountry); // Bind the region name
      rset = (OracleResultSet)pstmt.executeQuery();// Execute Query

      m_GUI.m_countriesList.m_citiesList.m_tableModel.clearTable();// Clear the table

      // loop through the resultset to get the collection of cities
      while ( rset.next() ) {
        // CitiesNt.java is automatically generated by Oracle JPublisher utility, it represents the
        // Nested table type column CITY_LIST of COUNTRY_LIST column of GEOGRAPHIC_REGION table
        CitiesNt cityList = (CitiesNt) rset.getORAData(1,CitiesNt.getORADataFactory());

        // CityType.java is automatically generated by Oracle JPublisher utility. Getting
        // the array representing the CITY_LIST Nested Table rows
        CityType[] citiesRef = cityList.getArray();

        // loop through the array to retrieve name, population, altitude values and then
        // populate the JTable.
        for (int i = 0;i<citiesRef.length;i= i+1) {
          // getName(), getPopulation(), getAltitude() are methods of automatically generated java class
          // CityType
          String name = citiesRef[i].getName();
          BigDecimal population =  citiesRef[i].getPopulation();
          BigDecimal altitude =  citiesRef[i].getAltitude();

          // add city row to the JTable
          m_GUI.m_countriesList.m_citiesList.addToJTable(name, population.intValue(),altitude.intValue());
        }
      }
      // close the statement

      pstmt.close();
   }catch (SQLException ex) {
     m_GUI.putStatus(" Error in Querying cities : "+ex.toString());
   }
   finally {
      try {
        // Close the Prepared Statement and ResultSet object
        if( pstmt != null) pstmt.close();
        if(rset != null) rset.close();
      } catch(Exception ex) { }
    }
  }


  /**
  *  This method performs the clean up action and closes the application
  **/
  void exitApplication(){
    // If connected to database
    if (m_conn != null) {
      try {
        // Close the connection
        m_conn.close();
        m_conn=null;
      }
      catch (Exception ex){      // Trap errors
        m_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