/*
 * @author  ukulkarn.in
 * @version 1.2
 *
 * Development Environment        :  JDeveloper 2.0
 * Name of the Application        :  STCollectionSample.java
 * Creation/Modification History  :
 *
 *    ukulkarn.in       30-Dec-1998     Created
 *    neshastr          22-May-2002     Certified on Oracle9i JDeveloper
 *    Stephen           15-May-2003     Certified on Linux
 *
 */

package oracle.otnsamples.jdbc.stcollection;

import java.util.Properties;
import java.util.ResourceBundle;
import java.util.Enumeration;
import java.io.IOException;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Connection;

// Packages for Oracle Extensions
import oracle.jdbc.driver.OracleResultSet;
import oracle.jdbc.pool.OracleDataSource;

/**
 * This sample illustrates the accessing of collection datatypes (VARRAYs and
 * Nested Tables), and also Object REFs using JDBC 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 JDBC 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 STCollectionFrame.java
 */


public class STCollectionSample {
  
  private Connection connection = null; // The database connection object
  
  private STCollectionFrame gui; // The gui handler for this class
  
  /**
   * Constructor. Instantiates gui
   */  
  public STCollectionSample() {
    gui = new STCollectionFrame( this );
    gui.setVisible( true );
  }
  
  /**
   * Main entry point to the application class. Instantiates the application
   * class, sets up the database connection and populates the Countries Jtable
   * with all the Countries Records
   */
  public static void main( String[] args ) {
    STCollectionSample rootFrame = new STCollectionSample(); // Instantiate Class
    rootFrame.dbConnection(); // Setup the Database Connection
    rootFrame.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 STCollectionFrame.java
   */
  public void dispatchEvent( String eventName ) {
    
    // Dispatch Event
    if( eventName.equals( "SELECT CITIES" ) ) {
      int selectedRow = gui.table.getSelectedRow();
      String name = (String)gui.tableModel.getValueAt( selectedRow, 0 );
      displayCitiesData( name );
    } else {
      if( eventName.equals( "EXIT" ) ) {
        exitApplication();
      }
    }
  }
  
  /**
   * This method reads a properties file which is passed as
   * the parameter to it and loads it into a java Properties
   * object and returns it.
   */
  private 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
   */
  private void dbConnection() {
    try {
      gui.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();
      
      // 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" ) );
      connection = ods.getConnection();
      gui.putStatus( " Connected to " + prop.get( "SID" ) + " Database as " + 
          prop.get( "UserName" ) );
    }
    catch( SQLException ex ) { // Trap SQL errors
      gui.putStatus( "Error in Connecting to the Database " + '\n' + 
          ex.toString() );
    }
    catch( IOException ex ) { // Trap SQL errors
      gui.putStatus( "Error in reading the properties file " + '\n' + 
          ex.toString() );
    }
  }
  
  /**
   * Method to populate rows from the countries table
   */
  private void populateCountries() {
    gui.putStatus( "Populating Countries ..." );
    gui.tableModel.clearTable();
    try {
      
      // create a Statment with the established connection to the database
      Statement stmt = connection.createStatement();
      
      // Execute the query and get the result set
      ResultSet rset = stmt.executeQuery( "SELECT Name, Geography, Currency " + 
          "FROM Obj_Table_Countries ORDER BY name" );
      
      // Loop through the resultset and retrieve all rows
      while( rset.next() ) {
        
        //Retrieve column values for this row
        String name = rset.getString( 1 );
        String geography = rset.getString( 2 );
        String currency = rset.getString( 3 );
        
        // Update the countries JTable
        gui.addToCountryJTable( name, geography, currency );
      }
      gui.putStatus( "Connected to database and populated all countries" );
      gui.appendStatus( "Please choose a country" );
    } catch( SQLException ex ) { // Trap SQL Errors
      gui.putStatus( " Error in Querying countries table: " );
      gui.appendStatus( " " + ex.toString() );
    }
  }
  
  /**
   * Method to retrieve the city records for the selected country, using the
   * collection of Object REFs held in CITY_LIST
   */  
  private void displayCitiesData( String country ) {
    try {
      gui.putStatus( "Retrieving cities for the selected country ..." );
      
      // Prepare a query to fetch cities for a selected country
      PreparedStatement pstmt = connection.prepareStatement( 
          "SELECT CITY_LIST FROM OBJ_TABLE_COUNTRIES WHERE NAME = ? " );
      pstmt.setString( 1, country ); // Bind the country name

      // Execute Query
      OracleResultSet rset = (OracleResultSet)pstmt.executeQuery(); 
      
      // Instantiate a HashTable to specify mapping
      java.util.Hashtable mymap = new java.util.Hashtable();
      
      // Load Java Class
      Class obj = Class.forName("oracle.otnsamples.jdbc.stcollection.CityRefs");
      
      // Map the loaded java class to the object-type
      mymap.put( "OBJ_TYPE_CITY", obj );
      
      // Loop through the resultset to get the collection of objects
      while( rset.next() ) {
        
        // clear the Table
        gui.tableModel1.clearTable();
        
        // Custom Java Object which holds the collection of ObjectRefs in the
        // CITY_LIST column       
        CityRefs cityList = (CityRefs)rset.getORAData(1, 
            CityRefs.getORADataFactory() );
        
        // Get the array of REFS into a custom Java class, representing the
        // REF to OBJ_TABLE_CITIES table rows.
        ObjTypeCityRef[] cityRefs = cityList.getArray();
        
        // Display all the cities
        for( int i = 0;i < cityRefs.length;i = i + 1 ) {
          
          // Resolve the city reference and fetch the city object into the
          // custom java class
          ObjTypeCity cityObject = (ObjTypeCity)cityRefs[ i ].getValue();
          
          // Using the methods defined in the Custom Java Class 'ObjTypeCity',
          // fetch 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 name = cityObject.getName().toString();
          String state = cityObject.getProvince().toString();
          String cityCode = cityObject.getCityCode().toString();
          
          // Add a city Row to JTable
          gui.addToCityJTable( name, state, cityCode );
        }
      }
      
      // close the statement
      pstmt.close();
      gui.putStatus( "Retrieving cities for the selected country ...Done" );
    } catch( SQLException ex ) {
      gui.putStatus( " Error in Querying: " );
      gui.appendStatus( " " + ex.toString() );
    } catch( Exception ex ) {
      gui.putStatus( " Unknown error: " );
      gui.appendStatus( " " + ex.toString() );
    }
  }
  
  /**
   *  Close the database Connection and exit the application
   */  
  private void exitApplication() {
    try {
      gui.putStatus( "Closing the connection....please wait....." );
      if( connection != null ) {
        connection.close(); //Close the connection object.
      }
    } catch( SQLException ex ) { //Trap SQL Errors
      gui.putStatus( ex.toString() );
    }
    System.exit( 0 ); //Exit the application
  }
}