sqljWTCollectionSample.sqlj
/** * @author ukulkarn.in * @version 1.0 * * Development Environment : JDeveloper 2.0 * Name of the Application : sqljWTCollectionSample.sqlj * Creation/Modification History : * * ukulkarn.in 20-Feb-1999 Created * * Overview of Application : * * This sample illustrates the accessing of collection datatypes (VARRAYs and * Nested Tables), and also Object REFs using SQLJ and Weakly Typed Oracle JDBC * driver extension classes. * * Collections allow the user to store an array of values in a database row, * while Object REFs allow users to point to a row in a Object Table. The * combination of collections and Object Refs is one way to implement a * master-detail relationship. * * In this we use a collection of object REFs to define a master-detail * relation between Countries and Cities. The sample shows the list of all * countries and when the user selects a country, the collection of Object REFs * is used to retrieve the list of cities in that country, which are then displayed * in another table. * * This sample uses custom java classes to retrieve the collections and object * REFs. This is a Strongly typed representation of the database object types. * * This sample uses Oracle JDBC driver extension classes, STRUCT, ARRAY and REF. * This is a weakly typed representation of the database object types. * * Database object types can also be retrieved using custom java classes representing * the object types. This is a strongly typed representation of the Object Types and * retrieval using these is illustrated in "Strongly Typed Access of Collections * and Object REFs" sample * * The GUI for this sample is handled in sqljWTCollectionFrame.java * */ // SQLJ Runtime Classes import sqlj.runtime.*; import sqlj.runtime.ref.*; import java.sql.*; // Package for JDBC classes // Packages for Oracle Extensions import oracle.jdbc.driver.*; import oracle.sql.*; // Iterator Declaration for representing Country Records #sql iterator CountryIter ( String Name, String Geography, String Currency ); public class sqljWTCollectionSample { DefaultContext m_ctx; // Database Connection Context sqljWTCollectionFrame m_GUI; // The GUI handler for this sample /** * Constructor. Instantiates GUI. **/ public sqljWTCollectionSample() { m_GUI = new sqljWTCollectionFrame(this); // Instantiate GUI m_GUI.setVisible(true); } /** * Main entry point for the class. Instantiates the application class and * sets up the database connection. Also populates the Countries Jtable * with all the Countries Records. **/ public static void main(String[] args) { sqljWTCollectionSample collSamp = new sqljWTCollectionSample();// Instantiate class collSamp.dbConnection(); // Connect to database if (collSamp.m_ctx != null) { collSamp.populateCountries(); // Populates the Countries JTable } } /** * 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 sqljWTCollectionFrame.java **/ public void dispatchEvent (String p_eventName) { //Dispatch Event if (p_eventName.equals("SELECT CITIES")) { int l_selectedRow = m_GUI.m_table.getSelectedRow(); String l_name = (String) m_GUI.m_tableModel.getValueAt(l_selectedRow,0); displayCitiesData(l_name); } else if (p_eventName.equals("EXIT")) exitApplication(); } /** * Creates a database connection object using SQLJ. Please substitute the * database connection parameters with appropriate values in * ConnectionParams.java **/ public void dbConnection(){ 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_ctx = 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_ctx); m_GUI.putStatus("Connected to "+ConnectionParams.s_databaseSID+ " as "+ ConnectionParams.s_userName); } catch(Exception ex){ //Trap SQL errors m_GUI.putStatus( "Error in Connecting to the Database "+'\n'+ex.toString()); } } /** * Method to populate rows from the countries table **/ public void populateCountries() { m_GUI.putStatus("Populating Countries ..."); m_GUI.m_tableModel.clearTable(); try { // Iterator Variable to hold Country Records CountryIter l_countryIter = null; // Embedded SQL : This SQLJ Call selects Name, Geography, Currency From // Obj_Table_Countries Table. These countries records are fetched into // an iterator variable 'm_countryIter'. #sql l_countryIter = { Select Name, Geography, Currency From Obj_Table_Countries ORDER BY NAME}; // Loop through the iterator and retrieve all rows while (l_countryIter.next()) // Retrieve column values and add the row to JTable in GUI m_GUI.addToCountryJTable(l_countryIter.Name(), l_countryIter.Geography(), l_countryIter.Currency()); l_countryIter.close(); // Close the iterator m_GUI.putStatus("Connected to database and populated all countries"); m_GUI.appendStatus("Please choose a country"); } catch (SQLException ex) { // Trap SQL Errors m_GUI.putStatus(" Error in Querying countries table: "); m_GUI.appendStatus(" "+ex.toString()); } } /** * Method to retrieve the city records for the selected country, using the * collection of Object REFs held in CITY_LIST column */ public void displayCitiesData(String p_country){ try { m_GUI.putStatus("Retrieving cities for the selected country ..."); // Variable holding the collection of object REFs from the CITY_LIST column // in obj_table_countries table. ARRAY is a class provided in // Oracle JDBC drivers to access colections (Varrys and Nested Tables) // from the Database oracle.sql.ARRAY l_cities; // Embedded SQL : This SQLJ Call selects the Collection of Object References // pointing city records into l_cities variable, for given country. #sql {Select CITY_LIST INTO :l_cities FROM OBJ_TABLE_COUNTRIES WHERE NAME = :p_country}; m_GUI.m_tableModel1.clearTable(); // clear the JTable int totalCities = l_cities.length(); // Number of cities // Cast the ARRAY class to a Java native array, to retrieve elements // Collections can also be retrieved using a ResultSet instead of a // native Array from the ARRAY class (using the getResultSet() method // in the ARRAY class) Object obs[] = (Object [] )l_cities.getArray(); // Loop and retrieve all cities for (int i = 0; i<totalCities; i++) { // Cast the array element to an object ref (oracle.sql.REF is a // Oracle JDBC extension class). If a ResultSet had been used // then the first element of the ResultSet represents the // index of the array and the second element represents the Array // object oracle.sql.REF l_objref = (oracle.sql.REF)obs[i]; // Retrieve the object pointed to by the above object REF. To access // the elements in the object type(OBJ_TYPE_CITY), cast the // object type to a oracle.sql.STRUCT oracle.sql.STRUCT l_objstruct = (oracle.sql.STRUCT)l_objref.getValue(); // get the attributes in the STRUCT l_objstruct Object l_objval[] = l_objstruct.getAttributes(); // Retrieve individual attributes String l_cityName = (String) l_objval[0]; String l_cityState = (String) l_objval[1]; String l_cityCode = (String) l_objval[2]; // Update the cities JTable m_GUI.addToCityJTable(l_cityName,l_cityState,l_cityCode); } m_GUI.putStatus("Retrieving cities for the selected country ...Done"); }catch (SQLException ex) { // Trap SQL Errors m_GUI.putStatus(" Error in Querying: "); m_GUI.appendStatus(" "+ex.toString()); ex.printStackTrace(); } } /** * Close the database Connection and exit the application **/ public void exitApplication() { try { m_GUI.putStatus("Closing the connection....please wait....."); if(m_ctx != null) m_ctx.close(); //Close the m_ctx object. } catch(SQLException ex){ //Trap SQL Errors m_GUI.putStatus(ex.toString()); } System.exit(0); //Exit the application } }
Bookmark
