Oracle9i SQLJ Developer's Guide and Reference Release 1 (9.0.1) Part Number A90212-01 |
|
This chapter discusses how Oracle SQLJ supports user-defined SQL types--namely objects (and related object references) and collections (variable arrays and nested tables). This includes discussion of the Oracle JPublisher utility, which you can use to generate Java classes corresponding to user-defined SQL types.
The following topics are discussed:
This section provides some background conceptual information about Oracle9i objects and collections.
For additional conceptual and reference information about Oracle objects, references, and collections, refer to the Oracle9i SQL Reference and the Oracle9i Application Developer's Guide - Fundamentals.
For information about how to declare objects and collections, see "User-Defined Types".
Oracle9i and Oracle SQLJ support user-defined SQL object types (composite data structures), related SQL object reference types, and user-defined SQL collection types. Oracle objects and collections are composite data structures consisting of individual data elements.
Oracle SQLJ supports either strongly typed or weakly typed Java representations of object types, reference types, and collection types to use in iterators or host expressions. Strongly typed representations use a custom Java class that maps to a particular object type, reference type, or collection type and must implement either the JDBC 2.0 standard java.sql.SQLData
interface (for object types only) or the Oracle oracle.sql.ORAData
interface. Either paradigm is supported by the Oracle JPublisher utility, which you can use to automatically generate custom Java classes. Weakly typed representations use the class oracle.sql.STRUCT
(for objects), oracle.sql.REF
(for object references), or oracle.sql.ARRAY
(for collections). Or, alternatively, you can use standard java.sql.Struct
, Ref
, or Array
objects in a weakly typed scenario.
The term "strongly typed" is used where a particular Java type is associated with a particular SQL named (user-defined) type. For example, if there is a PERSON
type with a corresponding Person
Java class.
The term "weakly typed" is used where a Java type is used in a generic way and can map to multiple SQL named types. The Java class (or interface) has no special information particular to any SQL type. This is the case for the oracle.sql.STRUCT
, REF
, and ARRAY
types and the java.sql.Struct
, Ref
, and Array
types.
Note that using Oracle extensions in your code requires the following:
The default customizer, oracle.sqlj.runtime.util.OraCustomizer
, is recommended.
For Oracle-specific generated code, produced through the -codegen=oracle
translator setting, no profiles are produced so customization is not applicable. Oracle JDBC APIs are called directly through the generated Java code.
The Oracle SQLJ runtime and an Oracle JDBC driver are required whenever you use the Oracle customizer, even if you do not actually use Oracle extensions in your code.
For Oracle-specific semantics-checking, you must use an appropriate checker. The default checker, oracle.sqlj.checker.OracleChecker
, acts as a front end and will run the appropriate checker based on your environment. This will be one of the Oracle-specific checkers if you are using an Oracle JDBC driver.
Oracle-specific types for Oracle objects and collections are included in the oracle.sql
package.
For information about translator options relating to semantics-checking, see "Connection Options" and "Semantics-Checking Options".
ORAData
can be used for other Oracle SQL types as well. A class implementing ORAData
can be employed to perform any kind of desired processing or conversion in the course of transferring data between SQL and Java. See "Additional Uses for ORAData Implementations".
SQLData
interface is intended only for custom object classes. The ORAData
interface can be used for any custom Java class.
For general information about Oracle object features and functionality, see the Oracle9i Application Developer's Guide - Object-Relational Features.
Oracle objects (SQL objects) are composite data structures that group related data items, such as facts about each employee, into a single data unit. An object type is functionally similar to a Java class--you can populate and use any number of individual objects of a given object type, just as you can instantiate and use individual objects of a Java type.
For example, you can define an object type EMPLOYEE
that has the attributes name
(type CHAR
), address
(type CHAR
), phonenumber
(type CHAR
), and employeenumber
(type NUMBER
).
Oracle objects can also have methods--stored procedures associated with the object type. These methods can be either static methods or instance methods and can be implemented either in PL/SQL or in Java. Their signatures can include any number of input, output, or input-output parameters. All this depends on how they are initially defined.
There are two categories of Oracle collections (SQL collections):
Both categories are one-dimensional, although the elements can be complex object types. VARRAY types
are used for one-dimensional arrays; nested table types are used for single-column tables within an outer table. A variable of any VARRAY type can be referred to as a VARRAY; a variable of any nested table type can be referred to as a nested table.
A VARRAY, as with any array, is an ordered set of data elements, with each element having an index and all elements being of the same datatype. The size of a VARRAY refers to the maximum number of elements. Oracle VARRAYs are of variable size (thus the name), but the maximum size of any particular VARRAY type must be specified when the VARRAY type is declared.
A nested table is an unordered set of elements. Nested table elements within a table can themselves be queried in SQL. A nested table, as with any table, is not created with any particular number of rows--this is determined dynamically.
User-specified object and collection definitions in Oracle9i function as SQL datatype definitions. You can then use these datatypes, as with any other datatype, in defining table columns, SQL object attributes, and stored procedure or function parameters. In addition, once you have defined an object type, the related object reference type can be used as any other SQL reference type.
Once you have defined EMPLOYEE
as an Oracle object, as described in "Oracle Object Fundamentals", it becomes an Oracle datatype, and you can have a table column of type EMPLOYEE
just as you can have a table column of type NUMBER
. Each row in an EMPLOYEE
column contains a complete EMPLOYEE
object. You can also have a column type of REF EMPLOYEE
, consisting of references to EMPLOYEE
objects.
Similarly, you can define a variable-length array MYVARR
as VARRAY(10)
of NUMBER
and a nested table NTBL
of CHAR(20)
. The MYVARR
and NTBL
collection types become Oracle datatypes, and you can have table columns of either type. Each row of a MYVARR
column consists of an array of up to ten numbers; each row of an NTBL
column consists of 20 characters.
The purpose of custom Java classes is to provide a way to convert data between SQL and Java and make the data accessible, particularly in supporting objects and collections or if you want to perform custom data conversions.
It is generally advisable to provide custom Java classes for all user-defined types (objects and collections) that you use in a SQLJ application. The Oracle JDBC driver will use instances of these classes in converting data, which is more convenient and less error-prone than using the weakly typed oracle.sql.STRUCT
, REF
, and ARRAY
classes.
Custom Java classes are first-class types that you can use to read from and write to user-defined SQL types transparently.
To be used in SQLJ iterators or host expressions, a custom Java class must implement either the oracle.sql.ORAData
(and ORADataFactory
) interface or the standard java.sql.SQLData
interface. This section provides an overview of these interfaces and custom Java class functionality, covering the following topics:
This section discusses specifications of the ORAData
and ORADataFactory
interfaces and the standard SQLData
interface.
Oracle9i includes a set of new APIs for Oracle-specific custom Java class functionality for user-defined types--oracle.sql.ORAData
and oracle.sql.ORADataFactory
.
The oracle.sql.CustomDatum
and oracle.sql.CustomDatumFactory
interfaces used previously for this functionality are deprecated in Oracle9i, but still supported for backward compatibility. You must use the CustomDatum
interfaces if you are working with an Oracle8i JDBC driver.
Oracle provides the interface oracle.sql.ORAData
and the related interface oracle.sql.ORADataFactory
to use in mapping and converting Oracle object types, reference types, and collection types to custom Java classes.
Data is sent or retrieved in the form of an oracle.sql.Datum
object, with the underlying data being in the format of the appropriate oracle.sql.Datum
subclass--oracle.sql.STRUCT
, for example. This data is still in its SQL format; the oracle.sql.Datum
object is just a wrapper. (For information about classes in the oracle.sql
package that support Oracle type extensions, see the Oracle9i JDBC Developer's Guide and Reference.)
The ORAData
interface specifies a toDatum()
method for data conversion from Java format to SQL format. This method takes as input your connection object and converts data to the appropriate oracle.sql.*
representation. The connection object is necessary so that the JDBC driver can perform appropriate type checking and type conversions at runtime. Here is the ORAData
and toDatum()
specification:
interface oracle.sql.ORAData { oracle.sql.Datum toDatum(java.sql.Connection c) throws SQLException; }
The ORADataFactory
interface specifies a create()
method that constructs instances of your custom Java class, converting from SQL format to Java format. This method takes as input a Datum
object containing the data, and a typecode, such as OracleTypes.RAW
, indicating the SQL type of the underlying data. It returns an object of your custom Java class, which implements the ORAData
interface. This object receives its data from the Datum
object that was input. Here is the ORADataFactory
and create()
specification:
interface oracle.sql.ORADataFactory { oracle.sql.ORAData create(oracle.sql.Datum d, int sqlType) throws SQLException; }
To complete the relationship between the ORAData
and ORADataFactory
interfaces, you must implement a static getORADataFactory()
method in any custom Java class that implements the ORAData
interface. This method returns an object that implements the ORADataFactory
interface and that, therefore, can be used to create instances of your custom Java class. This returned object can itself be an instance of your custom Java class, and its create()
method is used by the Oracle JDBC driver to produce further instances of your custom Java class, as necessary.
For information about Oracle SQLJ requirements of a class that implements ORAData
, see "Oracle Requirements for Classes Implementing ORAData".
For more information about the ORAData
and ORADataFactory
interfaces, the oracle.sql
classes, and the OracleTypes
class, see the Oracle9i JDBC Developer's Guide and Reference.
If you use JPublisher, specifying -usertypes=oracle
will result in JPublisher generating custom Java classes that implement the ORAData
and ORADataFactory
interfaces and the getORADataFactory()
method. Or, for backwards compatibility, you have the option of using the JPublisher -compatible=customdatum
setting in conjunction with -usertypes=oracle
to use the CustomDatum
and CustomDatumFactory
interfaces instead. See the Oracle9i JPublisher User's Guide for more information.
As a result of the oracle.jdbc
interfaces being introduced in Oracle9i as replacements for the oracle.jdbc.driver
classes, the oracle.sql.CustomDatum
and oracle.sql.CustomDatumFactory
interfaces, formerly used to access customized objects, have been deprecated in favor of new interfaces--oracle.sql.ORAData
and oracle.sql.ORADataFactory
. Like the CustomDatum
interfaces, these can be used as an Oracle-specific alternative to the standard SQLData
interface. The CustomDatum
interfaces are still supported for backward compatibility.
CustomDatum
and CustomDatumFactory
have the following definitions:
public interface CustomDatum { oracle.sql.Datum toDatum( oracle.jdbc.driver.OracleConnection conn ) throws SQLException; public interface CustomDatumFactory { oracle.sql.CustomDatum create( oracle.sql.Datum d, int sqlType ) throws SQLException; }
The connection conn
and typecode sqlType
are used as described for ORAData
and ORADataFactory
in "ORAData and ORADataFactory Specifications". Note, however, that CustomDatum
uses the Oracle-specific OracleConnection
type instead of the standard Connection
type.
Standard JDBC 2.0 supplies the interface java.sql.SQLData
to use in mapping and converting structured object types to Java classes. This interface is intended for mapping structured object types only, not object references, collections/arrays, or other SQL types.
The SQLData
interface is a JDBC 2.0 standard, specifying a readSQL()
method to read data into a Java object, and a writeSQL()
method to write to the database from a Java object.
For information about functionality that is required of a class that implements SQLData
, see "Requirements for Classes Implementing SQLData".
For additional information about standard SQLData
functionality, refer to the Sun Microsystems JDBC 2.0 API Specification.
If you use JPublisher, specifying -usertypes=jdbc
will result in JPublisher generating custom Java classes that implement the SQLData
interface.
Methods of Oracle objects can be invoked from custom Java class wrappers. Whether the underlying stored procedure is written in PL/SQL or is written in Java and published to SQL is invisible to the user.
A Java wrapper method used to invoke a server method requires a connection to communicate with the server. The connection object can be provided as an explicit parameter or can be associated in some other way (as an attribute of your custom Java class, for example).
If the connection object used by the wrapper method is a non-static attribute, then the wrapper method must be an instance method of the custom Java class in order to have access to the connection. Custom Java classes generated by JPublisher use this technique.
There are also issues regarding output and input-output parameters in methods of Oracle objects. If a stored procedure (SQL object method) modifies the internal state of one of its arguments, then the actual argument passed to the stored procedure is modified. In Java this is not possible. When a JDBC output parameter is returned from a stored procedure call, it must be stored in a newly created object. The original object identity is lost.
One way to return an output or input-output parameter to the caller is to pass the parameter as an element of an array. If the parameter is input-output, the wrapper method takes the array element as input; after processing, the wrapper assigns the output to the array element. Custom Java classes generated by JPublisher use this technique--each output or input-output parameter is passed in a one-element array.
When you use JPublisher, it implements wrapper methods by default. This is true for generated classes implementing either the SQLData
interface or the ORAData
interface. To disable this feature, set the JPublisher -methods
flag to false
. See the Oracle9i JPublisher User's Guide for more information.
Note: If you are implementing a custom Java class yourself, there are various ways that you can implement wrapper methods. Data processing in the server can be done either through the SQL object method directly, or by forwarding the object value from the client to the server and then executing the method there. To see how JPublisher implements wrapper methods, and whether this may meet your needs, see "JPublisher Implementation of Wrapper Methods". |
Custom Java classes must satisfy certain requirements to be recognized by the Oracle SQLJ translator as valid host variable types, and to allow type-checking by the translator.
This section discusses Oracle-specific requirements of custom Java classes so they can support this functionality. Requirements for both ORAData
implementations and SQLData
implementations are covered.
Oracle requirements for ORAData
implementations are primarily the same for any kind of custom Java class but vary slightly depending on whether the class is for mapping to objects, object references, collections, or some other SQL type.
These requirements are as follows:
oracle.sql.ORAData
interface.
getORADataFactory()
that returns an oracle.sql.ORADataFactory
object as follows:
public static oracle.sql.ORADataFactory getORADataFactory();
If using the deprecated CustomDatum
interface, the class implements the method getFactory()
that returns an oracle.sql.CustomDatumFactory
object as follows:
public static oracle.sql.CustomDatumFactory getFactory();
_SQL_TYPECODE
(string), initialized to the oracle.jdbc.OracleTypes
typecode of the Datum
subclass that toDatum()
returns.
For custom object classes:
public static final int _SQL_TYPECODE
=OracleTypes.STRUCT;
For custom reference classes:
public static final int _SQL_TYPECODE
=OracleTypes.REF;
For custom collection classes:
public static final int _SQL_TYPECODE
=OracleTypes.ARRAY;
For other uses, some other typecode might be appropriate. For example, for using a custom Java class to serialize and deserialize Java objects into or out of RAW
fields, a _SQL_TYPECODE
of OracleTypes.RAW
is used. See "Serialized Java Objects".
(The OracleTypes
class simply defines a typecode, which is an integer constant, for each Oracle datatype. For standard SQL types, the OracleTypes
entry is identical to the entry in the standard java.sql.Types
type definitions class.)
_SQL_TYPECODE
of STRUCT
, REF
, or ARRAY
(in other words, for custom Java classes that represent objects, object references, or collections), the class has a constant that indicates the relevant user-defined type name.
_SQL_NAME
(string), initialized to the SQL name you declared for the user-defined type, as follows:
public static final String _SQL_NAME
= UDT name;
Custom object class example for a user-defined PERSON
object:
public static final String _SQL_NAME
= "PERSON";
or (to specify the schema, if that is appropriate):
public static final String _SQL_NAME
= "SCOTT.PERSON";
Custom collection class example for a collection of PERSON
objects, which you have declared as PERSON_ARRAY
:
public static final String _SQL_NAME
= "PERSON_ARRAY";
_SQL_BASETYPE
(string), initialized to the SQL name you declared for the user-defined type being referenced, as follows:
public static final String _SQL_BASETYPE = UDT name;
Custom reference class example for PERSON
references:
public static final String _SQL_BASETYPE
= "PERSON";
For other ORAData
uses, specifying a UDT name is not applicable.
PERSON_ARRAY
for PERSON
objects, then the name of the collection type that you specify for the _SQL_NAME
entry is PERSON_ARRAY
, not PERSON
.
_SQL_NAME
field, if the SQL type was declared in a case-sensitive way (in quotes), then you must specify the SQL name exactly as it was declared, such as CaseSensitive
or SCOTT.CaseSensitive
. (Note this differs from usage in a JPublisher input file, where the case-sensitive name must also appear in quotes.) If you did not declare the SQL type in a case-sensitive way (no quotes), then you must specify the SQL name in all uppercase, such as ADDRESS
or SCOTT.ADDRESS
.
JPublisher automatically generates the value of this field appropriately, according to case-sensitivity and the JPublisher -omit_schema_names
setting if applicable.
The SQLJ ISO standard outlines requirements for type map definitions for classes implementing the SQLData
interface.
Alternatively, SQLData wrapper classes can identify associated SQL object types through public static final
fields. This non-standard functionality was introduced in Oracle SQLJ release 8.1.6 and continues to be supported.
Be aware of the following important points:
public static final
fields to specify mappings, you must be consistent in your approach. Either use a type map that specifies all relevant mappings so that you do not require public static final
fields, or do not use a type map at all and specify all mappings through public static final
fields.
SQLData
, unlike ORAData
, is for mapping structured object types only. It is not for object references, collections/arrays, or any other SQL types. If you are not using ORAData
, then your only choices for mapping object references and collections are the weak types java.sql.Ref
and java.sql.Array
, respectively (or oracle.sql.REF
and oracle.sql.ARRAY
).
SQLData
implementations require a JDK 1.2.x environment. Although Oracle JDBC supports JDBC 2.0 extensions under JDK 1.1.x through the oracle.jdbc2
package, Oracle SQLJ does not.
CaseSensitive
or SCOTT.CaseSensitive
. (Note this differs from usage in a JPublisher input file, where the case-sensitive name must also appear in quotes.) If you did not declare the SQL type in a case-sensitive way (no quotes), then you must specify the SQL name in all uppercase, such as ADDRESS
or SCOTT.ADDRESS
.
First, consider the mapping representation according to the SQLJ ISO standard. Assume that Address
, pack.Person
, and pack.Manager.InnerPM
(where InnerPM
is an inner class of Manager
) are three wrapper classes that implement java.sql.SQLData
.
SDContext
. Example:
Address a =...; pack.Person p =...; pack.Manager.InnerPM pm =...; SDContext ctx = new SDContext(url,user,pwd,false); #sql [ctx] { ... :a ... :p ... :pm ... };
with
attribute typeMap
that specifies an associated class implementing a java.util.PropertyResourceBundle
. In the preceding example, SDContext
might have been declared as follows:
#sql public static context SDContext with (typeMap="SDMap");
java.sql.SQLData
interface. This mapping is specified with entries of the following form:
class.<java_class_name>=STRUCT <sql_type_name>
The keyword STRUCT
can also be omitted. In our example, the resource file SDMap.properties
might contain the following entries:
class.Address=STRUCT SCOTT.ADDRESS class.pack.Person=PERSON class.pack.Manager$InnerPM=STRUCT PRODUCT_MANAGER
Although ".
" separates package and class name, you must use the character "$
" to separate an inner class name.
This mechanism is more complicated than the non-standard alternative (discussed next). Furthermore, it is not possible to associate a type map resource with the default connection context. The advantage is that all the mapping information is placed in a single location--the type map resource.This means that the type mapping in an already compiled application can be easily adjusted at a later time, for example to accommodate new SQL types and Java wrappers in an expanding SQL-Java type hierarchy.
Be aware of the following:
runtime12
or runtime12ee
library to use this feature. Type maps are represented as java.util.Map
objects. These are exposed in the SQLJ runtime API and, therefore, cannot be supported by the generic runtime libraries.
SQLData
wrapper classes occur as OUT or INOUT parameters in SQLJ statements. This is because the SQL type of such parameters is required for registerOutParameter()
by the Oracle JDBC driver. Furthermore, for OUT parameter type registration, the SQL type is "frozen in" by the type map in effect during translation.
SQLData
wrappers. However, you can easily extract the type map in effect on a given SQLJ connection context:
ctx.getTypeMap();
Alternatively, a class that implements SQLData
can satisfy the following non-standard requirement.
public static final String
-valued field _SQL_NAME
. This field defines the name of the SQL type that is being wrapped by the Java class.
In our example, the Address
class would have the following field declaration:
public static final String _SQL_NAME="SCOTT.ADDRESS";
The following declaration would be in pack.Person
:
public static final String _SQL_NAME="PERSON";
And the class pack.Manager.InnerPM
would hold the following:
public static final String _SQL_NAME="PRODUCT_MANAGER";
Note that JPublisher always generates SQLData
wrapper classes with the _SQL_NAME
field. However, this field is ignored in SQLJ statements that reference a type map.
_SQL_NAME
field is used in a SQLJ statement with an explicit connection context type and associated type map, then that type map is used, and the _SQL_NAME
field is ignored, thereby simplifying migration of existing SQLJ programs to the new SQLJ ISO standard.
_SQL_NAME
field is independent from any JDBC type map you may be using on the underlying connection. Thus, you must be careful if you are mixing SQLJ and JDBC code that both use SQLData
wrappers.
You can include the .java
file names for your custom Java classes (whether ORAData
or SQLData
implementations) on the SQLJ command line, together with your .sqlj
file names. However, this is not necessary if the SQLJ -checksource
flag is set to true
(the default) and your classpath includes the directory where the custom Java source is located.
For example, if ObjectDemo.sqlj
uses Oracle object types ADDRESS
and PERSON
and you have run JPublisher or otherwise produced custom Java classes for these objects, then you can run SQLJ as follows.
If -checksource=true
(default) and the classpath includes the custom Java source location:
% sqlj ObjectDemo.sqlj
Or, if -checksource=false
:
% sqlj ObjectDemo.sqlj Address.java AddressRef.java Person.java PersonRef.java
Any .sqlj
files that JPublisher produces, however, must be explicitly included on the SQLJ command line, even with -checksource=true
.
You also have the choice of using your Java compiler to compile custom Java source files directly. If you do this, you must do it prior to translating .sqlj
files.
Running the SQLJ translator is discussed in Chapter 8, "Translator Command Line and Options". For more information about the -checksource
flag, see "Source Check for Type Resolution (-checksource)".
Note:
Because |
Through the use of custom Java class instances, Oracle SQLJ and JDBC allow you to read and write user-defined types as though they are built-in types. Exactly how this is accomplished is transparent to the user.
For the mechanics of how data is read and written, for both ORAData implementations and SQLData implementations, see the Oracle9i JDBC Developer's Guide and Reference.
To this point, discussion of custom Java classes has been for use as one of the following:
oracle.sql.STRUCT
instances
oracle.sql.REF
instances
oracle.sql.ARRAY
instances
It might be useful, however, to provide custom Java classes to wrap other oracle.sql.*
types as well, for customized conversions or processing. You can accomplish this with classes that implement ORAData
(but not SQLData
), as in the following examples:
DATE
field to java.util.Date
format).
RAW
fields, for example
This last use is further discussed in "Serialized Java Objects".
"General Use of ORAData--BetterDate.java" provides an example of a class (BetterDate
) that implements ORAData
and can be used instead of java.sql.Date
to represent dates.
This section contains examples of creating and using user-defined object types and collection types in Oracle9i. A full SQL script for all the user-defined types employed in the object and collection sample applications is in "Definition of Object and Collection Types".
For more information about any of the SQL commands used here, refer to the Oracle9i SQL Reference.
Oracle SQL commands to create object types are of the following form:
CREATE TYPE typename AS OBJECT ( attrname1 datatype1, attrname2 datatype2, ... ... attrnameN datatypeN );
Where typename
is the desired name of your object type, attrname1
through attrnameN
are the desired attribute names, and datatype1
through datatypeN
are the attribute datatypes.
The remainder of this section provides an example of creating user-defined object types in Oracle9i.
The following items are created using the SQL script below:
PERSON
and ADDRESS
PERSON
objects
EMPLOYEES
table that includes an ADDRESS
column and two columns of PERSON
references
Here is the script:
/*** Using user-defined types (UDTs) in SQLJ ***/ / /*** Create ADDRESS UDT ***/ CREATE TYPE ADDRESS AS OBJECT ( street VARCHAR(60), city VARCHAR(30), state CHAR(2), zip_code CHAR(5) ) / /*** Create PERSON UDT containing an embedded ADDRESS UDT ***/ CREATE TYPE PERSON AS OBJECT ( name VARCHAR(30), ssn NUMBER, addr ADDRESS ) / /*** Create a typed table for PERSON objects ***/ CREATE TABLE persons OF PERSON / /*** Create a relational table with two columns that are REFs to PERSON objects, as well as a column which is an Address ADT. ***/ CREATE TABLE employees ( empnumber INTEGER PRIMARY KEY, person_data REF PERSON, manager REF PERSON, office_addr ADDRESS, salary NUMBER ) /*** Insert some data--2 objects into the persons typed table ***/ INSERT INTO persons VALUES ( PERSON('Wolfgang Amadeus Mozart', 123456, ADDRESS('Am Berg 100', 'Salzburg', 'AT','10424'))) / INSERT INTO persons VALUES ( PERSON('Ludwig van Beethoven', 234567, ADDRESS('Rheinallee', 'Bonn', 'DE', '69234'))) / /** Put a row in the employees table **/ INSERT INTO employees (empnumber, office_addr, salary) VALUES ( 1001, ADDRESS('500 Oracle Parkway', 'Redwood Shores', 'CA', '94065'), 50000) / /** Set the manager and PERSON REFs for the employee **/ UPDATE employees SET manager = (SELECT REF(p) FROM persons p WHERE p.name = 'Wolfgang Amadeus Mozart') / UPDATE employees SET person_data = (SELECT REF(p) FROM persons p WHERE p.name = 'Ludwig van Beethoven')
There are two categories of collections
Oracle SQL commands to create VARRAY types are of the following form:
CREATE TYPE typename IS VARRAY(n) OF datatype;
Where typename
is the desired name of your VARRAY type, n
is the desired maximum number of elements in the array, and datatype
is the datatype of the array elements. For example:
CREATE TYPE myvarr IS VARRAY(10) OF INTEGER;
Oracle SQL commands to create nested table types are of the following form:
CREATE TYPE typename AS TABLE OF datatype;
Where typename
is the desired name of your nested table type, and datatype
is the datatype of the table elements (this can be a user-defined type as well as a standard datatype). A nested table is limited to one column, although that one column type can be a complex object with multiple attributes. The nested table, as with any database table, can have any number of rows. For example:
CREATE TYPE person_array AS TABLE OF person;
This command creates a nested table where each row consists of a PERSON
object.
The rest of this section provides an example of creating a user-defined collection type (as well as object types) in Oracle9i.
The following items are created and populated using the SQL script below:
PARTICIPANT_T
and MODULE_T
MODULETBL_T
, which is a nested table of MODULE_T
objects
PROJECTS
table that includes a column of PARTICIPANT_T
references and a column of MODULETBL_T
nested tables
PHONE_ARRAY
, which is a VARRAY of VARCHAR2(30)
PERSON
and ADDRESS
objects (repeating the same definitions used earlier in "Creating Object Types")
EMPLOYEES
table, which includes a PHONE_ARRAY
column
Here is the script:
Rem This is a SQL*Plus script used to create schema to demonstrate collection Rem manipulation in SQLJ CREATE TYPE PARTICIPANT_T AS OBJECT ( empno NUMBER(4), ename VARCHAR2(20), job VARCHAR2(12), mgr NUMBER(4), hiredate DATE, sal NUMBER(7,2), deptno NUMBER(2)) / show errors CREATE TYPE MODULE_T AS OBJECT ( module_id NUMBER(4), module_name VARCHAR2(20), module_owner REF PARTICIPANT_T, module_start_date DATE, module_duration NUMBER ) / show errors create TYPE MODULETBL_T AS TABLE OF MODULE_T; / show errors CREATE TABLE projects ( id NUMBER(4), name VARCHAR(30), owner REF PARTICIPANT_T, start_date DATE, duration NUMBER(3), modules MODULETBL_T ) NESTED TABLE modules STORE AS modules_tab; show errors CREATE TYPE PHONE_ARRAY IS VARRAY (10) OF varchar2(30) / /*** Create ADDRESS UDT ***/ CREATE TYPE ADDRESS AS OBJECT ( street VARCHAR(60), city VARCHAR(30), state CHAR(2), zip_code CHAR(5) ) / /*** Create PERSON UDT containing an embedded ADDRESS UDT ***/ CREATE TYPE PERSON AS OBJECT ( name VARCHAR(30), ssn NUMBER, addr ADDRESS ) / CREATE TABLE employees ( empnumber INTEGER PRIMARY KEY, person_data REF person, manager REF person, office_addr address, salary NUMBER, phone_nums phone_array ) /
Oracle offers flexibility in how users can customize the mapping of Oracle object types, reference types, and collection types to Java classes in a strongly typed paradigm. Developers have the following choices in creating these custom Java classes:
Although you have the option of manually coding your custom Java classes, using or subclassing JPublisher-generated classes is advisable.
JPublisher can implement either the Oracle oracle.sql.ORAData
interface or the standard java.sql.SQLData
interface when it generates a custom object class. If you choose the ORAData
implementation, then JPublisher will also generate a custom reference class. For compatibility with older JDBC versions, JPublisher can also generate classes that implement the oracle.sql.CustomDatum
interface.
The SQLData
interface is not intended for custom reference or custom collection classes. If you want your code to be portable, you have no choice but to use standard, weakly typed java.sql.Ref
objects to map to references, and java.sql.Array
objects to map to collections.
This manual provides only minimal information and detail regarding the JPublisher utility. See the Oracle9i JPublisher User's Guide for more information.
For detailed discussion of the ORAData
and SQLData
interfaces and relative advantages of the ORAData
interface, see the Oracle9i JDBC Developer's Guide and Reference.
When you use JPublisher to generate custom Java classes, you can use either an ORAData
implementation (for custom object classes, custom reference classes, or custom collection classes) or a SQLData
implementation (for custom object classes only). An ORAData
implementation will also implement the ORADataFactory
interface, for creating instances of the custom Java class.
This is controlled by how you set the JPublisher -usertypes
option. A setting of -usertypes=oracle
specifies an ORAData
implementation; a setting of -usertypes=jdbc
specifies a SQLData
implementation.
When you run JPublisher for a user-defined object type and choose the ORAData
implementation for your custom object class (through the -usertypes=oracle
setting), JPublisher automatically creates the following:
This class includes getter and setter methods for each attribute. The method names are of the form getFoo()
and setFoo()
for attribute foo
.
In addition, JPublisher by default will generate wrapper methods in your class that invoke the associated Oracle object methods executing in the server. This can be disabled, however, by setting -methods=false
. This option is described later in this section.
This class includes a getValue()
method that returns an instance of your custom object class, and a setValue()
method that updates an object value in the database, taking as input an instance of the custom object class.
A strongly typed reference class is always generated, regardless of whether the SQL object type uses references.
Advantages of using strongly typed instead of weakly typed references are described in"Strongly Typed Object References for ORAData Implementations".
This is necessary so that attributes can be materialized in Java whenever an instance of the top-level class is materialized.
When you run JPublisher for a user-defined collection type, choosing the ORAData
implementation, JPublisher automatically creates the following:
This class includes overloaded getArray()
and setArray()
methods to retrieve or update a collection as a whole, a getElement()
method and setElement()
method to retrieve or update individual elements of a collection, and additional utility methods.
This is necessary so that object elements can be materialized in Java whenever an instance of the collection is materialized.
JPublisher-generated custom Java classes in any of these categories implement the ORAData
interface, the ORADataFactory
interface, and the getORADataFactory()
method.
Notes:
|
For Oracle ORAData
implementations, JPublisher always generates strongly typed object reference classes as opposed to using the weakly typed oracle.sql.REF
class. This is to provide greater type safety and to mirror the behavior in SQL, where object references are strongly typed. The strongly typed classes (with names such as PersonRef
for references to PERSON
objects) are essentially wrappers for the REF
class.
In these strongly typed REF
wrappers, there is a getValue()
method that produces an instance of the SQL object that is referenced, in the form of an instance of the corresponding Java class. (Or, in the case of inheritance, perhaps as an instance of a subclass of the corresponding Java class.) For example, if there is a PERSON
SQL object type, with a corresponding Person
Java class, there will also be a PersonRef
Java class. The getValue()
method of the PersonRef
class would return a Person
instance containing the data for a PERSON
object in the database.
Whenever a SQL object type has an attribute that is an object reference, the Java class corresponding to the object type would have an attribute that is an instance of a Java class corresponding to the appropriate reference type. For example, if there is a PERSON
object with a MANAGER REF
attribute, then the corresponding Person
Java class will have a ManagerRef
attribute.
For standard SQLData
implementations, strongly typed object references are not supported (they are not part of the standard). JPublisher does not create a custom reference class; you must use java.sql.Ref
or oracle.sql.REF
as the reference type.
When you run JPublisher for a user-defined object type and choose the SQLData
implementation for your custom object class (through the -usertypes=jdbc
setting), JPublisher will produce a custom object class to act as a type definition to correspond to your Oracle object type. This class will include the following:
SQLData
interface readSQL()
and writeSQL()
methods
-methods=false
when you run JPublisher)
Because the SQLData
interface is intended only for objects, however, and not for references or collections, JPublisher will not generate a custom reference class for references to the Oracle object type. You will have to use standard, weakly typed java.sql.Ref
instances, or perhaps oracle.sql.REF
instances if you do not require portability. Note that REF
instances, like custom reference class instances, have Oracle extension methods getValue()
and setValue()
to read or write instances of the referenced object. Standard Ref
instances do not have this functionality.
Similarly, because you cannot use a SQLData
implementation for a custom collection class, you must use standard, weakly typed java.sql.Array
instances, or perhaps oracle.sql.ARRAY
instances if you do not require portability. Array
and ARRAY
instances, like custom collection class instances, have getArray()
functionality to read the collection as a whole or in part, but do not have the element-level access and writability offered by the custom collection class getElement()
and setElement()
methods.
This section discusses key JPublisher command-line functionality for specifying the user-defined types that you want to map to Java and for specifying object class names, collection class names, attribute type mappings, and wrapper methods. These key points can be summarized as follows:
ORAData
or SQLData
; use the JPublisher -usertypes
option).
-sql
, -user
, and -case
options).
-XXXtypes
options: -numbertypes
, -builtintypes
, and -lobtypes
).
-methods
flag, which is enabled by default).
Before running JPublisher, consider whether you want the generated classes to implement the Oracle ORAData
interface or the standard SQLData
interface. Using SQLData
will likely make your code more portable, but using ORAData
offers a number of advantages, including no need for type maps.
The preceding section, "What JPublisher Produces", discusses some of the implementation details for each scenario.
Remember the following:
ORAData
implementations for custom collection classes. The SQLData
interface does not support collections (arrays).
ORAData
custom object class implementations, but not for SQLData
custom object class implementations.
For detailed discussion of the ORAData
and SQLData
interfaces and relative advantages of the ORAData
interface, see the Oracle9i JDBC Developer's Guide and Reference.
Use the JPublisher -usertypes
option to specify which interface you want your classes to implement. A setting of -usertypes=oracle
(the default) specifies the ORAData
interface, while a setting of -usertypes=jdbc
specifies the SQLData
interface.
Note:
If you have a requirement to implement the
(The setting |
The following JPublisher command-line examples will result in implementation of ORAData
, CustomDatum
, and SQLData
, respectively (assume %
is a system prompt).
% jpub -usertypes=oracle ... <other option settings> % jpub -usertypes=oracle -compatible=customdatum ... <other option settings> % jpub -usertypes=jdbc ... <other option settings>
JPublisher will ignore a -compatible=customdatum
or -compatible=oradata
setting if -usertypes=jdbc
.
In using JPublisher to create custom Java classes, use the -sql
option to specify the user-defined SQL types that you want to map to Java. You can either specify the custom object class names and custom collection class names, or you can accept the defaults.
The default names of your top-level custom classes--the classes that will correspond to the user-defined type names you specify to the -sql
option--are identical to the user-defined type names as you enter them on the JPublisher command line. Because SQL names in the database are case-insensitive by default, you can capitalize them to ensure that your class names are capitalized per Java convention. For example, if you want to generate a custom class for employee
objects, you can run JPublisher as follows:
% jpub -sql=Employee ...
The default names of other classes, such as for home_address
objects that are attributes of employee
objects, are determined by the JPublisher -case
option. If you do not set the -case
option, it is set to mixed
. This means that the default for the custom class name is to capitalize the initial character of the corresponding user-defined type name and the initial character of every word unit thereafter. JPublisher interprets underscores (_), dollar signs ($), and any characters that are illegal in Java identifiers as word-unit separators; these characters are discarded in the process.
For example, for Oracle object type home_address
, JPublisher would create class HomeAddress
in a HomeAddress.java
source file.
Important:
Only non-case-sensitive SQL names are supported on the JPublisher command line. If a user-defined type was defined in a case-sensitive way (in quotes) in SQL, then you must specify the name in the JPublisher |
On the JPublisher command line, use syntax as in the next example for the -sql
option (you can specify multiple actions in a single option setting).
-sql=udt1<:mapclass1><,udt2<:mapclass2>>,...,<udtN<:mapclassN>> ...
And use the -user
option to specify the database schema. Following is an example:
% jpub -sql=Myobj,mycoll:MyCollClass -user=scott/tiger
(There can be no space before or after the comma.)
For the Oracle object MYOBJ
, this command will name it as you typed it, creating source Myobj.java
to define a Myobj
class. For the Oracle collection MYCOLL
, this command will create source MyCollClass.java
to define a MyCollClass
class.
You can optionally specify schema names in the -sql
option--for example, the scott
schema:
% jpub -sql=scott.Myobj,scott.mycoll:MyCollClass -user=scott/tiger
You cannot specify custom reference class names; JPublisher automatically derives them by adding "Ref" to custom object class names (relevant to ORAData
implementations only). For example, if JPublisher produces Java source Myobj.java
to define custom object class Myobj
, then it will also produce Java source MyobjRef.java
to define a MyobjRef
custom reference class.
To create custom Java classes for the object and collection types defined in "User-Defined Types", you can run JPublisher as follows:
%jpub -user=scott/tiger -sql=Address,Person,Phone_array,Participant_t, Module_t,Moduletbl_t
Or, to explicitly specify custom object class and custom collection class names:
%jpub -user=scott/tiger -sql=Address,Person,phone_array:PhoneArray, participant_t:ParticipantT,module_t:ModuleT,moduletbl_t:ModuletblT
(Each of the preceding two examples is a single wrap-around command line.)
The second example will produce Java source files Address.java
, AddressRef.java
, Person.java
, PersonRef.java
, PhoneArray.java
, ParticipantT.java
, ParticipantTRef.java
, ModuleT.java
, ModuleTRef.java
, and ModuletblT.java
. Examples of some of these source files are provided in "JPublisher Custom Java Class Examples".
So that it knows how to populate the custom Java classes, JPublisher connects to the specified schema (here, scott/tiger
) to determine attributes of your specified object types or elements of your specified collection types.
If you want to change how JPublisher uses character case in default names for the methods and attributes that it generates, including lower-level custom Java class names for attributes that are objects or collections, you can accomplish this using the -case
option. There are four possible settings:
-case=mixed
(default)--The following will be uppercase: the first character of every word unit of a class name, every word unit of an attribute name, and every word unit after the first word unit of a method name. All other characters are in lowercase. JPublisher interprets underscores (_), dollar signs ($), and any characters that are illegal in Java identifiers as word-unit separators; these characters are discarded in the process.
-case=same
--Character case is unchanged from its representation in the database. Underscores and dollar signs are retained; illegal characters are discarded.
-case=upper
--Lowercase letters are converted to uppercase. Underscores and dollar signs are retained; illegal characters are discarded.
-case=lower
--Uppercase letters are converted to lowercase. Underscores and dollar signs are retained; illegal characters are discarded.
JPublisher offers several choices for how to map user-defined types and their attribute and element types between SQL and Java. The rest of this section lists categories of SQL types and the mapping options available for each category.
(See "Supported Types for Host Expressions" for general information about how Oracle datatypes map to Java types.)
For more information about JPublisher features or options, see the Oracle9i JPublisher User's Guide.
JPublisher categorizes SQL types into the following groups, with corresponding JPublisher options as noted:
NUMBER
Use the JPublisher -numbertypes
option to specify type-mapping for numeric types.
BLOB
and CLOB
Use the JPublisher -lobtypes
option to specify type-mapping for LOB types.
CHAR
, VARCHAR2
, LONG
, and RAW
Use the JPublisher -builtintypes
option to specify type-mapping for built-in types.
JPublisher defines the following type-mapping modes:
jdbc
)--Uses standard default mappings between SQL types and Java native types. This can be used as a setting for the -numbertypes
, -lobtypes
, and -builtintypes
options.
oracle
)--Uses corresponding oracle.sql
types to map to SQL types. This can be used as a setting for the -numbertypes
, -lobtypes
, and -builtintypes
options.
objectjdbc
)--This is an extension of JDBC mapping. Where relevant, object-JDBC mapping uses numeric object types from the standard java.lang
package (such as java.lang.Integer
, Float
, and Double
) instead of primitive Java types (such as int
, float
, and double
). The java.lang
types are nullable; the primitive types are not. This can be used as a setting for the -numbertypes
option only.
BigDecimal
mapping (setting bigdecimal
)--Uses java.math.BigDecimal
to map to all numeric attributes; appropriate if you are dealing with large numbers but do not want to map to the oracle.sql.NUMBER
type. This can be used as a setting for the -numbertypes
option only.
The next section discusses type mapping options that you can use for object attributes and collection elements.
If you do not specify mappings for the attribute types of a SQL object type or the element types of a SQL collection type, then JPublisher uses the following defaults:
If you want alternate mappings, use the -numbertypes
, -lobtypes
, and -builtintypes
options as necessary, depending on the attribute types you have and the mappings you desire.
If an attribute type is itself a SQL object type, it will be mapped according to the -usertypes
setting.
Table 6-1 summarizes JPublisher categories for SQL types, the mapping settings relevant for each category, and the default settings.
Note:
The JPublisher |
In creating custom object classes to map Oracle objects to Java, the -methods
option instructs JPublisher whether to include Java wrappers for Oracle object methods (member functions). The default -methods=true
setting generates wrappers.
Wrapper methods generated by JPublisher are always instance methods, even when the original object methods are static. See "Custom Java Class Support for Object Methods" for more information.
The following example shows how to set the -methods
option:
% jpub -sql=Myobj,mycoll:MyCollClass -user=scott/tiger -methods=true
This will use default naming--the Java method names will be derived in the same fashion as custom Java class names (as described in "Specify User-Defined Types to Map to Java"), except that the initial character will be lowercase. For example, by default an object method name of CALC_SAL
results in a Java wrapper method of calcSal()
.
Alternatively, you can specify desired Java method names, but this requires use of a JPublisher INPUT
file and is discussed in "Creating Custom Java Classes and Specifying Member Names".
Note:
The |
If you run JPublisher for an Oracle object that has an overloaded method where multiple signatures have the same corresponding Java signature, then JPublisher will generate a uniquely named method for each signature. It accomplishes this by appending _n
to function names, where n
is a number. This is to ensure that no two methods in the generated custom Java class have the same name and signature. Consider, for example, the SQL functions defined in creating a MY_TYPE
object type:
CREATE OR REPLACE TYPE my_type AS OBJECT
( ... MEMBER FUNCTION myfunc(x INTEGER) RETURN my_return IS BEGIN ... END; MEMBER FUNCTION myfunc(y SMALLINT) RETURN my_return IS BEGIN ... END; ...);
Without precaution, both definitions of myfunc
result in the following name and signature in Java:
myfunc(Integer)
This is because both INTEGER
and SMALLINT
in SQL map to the Java Integer
type.
Instead, JPublisher might call one myfunc_1
and the other myfunc_2
. (The _n
is unique for each. In simple cases it will likely be _1
, _2
, and so on, but it might sometimes be arbitrary, other than being unique for each.)
You can use JPublisher to generate a custom Java class but instruct it to map the object type (or collection type) to an alternative class instead of to the generated class.
A typical scenario is to treat JPublisher-generated classes as superclasses, extend them to add functionality, and map the object types to the subclasses. For example, presume you have an Oracle object type ADDRESS
and want to produce a custom Java class for it that has functionality beyond what is produced by JPublisher. You can use JPublisher to generate a custom Java class JAddress
for the purpose of subclassing it to produce a class MyAddress
. Under this scenario you will add any special functionality to MyAddress
and will want JPublisher to map ADDRESS
objects to that class, not to the JAddress
class. You will also want JPublisher to produce a reference class for MyAddress
, not JAddress
.
JPublisher has functionality to streamline the process of mapping to alternative classes. Use the following syntax in your -sql
option setting:
-sql=object_type:generated_class:map_class
For the above example, use this setting:
-sql=ADDRESS:JAddress:MyAddress
This generates class JAddress
in source file JAddress.java
, but does the following:
ADDRESS
to the MyAddress
class, not to the JAddress
class. Therefore, if you retrieve an object from the database that has an ADDRESS
attribute, then this attribute will be created as an instance of MyAddress
in Java. Or, if you retrieve an ADDRESS
object directly, you will retrieve it into a MyAddress
instance.
MyAddressRef
class in MyAddressRef.java
, instead of creating a JAddressRef
class.
You must manually define a MyAddress
class in a MyAddress.java
source file. This class implements your required functionality by subclassing JAddress
.
For further discussion about subclassing JPublisher-generated classes or using them as fields (continuing the preceding example), see "Extending Classes Generated by JPublisher".
JPublisher supports the use of special INPUT
files and standard properties files to specify type mappings and additional option settings.
You can use the JPublisher -input
command-line option to specify an INPUT
file for JPublisher to use for additional type mappings.
"SQL
" in an INPUT
file is equivalent to "-sql
" on the command line, and "AS
" or "GENERATE...AS
" syntax is equivalent to command-line colon syntax. Use the following syntax, specifying just one mapping per SQL command:
SQL udt1 <GENERATE GeneratedClass1> <AS MapClass1> SQL udt2 <GENERATE GeneratedClass2> <AS MapClass2> ...
This generates GeneratedClass1
and GeneratedClass2
, but maps udt1
to MapClass1
and udt2
to MapClass2
.
In the following example, JPublisher will pick up the -user
option from the command line and go to INPUT
file myinput.in
for type mappings.
Command line:
% jpub -input=myinput.in -user=scott/tiger
Contents of INPUT
file myinput.in
:
SQL Myobj SQL mycoll AS MyCollClass SQL employee GENERATE Employee AS MyEmployee
This accomplishes the following:
MYOBJ
gets the custom object class name Myobj
because that's how you typed it--JPublisher creates source Myobj.java
(and MyobjRef.java
).
MYCOLL
is mapped to MyCollClass
. JPublisher creates a MyCollClass.java
source file.
EMPLOYEE
is mapped to the MyEmployee
class. JPublisher creates source Employee.java
and MyEmployeeRef.java
. If you retrieve an object from the database that has an EMPLOYEE
attribute, this attribute would be created as an instance of MyEmployee
in Java. Or if you retrieve an EMPLOYEE
object directly, presumably you will retrieve it into a MyEmployee
instance. You must manually create source file MyEmployee.java
to define class MyEmployee
, which would subclass the Employee
class.
You can use the JPublisher -props
command-line option to specify a properties file for JPublisher to use for additional type mappings and other option settings.
In a properties file, "jpub.
" (including the period) is equivalent to the command-line "-
" (single-dash), and other syntax remains the same. Specify only one option per line.
For type mappings, for example, "jpub.sql
" is equivalent to "-sql
". As on the command line, but unlike in an INPUT
file, you can specify multiple mappings in a single jpub.sql
setting.
In the following example, JPublisher will pick up the -user
option from the command line and go to properties file jpub.properties
for type mappings and the attribute-mapping option.
Command line:
% jpub -props=jpub.properties -user=scott/tiger
Contents of properties file jpub.properties
:
jpub.sql=Myobj,mycoll:MyCollClass,employee:Employee:MyEmployee jpub.usertypes=oracle
This produces the same results as the input-file example above, explicitly specifying the oracle
mapping setting.
In generating custom Java classes you can specify the names of any attributes or methods of the custom class. This cannot be specified on the JPublisher command line, however--only in a JPublisher INPUT
file using TRANSLATE
syntax, as follows:
SQL udt <GENERATE GeneratedClass> <AS MapClass> <TRANSLATE membername1 AS Javaname1> <, membername2 AS Javaname2> ...
(This is a single wrap-around command line.)
TRANSLATE
pairs (membernameN
AS
JavanameN
) are separated by commas.
For example, presume the Oracle object type EMPLOYEE
has an ADDRESS
attribute that you want to call HomeAddress
, and a GIVE_RAISE
method that you want to call giveRaise()
. Also presume that you want to generate an Employee
class but map EMPLOYEE
objects to a MyEmployee
class that you will create (this is not related to specifying member names, but provides a full example of INPUT
file syntax).
SQL employee GENERATE Employee AS MyEmployee TRANSLATE address AS HomeAddress, GIVE_RAISE AS giveRaise
(This is a single wrap-around command line.)
This section describes how JPublisher generates wrapper methods and how wrapper method calls are processed at runtime.
The following points describe how JPublisher generates wrapper methods:
-methods=true
, the custom object class will be defined in a .sqlj
file instead of in a .java
file, assuming the object type defines methods. Run SQLJ to translate the .sqlj
file.
Even if the object type does not define methods, you can ensure that a
Note:
.sqlj
file is generated by setting -methods=always
. See the Oracle9i JPublisher User's Guide for more information.
The following points describe what JPublisher-generated Java wrapper methods execute at runtime. In this discussion, "Java wrapper method" refers to a method in the custom Java object, while "wrapped SQL method" refers to the SQL object method that is wrapped by the Java wrapper method.
This section provides examples of JPublisher-generated ORAData
implementations for the following user-defined types (created in "User-Defined Types"):
Address
, corresponding to the Oracle object type ADDRESS
) and related custom reference class (AddressRef
)
ModuletblT
, corresponding to the Oracle collection type MODULETBL_T
)
For examples of JPublisher-generated
Note:
SQLData
implementations, as well as further examples of JPublisher-generated ORAData
implementations, see the Oracle9i JPublisher User's Guide.
Following is an example of the source code that JPublisher generates for a custom object class. Implementation details have been omitted.
In this example, unlike in "Creating Object Types", assume the Oracle object ADDRESS
has only the street
and zip_code
attributes.
package bar; import java.sql.SQLException; import java.sql.Connection; import oracle.jdbc.OracleTypes; import oracle.sql.ORAData; import oracle.sql.ORADataFactory; import oracle.sql.Datum; import oracle.sql.STRUCT; import oracle.jpub.MutableStruct; public class Address implements ORAData, ORADataFactory { public static final String _SQL_NAME = "SCOTT.ADDRESS"; public static final int _SQL_TYPECODE = OracleTypes.STRUCT; public static ORADataFactory getORADataFactory() { ... } /* constructor */ public Address() { ... } /* ORAData interface */ public Datum toDatum(Connection c) throws SQLException { ... } /* ORADataFactory interface */ public ORAData create(Datum d, int sqlType) throws SQLException { ... } /* accessor methods */ public String getStreet() throws SQLException { ... } public void setStreet(String street) throws SQLException { ... } public String getZipCode() throws SQLException { ... } public void setZipCode(String zip_code) throws SQLException { ... } }
Following is an example of the source code that JPublisher generates for a custom reference class to be used for references to ADDRESS
objects. Implementation details have been omitted.
package bar; import java.sql.SQLException; import java.sql.Connection; import oracle.jdbc.OracleTypes; import oracle.sql.ORAData; import oracle.sql.ORADataFactory; import oracle.sql.Datum; import oracle.sql.REF; import oracle.sql.STRUCT; public class AddressRef implements ORAData, ORADataFactory { public static final String _SQL_BASETYPE = "SCOTT.ADDRESS"; public static final int _SQL_TYPECODE = OracleTypes.REF; public static ORADataFactory getORADataFactory() { ... } /* constructor */ public AddressRef() { ... } /* ORAData interface */ public Datum toDatum(Connection c) throws SQLException { ... } /* ORADataFactory interface */ public ORAData create(Datum d, int sqlType) throws SQLException { ... } public Address getValue() throws SQLException { ... } public void setValue(Address c) throws SQLException { ... } }
Following is an example of the source code that JPublisher generates for a custom collection class. Implementation details have been omitted.
import java.sql.SQLException; import java.sql.Connection; import oracle.jdbc.OracleTypes; import oracle.sql.ORAData; import oracle.sql.ORADataFactory; import oracle.sql.Datum; import oracle.sql.ARRAY; import oracle.sql.ArrayDescriptor; import oracle.jpub.runtime.MutableArray; public class ModuletblT implements ORAData, ORADataFactory { public static final String _SQL_NAME = "SCOTT.MODULETBL_T"; public static final int _SQL_TYPECODE = OracleTypes.ARRAY; public static ORADataFactory getORADataFactory() { ... } /* constructors */ public ModuletblT() { ... } public ModuletblT(ModuleT[] a) { ... } /* ORAData interface */ public Datum toDatum(Connection c) throws SQLException { ... } /* ORADataFactory interface */ public ORAData create(Datum d, int sqlType) throws SQLException { ... } public String getBaseTypeName() throws SQLException { ... } public int getBaseType() throws SQLException { ... } public ArrayDescriptor getDescriptor() throws SQLException { ... } /* array accessor methods */ public ModuleT[] getArray() throws SQLException { ... } public void setArray(ModuleT[] a) throws SQLException { ... } public ModuleT[] getArray(long index, int count) throws SQLException { ... } public void setArray(ModuleT[] a, long index) throws SQLException { ... } public ModuleT getObjectElement(long index) throws SQLException { ... } public void setElement(ModuleT a, long index) throws SQLException { ... } }
You might want to enhance the functionality of a custom Java class generated by JPublisher by adding methods and transient fields. You can accomplish this by extending the JPublisher-generated class.
For example, suppose you want JPublisher to generate the class JAddress
from the SQL object type ADDRESS
. You also want to write a class MyAddress
to represent ADDRESS
objects and implement special functionality. The MyAddress
class must extend JAddress
.
Another way to enhance the functionality of a JPublisher-generated class is to simply add methods to it. However, adding methods to the generated class is not recommended if you anticipate running JPublisher at some future time to regenerate the class. If you run JPublisher to regenerate a class that you have modified in this way, you would have to save a copy and then manually merge your changes back in.
As discussed in "Generate Custom Java Classes and Map Alternate Classes", the JPublisher syntax to generate JAddress
but map to MyAddress
is as follows:
-sql=ADDRESS:JAddress:MyAddress
or, in an INPUT
file:
SQL ADDRESS GENERATE JAddress AS MyAddress
As a result of this, JPublisher will generate the reference class MyAddressRef
(in MyAddressRef.java
) rather than JAddressRef
.
In addition, JPublisher alters the code it generates to implement the following functionality:
MyAddress
class, instead of the JAddress
class, is used to represent attributes whose SQL type is ADDRESS
.
MyAddress
class, instead of the JAddress
class, is used to represent method arguments and function results whose type is ADDRESS
.
MyAddress
factory, instead of the JAddress
factory, is used to construct Java objects whose SQL type is ADDRESS
.
You would presumably use MyAddress
similarly in any additional code that you write.
At runtime, the Oracle JDBC driver will map any occurrences of ADDRESS
data in the database to MyAddress
instances, instead of to JAddress
instances.
The class that you create (for example, MyAddress.java
) must have a no-argument constructor. The easiest way to construct a properly initialized object is to invoke the constructor of the superclass, either explicitly or implicitly.
As a result of subclassing the JPublisher-generated class, the subclass will inherit definitions of the _SQL_NAME
field, which it requires, and the _SQL_TYPECODE
field.
In addition, one of the following will be true.
ORAData
and ORADataFactory
interfaces, then the subclass will inherit this implementation and the necessary toDatum()
and create()
functionality of the generated class. You must implement a getORADataFactory()
method that returns an instance of your map class (such as a MyAddress
object).
or:
SQLData
interface, then the subclass will inherit this implementation and the necessary readSQL()
and writeSQL()
functionality of the generated class.
Continuing the example in the preceding sections, here is sample code for the JPublisher-generated class (JAddress
), implementing ORAData
and ORADataFactory
. Implementation details have been omitted.
import java.sql.SQLException; import java.sql.Connection; import oracle.jdbc.OracleTypes; import oracle.sql.ORAData; import oracle.sql.ORADataFactory; import oracle.sql.Datum; import oracle.sql.STRUCT; import oracle.jpub.runtime.MutableStruct; public class JAddress implements ORAData, ORADataFactory { public static final String _SQL_NAME = "SCOTT.ADDRESS"; public static final int _SQL_TYPECODE = OracleTypes.STRUCT; public static ORADataFactory getORADataFactory() { ... } /* constructor */ public JAddress() { ... } /* ORAData interface */ public Datum toDatum(Connection c) throws SQLException { ... } /* ORADataFactory interface */ public ORAData create(Datum d, int sqlType) throws SQLException { ... } /* accessor methods */ public String getStreet() throws SQLException { ... } public void setStreet(String street) throws SQLException { ... } public String getCity() throws SQLException { ... } public void setCity(String city) throws SQLException { ... } public String getState() throws SQLException { ... } public void setState(String state) throws SQLException { ... } public java.math.BigDecimal getZip() throws SQLException { ... } public void setZip(java.math.BigDecimal zip) throws SQLException { ... } }
Continuing the example in the preceding sections, here is sample code for the JPublisher-generated reference class (MyAddressRef
, as opposed to JAddressRef
, because MyAddress
is the class that ADDRESS
objects map to). This class also implements ORAData
and ORADataFactory
. Implementation details have been omitted.
import java.sql.SQLException; import java.sql.Connection; import oracle.jdbc.OracleTypes; import oracle.sql.ORAData; import oracle.sql.ORADataFactory; import oracle.sql.Datum; import oracle.sql.REF; import oracle.sql.STRUCT; public class MyAddressRef implements ORAData, ORADataFactory { public static final String _SQL_BASETYPE = "SCOTT.ADDRESS"; public static final int _SQL_TYPECODE = OracleTypes.REF; public static ORADataFactory getORADataFactory() { ... } /* constructor */ public MyAddressRef() { ... } /* ORAData interface */ public Datum toDatum(Connection c) throws SQLException { ... } /* ORADataFactory interface */ public ORAData create(Datum d, int sqlType) throws SQLException { ... } public MyAddress getValue() throws SQLException { ... } public void setValue(MyAddress c) throws SQLException { ... } }
Continuing the example in the preceding sections, here is sample code for a MyAddress
class that subclasses the JPublisher-generated JAddress
class. The comments in the code show what is inherited from JAddress
. Implementation details have been omitted.
import java.sql.SQLException; import oracle.sql.ORAData; import oracle.sql.ORADataFactory; import oracle.sql.Datum; import oracle.sql.STRUCT; import oracle.jpub.runtime.MutableStruct; public class MyAddress extends JAddress { /* _SQL_NAME inherited from MyAddress */ /* _SQL_TYPECODE inherited from MyAddress */ static _myAddressFactory = new MyAddress(); public static ORADataFactory getORADataFactory() { return _myAddressFactory; } /* constructor */ public MyAddress() { super(); } /* ORAData interface */ /* toDatum() inherited from JAddress */ /* ORADataFactory interface */ public ORAData create(oracle.sql.Datum d, int sqlType) throws SQLException { ... } /* accessor methods inherited from JAddress */ /* Additional methods go here. These additional methods (not shown) are the reason that JAddress was extended. */ }
Oracle SQLJ is flexible in how it allows you to use host expressions and iterators in reading or writing object data through strongly typed objects or references.
For iterators, you can use custom object classes as iterator column types. Alternatively, you can have iterator columns that correspond to individual object attributes (similar to extent tables), using column types that appropriately map to the SQL datatypes of the attributes.
For host expressions, you can use host variables of your custom object class type or custom reference class type. Alternatively, you can use host variables that correspond to object attributes, using variable types that appropriately map to the SQL datatypes of the attributes.
The remainder of this section provides examples of how to manipulate Oracle objects using custom object classes, custom object class attributes, and custom reference classes for host variables and iterator columns in SQLJ executable statements.
The first two examples operate at the object level:
The third example operates at the scalar-attribute level:
The fourth example operates through a reference:
Refer back to the Oracle object types ADDRESS
and PERSON
in "Creating Object Types".
For a complete sample application that includes most of the code in the following examples, see "Oracle Objects--ObjectDemo.sqlj".
This example uses a custom Java class and a custom reference class (ORAData
implementations) as iterator column types.
Presume the following definition of Oracle object type ADDRESS
:
CREATE TYPE ADDRESS AS OBJECT ( street VARCHAR(40), zip NUMBER );
And the following definition of the table EMPADDRS
, which includes an ADDRESS
column and an ADDRESS
reference column:
CREATE TABLE empaddrs ( name VARCHAR(60), home ADDRESS, loc REF ADDRESS );
Once you use JPublisher or otherwise create a custom Java class Address
and custom reference class AddressRef
corresponding to the Oracle object type ADDRESS
, you can use Address
and AddressRef
in a named iterator as follows:
Declaration:
#sql iterator EmpIter (String name, Address home, AddressRef loc);
Executable code:
EmpIter ecur; #sql ecur = { SELECT name, home, loc FROM empaddrs }; while (ecur.next()) { Address homeAddr = ecur.home(); // Print out the home address. System.out.println ("Name: " + ecur.name() + "\n" + "Home address: " + homeAddr.getStreet() + " " + homeAddr.getZip()); // Now update the loc address zip code through the address reference. AddressRef homeRef = ecur.loc(); Address location = homeRef.getValue(); location.setZip(new BigDecimal(98765)); homeRef.setValue(location); } ...
The method call ecur.home()
extracts an Address
object from the home
column of the iterator and assigns it to the local variable homeAddr
(for efficiency). The attributes of that object can then be accessed using standard Java dot syntax:
homeAddr.getStreet()
Use the getValue()
and setValue()
methods, standard with any JPublisher-generated custom reference class, to manipulate the location address (in this case its zip code).
Note: The remaining examples in this section use the types and tables defined in the SQL script in "Creating Object Types". |
This example declares and sets an input host variable of Java type Address
to update an ADDRESS
object in a column of the employees
table. Both before and after the update, the address is selected into an output host variable of type Address
and printed for verification.
... // Updating an object static void updateObject() { Address addr; Address new_addr; int empnum = 1001; try { #sql { SELECT office_addr INTO :addr FROM employees WHERE empnumber = :empnum }; System.out.println("Current office address of employee 1001:"); printAddressDetails(addr); /* Now update the street of address */ String street ="100 Oracle Parkway"; addr.setStreet(street); /* Put updated object back into the database */ try { #sql { UPDATE employees SET office_addr = :addr WHERE empnumber = :empnum }; System.out.println ("Updated employee 1001 to new address at Oracle Parkway."); /* Select new address to verify update */ try { #sql { SELECT office_addr INTO :new_addr FROM employees WHERE empnumber = :empnum }; System.out.println("New office address of employee 1001:"); printAddressDetails(new_addr); } catch (SQLException exn) { System.out.println("Verification SELECT failed with "+exn); } } catch (SQLException exn) { System.out.println("UPDATE failed with "+exn); } } catch (SQLException exn) { System.out.println("SELECT failed with "+exn); } } ...
Note the use of the setStreet()
accessor method of the Address
object. Remember that JPublisher provides such accessor methods for all attributes in any custom Java class that it produces.
This example uses the printAddressDetails()
utility. For the source code of this method, see "Oracle Objects--ObjectDemo.sqlj".
This example declares and sets input host variables corresponding to attributes of PERSON
and nested ADDRESS
objects, then uses these values to insert a new PERSON
object into the persons
table in the database.
... // Inserting an object static void insertObject() { String new_name = "NEW PERSON"; int new_ssn = 987654; String new_street = "NEW STREET"; String new_city = "NEW CITY"; String new_state = "NS"; String new_zip = "NZIP"; /* * Insert a new PERSON object into the persons table */ try { #sql { INSERT INTO persons VALUES (PERSON(:new_name, :new_ssn, ADDRESS(:new_street, :new_city, :new_state, :new_zip))) }; System.out.println("Inserted PERSON object NEW PERSON."); } catch (SQLException exn) { System.out.println("INSERT failed with "+exn); } } ...
This example selects a PERSON
reference from the persons
table and uses it to update a PERSON
reference in the employees
table. It uses simple (int
and String
) input host variables to check attribute value criteria. The newly updated reference is then used in selecting the PERSON
object to which it refers, so that information can be output to the user to verify the change.
... // Updating a REF to an object static void updateRef() { int empnum = 1001; String new_manager = "NEW PERSON"; System.out.println("Updating manager REF."); try { #sql { UPDATE employees SET manager = (SELECT REF(p) FROM persons p WHERE p.name = :new_manager) WHERE empnumber = :empnum }; System.out.println("Updated manager of employee 1001. Selecting back"); } catch (SQLException exn) { System.out.println("UPDATE REF failed with "+exn); } /* Select manager back to verify the update */ Person manager; try { #sql { SELECT deref(manager) INTO :manager FROM employees e WHERE empnumber = :empnum }; System.out.println("Current manager of "+empnum+":"); printPersonDetails(manager); } catch (SQLException exn) { System.out.println("SELECT REF failed with "+exn); } } ...
As with strongly typed objects and references, Oracle SQLJ supports different scenarios for reading and writing data through strongly typed collections, using either iterators or host expressions.
From the perspective of a SQLJ developer, both categories of collections--VARRAY and nested table--are treated essentially the same, but there are some differences in implementation and performance.
Oracle SQLJ, and Oracle SQL in general, support syntax choices so that nested tables can be accessed and manipulated either apart from or together with their outer tables. In this section, manipulation of a nested table by itself will be referred to as detail-level manipulation; manipulation of a nested table together with its outer table will be referred to as master-level manipulation.
Most of this section, after a brief discussion of some syntax, focuses on examples of manipulating nested tables, given that their use is somewhat more complicated than that of VARRAYs.
Refer back to the Oracle collection type MODULETBL_T
and related tables and object types defined in "Creating Collection Types".
For complete nested table sample applications, including one that incorporates the sample code below, see "Oracle Nested Tables--NestedDemo1.sqlj and NestedDemo2.sqlj".
Following the nested table discussion are some brief VARRAY examples. There are also complete VARRAY sample applications, including one that incorporates this code, in "Oracle VARRAYs--VarrayDemo1.sqlj and VarrayDemo2.sqlj".
Oracle SQLJ supports the use of nested iterators to access data in nested tables. Use the CURSOR
keyword in the outer SELECT
statement to encapsulate the inner SELECT
statement. This is shown in "Selecting Data from a Nested Table Using a Nested Iterator".
Oracle SQLJ also supports use of the TABLE
keyword to manipulate the individual rows of a nested table. This keyword informs Oracle that the column value returned by a subquery is a nested table, as opposed to a scalar value. You must prefix the TABLE
keyword to a subquery that returns a single column value or an expression that yields a nested table.
The following example shows the use of TABLE
syntax:
UPDATE TABLE(SELECT a.modules FROM projects a WHERE a.id=555) b SET module_owner= (SELECT ref(p) FROM employees p WHERE p.ename= 'Smith') WHERE b.module_name = 'Zebra';
When you see TABLE
used as it is here, realize that it is referring to a single nested table that has been selected from a column of an outer table.
This example shows an operation that manipulates the master level (outer table) and detail level (nested tables) simultaneously and explicitly. This inserts a row in the projects
table, where each row includes a nested table of type MODULETBL_T
, which contains rows of MODULE_T
objects.
First, the scalar values are set (id
, name
, start_date
, duration
), then the nested table values are set. This involves an extra level of abstraction, because the nested table elements are objects with multiple attributes. In setting the nested table values, each attribute value must be set for each MODULE_T
object in the nested table. Finally, the owner
values, initially set to null
, are set in a separate statement.
// Insert Nested table details along with master details public static void insertProject2(int id) throws Exception { System.out.println("Inserting Project with Nested Table details.."); try { #sql { INSERT INTO Projects(id,name,owner,start_date,duration, modules) VALUES ( 600, 'Ruby', null, '10-MAY-98', 300, moduletbl_t(module_t(6001, 'Setup ', null, '01-JAN-98', 100), module_t(6002, 'BenchMark', null, '05-FEB-98',20) , module_t(6003, 'Purchase', null, '15-MAR-98', 50), module_t(6004, 'Install', null, '15-MAR-98',44), module_t(6005, 'Launch', null,'12-MAY-98',34))) }; } catch ( Exception e) { System.out.println("Error:insertProject2"); e.printStackTrace(); } // Assign project owner to this project try { #sql { UPDATE Projects pr SET owner=(SELECT ref(pa) FROM participants pa WHERE pa.empno = 7698) WHERE pr.id=600 }; } catch ( Exception e) { System.out.println("Error:insertProject2:update"); e.printStackTrace(); } }
This example presents an operation that works directly at the detail level of the nested table. Recall that ModuletblT
is a JPublisher-generated custom collection class (ORAData
implementation) for MODULETBL_T
nested tables, ModuleT
is a JPublisher-generated custom object class for MODULE_T
objects, and MODULETBL_T
nested tables contain MODULE_T
objects.
A nested table of MODULE_T
objects is selected from the modules
column of the projects
table into a ModuletblT
host variable.
Following that, the ModuletblT
variable (containing the nested table) is passed to a method that accesses its elements through its getArray()
method, writing the data to a ModuleT[]
array. (All custom collection classes generated by JPublisher include a getArray()
method.) Then each element is copied from the ModuleT[]
array into a ModuleT
object, and individual attributes are retrieved through accessor methods (getModuleName()
, for example) and then printed. (All JPublisher-generated custom object classes include such accessor methods.)
static ModuletblT mymodules=null; ... public static void getModules2(int projId) throws Exception { System.out.println("Display modules for project " + projId ); try { #sql {SELECT modules INTO :mymodules FROM projects WHERE id=:projId }; showArray(mymodules); } catch(Exception e) { System.out.println("Error:getModules2"); e.printStackTrace(); } } public static void showArray(ModuletblT a) { try { if ( a == null ) System.out.println( "The array is null" ); else { System.out.println( "printing ModuleTable array object of size " +a.length()); ModuleT[] modules = a.getArray(); for (int i=0;i<modules.length; i++) { ModuleT module = modules[i]; System.out.println("module "+module.getModuleId()+ ", "+module.getModuleName()+ ", "+module.getModuleStartDate()+ ", "+module.getModuleDuration()); } } } catch( Exception e ) { System.out.println("Show Array"); e.printStackTrace(); } }
This example uses TABLE
syntax to work at the detail level to access and update nested table elements directly, based on master-level criteria.
The assignModule()
method selects a nested table of MODULE_T
objects from the MODULES
column of the PROJECTS
table, then updates MODULE_NAME
for a particular row of the nested table.
Similarly, the deleteUnownedModules()
method selects a nested table of MODULE_T
objects, then deletes any unowned modules in the nested table (where MODULE_OWNER
is null
).
These methods use table alias syntax, as discussed previously--in this case, m
for the nested table and p
for the participants
table. See the Oracle9i SQL Reference for more information about table aliases.
/* assignModule // Illustrates accessing the nested table using the TABLE construct // and updating the nested table row */ public static void assignModule(int projId, String moduleName, String modOwner) throws Exception { System.out.println("Update:Assign '"+moduleName+"' to '"+ modOwner+"'"); try { #sql {UPDATE TABLE(SELECT modules FROM projects WHERE id=:projId) m SET m.module_owner= (SELECT ref(p) FROM participants p WHERE p.ename= :modOwner) WHERE m.module_name = :moduleName }; } catch(Exception e) { System.out.println("Error:insertModules"); e.printStackTrace(); } } /* deleteUnownedModules // Demonstrates deletion of the Nested table element */ public static void deleteUnownedModules(int projId) throws Exception { System.out.println("Deleting Unowned Modules for Project " + projId); try { #sql { DELETE TABLE(SELECT modules FROM projects WHERE id=:projId) m WHERE m.module_owner IS NULL }; } catch(Exception e) { System.out.println("Error:deleteUnownedModules"); e.printStackTrace(); } }
SQLJ supports the use of nested iterators as a way of accessing nested tables. This requires CURSOR
syntax, as used in the example below.
The code defines a named iterator class ModuleIter
, then uses that class as the type for a modules
column in another named iterator class ProjIter
. Inside a populated ProjIter
instance, each modules
item is a nested table rendered as a nested iterator.
The CURSOR
syntax is part of the nested SELECT
statement that populates the nested iterators.
Once the data has been selected, it is output to the user through the iterator accessor methods.
This example uses required table alias syntax, as discussed previously--in this case, a
for the projects
table and b
for the nested table. See the Oracle9i SQL Reference for more information about table aliases.
... // The Nested Table is accessed using the ModuleIter // The ModuleIter is defined as Named Iterator #sql public static iterator ModuleIter(int moduleId , String moduleName , String moduleOwner); // Get the Project Details using the ProjIter defined as // Named Iterator. Notice the use of ModuleIter below: #sql public static iterator ProjIter(int id, String name, String owner, Date start_date, ModuleIter modules); ... public static void listAllProjects() throws SQLException { System.out.println("Listing projects..."); // Instantiate and initialize the iterators ProjIter projs = null; ModuleIter mods = null; #sql projs = {SELECT a.id, a.name, initcap(a.owner.ename) as "owner", a.start_date, CURSOR ( SELECT b.module_id AS "moduleId", b.module_name AS "moduleName", initcap(b.module_owner.ename) AS "moduleOwner" FROM TABLE(a.modules) b) AS "modules" FROM projects a }; // Display Project Details while (projs.next()) { System.out.println( "\n'" + projs.name() + "' Project Id:" + projs.id() + " is owned by " +"'"+ projs.owner() +"'" + " start on " + projs.start_date()); // Notice below the modules from the ProjIter are assigned to the module // iterator variable mods = projs.modules(); System.out.println ("Modules in this Project are : "); // Display Module details while(mods.next()) { System.out.println (" "+ mods.moduleId() + " '"+ mods.moduleName() + "' owner is '" + mods.moduleOwner()+"'" ); } // end of modules mods.close(); } // end of projects projs.close(); }
This section provides an example of selecting a VARRAY into a host expression. Presume the following SQL definitions:
CREATE TYPE PHONE_ARRAY IS VARRAY (10) OF varchar2(30) / /*** Create ADDRESS UDT ***/ CREATE TYPE ADDRESS AS OBJECT ( street VARCHAR(60), city VARCHAR(30), state CHAR(2), zip_code CHAR(5) ) / /*** Create PERSON UDT containing an embedded ADDRESS UDT ***/ CREATE TYPE PERSON AS OBJECT ( name VARCHAR(30), ssn NUMBER, addr ADDRESS ) / CREATE TABLE employees ( empnumber INTEGER PRIMARY KEY, person_data REF person, manager REF person, office_addr address, salary NUMBER, phone_nums phone_array ) /
And presume that JPublisher is used to create a custom collection class PhoneArray
to map from the PHONE_ARRAY
SQL type.
The following method selects a row from this table, placing the data into a host variable of the PhoneArray
type.
private static void selectVarray() throws SQLException { PhoneArray ph; #sql {select phone_nums into :ph from employees where empnumber=2001}; System.out.println( "there are "+ph.length()+" phone numbers in the PhoneArray. They are:"); String [] pharr = ph.getArray(); for (int i=0;i<pharr.length;++i) System.out.println(pharr[i]); }
This section provides an example of inserting data from a host expression into a VARRAY, using the same SQL definitions and custom collection class (PhoneArray
) as in the previous section.
The following methods populate a PhoneArray
instance and use it as a host variable, inserting its data into a VARRAY in the database.
// creates a varray object of PhoneArray and inserts it into a new row private static void insertVarray() throws SQLException { PhoneArray phForInsert = consUpPhoneArray(); // clean up from previous demo runs #sql {delete from employees where empnumber=2001}; // insert the PhoneArray object #sql {insert into employees (empnumber, phone_nums) values(2001, :phForInsert)}; } private static PhoneArray consUpPhoneArray() { String [] strarr = new String[3]; strarr[0] = "(510) 555.1111"; strarr[1] = "(617) 555.2222"; strarr[2] = "(650) 555.3333"; return new PhoneArray(strarr); }
When writing and reading instances of Java objects to or from the database, it is sometimes advantageous to define a SQL object type that corresponds to your Java class, and use the mechanisms of mapping custom Java classes described previously. This fully permits SQL queries on your Java objects.
In some cases, however, you may want to store Java objects "as-is" and retrieve them later, using database columns of type RAW
or BLOB
. There are different ways to accomplish this:
RAW
or BLOB
columns by using a non-standard extension to the type map facility, or by adding a typecode field to the serializable class, so that instances of the serializable class can be stored as RAW
or BLOB
.
ORAData
facility to define a serializable wrapper class whose instances can be stored in RAW
or BLOB
columns.
Serializing in any of these ways works for any Oracle SQLJ runtime library except runtime-nonoracle
.
If you want to store instances of Java classes directly in RAW
or BLOB
columns, then you must meet certain non-standard requirements to specify the desired SQL-Java mapping. (Note that in SQLJ statements the serializable Java objects can be transparently read and written as if they were built-in types.)
You have two options in specifying the SQL-Java type mapping:
_SQL_TYPECODE
to specify the mapping.
The rest of this section describes each of these options.
Consider an example where SAddress
, pack.SPerson
, and pack.Manager.InnerSPM
(where InnerSPM
is an inner class of Manager
) are serializable Java classes. In other words, these classes implement the java.io.Serializable
interface.
You must employ the classes only in statements that use explicit connection context instances of a declared connection context type, such as SerContext
in the following example:
SAddress a =...; pack.SPerson p =...; pack.Manager.InnerSPM pm =...; SerContext ctx = new SerContext(url,user,pwd,false); #sql [ctx] { ... :a ... :OUT p ... :INOUT pm ... };
The following is required:
typeMap
attribute of a with
clause to specify an associated class implementing a java.util.PropertyResourceBundle
. In our example, SerContext
might have been declared as follows.
#sql public static context SerContext with (typeMap="SerMap");
RAW
or BLOB
columns to the serializable Java classes. This mapping is specified with entries of the following form, depending on whether the Java class is mapped to a RAW
or a BLOB
column:
oracle-class.<java_class_name>=JAVA_OBJECT RAW oracle-class.<java_class_name>=JAVA_OBJECT BLOB
The keyword oracle-class
marks this as an Oracle-specific extension. In our example, the resource file SerMap.properties
might contain the following entries:
oracle-class.SAddress=JAVA_OBJECT RAW oracle-class.pack.SPerson=JAVA_OBJECT BLOB oracle-class.packManager$InnerSPM=JAVA_OBJECT RAW
(Although ".
" separates package and class names, you must use the character "$
" to separate an inner class name.)
Note that this Oracle-specific extension can be placed in the same type map resource as standard SQLData
type map entries.
As an alternative to using a type map for a serializable class, you can use static fields in the serializable class to determine type mapping.
You can add either of the following fields to a class that implements the java.io.Serializable
interface, such as the SAddress
and SPerson
classes from the example in "Defining a Type Map for Serializable Classes" above.
public final static int _SQL_TYPECODE = oracle.jdbc.OracleTypes.RAW;
or:
public final static int _SQL_TYPECODE = oracle.jdbc.OracleTypes.BLOB;
You should be aware of the effect of serialization. If two objects, A and B, share the same object, C, then upon serialization and subsequent deserialization of A and B, each will point to its own clone of the object C. Sharing is broken.
In addition, note that for a given Java class, you can declare only one kind of serialization: either into RAW
or into BLOB
. The SQLJ translator can check only that the actual usage conforms to either RAW
or BLOB
.
RAW
columns are limited in size--you may experience runtime errors if the actual size of the serialized Java object exceeds the size of the column.
Although column size is much less restrictive for BLOB
columns, writing a serialized Java object to a BLOB
column is currently supported only in the JDBC OCI driver. On the other hand, retrieving a serialized object from a BLOB
column is supported by all Oracle JDBC drivers.
Finally, treating serialized Java objects this way is an Oracle-specific extension and requires the Oracle SQLJ runtime as well as either Oracle-specific profile customization (with standard SQLJ code generation) or Oracle-specific code generation (specified with -codegen=oracle
during translation). Note that future versions of Oracle may support SQL types that directly encapsulate Java serialized objects -- these are described as JAVA_OBJECT
SQL types in JDBC 2.0. At that point, you can replace each of the BLOB
and RAW
designations by the names of their corresponding JAVA_OBJECT
SQL types, and you can drop the oracle-
prefix on the entries.
"Additional Uses for ORAData Implementations" includes examples of situations where you might want to define a custom Java class that maps to some oracle.sql.*
type other than the oracle.sql.STRUCT
, oracle.sql.REF
, or oracle.sql.ARRAY
type.
An example of such a situation is if you want to serialize and deserialize Java objects into and out of RAW
fields, with a custom Java class that maps to the oracle.sql.RAW
type. (This could apply equally to BLOB
fields, with a custom Java class that maps to the oracle.sql.BLOB
type.)
This section presents an example of this, creating a class SerializableDatum
that implements the ORAData
interface and follows the general form of custom Java classes, as described in "Custom Java Classes".
The example starts with a step-by-step approach to the development of SerializableDatum
, followed by the complete sample code.
public class SerializableDatum implements ORAData { // <Client methods for constructing and accessing the Java object> public Datum toDatum(java.sql.Connection c) throws SQLException { // <Implementation of toDatum()> } public static ORADataFactory getORADataFactory() { return FACTORY; } private static final ORADataFactory FACTORY = // <Implementation of an ORADataFactory for SerializableDatum> // <Construction of SerializableDatum from oracle.sql.RAW> public static final int _SQL_TYPECODE = OracleTypes.RAW; }
SerializableDatum
does not implement the ORADataFactory
interface, but its getORADataFactory()
method returns a static member that implements this interface.
The _SQL_TYPECODE
is set to OracleTypes.RAW
because this is the datatype being read from and written to the database. The SQLJ translator needs this typecode information in performing online type-checking to verify compatibility between the user-defined Java type and the SQL type.
SerializableDatum
object.
SerializableDatum
object.
SerializableDatum
object.
// Client methods for constructing and accessing a SerializableDatum private Object m_data; public SerializableDatum() { m_data = null; } public void setData(Object data) { m_data = data; } public Object getData() { return m_data; }
toDatum()
method that serializes data from a SerializableDatum
object to an oracle.sql.RAW
object. The implementation of toDatum()
must return a serialized representation of the object in the m_data
field as an oracle.sql.RAW
instance.
// Implementation of toDatum() try { ByteArrayOutputStream os = new ByteArrayOutputStream(); ObjectOutputStream oos = new ObjectOutputStream(os); oos.writeObject(m_data); oos.close(); return new RAW(os.toByteArray()); } catch (Exception e) { throw new SQLException("SerializableDatum.toDatum: "+e.toString()); }
oracle.sql.RAW
object to a SerializableDatum
object. This step deserializes the data.
// Constructing SerializableDatum from oracle.sql.RAW private SerializableDatum(RAW raw) throws SQLException { try { InputStream rawStream = new ByteArrayInputStream(raw.getBytes()); ObjectInputStream is = new ObjectInputStream(rawStream); m_data = is.readObject(); is.close(); } catch (Exception e) { throw new SQLException("SerializableDatum.create: "+e.toString()); } }
ORADataFactory
. In this case, it is implemented as an anonymous class.
// Implementation of an ORADataFactory for SerializableDatum new ORADataFactory() { public ORAData create(Datum d, int sqlCode) throws SQLException { if (sqlCode != _SQL_TYPECODE) { throw new SQLException ("SerializableDatum: invalid SQL type "+sqlCode); } return (d==null) ? null : new SerializableDatum((RAW)d); } };
Given the SerializableDatum
class created in the preceding section, this section shows how to use an instance of it in a SQLJ application, both as a host variable and as an iterator column.
Presume the following table definition:
CREATE TABLE PERSONDATA (NAME VARCHAR2(20) NOT NULL, INFO RAW(2000));
The following uses a SerializableDatum
instance as a host variable.
... SerializableDatum pinfo = new SerializableDatum(); pinfo.setData ( new Object[] {"Some objects", new Integer(51), new Double(1234.27) } ); String pname = "MILLER"; #sql { INSERT INTO persondata VALUES(:pname, :pinfo) }; ...
Here is an example of using SerializableDatum
as a named iterator column.
Declaration:
#sql iterator PersonIter (SerializableDatum info, String name);
Executable code:
PersonIter pcur; #sql pcur = { SELECT * FROM persondata WHERE info IS NOT NULL }; while (pcur.next()) { System.out.println("Name:" + pcur.name() + " Info:" + pcur.info()); } pcur.close(); ...
This section shows you the entire SerializableDatum
class previously developed in step-by-step fashion.
import java.io.*; import java.sql.*; import oracle.sql.*; import oracle.jdbc.*; public class SerializableDatum implements ORAData { // Client methods for constructing and accessing a SerializableDatum private Object m_data; public SerializableDatum() { m_data = null; } public void setData(Object data) { m_data = data; } public Object getData() { return m_data; } // Implementation of toDatum() public Datum toDatum(Connection c) throws SQLException { try { ByteArrayOutputStream os = new ByteArrayOutputStream(); ObjectOutputStream oos = new ObjectOutputStream(os); oos.writeObject(m_data); oos.close(); return new RAW(os.toByteArray()); } catch (Exception e) { throw new SQLException("SerializableDatum.toDatum: "+e.toString()); } } public static ORADataFactory getORADataFactory() { return FACTORY; } // Implementation of an ORADataFactory for SerializableDatum private static final ORADataFactory FACTORY = new ORADataFactory() { public ORAData create(Datum d, int sqlCode) throws SQLException { if (sqlCode != _SQL_TYPECODE) { throw new SQLException( "SerializableDatum: invalid SQL type "+sqlCode); } return (d==null) ? null : new SerializableDatum((RAW)d); } }; // Constructing SerializableDatum from oracle.sql.RAW private SerializableDatum(RAW raw) throws SQLException { try { InputStream rawStream = new ByteArrayInputStream(raw.getBytes()); ObjectInputStream is = new ObjectInputStream(rawStream); m_data = is.readObject(); is.close(); } catch (Exception e) { throw new SQLException("SerializableDatum.create: "+e.toString()); } } public static final int _SQL_TYPECODE = OracleTypes.RAW; }
Weakly typed objects, references, and collections are supported by SQLJ. Their use is not generally recommended, and there are some specific restrictions, but in some circumstances they can be useful. For example, you might have generic code that can use "any STRUCT
" or "any REF
".
In using Oracle objects, references, or collections in a SQLJ application, you have the option of using generic and weakly typed java.sql
or oracle.sql
instances instead of the strongly typed custom object, reference, and collection classes that implement the ORAData
interface or the strongly typed custom object classes that implement the SQLData
interface. (Note that if you use SQLData
implementations for your custom object classes, you will have no choice but to use weakly typed custom reference instances.)
The following weak types can be used for iterator columns or host expressions in Oracle SQLJ:
java.sql.Struct
or oracle.sql.STRUCT
for objects
java.sql.Ref
or oracle.sql.REF
for object references
java.sql.Array
or oracle.sql.ARRAY
for collections
In host expressions, they are supported as follows:
Using these weak types is not generally recommended, however, as you would lose all the advantages of the strongly typed paradigm that SQLJ offers.
Each attribute in a STRUCT
object or each element in an ARRAY
object is stored in an oracle.sql.Datum
object, with the underlying data being in the form of the appropriate oracle.sql.*
type (such as oracle.sql.NUMBER
or oracle.sql.CHAR
). Attributes in a STRUCT
object are nameless.
Because of the generic nature of the STRUCT
and ARRAY
classes, SQLJ cannot perform type checking where objects or collections are written to or read from instances of these classes.
It is generally recommended that you use custom Java classes for objects, references, and collections, preferably classes generated by JPublisher.
A weakly typed object (Struct
or STRUCT
instance), reference (Ref
or REF
instance), or collection (Array
or ARRAY
instance) cannot be used in host expressions in the following circumstances:
IN
parameter if null
OUT
or INOUT
parameter in a stored procedure or function call
OUT
parameter in a stored function result expression
They cannot be used in these ways because there is no way to know the underlying SQL type name (such as Person
), which is required by the Oracle JDBC driver to materialize an instance of a user-defined type in Java.
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|