/*
 * @author  Arun Prasath S
 * @version 1.1
 *
 * Development Environment        :  JDeveloper 2.0
 * Name of the Application        :  ObjectJavaSample.java
 * Creation/Modification History  :
 *
 *    asriniva.in    10-Feb-1999    Created
 *    Stephen        09-May-2003    Certified on Linux and Oracle9i JDeveloper
 *
 */
package oracle.otnsamples.jdbc.javaobjects;
import java.util.Enumeration;
import java.util.Properties;
import java.util.ResourceBundle;
import java.math.BigDecimal;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.io.IOException;

//Package for Oracle Extensions to JDBC
import oracle.jdbc.driver.OraclePreparedStatement;
import oracle.jdbc.driver.OracleResultSet;
import oracle.jdbc.pool.OracleDataSource;


/**
 * This sample illustrates retrieval and manipulation of Objects from a
 * Oracle database using JDBC.
 *
 * Objects can be accessed either using oracle.sql.STRUCT or by defining
 * custom Java Classes to represent the Oracle Object Type. This sample
 * illustrates access using the Strongly typed object generated by JPublisher,
 * while retrieval using weakly typed objects (oracle.sql.STRUCT) is illustrated
 * in Object Oracle Sample.
 *
 * In this sample the user can perform DML operations like select, insert
 * update and delete on the table TABLE_OBJ_CITIES, which has a column
 * of the object type OBJ_TYPE_CITIES.
 * 
 * The GUI part of this sample is handled seperately in ObjectJavaFrame.java
 */
public class ObjectJavaSample {
  Connection connection = null;   // database connection object
  ObjectJavaFrame gui;            // For Handling the GUI related operations

  /**
   * Constructor which in turn calls the constructor of GUI class
   */
  public ObjectJavaSample() {
    try {
      gui =new ObjectJavaFrame(this);
    } catch (Exception e) {     // trap the errors if any
      gui.putStatus(" Error :");
      gui.appendStatus(" "+e.toString());
    }
  }

  /**
   * Main entry point for the class. Instantiates the root frame and
   * sets up the database connection.
   */
  public static void main(String[] args) {
    ObjectJavaSample ojs = new ObjectJavaSample();
    ojs.dbConnection();   // setup database connectivity
  }

  /**
   * 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 section of ObjectJavaFrame.java
   */
  public void dispatchEvent (String eventName) {
    // Get the values from the GUI
    String name    = gui.cityName.getText();
    String density = gui.populationDensity.getText();
    String state   = gui.stateProvince.getText();
    String code    = gui.cityCode.getText();

    //  For dispatching the select event
    if (eventName.equals("SELECT"))
      selectRecord(name, density, state, code);
    else if (eventName.equals("INSERT"))
      insertRecord(name, density, state, code);
    else if (eventName.equals("UPDATE"))
      updateRecord(name, density, state, code);
    else if (eventName.equals("DELETE")) {
      int row = gui.cityTable.getSelectedRow();
      String scode = (String)gui.cityTableModel.getRow(row).elementAt(2);
      deleteRecord(scode);
      gui.cityTableModel.deleteRow(row);
    } else if (eventName.equals("EXIT"))     // For dispatching the exit event
      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 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 boolean 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 I/O errors
      gui.putStatus("Error in reading the properties file "+'\n'+ex.toString());
    }
 
    // Return false if failed to obtain connection object
    if( connection != null )
        return true;
        
