print Printer View e-mail E-mail this page Bookmark Bookmark 
/*
 * @author  Savitha
 * @version 1.0
 *
 * Development Environment        :  Oracle JDeveloper 10g
 * Name of the Application        :  IeeeApplication.java
 * Creation/Modification History  :
 *
 *    Savitha      12-Sep-2003      Created.
 */
package oracle.otnsamples.jdbc;

// JDBC related classes
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

// SWING, AWT imports
import javax.swing.UIManager;
import java.awt.Dimension;
import java.awt.Toolkit;

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

// IO class
import java.io.IOException;

// Oracle JDBC classes
import oracle.jdbc.OraclePreparedStatement;
import oracle.jdbc.OracleResultSet;
import oracle.sql.BINARY_DOUBLE;
import oracle.sql.BINARY_FLOAT;
import oracle.jdbc.pool.OracleDataSource;

/**
 * This class demonstrates the support for the IEEE datatypes BINARY_FLOAT,
 * BINARY_DOUBLE from Oracle Database 10g and Oracle JDBC 10g.  BINARY_DOUBLE
 * datatype provides with the most data precision when compared to the rest of
 * the datatypes like BINARY_FLOAT, DOUBLE, FLOAT and NUMBER.
 *
 * The class actually uses a scenario where, the distance between the Sun and
 * different planets in our Solar System is expressed in terms of Astronomical
 * Unit.
 *
 * 1 Astronomical Unit (AU) = 149600000 Kms; the average distance between the
 * Sun and the Earth.
 *
 * For example, the average distance between Mercury and Sun is 58000000.
 * kilometers. To gets its AU distance,  the formula is: (distance in kms)/1 AU.
 * i.e 58000000/149600000 = 0.38770053475935828877005347593583...

 * The distance in terms of AU is stored in the database columns of the types:
 * BINARY_FLOAT, BINARY_DOUBLE and NUMBER. Depending on the precision these
 * datatypes offer, the above value is stored in the database. A clear
 * difference in the value can be seen when these values are retrieved.
 */
public class IeeeApplication {

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

 /**
  * The application constructor class to instantiate the class. This method will
  * instantiate the application GUI and places the application frame to the
  * center of the screen.
  */
  public IeeeApplication()  {

    // Instantiate GUI
    gui = new IeeeFrame(this);

    // 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 runnable method. Calls methods for creating and displaying
   * planet_au_distance table records.
   */
  public static void main(String[] args)  {
    try    {
      UIManager.setLookAndFeel(UIManager.getSystemLookAndFeelClassName());

      // Instantiate IeeeApplication class
      IeeeApplication sample = new IeeeApplication();

      // Check if the 'planet_au_distance' table is present, else create it.
      sample.checkTables();

      // Display existing records.
      sample.selectAllRecords();

    }  catch(Exception e) {
         System.out.println("Some error in the Application main method: "+
                           e.toString());
    }
  }

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

    // Get the user input values for planet and distance.
    String planet = gui.planet.getText();
    String distance = gui.distance.getText();

    // Dispatch INSERT and EXIT Events. CLEAR is handled by the frame itself.
    if( eventName.equals("INSERT") )
      insertRecord( planet, distance );
    else if( eventName.equals("EXIT") )
      exitApplication();
  }

  /**
   *  Method to insert a new row into the database using JDBC. Note that here in
   *  this method, the new APIs for handling BINARY_DOUBLE and BINARY_FLOAT are
   *  used. setBinaryDouble() and setBinaryFloat() are the APIs used to bind the
   *  parameters in the OraclePreparedStatement for the Oracle Database columns
   *  of the type BINARY_DOUBLE and BINARY_FLOAT respectively.
   */
  private void insertRecord( String planet,String dist) {
    // Database connection object
    Connection conn = null;

    // OraclePreparedStatement object to execute the SQL INSERT statement.
    OraclePreparedStatement opstmt = null;

    try  {
      // Get the database connection.
      conn = this.dbConnection();

      // Create the SQL query string to select the records.
      String sqlQuery = "INSERT INTO planet_au_distance "+
                        " VALUES(?,?,?,?,?,149600000)";

      // Create the PreparedStatement object with the SQL query String.
      opstmt = (OraclePreparedStatement)conn.prepareStatement(sqlQuery);

      // Variable to hold distance.
      double distDble = 0;

      // Validate the user input so that, valid values are obtained to insert
      // the new record.
      int flag = 1;
      if (("".equals(planet)) || ("".equals(dist))) {
        gui.putStatus("Enter Planet's name and its distance from the Sun "+
                      "in kilometers");
      }else if (dist.length()>19) {
        gui.putStatus("Enter a valid number for 'Distance' with "+
                      "length < 20 and without spaces.");
      } else if (!("".equals(dist))) {
           try {
             distDble = Double.parseDouble(dist);
             flag = 0;
           } catch (NumberFormatException numEx) {
               gui.putStatus("Enter a valid number for 'Distance' with "+
                             "length < 20 and without spaces.");
               flag =1;
           }
      }

      // Valid user input is indicated when the flag =0.
      // Then, insert the record.
      if (flag==0) {
      // Set the value for the bind parameters.
      opstmt.setString(1,planet);
      opstmt.setDouble(2,distDble);

      // 1 Astronomical Unit(AU)= 1.49600000 Km; avg. distance between Sun-Earth.
      Double astrUnit = new Double("149600000");
      double distance = Double.parseDouble(dist);

      // Divide the distance by AU.
      double distVal = distance/149600000;
      Double dbl1 = new Double(distVal);

      // Bind the 'BINARY_DOUBLE' datatype value.
      BINARY_DOUBLE biD = new BINARY_DOUBLE(dbl1.doubleValue());
      opstmt.setBinaryDouble(3,biD);

      // Bind the 'BINARY_FLOAT' datatype value.
      BINARY_FLOAT biF = new BINARY_FLOAT(dbl1.floatValue());
      opstmt.setBinaryFloat(4,biF);

      // Bind the normal 'NUMBER' datatype value.
      int biN = (int)distVal;
      opstmt.setInt(5,biN);

      // Execute the PreparedStatement.
      int val =opstmt.executeUpdate();

      // Update the jtable to reflect the new record.
      gui.addToJTable( planet, Double.parseDouble(dist), dbl1.doubleValue(),
                       dbl1.floatValue(),biN );

      gui.putStatus("The Record is inserted");
      }
    } catch (SQLException ex) { //Trap the SQL exceptions.
        gui.putStatus("Error in inserting record to the database "
                      +ex.toString());
        System.out.println(ex.toString());
    } catch (Exception ex) {    //Trap general exceptions.
        gui.putStatus("Exception in the method while inserting: "
                      +ex.toString());
        System.out.println(ex.toString());
    } finally  {
        try {
          // Close the PreparedStatement and the connection objects.
          if (opstmt != null ) opstmt.close();
          if (conn   != null ) conn.close();
        } catch(SQLException ex) {
            gui.putStatus(" SQLException : "+ex.toString());
        }
    }
  }

