SQLJ Strongly 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
• Generating Custom Java using JPublisher
• 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.

The Collections and REFs are 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 modelled 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 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 SqljSTCollectionSample.sqlj file under src/oracle/otnsamples/sqlj/stcollection folder. Look into Description of Sample Files section for folder and file details.

private void displayCitiesData(String country) { 
..........
..........
// Fetch the collection of Object References into a Custom Java Object.
// Note that CITY_LIST Column holds the collection of Object References 
// pointing City Objects
#sql {Select CITY_LIST INTO :cityList FROM OBJ_TABLE_COUNTRIES 
      WHERE NAME = :country};

// 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', 
  // fecth 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 SqljSTCollectionSample.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 SqljSTCollectionSample.jar using the following command 
  

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

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

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

• Edit SqljSTCollectionSample\Connection.properties file in your favorite editor. Change the sqlj.url value to connect to your own database.