/*
 * @author  Reghu
 * @version 1.2
 *
 * Development Environment        :  JDeveloper 2.0
 * Name of the Application        :  DMLSample.java
 * Creation/Modification History  :
 *
 *    Reghu       27-Dec-1998      Created
 *    Sujatha     21-May-2002      Certified on Oracle9i JDeveloper
 *    Savitha     10-Jan-2003      Certified on Linux
 *                                 Included checkTables() method
 *
 */
package oracle.otnsamples.jdbc.dml;

// Package for JDBC classes
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.Connection;
import java.sql.ResultSet;

// Package for AWT classes
import java.awt.Dimension;
import java.awt.Toolkit;

// Java utility classes
import java.util.Enumeration;
import java.util.Properties;
import java.util.ResourceBundle;
import java.io.IOException;

// oracle jdbc classes
import oracle.jdbc.pool.OracleDataSource;

/**
 * This application illustrates the SQL DML operations like SELECT, INSERT,
 * UPDATE and DELETE from a java program using JDBC. The user can insert,select,
 * update and delete records from the otn_airlines table using this sample.
 *
 * The GUI for this sample is handled in DMLFrame.java
 *
 *The sample uses the JDBC-Thin driver.
 *
 */
public class DMLSample {

  /** Database Connection Object */

  private Connection connection = null;

  /** GUI handler for this sample */
  private DMLFrame gui;

  /**
   *  Constructor , instantiates GUI.
   */
  public DMLSample() {
    gui = new DMLFrame(this); // Instantiate GUI


    // Display the frame in the center of screen
    Dimension screenSize = Toolkit.getDefaultToolkit().getScreenSize();
    Dimension frameSize  = gui.getSize();
    if( frameSize.height > screenSize.height ) {
      frameSize.height = screenSize.height;
    }
    if (frameSize.width > screenSize.width) {
      frameSize.width = screenSize.width;
    }
    gui.setLocation((screenSize.width - frameSize.width) / 2,
                      (screenSize.height - frameSize.height) / 2);
    gui.setVisible(true);
  }

  /**
   *  Main entry point for the class. Instantiates the DMLSample class
   *  and sets up the database connection.
   */
  public static void main( String args[] ) {
    DMLSample dml = new DMLSample();  // Instantiate DMLSample class

    //create connection object
    dml.connection = dml.dbConnection();

    // check if connection exists, else create it
    if( dml.connection != null ) {
      // Check if 'otn_airlines' table is present, else create it
      dml.checkTables();
    }
  }

  /**
   *  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 DMLFrame.java
   */
  public void dispatchEvent (String eventName) {

    // Get the user input values for code, name and partner
    String code = gui.codeTextfield.getText();
    String name = gui.nameTextfield.getText();
    String partner = gui.partnerTextfield.getText();

    // Dispatch Event
    if( eventName.equals("SELECT") )
      selectRecords( code, name, partner );
    else if( eventName.equals("UPDATE") )
      updateRecord( code, name, partner );
    else if( eventName.equals("INSERT") )
      insertRecord( code, name, partner );
    else if( eventName.equals("DELETE") )
      deleteRecord( code );
    else if( eventName.equals("EXIT") )
      exitApplication();
  }

  /**
   * This method reads Connection.properties file which is passed as
   * the parameter to it and load 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 Connection dbConnection() {
    try {
      gui.putStatus("Trying to connect to the Database");

      // Load the properties file to get the connection information
      Properties prop = 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")+". Please click on SELECT"+
                    " button to view records");

    } catch(SQLException ex) { // Trap SQL errors
        gui.putStatus("Error in Connecting to the Database "+'\n'+ex.toString());
    } catch(IOException ex) { // Trap IO errors
        gui.putStatus("Error in reading the properties file "+'\n'+ex.toString());
    }
    return connection;
  }

  /**
   * Checks if the table ('otn_airlines') is present, else creates it.
   * Look into PopulateTable.java for more details
   */
  private void checkTables()   {
    Statement stmt = null;
    ResultSet rset = null;
    try     {
      stmt = connection.createStatement();

      // check from User_tables data dictionary table if the table is existing.
      rset = stmt.executeQuery(" SELECT Table_Name FROM User_Tables "+
                               " WHERE Table_Name = 'OTN_AIRLINES' ");

      // if the resultset of the above query does not have any record, it means
      // table is not existing. So the table is created.
      if (!rset.next()) {
        // call the class to create the table
        PopulateTable pop = new PopulateTable(connection, gui);
        pop.createSchemaTables();
        gui.putStatus( "otn_airlines Table created." +". Please click on SELECT"+
                    " button to view records");
      }
    } catch (SQLException sqlEx) {
       gui.putStatus(" Could not create table otn_airlines : "+sqlEx.toString());
    } finally  {
        try {
          if( rset != null ) rset.close( );
          if( stmt != null ) stmt.close( );
        } catch(SQLException ex) { }
    }
  }

