print Printer View e-mail E-mail this page Bookmark Bookmark 
/**
 * @author  Arun Prasath S
 * @version 1.0

 *
 * Development Environment        :  JDeveloper 2.0
 * Name of the Application        :  sqljObjectJavaSample.sqlj
 * Creation/Modification History  :
 *
 *    asriniva.in       10-Feb-1999      Created

 *
 * This sample illustrates retrieval and manipulation of Objects from a
 * Oracle8 database using SQLJ.
 *
 * Objects can be accessed either using oracle.sql.STRUCT or by defining
 * custom Java Classes to represent the Oracle 8 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 SQLJ Object Oracle 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 generated java class representing OBJ_TYPE_CITIES (CityObject) has been
 * extended by myCityObject which has an additional method getDensityString

 * that converts the population density to words. This method is used by the
 * sample to display the population density in words.
 *
 * The GUI part of this sample is handled seperately in sqljObjectJavaFrame.java
 *
 */

import java.util.*;
import java.math.*;


import java.sql.*;         //Package for JDBC classes

// SQLJ Runtime Classes
import sqlj.runtime.*;
import sqlj.runtime.ref.*;

//Package for Oracle Extensions to JDBC
import oracle.sql.*;
import oracle.jdbc.driver.*;


// Iterator to retrieve all rows in the TABLE_CITY_OBJ table
#sql iterator CityIter (myCityObject city );

public class sqljObjectJavaSample {

  DefaultContext m_dCtx;            // Default database connection context
  sqljObjectJavaFrame m_GUI;            // For Handling the GUI related operations


  /**

  * Constructor which instantiates the GUI class
  **/
  public sqljObjectJavaSample() {
    try {
      m_GUI =new sqljObjectJavaFrame(this);
    }catch (Exception e) {     // trap the errors if any
      m_GUI.putStatus(" Error :");
      m_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) {
    sqljObjectJavaSample OOS = new sqljObjectJavaSample();
    OOS.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 p_eventName) {
    // Get the values from the GUI
    String l_name    = m_GUI.m_cityName.getText();
    String l_density = m_GUI.m_populationDensity.getText();
    String l_state   = m_GUI.m_stateProvince.getText();
    String l_code    = m_GUI.m_cityCode.getText();

    //  For dispatching the select event
    if (p_eventName.equals("SELECT"))
      selectRecord(l_name, l_density, l_state, l_code);
    else if (p_eventName.equals("INSERT"))
      insertRecord(l_name, l_density, l_state, l_code);
    else if (p_eventName.equals("UPDATE"))

      updateRecord(l_name, l_density, l_state, l_code);
    else if (p_eventName.equals("DELETE")) {
      int l_row = m_GUI.m_cityTable.getSelectedRow();
      String l_scode = (String)m_GUI.m_cityTableModel.getRow(l_row).elementAt(2);
      deleteRecord(l_scode);
    } else if (p_eventName.equals("EXIT"))     // For dispatching the exit event
      exitApplication();
  }


  /**
  *  Creates a database connection object using SQLJ. Please substitute the
  *  database connection parameters with appropriate values in
  *  ConnectionParams.java
  **/

  public void dbConnection(){

    m_GUI.enableButtons(false);
    try{
      // Load the Oracle JDBC Driver and register it.
      DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

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

      // Form the database connect string(TNSNAMES entry) as a name-value pair
      // using the connection parameters as specified in ConnectionParams.java
      String l_dbConnectString =
          "(DESCRIPTION=(ADDRESS=(HOST="+ConnectionParams.s_hostName+")"+
          "(PROTOCOL=tcp)(PORT="+ConnectionParams.s_portNumber+"))"+
          "(CONNECT_DATA=(SID="+ConnectionParams.s_databaseSID+")))";


      // To Connect to the  Oracle database, use an instance of  the
      // DefaultContext class. The syntax of the DefaultContext constructor is
      // DefaultContext(databaseURL,username,password, boolean auto-commit);
      // The databaseURL syntax is as follows:
      //      "jdbc:oracle:<driver>:@<db connection string>"
      // <driver>, can be 'thin' or 'oci8'
      // <db connect string>, is a Net8 name-value, denoting the TNSNAMES entry
      m_dCtx = new DefaultContext(
                  "jdbc:oracle:thin:@"+l_dbConnectString,
                  ConnectionParams.s_userName,
                  ConnectionParams.s_password,
                  false);

      // Set the above connection context as the default context for this
      // application
      DefaultContext.setDefaultContext(m_dCtx);


      m_GUI.putStatus("Connected to "+ConnectionParams.s_databaseSID+ " as "+
              ConnectionParams.s_userName);
      m_GUI.enableButtons(true);
    } catch(Exception ex){ //Trap SQL errors
      m_GUI.putStatus(
             "Error in Connecting to the Database "+'\n'+ex.toString());
    }
  }

  /**
  * This function selects the rows from the TABLE_CITY_OBJ table  based on the
  * query condition entered
  **/
  void selectRecord(String p_name, String p_density, String p_state, String p_code){
    try {

      //If no query condition has been entered, change the bind values
      //for name and code to select all records

      if (p_code.equals(""))
        p_code = "%";
      if(p_name.equals(""))
        p_name = "%";

      CityIter l_cities;  // Iterator to hold all retrieved rows

      // Embedded SQL: Retrieves all rows from the
      #sql l_cities = { select a.OBJCITY city from TABLE_CITY_OBJ a where
                        a.OBJCITY.CITY_CODE LIKE :p_code
                        AND a.OBJCITY.NAME LIKE :p_name };  

      // loop through the resultset and retrieve all rows
      while(l_cities.next()) {
        // specify the type map while calling getObject method
        myCityObject l_city_object = l_cities.city();

        // Add the row  to the JTable
        // Need to check for NULL in province

        m_GUI.addToJTable(l_city_object.getName(),
              (l_city_object.getProvince()==null)?"":l_city_object.getProvince(),
              l_city_object.getCityCode(),l_city_object.getDensityString(),
              l_city_object.getPopulationDensity());
      }
      l_cities.close();          // close the statement which also closes the resultset
      m_GUI.appendStatus(" Rows selected");

    } catch (Exception ex) {    //trap SQL and other errors
      m_GUI.putStatus(" Error in Querying: ");
      m_GUI.appendStatus(" "+ex.toString());
    }
  }