  /**
   *  Method to select records from the planet_au_distance table using JDBC.
   *  To retrieve the BINARY_DOUBLE and BINARY_FLOAT column values from the
   *  database, getDouble() and getFloat() APIs are used.
   */
  private void selectAllRecords() {

    // Database connection object.
    Connection conn = null;

    // PreparedStatement object for executing the SQL Query.
    OraclePreparedStatement opstmt = null;

    // Create the SQL query string to select the records.
    String sqlQuery = "SELECT planet, dist_kms, au_dist_binaryfloat, "+
                      " au_dist_binarydouble, au_dist_num "+
                      " FROM planet_au_distance ORDER BY dist_kms ";
    try {
      // Get the database connection.
      conn = this.dbConnection();

      // Create the PreparedStatement object with the SQL query String.
      opstmt = (OraclePreparedStatement)conn.prepareStatement(sqlQuery);

      // Execute the PreparedStatement.
      OracleResultSet orset =(OracleResultSet)opstmt.executeQuery();

      // Declare variables to hold database values.
      long           distKms  = 0;
      int            distNum  = 0;
      double         dbleVal  = 0;
      float          fltVal   = 0;
      String         name     = null;
      String         val      = null;

      // Loop through the resultset to get the values.
      while (orset.next()) {
        name = orset.getString(1);
        distKms = orset.getLong(2);
        val = String.valueOf(orset.getDouble(3));

        // Get the BINARY_DOUBLE value.
        dbleVal = orset.getDouble(3);

        // Get the BINARY_FLOAT value.
        fltVal = orset.getFloat(4);

        // Get the normal number value.
        distNum = orset.getInt(5);

        // Update the Jtable.
        gui.addToJTable( name,distKms, dbleVal, fltVal,distNum );
      }

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

      // Put the record retrieval message on the status bar.
      gui.putStatus(gui.tmodel.getRowCount() +" Records selected."+
                    "\n Enter values to insert a new record.");
    } catch( SQLException ex ) { //Trap SQL errors
        gui.putStatus("Error in selecting from the database " +ex.toString());
    } finally  {
        try {
          // Close the PreparedStatement and the connection objects.
          if (opstmt != null ) opstmt.close();
          if (conn   != null ) conn.close();
        } catch(SQLException ex) {
            gui.putStatus(" SQLException : "+ex.toString());
        }
    }
  }

  /**
   * 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() {

    // Database Connection Object
    Connection connection = null;

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

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

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

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

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

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

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

      // Set 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 SQLException
        gui.putStatus("Error in Connecting to the Database "+
                      '\n'+ex.toString());
        System.out.println(ex.toString());
    } catch(IOException ex) {  // Trap IOException
        gui.putStatus("Error in reading the properties file "+
                      '\n'+ex.toString());
        System.out.println(ex.toString());
    }
    return connection;
  }

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

    try     {
      conn = this.dbConnection();
      stmt = conn.createStatement();

      // Check from 'user_tables' data dictionary table if the table
      // 'planet_au_distance' exists.
      rset = stmt.executeQuery(" SELECT table_name FROM user_tables "+
                               " WHERE table_name = 'PLANET_AU_DISTANCE' ");

      // If the resultset of the above query does not have any record, it means
      // table does not exist.
      if (!rset.next()) {
        // Call the PopulateTable class to create the table.
        PopulateTable pop = new PopulateTable(conn, gui);
        pop.createDbObjects();
        gui.putStatus( "planet_au_distance Table created.");
      }
    } catch (SQLException sqlEx) {
        gui.putStatus(" Could not create table planet_au_distance : "
                      +sqlEx.toString());
    } finally  {
        try {
          if( rset != null ) rset.close();
          if( stmt != null ) stmt.close();
          if (conn != null ) conn.close();
        } catch(SQLException ex) {
            gui.putStatus(" SQLException : "+ex.toString());
        }
    }
  }

  /**
   *  Exit the application.
   */
  private void exitApplication() {
    try {
      gui.putStatus("Closing the application....please wait.....");

    } catch( Exception ex ) {   //Trap exceptions
        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