sqljObjectOracleSample.sqlj
/** * @author asriniva.in * @version 1.0 * * Development Environment : JDeveloper 2.0 * Name of the Application : SqljObjectOracleSample.sqlj * Creation/Modification History : * * asriniva.in 13-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 weakly typed objects (oracle.sql.STRUCT), while * retrieval using strongly typed objects is illustrated in SQLJ Object Java * Sample. * * In this sample the uthe TABLE_OBJ_CITIES table that contains a column of * the object type OBJ_TYPE_CITY * * The GUI part of this sample is handled seperately in * SqljObjectOracleFrame.java */ import java.util.*; import java.sql.*; // Package containing JDBC classes //Package for Oracle Extensions to JDBC import oracle.sql.*; import oracle.jdbc.driver.*; // SQLJ runtime classes import sqlj.runtime.*; import sqlj.runtime.ref.*; // Embedded SQL: // Iterator declaration for TABLE_CITY_OBJ records being retrieved. #sql iterator CitiesIter ( oracle.sql.STRUCT objcity); public class sqljObjectOracleSample { DefaultContext m_dCtx; // Default connection context sqljObjectOracleFrame m_GUI; // For Handling the GUI related operations /** * Constructor. Initializes the JTable and other GUI components. **/ public sqljObjectOracleSample() { try { m_GUI =new sqljObjectOracleFrame(this); } catch (Exception e) { // trap the errors if any m_GUI.putStatus(" Error :"); m_GUI.appendStatus(" "+e.toString()); } } /** * The main entry point to the application. Instantiates root frame * and database connection is made here. **/ public static void main(String[] args) { sqljObjectOracleSample OOS = new sqljObjectOracleSample(); 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 SqljObjectOracleFrame.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("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("UPDATE")) updateRecord(l_name, l_density, l_state, l_code); else if (p_eventName.equals("EXIT")) // For dispatching the exit event exitApplication(); } /** * Creates a database connection object using JDBC. 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){ // Declare an instance of the CitiesIter iterator, which will hold // all the TABLE_CITY_OBJ records returned by the query executed. CitiesIter l_cityobjects; try { //If no query condition has been entered, change the bind values //to select all records if(p_code.equals("")) p_code = "%"; if(p_name.equals("")) p_name = "%"; // Embedded SQL: This call selects the code, name, state, // population density of all records which matches the given code or // name of the city in the TABLE_CITY_OBJ table. // The result of the query is assigned to the iterator instance to access // the rows returned #sql l_cityobjects = { select * from TABLE_CITY_OBJ a where a.OBJCITY.CITY_CODE LIKE :p_code AND a.OBJCITY.NAME LIKE :p_name }; // loop through the iterator and retrieve all objects while(l_cityobjects.next()) { // Obtain the column as a STRUCT. (To represent the Object Type) oracle.sql.STRUCT mystruct = l_cityobjects.objcity(); // Use the getAttributes method of oracle.sql.STRUCT to get the indvidual // column values and insert into JTable Object l_cols[] = mystruct.getAttributes(); // If column in STRUCT is null set it to an empty string for the JTable l_cols[1] = (l_cols[1]==null)?"":l_cols[1]; l_cols[2] = (l_cols[2]==null)?"":l_cols[2]; m_GUI.addToJTable(l_cols[0],l_cols[1],l_cols[2],l_cols[3]); } l_cityobjects.close(); m_GUI.appendStatus(" Rows selected"); } catch (SQLException ex) { // trap sql 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){ Integer l_densityint; // If the field is empty assign 0 to l_densityint if (p_density.compareTo("") == 0) { l_densityint = new Integer(0); } else { // else convert the string to equivalent data type l_densityint = new Integer(p_density); } // validate for not null if ((p_code.compareTo(new String(""))==0) || (p_name.compareTo(new String(""))==0)) { // display error message in the status bar. m_GUI.putStatus("Enter values in Code and Name for inserting to the table"); } else { try { // get the integer from the l_densityint int l_popdens = l_densityint.intValue(); // get the connection object from the default context Connection l_connection = m_dCtx.getConnection(); // construct the object array containing the attribute values for the // Object to be inserted Object l_objArray[]=new Object[4]; l_objArray[0] = p_name; l_objArray[1] = p_state; l_objArray[2] = p_code; l_objArray[3] = l_densityint; // create the StructDescriptor from the connection StructDescriptor l_colStructDesc = StructDescriptor.createDescriptor("OBJ_TYPE_CITY", l_connection); // construct the Struct from the StructDescriptor and objects oracle.sql.STRUCT l_colStruct = new STRUCT(l_colStructDesc, l_connection, l_objArray); // Embedded SQL: This call inserts a new row into the object table // OBJ_TABLE_AIRPORTS table. #sql { insert into TABLE_CITY_OBJ values (:l_colStruct) }; // Add the inserted row to the JTable m_GUI.addToJTable(p_name, p_state, p_code, l_densityint); m_GUI.putStatus(" Row inserted"); } catch (SQLException ex){ // trap sql errors m_GUI.putStatus(" Error in Inserting: "); m_GUI.appendStatus(" "+ex.toString()); } } } /** * This function updates an existing record in the TABLE_OBJ_CITIES table **/ void updateRecord(String p_name, String p_density, String p_state, String p_code){ Integer l_densityint; // if the field is empty assign 0 to l_densityint if (p_density.compareTo("") == 0) { l_densityint = new Integer(0); } else { // else convert the string to equivalent data type l_densityint = new Integer(p_density); } try { int l_popdens = l_densityint.intValue(); // get the connection object from the default context Connection l_connection = m_dCtx.getConnection(); // construct the object array containing the values to be inserted Object l_objArray[]=new Object[4]; l_objArray[0] = p_name; l_objArray[1] = p_state; l_objArray[2] = p_code; l_objArray[3] = l_densityint; // create the StructDescriptor from the connection StructDescriptor l_colStructDesc = StructDescriptor.createDescriptor("OBJ_TYPE_CITY",l_connection); // construct the Struct from the StructDescriptor and objects oracle.sql.STRUCT l_colStruct = new STRUCT(l_colStructDesc, l_connection, l_objArray); // Embedded SQL: to update the record identified by the code p_code // to the new object type represented by l_colStruct #sql { update TABLE_CITY_OBJ a set a.OBJCITY = :l_colStruct where a.OBJCITY.CITY_CODE = :p_code }; // call getAttributes method of oracle.sql.STRUCT to get the indvidual // column values Object l_cols[] = l_colStruct.getAttributes(); // Update GUI m_GUI.putStatus(" Row updated"); m_GUI.updateJTable(l_cols[0],l_cols[1],l_cols[2],l_cols[3]); } catch (SQLException ex){ // trap sql errors m_GUI.putStatus(" Error in Updating: "); m_GUI.appendStatus(" "+ex.toString()); } } /** * This functions deletes the selected row from the Object table **/ void deleteRecord(String p_code){ try { // Embedded SQL to delete from table_obj_cities #sql { delete from TABLE_CITY_OBJ a where a.OBJCITY.CITY_CODE = :p_code}; m_GUI.putStatus("Row deleted"); } catch (SQLException ex){ // Trap SQL errors 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); } }
Bookmark