  /**
  * This function inserts a new row into TABLE_CITY_OBJ table
  **/
  void insertRecord(String p_name, String p_density, String p_state, String p_code){

    java.math.BigDecimal l_densityint = new BigDecimal(0);

    // Convert density to Bigdecimal if entered.
    if (p_density.compareTo("") != 0) {
      try {
        l_densityint = new BigDecimal(p_density);
      } catch (NumberFormatException ex) {
        m_GUI.putStatus(" Error in constructing BigDecimal: ");
        m_GUI.appendStatus(" "+ex.toString());
      }
    }
    try {
        // Construct an instance of the cityObject class for
        // insertion. This object represents the object type OBJ_TYPE_CITIES
        // in the database.
      myCityObject l_row = new myCityObject();
      l_row.setCityCode(p_code);
      l_row.setName(p_name);
      l_row.setPopulationDensity(l_densityint);
      l_row.setProvince(p_state);

      // Embedded SQL: Insert the row with the column value being the
      // myCityObject type instantiated above

      #sql { delete from TABLE_CITY_OBJ a where a.OBJCITY.CITY_CODE = :p_code };

      m_GUI.putStatus("Row deleted");
    } catch (SQLException ex){ // catch any exceptions that occur
      m_GUI.putStatus(" Error in Deleting: ");
      m_GUI.appendStatus(" "+ex.toString());
    }
    // clear the selection
    m_GUI.m_cityTable.clearSelection();
  }
  /**
  *  Closes the connection and exits from the program when exit button is
  *  pressed
  **/
  public void exitApplication(){
    if (m_dCtx != null) {
      try {
        m_dCtx.close();              // close the connection
      } catch (Exception ex){              // Trap SQL Errors
        m_GUI.putStatus(" Error in Closing the connection: ");
        m_GUI.appendStatus(" "+ex.toString());
      }

    }
    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