  /**
   *  Method to insert a new row into the database using JDBC.
   */
  private void insertRecord( String code,String name,String partner ) {
    try {
      // Prepare a SQL statement to insert a new record into the otn_airlines table
      PreparedStatement pst = connection.prepareStatement(
                                       "INSERT INTO otn_airlines VALUES (?,?,?)");

      // Bind the column values into the PreparedStatement
      pst.setString(1, code);
      pst.setString(2, name);
      pst.setString(3, partner);

      // Execute the PreparedStatement
      pst.execute();

      // Close the PreparedStatement object
      pst.close();

      // Update GUI to reflect new record
      gui.addToJTable( code, name, partner);
      gui.putStatus("The Record is inserted");
    } catch (SQLException ex) { //Trap SQL errors
        gui.putStatus("Error in inserting record to the database " +ex.toString());
    }
  }

  /**
   *  Method to select records from the otn_airlines table using JDBC, based on the
   *  query conditions entered by the user
   */

  private void selectRecords(String code,String name, String partner) {
    // The SQL query for selecting the rows from the otn_airlines table
    String query = "SELECT * FROM otn_airlines "
                  + "WHERE UPPER(code) LIKE UPPER(?) AND UPPER(name) LIKE UPPER(?) "+
                  " AND UPPER(partner) LIKE UPPER(?)";
    try {
      // Create a PreparedStatement based on the query
      PreparedStatement pst = connection.prepareStatement( query );

      // Use wild card % to query all records if no query condition was entered.
      code    = "%"+code+"%";
      name    = "%"+name+"%";
      partner = "%"+partner+"%";

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

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

      gui.tmodel.clearTable(); //Clear JTable in GUI


      // Populating the Result set, retrieve rows, and update GUI to
      // reflect each selected record
      while (resultSet.next()) { // Point result set to next row
        // Retrieve column values for this row
        String codeVal    = resultSet.getString(1);
        String nameVal    = resultSet.getString(2);
        String partnerVal = resultSet.getString(3);
        gui.addToJTable( codeVal, nameVal, partnerVal ); // Update Jtable
      }

       // Close PreparedStatement object
       pst.close();

       gui.putStatus(gui.tmodel.getRowCount() +" Records selected");
    } catch( SQLException ex ) { //Trap SQL errors
       gui.putStatus("Error in selecting from the database " +ex.toString());
    }
  }

  /**
   *  Method to update the selected row with new values using JDBC
   */
  private void updateRecord(String code, String name, String partner) {
    try {
      //Prepare a SQL statement to update the selected record
      PreparedStatement pst = connection.prepareStatement(
                  "UPDATE otn_airlines SET name = ?, partner = ? WHERE code = ?");

      // Bind the values for the update statement
      pst.setString(1, name);    // Sets the value of name
      pst.setString(2, partner); // Sets value of partner
      pst.setString(3, code);    // Sets the value of code

      // Execute the PreparedStatement and get the number of rows updated
      int numb = pst.executeUpdate();

      // Close the PreparedStatement object
      pst.close();

      // Update the GUI to reflect the changed record
      if( numb > 0 ) {
        gui.updateJTable( code, name, partner);
        gui.putStatus( numb + "  Record updated ");
      } else
      gui.putStatus("Record not updated. Code cannot be changed.");
    } catch( SQLException ex ) {  //Trap the SQL errors
        gui.putStatus("Error in updating to the database"+ '\n'+ex.toString());
    }
  }

  /**
   *  Method to delete the selected row from the JTable
   */
  private void deleteRecord(String code) {
    try {
      // Prepare a SQL statement to delete the selected row
      PreparedStatement pst = connection.prepareStatement(
                                        " DELETE FROM otn_airlines WHERE code=?");

      // Bind the airline code to the SQL statement
      pst.setString(1, code);//sets the value of code

      // Execute the PreparedStatement and obtain the number of rows deleted
      int numb =  pst.executeUpdate();

      // Close the PreparedStatement
      pst.close();

      // Delete the row from the JTable
      if( numb > 0 ) {
        gui.deleteFromJTable();
        gui.putStatus( numb + " Record deleted" );
      } else
        gui.putStatus( "Record not deleted" );
    } catch( Exception ex ) {  //Trap the SQL errors
        gui.putStatus("Error in deleting the row "+'\n'+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
  }
}