Sample Illustrating Strongly Typed Access of Collections and Object REFs

Table Of Contents 

• Overview of the Sample Application
• Required Software
• Application Set-up and Configuration
• Generating Custom Java using JPublisher
• Running the Application 
• From Oracle9i JDeveloper 
• From JDK for Windows
• From JDK for Linux
• Description of Sample Files
• Troubleshooting
  

This Sample illustrates accessing of Collections (i.e Nested Tables and Varrays) from the Database Columns using JDBC. The Sample also demonstrates accessing of Object References. Object References along with the collections allows us to easily model Master-Detail Relationship
in Object-Oriented Databases.

This sample accesses the Collections and REFs using custom Java Classes representing the specific Object Type or REF. This is a Strongly Typed method of accessing the Object datatypes and Object References. Collections and References can also be retrieved using the Oracle JDBC driver extension classes: STRUCT, REF and ARRAY. This is a Weakly Typed access of these Oracle Datatypes. This method of access is illustrated in the "Weakly Typed access of Collections and Object REFs" sample.

The Sample allows us to view all the cities associated with a country by clicking on any country record. The relationship between Countries and Cities, which is Master-Detail Relationship, is modeled using Collections and Object References. Here, OBJ_CITIES_TABLE is an Object Table and CITY_LIST Column of OBJ_TABLE_COUNTRIES is a Collection of Object References to OBJ_CITIES_TABLE.

Working of the Sample

• When the user invokes the application, it connects to the database. The status and errors, if any, will be displayed in the status bar.
• If the connection was made successfully the application retrieves the Name, Geography and Currency Columns for all rows in the
  OBJ_TABLE_COUNTRIES table and displays them in the JTable.
• When the user chooses a row from the Countries JTable, the corresponding City records for the Selected Country are retrieved and displayed in the Cities JTable.

Here is the code to access Collections and REFs using java classes generated by JPublisher. You can find more details of the code in STCollectionSample.java file under src/oracle/otnsamples/jdbc/stcollection folder. Look into Description of Sample Files section for folder and file details.

