SQLJ Weakly Typed Access of Collections and Object REFs Application

Table Of Contents 

• Overview of the Sample Application
• Notations used
• Required Software
• Application Set-up and Configuration
• Deploying and Running the Application 
• Using Oracle9i JDeveloper 
• From command line
• Description of Sample Files
• Troubleshooting

SQLJ enables applications programmers to embed SQL operations in Java code. A SQLJ program is a Java program containing embedded SQL statements that comply with the ISO standard SQLJ Language Reference syntax.

SQLJ consists of a translator and a runtime component (translator.jar/zip and runtime12ee.jar/zip) and is smoothly integrated into the development environment. The translation, compilation, and customization take place in a single step when the front-end utility sqlj is run. The translation process replaces embedded SQL with calls to the SQLJ runtime, which implements the SQL operations. When the end user runs the SQLJ application, the runtime is invoked to handle the SQL operations.

SQLJ runs on top of JDBC. To access an Oracle database, you would typically use an Oracle JDBC driver. In order to run SQLJ programs, apart from SQLJ classes, JDBC classes should be present in the system CLASSPATH. SQLJ code is written and saved in *.sqlj files and should be translated to *.java files before compiling using the front-end utility sqlj.

This sample illustrates accessing of Collections (i.e Nested Tables and Varrays) from the database columns using SQLJ.

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 database Object types and Object REFs using the Oracle JDBC driver extension classes: STRUCT, REF and ARRAY. This is a Weakly Typed access of these Oracle Datatypes.

Collections and REFs can also be retrieved using custom Java classes representing the specific Object type or REF in SQLJ. This is Strongly Typed method of accessing the above datatypes. This method of access is illustrated in the "Strongly Typed access of Collections and REFs" sample.

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 table.

This sample uses the JDBC-Thin driver; the code is the same for JDBC-OCI, except for the database URL syntax.

Sample Application Scenario

• When the application is invoked, 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 a row from the Countries JTable is selected, the corresponding City records for the selected Country are retrieved and displayed in the Cities JTable.

Here is the code that shows the access of collection. You can find more details of the code in SqljWTCollectionSample.sqlj file under src/oracle/otnsamples/sqlj/wtcollection folder. Look into Description of Sample Files section for folder and file details.

private void displayCitiesData(String country) {
..........
..........

// Embedded SQL : This SQLJ Call selects the Collection of Object References
// pointing city records into cities variable, for given country.
#sql {Select CITY_LIST INTO :cities
      FROM OBJ_TABLE_COUNTRIES WHERE NAME = :country};

gui.tableModel1.clearTable(); // clear the JTable
int totalCities = 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 [] )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 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 objstruct = (oracle.sql.STRUCT)objref.getValue();


  // 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];
  
  // Update the cities JTable
  gui.addToCityJTable(cityName,cityState,cityCode);
..........
..........
}

The following notations are used through out this document

Notation	Description
<SAMPLE_HOME>	Folder where the SqlWTCollectionSample.jar is unzipped.
<JAVA_HOME>	Folder where JAVA is installed.
<JDBC_LIB>	Folder where the Oracle JDBC driver exists.
<SQLJ_LIB>	Folder where the Oracle SQLJ translator and runtime files exist.
<SQLJ_EXE_HOME>	Folder where the SQLJ executable exists.

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.3.x or above This can be downloaded from here .
• Oracle9i Database or higher running SQL*Net TCP/IP listener. This can be downloaded from OTN site.
• Oracle9i v9.0.2 or higher SQLJ translators and runtime, downloadable from OTN site.
• Oracle9i v9.0.2 or higher JDBC Drivers, downloadable from OTN site. When downloading JDBC driver make sure you also download nls_charset12.jar/zip.
  Note : If Oracle9i client is already installed on your system then this driver need not be downloaded separately.
  

• For setting up the environment variables in different platforms, please refer environment set up document.

• Unjar the provided SqljWTCollectionSample.jar using the following command 
  

  > jar xvf SqljWTCollectionSample.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 SqljWTCollectionSample with all the source files.

• Database Setup
  Connect to your database as any user and execute the sql script located at SqljWTCollectionSample\config using the following command

  SQL>@<SAMPLE_HOME>\SqljWTCollectionSample\config\CollectionSample.sql

• Edit SqljWTCollectionSample\Connection.properties file in your favorite editor. Change the sqlj.url value