    return false;
  } 

  /**
   * This function selects the rows from the TABLE_CITY_OBJ table,  based on the
   * query condition entered
   */
  private void selectRecord(String name, String density, String state, 
      String code) {
  
    //The SQL query for selecting the rows from the TABLE_CITY_OBJ table
    String query = "SELECT * FROM TABLE_CITY_OBJ a "+
        "WHERE a.OBJCITY.CITY_CODE LIKE ? AND a.OBJCITY.NAME LIKE ?";
    try {
      //Create a PreparedStatement based on the query in query
      PreparedStatement pst = connection.prepareStatement(query);

      //If no query condition has been entered, change the bind values
      //to select all records
      if(code.equals(""))
        code = "%";
      else 
        code = "%"+code+"%";
      if(name.equals(""))
        name = "%";
      else name = "%"+name+"%";

      //Bind the PreparedStatement with corresponding values
      pst.setString(1,code);
      pst.setString(2,name);

      //Execute the PreparedStatement
      OracleResultSet resultSet = (OracleResultSet)pst.executeQuery();

      // create a type map using java.util.Hashtable object for specifying the
      // mapping between the Oracle Object-Type and the generated java class
      // representing the Object-Type
      // Instantiate a HashTable to specify mapping
      java.util.Hashtable mymap = new java.util.Hashtable();

      // Load Java Class
      Class obj = Class.forName("oracle.otnsamples.jdbc.javaobjects.CityObject"); 

      // Map the loaded java class to the object-type
      mymap.put ("OBJ_TYPE_CITY", obj);

      // loop through the resultset and retrieve all rows
      while(resultSet.next()) {
      
        // specify the type map while calling getObject method
        CityObject city;

        // Retrieve the column from the ResultSet into the  CityObject
        // class. This class represents the OBJ_TYPE_CITIES column in the
        // database, and hence the retrieved object is cast to CityObject
        city = (CityObject)resultSet.getObject(1, mymap);

        // add the row values to the JTable
        gui.addToJTable(city.getName(),
            (city.getProvince()==null)?"":city.getProvince(),
            city.getCityCode(),
            city.getPopulationDensity());
      }
      pst.close();     // close the statement which also closes the resultset
      gui.appendStatus(" Rows selected");

    } catch (SQLException ex) { // trap sql errors
      gui.putStatus(" Error in Querying: ");
      gui.appendStatus("\n"+ex.toString());
    } catch (Exception ex) {    //trap general errors
      gui.putStatus(" Error in Querying: ");
      gui.appendStatus("\n"+ex.toString());
    }
  }

  /**
   * This function inserts a new row into TABLE_CITY_OBJ table
   */
  private void insertRecord(String name, String density, String state,
      String code) {
    java.math.BigDecimal densityint = new BigDecimal(0);
    
    // Convert density to Bigdecimal if entered.
    if (density.compareTo("") != 0) {
      try {
        densityint = new BigDecimal(density);
      } catch (NumberFormatException ex) {
        gui.putStatus(" Error in constructing BigDecimal: ");
        gui.appendStatus("\n"+ex.toString());
      }
    }
    try {
    
      // Construct an instance of the cityObject class for
      // insertion. This object represents the object type OBJ_TYPE_CITIES
      // in the database.
      CityObject row = new CityObject();
      row.setCityCode(code);
      row.setName(name);
      row.setPopulationDensity(densityint);
      row.setProvince(state);

      // Prepare the SQL statement for insertion
      OraclePreparedStatement ps = 
          (OraclePreparedStatement)connection.prepareStatement(
          "INSERT INTO TABLE_CITY_OBJ VALUES (?)");

      // Bind the Java Object and City code to the above PreparedStatement
      ps.setObject(1,row); // Bind the Java Object to the above Statement
      ps.executeUpdate();  // execute the insert statement
      ps.close();         // close the prepared statement
      gui.putStatus(" Row inserted");

      // add the inserted row to the JTable
      gui.addToJTable(row.getName(),
          (row.getProvince()==null)?"":row.getProvince(),
          row.getCityCode(), row.getPopulationDensity());
    } catch (SQLException ex) {  // trap sql errors
      gui.putStatus(" Error in Inserting: ");
      gui.appendStatus(" "+ex.toString());
    }
  }

  /**
   * This functions Updates the existing rows from the Object table
   */
  private void updateRecord(String name, String density, String state, 
      String code) {
    java.math.BigDecimal densityint = new BigDecimal(0);
    // Convert density to Bigdecimal if entered
    if (density.compareTo("") != 0) {
      try {
        densityint = new BigDecimal(density);
      } catch (NumberFormatException ex) {
        gui.putStatus(" Error in constructing BigDecimal: ");
        gui.appendStatus("\n"+ex.toString());
      }
    }
    try {
      // Construct an instance of the cityObject class for
      // updation. This object represents the object type OBJ_TYPE_CITIES
      // in the database.
      CityObject row = new CityObject();
      row.setCityCode(code);
      row.setName(name);
      row.setPopulationDensity(densityint);
      row.setProvince(state);

      // prepare the SQL statement for updation
      OraclePreparedStatement ps = 
          (OraclePreparedStatement)connection.prepareStatement(
          "UPDATE TABLE_CITY_OBJ a SET a.OBJCITY = ?"+
          "WHERE a.OBJCITY.CITY_CODE = ?");

      // Bind the Java Object and City code to the above PreparedStatement
      ps.setObject(1,row);
      ps.setString(2,code);

      ps.executeUpdate(); // execute the update statement
      ps.close();         // close the prepared statement
      gui.putStatus(" Row updated");

      // update the row values to the JTable
      gui.updateJTable(row.getName(),row.getProvince(),
          row.getCityCode(),row.getPopulationDensity());
    } catch (SQLException ex){ // trap sql errors
      gui.putStatus(" Error in Updating: ");
      gui.appendStatus("\n"+ex.toString());
    }
  }

  /**
   * This function deletes the selected row from the Object table
   */
  private void deleteRecord(String code){

    try {
      // Prepare the SQL statement for deletion
      PreparedStatement ps = connection.prepareStatement(
                "DELETE FROM TABLE_CITY_OBJ a WHERE a.OBJCITY.CITY_CODE =?");
      ps.setString(1,code); // set the values in the statement

      ps.executeUpdate();      // execute the delete statement
      ps.close();         // close the prepared statement
      gui.putStatus("Row deleted");
    } catch (SQLException ex){ // catch any exceptions that occur
      gui.putStatus(" Error in Deleting: ");
      gui.appendStatus("\n"+ex.toString());
    }
    // clear the selection
    gui.cityTable.clearSelection();
  }
  
  /**
   * Closes the connection and exits from the program when exit button is
   * pressed
   */
  private void exitApplication(){
    if (connection != null) {
      try {
        connection.close();              // close the connection
      } catch (Exception ex){              // Trap SQL Errors
        gui.putStatus(" Error in Closing the connection: ");
        gui.appendStatus(" "+ex.toString());
      }
    }
    System.exit(0);
  }
}