Sample
demonstrating usage of Multiple Collection Support in Oracle9i
JDBC Drivers
Table Of Contents
Multi-level collection types are collection types
whose elements are themselves directly or indirectly another collection
type. Possible multi-level collection types are:
- Nested table of nested table type
- Nested table of varray type
- Varray of nested table type
- Varray of varray type
- Nested table or varray of a user-defined type that has an attribute
that inturn a nested table or varray type.
Like ordinary, single-level collection types,
multi-level collection types can be used with columns in a relational
table or with object attributes in an object table. Oracle9i
JDBC Driver supports accessing/ manipulating of multiple level collections.
Sample Application Scenario
GEOGRAPHIC_REGION table
consists of a nested table type column "Country_List",
which is stored as COUNTRIES_NT nested table. COUNTRIES_NT consists
of a column "City_List" which is stored as CITIES_NT nested
table. Hence it represents Multi-Level Collections. i.e. a nested
table within another nested table. The figure below explains the
database table and collections used in this sample.
- When this application is executed, a table with various geographic
regions data appears.
- The user can select a region and click "View Countries"
button. A list of countries corresponding to the region selected
appears. The data is retrieved from
the COUNTIES_NT Nested Table, which is represented as COUNTRY_LIST
column in the GEOGRAPHIC_REGION table.
- The user can further select a country
and click the "View Cities" button. A list of cities
corresponding to the country selected appears. The cities data
is retrieved from the Cities_NT Nested Table, which is represented
as CITY_LIST column in COUNTRIES_NT Nested table.
Here is the code usage for Multiple Collection Sample. You can
find more details of the code in MultipleCollectionSample.java
file under src/oracle/otnsamples/oracle9ijdbc/multiplecollection
folder. Look into Description of Sample
Files section for folder and file details.
|
public class MultipleCollectionSample
{
.......................
/**
* Method to retrieve the countries data for the selected
region from COUNTRY_LIST
* nested type column of GEOGRAPHIC_REGION table.
*/
public void displayCountries() {
........................................
try {
..............................
// prepare a query to fetch countries
for the selected region
// which returns a nested table
pstmt = connection.prepareStatement(
"SELECT country_list FROM geographic_region
WHERE name = ? ");
..............................
// CountriesNt.java is automatically
generated by Oracle JPublisher utility which
// represents the Nested table type
column(COUNTRY_LIST) in GEOGRAPHIC_REGION table
CountriesNt countryList = (CountriesNt)
rset.getORAData(1,CountriesNt.getORADataFactory());
// CountriesType.java is automatically
generated by Oracle JPublisher utility.
// Get the array that represents
the COUNTRY_LIST Nested Table rows
CountriesType[] countriesRef
= countryList.getArray();
// loop through the array to retrieve
name and capital values and then
// populate the JTable.
for (int i = 0;i<countriesRef.length;i=
i+1) {
// getName() and getCapital() are
methods of automatically generated methods in
// java class CountriesType
String name = countriesRef[i].getName();
String capital = countriesRef[i].getCapital();
// Add a country row to the JTable
GUI.countriesList.addToJTable(name,
capital);
}
} catch (SQLException ex) {
......................
}
......................
|
|
This following notations are used through out
this document
|
Notation
|
Description
|
|
<SAMPLE_HOME>
|
Folder where the MultipleCollectionSample will be unzipped.
|
|
<JAVA_HOME>
|
Folder where JAVA is installed.
|
|
<JDBC_HOME>
|
Folder where the Oracle JDBC driver is installed.
|
|
<ORACLE_HOME>
|
Folder where the ORACLE installed.
|
- 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 Drivers,
downloadable from OTN
site.
If Oracle9i client is already
installed on your system then this driver need not be downloaded
separately.
-
For setting up environment variables in
different platforms, please refer environment
set up readme document.
- Unjar the provided MultipleCollectionSample.jar
using the following command
| >
jar xvf MultipleCollectionSample.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 MultipleCollectionSample
with all the source files.
-
Edit MultipleCollectionSample\Connection.properties file in your favorite editor. Change the HostName,
Port, SID, UserName and Password to connect to your own database.
|
HostName
|
= |
localhost
|
|
SID
|
= |
ORCL
|
|
Port
|
= |
1521
|
|
UserName
|
= |
scott
|
|
Password
|
= |
tiger
|
NOTE: The Custom java classes which are generated
by the JPublisher and included along with the sample use "SCOTT"
schema. In case you use some other database user, then you need to
generate the Custom java classes using JPublisher utility. Firstly,
create Nested Types, see Database Setup section
and then follow instructions for
generating custom java classes.
- Follow the step mentioned below to create the database objects
required for this sample.
Connect to database using SQL*plus. After connecting, run the
SQL script install.sql by executing the command below:
SQL> @<SAMPLE_HOME>\sql\install.sql
Specify the same username/password as connection parameters in
the Connection.properties file.
Running the application
using Oracle9i JDeveloper
|
Back
To Top |
This section describes the steps required in
running this application using Oracle9i
JDeveloper.
- Open Oracle9iJDeveloper
and use File/Open option to select the MultipleCollectionSample.jws
from the MultipleCollectionSample directory.
- Now, select Run/Run
MultipleCollectionSample.jpr from main menu which opens up the browser and runs the
MultipleCollectionSample .
|
Bookmark