private void displayCitiesData( String country ) { // Prepare a query to fetch cities for a selected country PreparedStatement pstmt = connection.prepareStatement( "SELECT CITY_LIST FROM OBJ_TABLE_COUNTRIES WHERE NAME = ? " ); pstmt.setString( 1, country ); // Bind the country name // Execute Query OracleResultSet rset = (OracleResultSet)pstmt.executeQuery(); // Instantiate a HashTable to specify mapping java.util.Hashtable mymap = new java.util.Hashtable(); // Load Java Class Class obj = Class.forName("oracle.otnsamples.jdbc.stcollection.CityRefs"); // Map the loaded java class to the object-type mymap.put( "OBJ_TYPE_CITY", obj ); // Loop through the resultset to get the collection of objects while( rset.next() ) { // clear the Table gui.tableModel1.clearTable(); // Custom Java Object which holds the collection of ObjectRefs in the // CITY_LIST column CityRefs cityList = (CityRefs)rset.getORAData(1, CityRefs.getORADataFactory() ); // Get the array of REFS into a custom Java class, representing the // REF to OBJ_TABLE_CITIES table rows. ObjTypeCityRef[] cityRefs = cityList.getArray(); // Display all the cities for( int i = 0;i < cityRefs.length;i = i + 1 ) { // Resolve the city reference and fetch the city object into the // custom java class ObjTypeCity cityObject = (ObjTypeCity)cityRefs[ i ].getValue(); // Using the methods defined in the Custom Java Class 'objTypeCity', // fetch the city details. // Note : Methods getName(), getProvince(), getCityCode() etc in // objTypeCity class are generated by JPublisher automatically. This // makes the code much easier and less error prone. String name = cityObject.getName().toString(); String state = cityObject.getProvince().toString(); String cityCode = cityObject.getCityCode().toString(); // Add a city Row to JTable gui.addToCityJTable( name, state, cityCode ); .......... ..........

The following notations are used through out this document

Notation	Description
<SAMPLE_HOME>	Folder where the STCollectionSample.jar is unzipped.
<JAVA_HOME>	Folder where JAVA is installed.
<ORACLE_HOME>	Folder where the Oracle is installed.

Required Software

Back To Top

• 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. The JDBC driver is available at ORACLE_HOME/jdbc/lib. Or it could be downloaded from here.

• Unjar the provided STCollectionSample.jar using the following command 

> jar xvf STCollectionSample.jar

Note: You will find jar.exe in JAVA_HOME\bin. Ensure JAVA_HOME\bin is present in your system path. 
This creates a folder STCollectionSample with all the source files

• Database Setup
  Connect to your database as any user and execute the sql script located at STCollectionSample\config using the following command
  SQL>@<SAMPLE_HOME>\STCollectionSample\config\CollectionSample.sql

• Edit STCollectionSample/Connection.properties file in your favorite editor. Change the HostName, Port, SID, UserName and 
Password to connect to schema where you have run the sql script CollectionSample.sql in your database.

This section describes the steps required to create custom Java classes for object type and REF types using JPublisher. The Java classes are already provided for you along with the sample. So this is an optional step.

• JPublisher utility is available at ORACLE_HOME/bin directory of your database or client installation. Make sure ORACLE_HOME/bin and JAVA_HOME/bin are in the PATH.
• Add translator.jar and runtime12.jar (available in ORACLE_HOME/sqlj/lib directory)  and classes12.jar (available in ORACLE_HOME/jdbc/lib directory )  to the CLASSPATH.
• To generate the Java classes use the following command from the command prompt:

  >jpub -user=<UserName>/<Password> -sql=COLL_REF_TO_CITIES:CityRefs -url=jdbc:oracle:<DriverType>:<ConnectString> -builtintypes=oracle -package=oracle.otnsamples.jdbc.stcollection -omit_schema_names

  Replace <UserName>, <Password>, <DriverType>, <ConnectString> with
  appropriate values.

  Example:

  >jpub -user=scott/tiger -sql=COLL_REF_TO_CITIES:CityRefs -url=jdbc:oracle:thin:@localhost:1521:ORCL -builtintypes=oracle -package=oracle.otnsamples.jdbc.stcollection -omit_schema_names

  This command generates CityRefs.java ,ObjTypeCity.java and ObjTypeCityRef.java for the object type COLL_REF_TO_CITIES. You can replace these files that are present at STCollectionSample\src\oracle\otnsamples\jdbc\stcollection with the generated files.
  

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 STCollectionSample.jws from the STCollectionSample directory.
• Next, select Project/STCollectionSample.jpr from main menu.
• Now, select Run/Run STCollectionSample.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 STCollectionSample directory. Environmental variables JAVA_HOME and JDBC_HOME have to be set before running run.bat file.
Example:

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

Running the application manually:

• Set CLASSPATH to include Oracle9i JDBC Driver file: classes12.zip or classes12.jar or ojdbc14.jar. 
• Also add STCollectionSample directory where Connection.properties exists and the current directory to the CLASSPATH
  Example: 
  D:\STCollectionSample>set CLASSPATH=D:\oracle9i\jdbc\lib\classes12.zip;D:\STCollectionSample;.
  
• From the directory STCollectionSample\src\oracle\otnsamples\jdbc\stcollection, compile all the java files using javac:
  Example: 
  D:\STCollectionSample\src\oracle\otnsamples\jdbc\stcollection>javac -d . *.java
  
• Run the class file using java from  STCollectionSample\src\oracle\otnsamples\jdbc\stcollection directory
  Example:
  D:\STCollectionSample\src\oracle\otnsamples\jdbc\stcollection>
  java oracle.otnsamples.jdbc.stcollection.STCollectionSample

From JDK for Red Hat Linux Advanced Server release 2.1

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

Run application using batch File: run.sh provided:

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

• Go to STCollectionSample directory and from the $ prompt use the command below to run the script:
  $sh run.sh

Running the application manually:

• Set CLASSPATH to include Oracle9i JDBC Driver file: classes12.zip or classes12.jar or ojdbc14.jar.
• STCollectionSample  directory where Connection.properties exists and current directory are also added to the CLASSPATH.
  Example:
  $export CLASSPATH=/home1/jdbc/lib/classes12.zip:/home1/STCollectionSample:.
• From the directory STCollectionSample/src/oracle/otnsamples/jdbc/stcollection, compile all the java files using javac:
  Example:
  $javac -d . *.java
• Run the class file using java from the same STCollectionSample/src/oracle/otnsamples/jdbc/stcollection directory.
  Example:
  $java oracle.otnsamples.jdbc.stcollection.STCollectionSample

• When you run the sample, if you get the following error
  Unknown error:
  java.lang.ClassCastException
  Generate the java classes using JPublisher.