Weakly Typed Collection Sample Application

Table Of Contents 

• Overview of the Sample Application
• Required Software
• Notations Used
• Application Set-up and Configuration
• Database Set-up
• Running the Application 
• From Oracle9i JDeveloper 
• From JDK for Windows
• From JDK for Linux
• Description of Sample Files
• Troubleshooting

This sample application illustrates accessing of Collections (i.e Nested Tables and Varrays) from the Database Columns using JDBC 2.0 API. The sample also demonstrates accessing Object References. Collections are used store an array of values in a database row, while Object REFs are used to point to a row in a Object Table. The combination of collections and Object Refs is one of the ways to implement a master-detail relationship.

The sample application accesses the database Object Types and Object REFs using the JDBC 2.0 API : STRUCT, REF and ARRAY. Collections and REFs can also be retrieved using custom Java Classes representing the specific Object Type or REF. This is a Strongly Typed method of accessing the above datatypes.

Working of the Sample

In the database, the Master-Detail relationship between the tables OTN_COUNTRIES and OTN_CITIES is modelled using Collections and Object References. OBJ_TYPE_CITY is an object which has attributes of a city. COLL_REF_TO_CITIES is a collection of references to OBJ_TYPE_CITY. OBJ_TABLE_CITIES is a table of object type OBJ_TYPE_CITY. OBJ_TABLE_COUNTRIES has a column CITY_LIST which is of type COLL_REF_TO_CITIES.

When the application is invoked, list of countries is displayed. When the user selects a country, the collection of Object REFs is used to retrieve
the list of cities in that country.

Here is the code sample for creating and using Collections and references. You can find more details of the code in WTCollectionSample.sql and WTCollectionSample.java files under config and src/oracle/otnsamples/jdbc/wtcollection folders. Look into Description of Sample Files section for folder and file details.

Below is the SQL code sample from WTCollectionSample.sql for creating database objects.

//SQL statements to creates objects and references:


CREATE OR REPLACE TYPE OBJ_TYPE_CITY AS OBJECT (
          NAME      VARCHAR2(40),
          PROVINCE  VARCHAR2(40),
          CITY_CODE VARCHAR2(10),
          POPULATION_DENSITY NUMBER );

CREATE OR REPLACE TYPE COLL_REF_TO_CITIES AS TABLE OF REF OBJ_TYPE_CITY;

CREATE TABLE OBJ_TABLE_CITIES OF OBJ_TYPE_CITY;
CREATE TABLE OBJ_TABLE_COUNTRIES (
      NAME     VARCHAR2(40),
      GEOGRAPHY VARCHAR2(2),
      CURRENCY VARCHAR2(3),
      CITY_LIST  COLL_REF_TO_CITIES) Nested TABLE CITY_LIST STORE AS t_1;

//  The following PL/SQL Block, extracts all the records from otn_cities and
//  otn_Countries table and inserts corresponding records into obj_table_countries
//  and obj_table_cities tables.

DECLARE
  TYPE rc IS ref cursor;
  c1 rc;
  CURSOR c2( pid VARCHAR2 ) IS
     SELECT name, state_province, TO_CHAR(id)
     FROM otn_cities 
     WHERE con_id = pid;

     
  local_call  coll_ref_to_cities := coll_ref_to_cities();
  i NUMBER :=  0;
  j NUMBER := 1000;
  r1 REF OBJ_TYPE_CITY;
  
  cid    NUMBER(10);   -- Country Id
  cname  VARCHAR2(40); -- Country Name
  cgep   VARCHAR2(2);  -- Country Geography
  ccur   VARCHAR2(3);  -- Country Currency
  ctname VARCHAR2(40); -- City Name
  ctpro  VARCHAR2(40); -- City Province/State
  ctcode VARCHAR2(10); -- City Code
  
BEGIN

  OPEN c1 FOR 'SELECT id, name, geography, currency FROM otn_countries WHERE id > 0';
  LOOP
    FETCH c1 INTO cid,cname, cgep, ccur;
    EXIT WHEN (c1%NOTFOUND);
    OPEN c2(cid);
    LOOP
      FETCH c2 into ctname, ctpro, ctcode;
      EXIT WHEN(c2%NOTFOUND);
      INSERT INTO obj_table_cities a VALUES
         ( OBJ_TYPE_CITY(ctname, NVL(ctpro,'N A'), ctcode,j+1) )
         RETURNING REF(a) INTO r1;
      local_call.EXTEND(1);
      i := i + 1;
      local_call(i) := r1;
      j := j+1;
    END LOOP;

    CLOSE c2;
    INSERT INTO obj_table_countries VALUES(cname, cgep, ccur, local_call);
    COMMIT;
    local_call.DELETE;
    i := 0;
  END LOOP;
