SQLJ Strongly Typed Access
of Collections and Object REFs Application
Table Of Contents
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.
|
- 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.
|
Bookmark