END;
/
commit;

Below is the code snippet from WTCollectionSample.java which retrieves cities using the Objectf REFs.

/**
     * Method to retrieve the city records for the selected country, using the
     * collection of Object REFs held in CITY_LIST.
     */
    private void displayCitiesData( String country ) {
      try {
        // prepare a query with the selected values.
        PreparedStatement pstmt = connection.prepareStatement( 
        "SELECT city_list FROM obj_table_countries WHERE name = ? " );

        pstmt.setString( 1, country ); // Bind the country name.
        ResultSet rset = pstmt.executeQuery(  ); // execute the query.


        java.sql.Array cities;
        // Retrieve the CITY_LIST collection from the ResultSet.
        if ( rset.next(  ) ) {
          gui.cityTableModel.clearTable(  ); // clear the Table.
          cities = (java.sql.Array) rset.getObject( 1 );

          // No. of cities.
          int totalCities = ( (oracle.sql.ARRAY) cities ).length(  ); 
          Object[] obs = (Object[]) cities.getArray(  );

          // Loop and retrieve all cities.
          for ( int i = 0; i < totalCities; i++ ) {
            // Cast the array element to an object ref (java.sql.REF is part
            // of JDBC 2.0 API)
            // If a ResultSet had been used to retrieve the Collection Elements
            // the first object of the ResultSet is the Array index and the
            // second element onwards, the Array elements
            java.sql.Ref objref = (java.sql.Ref) obs[ i ];


            // Prepare a statement for de-referencing the REF so as to get the 
            // object having details of the city.
            PreparedStatement pstmt1 = connection.prepareStatement(
                                           SELECT DEREF(?) FROM dual" );

            pstmt1.setRef( 1, objref ); // bind column value.

            // Execute the prepared statement.
            ResultSet res1 = pstmt1.executeQuery(  );
            res1.next(  );

            // get the de-referenced object.
            Struct objstruct = (Struct) res1.getObject( 1 );

            // get the attributes in the STRUCT objstruct.
            Object[] objval = objstruct.getAttributes(  );

            // Retrieve individual attributes.
            String cityName = (String) objval[ 0 ];

            String cityState = (String) objval[ 1 ];
            String cityCode = (String) objval[ 2 ];

            pstmt1.close(  ); // close the preparedstatement.
            ...............
          }
        }
        pstmt.close(  ); // close the preparedstatement.
      }
       catch ( SQLException ex ) {
         ...........
      }
    }

• Oracle9i JDeveloper ( Note: Oracle9i JDeveloper is Oracle's Visual Java Development Tool and can be downloaded from here)
  or JDK1.2.x or above This can be downloaded from here.
• Oracle9i Database or higher running SQL*Net TCP/IP listener. This can be downloaded from here.
• Oracle9i JDBC Driver. This can be downloaded from here.

• Unjar the provided WTCollectionSample.jar using the following command 

> jar xvf WTCollectionSample.jar

Note: You will find jar.exe in JDK_HOME\bin. Ensure JDK_HOME\bin is present in your system path.
(JDK_HOME is the root directory of the JDKx.x installation). This creates a folder WTCollectionSample with all the source files.

• Edit WTCollectionSample/Connection.properties file in your favorite editor. Change the HostName, Port, SID, UserName and
  Password to connect to your own database.

From a SQL*Plus Client, connect to the database using the credentials mentioned above. Run the SQL Script file WTCollection.sql to create the database tables and records required by the application. Look into Description of Sample Files section for folder and file details of the SQL file.
For example: SQL>@D:\WTCollectionSample\config\WTCollection.sql

This sample application can be run in 3 different ways listed below.

• From Oracle9i JDeveloper
• From JDK for Windows
• From JDK for Linux

From Oracle9i JDeveloper

• Open Oracle9i JDeveloper and use File/Open option to select the WTCollectionSample.jws from the WTCollectionSample directory.
• Next, select Project/Make WTCollectionSample.jpr from main menu.
• Now, select Run/Run WTCollectionSample.jpr from main menu to run the application.

From JDK for Windows

This section will describe steps to run the application from console using JDK on Windows. The sample can be run either manually or using a script file .

Run application using batch file: run.bat provided:

By setting few environment variables, the sample application could be directly run by just executing the batch file: run.bat from the command prompt, from WTCollectionSample directory. Environmental variables JAVA_HOME and JDBC_HOME have to be set before running run.bat file.
Example:

D:\WTCollectionSample> set JDBC_HOME=d:\oracle9i\jdbc\lib
D:\WTCollectionSample> set JAVA_HOME=d:\jdk1.3.1
D:\WTCollectionSample> run

Running the application manually:

• Set CLASSPATH to include Oracle9i JDBC Driver files. Example: classes12.zip and nls_charset12.zip
• WTCollectionSample directory where Connection.properties exists and current directory are also added to the CLASSPATH.
  Example: 
  D:\WTCollectionSample>set CLASSPATH=D:\oracle9i\jdbc\lib\classes12.zip;
  D:\oracle9i\jdbc\lib\nls_charset12.zip;D:\WTCollectionSample;.
  
• Make sure that Java is in the PATH
  Example: D:\WTCollectionSample>set PATH=.;d:\jdk1.3.1\bin;%PATH%
  
• From the directory WTCollectionSample\src\oracle\otnsamples\jdbc\wtcollection, compile all the java files using javac:
  Example: 
  D:\WTCollectionSample\src\oracle\otnsamples\jdbc\wtcollection>javac -d . *.java
  
• Run the class file using java from the same WTCollectionSample\src\oracle\otnsamples\jdbc\wtcollection directory.
  Example:
  D:\WTCollectionSample\src\oracle\otnsamples\jdbc\wtcollection>
  java oracle.otnsamples.jdbc.wtcollection.WTCollectionSample

From JDK for Linux

This section will describe the steps to run the application from console using JDK on Red Hat Linux Advanced Server Release 2.1. The sample can be run either manually or using a script file.

Run application using script file: run.sh provided:

By setting few environment variables, the sample application could be directly run by just executing the script file: run.sh from the command prompt, from WTCollectionSample directory. Environmental variables JAVA_HOME and JDBC_HOME have to be set, else the user will be prompted to enter values.

• Go to WTCollectionSample directory and from the $ prompt, use the command below to run the script file.
  $sh run.sh

Running the application manually:

• Set CLASSPATH to include Oracle9i JDBC Driver files. Example: classes12.zip and nls_charset12.zip
• WTCollectionSample directory where Connection.properties exists and current directory are also added to the CLASSPATH.
  Example:
  $export CLASSPATH=/home1/idcotn/download/jdbc/lib/classes12.zip:
  /home1/idcotn/download/jdbc/lib/nls_charset12.zip:
  /home1/idcotn/download/basicjdbc/WTCollectionSample:.
• Make sure that Java is in the PATH
  Example: $export PATH=/usr/java/jdk1.3.1_02/bin:$PATH
  
• From the directory WTCollectionSample/src/oracle/otnsamples/jdbc/wtcollection, compile all the java files using javac:
  Example:
  $javac -d . *.java
• Run the class file using java from the same WTCollectionSample/src/oracle/otnsamples/jdbc/wtcollection directory.
  Example:
  $java oracle.otnsamples.jdbc.wtcollection.WTCollectionSample

1. From Oracle9i JDeveloper, if the cities data is not displayed properly, follow the steps below to re-configure Oracle9i JDeveloper to use classes12.zip shipped with Oracle9i database.
   • Take a backup of the present Oracle9i JDBC Driver (classes12dms.jar) by renaming it as classes12dms_backup.jar
   • Copy the classes12.jar from the <ORACLE_HOME>/jdbc/lib directory into <JDEV_HOME>/jdbc/lib
   • Rename the copied classes12.jar as classes12dms.jar to complete the configuration.
   • Build the sample project again and re-run the application.
2. If you are using JDK 1.4 and corresponding JDBC driver for JDK 1.4, if the cities table data is not displayed properly, you will have to use nls_charset12.zip along with ojdbc14.jar to see the correct data. Please follow the steps below for this:
   • Copy nls_charset12.zip to your JDBC_HOME directory where ojdbc14.jar exists. You can get this zip file if you have a previous
     version of JDBC driver i.e. JDBC Driver for JDK1.3. Else, please download this from OTN.
   • After recompiling, you can run the application again to see the correct data of countries and cities.