This chapter does not purport to provide exhaustive coverage of JPublisher. Rather, it will introduce the rationale for using JPublisher, the utility, its features, its environment, the options, the mapping categories or possibilities, and some examples.
I will use a PL/SQL RECORD
type that cannot be mapped directly to or from Java. Assume you want to map the following MYPKG
PL/SQL package, which contains a procedure that takes a RECORD
as IN OUT
parameter, into a Java class Mypkg
:
create or replace package mypkg as type studrec is record (name varchar2(10), age number); procedure proc1 (enrold timestamp, stud in out studrec); end; /
Assume also that you want to map SQL TIMESTAMP
to java.util.Date
; the signature of the corresponding Java proc1 method would be as follows:
public void proc1(java.util.Date enrold, StudRecSql[] stud_inout);
Without JPublisher, here are the steps you would manually follow to perform the PL/SQL-to-Java mapping for the MYPKG package:
Create a SQL object type, STUDREC_SQL
, for the PL/SQL RECORD
type, STUDREC
. Remember, an intermediate wrapper must be used to map a RECORD
to a SQL type that JDBC supports.
Create a Java type for the SQL type you created (e.g., create the Java type StudRecSql
for the SQL type STUDREC_SQL
).
In the Java code, use Java Date
for SQL TIMESTAMP
.
Pass IN
or IN OUT
arguments (i.e., StudRecSql and TimeStamp
to proc1
via JDBC).
In the PL/SQL block, convert STUDREC_SQL
to STUDREC
.
Call the PL/SQL stored procedure.
In PL/SQL, convert OUT, IN OUT
arguments, or function result, from unsupported JDBC types to the corresponding supported types (i.e., convert STUDREC to STUDREC_SQL
).
Return each OUT
, IN OUT
arguments, or function result, from the PL/SQL block.
In Java, convert each OUT, IN OUT
argument, or function result, from supported JDBC types (TimeStamp
) to the unsupported types (java.util.Date
).
Using JPublisher, the following command directly “publishes” the MYPKG
package:
$ jpub -u scott/tiger -s mypkg:Mypkg -style=webservice10 -outarguments=array -plsqlfile=mypkgWrap.sql SCOTT.MYPKG SCOTT."MYPKG.STUDREC" J2T-138, NOTE: Wrote PL/SQL package JPUB_PLSQL_WRAPPER to file mypkgWrap.sql. Wrote the dropping script to file mypkgWrap_drop.sql
Notice the use of –style
, -outarguments
, and –plsqlfile
options, described later.
$ ls Mypkg.class Mypkg.java MypkgStudrec.class MypkgStudrec.java MypkgStudrecRef.class MypkgStudrecRef.java mypkgWrap.sql mypkgWrap_drop.sql $
Here is the description of the files generated by JPublisher:
Mypkg.java:
The Java base class generated for the PL/SQL package MYPKG
MypkgBase.java:
The Java user subclass extended from MypkgBase
MypkgUser.java:
The Java classes extended from MypkgBase
, for mapping TimeStamp
to java.util.Date
MypkgStudrec.java:
The Java interface generated for STUDREC_SQL
MypkgStudrecRef.java
: The Java type for the REF type of STUDREC_SQL
MypkgWrap_drop.sql
: Cleans up type and the package
MypkgWrap.sql
: Contains the definition of the SQL type STUDREC_SQL
for the PL/SQL STUDREC
type and conversion functions between STUDREC
and STUDREC_SQL
. It also contains a wrapper for the PL/SQL stored procedure proc1
. It must be executed before using the generated classes, as follows:
$ sqlplus scott/tiger @mypkgWrap.sql
Isn’t that cool? Even power developers will benefit from JPublisher. Now that I have your attention, let’s look deeper into JPublisher, starting with the overview.
Oracle JPublisher is a utility for generating a Java wrapper, which implements the SQLData
or ORAData/ORADataFactory
interfaces (i.e., strongly typed custom mapping) to represent the following database entities: user-defined SQL object types, object references, user-defined SQL collections, OPAQUE types, XMLType, PL/SQL Boolean, PL/SQL records, PL/SQL associative arrays, PL/SQL package, Java in the database, SQL queries, SQL DML, and streams AQ. In addition, as support to database Web services (covered in the next chapter), JPublisher also wraps PL/SQL packages, Java classes in the database, SQL queries, SQL data manipulation language (DML) statements, streams/AQ, and external Web services.
JPublisher furnishes the following features in Oracle Database 9i Release 2 (9.2):
Supports PL/SQL RECORD
and INDEX-BY TABLE
Publishes server-side Java class to PL/SQL
Wraps external Web services for SQL, Java, and PL/SQL consumption
Client-side stub for server-side Java invocation (also known as Native Java Interface (-java)
)
Generates PL/SQL Wrapper (Call Spec) for Java in the Database (-dbjava
)
Accesses SQLJ functionality (-sqlj
)
Publishes SQL Queries and DMLs to Java
JPublisher furnishes the following features in Oracle Database 10g Release 1:
The value of the CLASSPATH
environment variable is appended to the classpath-value that is provided through the JPublisher command line.
Implicit SQLJ translation and Java compilation. By default, in Oracle Database 10g, if SQLJ code is generated, it is automatically compiled into a Java class and then deleted (i.e., not retained). However, you have the option to instruct JPublisher to retain the intermediate SQLJ file.
Generation of SQLJ runtime free code that only requires the Oracle JDBC drivers.
Generation of Java interfaces.
Style files for Java-to-Java type mappings.
REF CURSOR
returning and result set mapping.
Filtering what JPublisher publishes.
JPublisher furnishes the following features in Oracle Database 10g Release 2:
Support for JDBC types when calling server-side Java
Support for publishing Oracle Streams Advanced Queue (AQ) as Web services
Support for complex types in Web services call-outs
JPublisher and SQLJ can both be installed using the following steps from the Oracle Database 10g Client (from the client CD or downloaded from OTN):
Insert the Oracle Database 10g Client CD or download and unzip the Oracle Database 10g Client.
Start the installer via the setup.exe
.
Choose “Custom Install
.”
Accept or change the ORACLE_HOME
.
Select ORACLE SQLJ
and proceed with the installation.
This section describes the environment requirements for JPublisher. Assume you have already installed an Oracle Database 10g or earlier; in order to use JPublisher, the following components must be installed:
The Java Developer’s Kit (JDK) release 1.2 or higher (i.e., JDK 1.3, and 1.4).
The Oracle JDBC drivers either downloaded from the Oracle Technology Network (OTN) or from the Oracle Database 10g client distribution: classes12.jar
for JDK 1.2.x /1.3.x or ojdbc14.jar
for JDK 1.4.x, usually installed in ORACLE_HOME
/jdbc/lib
. The JPublisher runtime (i.e., oracle.jpub.runtime
package) is included with the JDBC libraries.
The SQLJ translator and runtime, used by JPublisher: translator.j
ar and runtime12.jar
, typically located in in ORACLE_HOME
/sqlj/lib
.
The SQLJUTL
package installed in the database, to support PL/SQL types.
The SQLJUTL2
package installed in the database, to support the invocation of server-side Java classes (described later).
The JPublisher script (jpub
) for UNIX/LINUX environments or JPublisher executable (jpub.exe
) for Microsoft Windows environments are typically located in $ORACLE_HOME/bin
or ORACLE_HOME/sqlj/bin
. It is invoked as follows:
$ jpub JPub: Java Object Type Publisher, version 10.2.0.0.0 Production Copyright (c) 1997, 2004, Oracle Corporation. All Rights Reserved. JPub generates Java or SQLJ source code for the following SQL entities: object types, collection types, and packages. Invocation: jpub <options> The following option is required: -user=<username>/<password> Other options are: -input=<input file> Types and packages to be processed by JPub may be listed in the -input file The contents of the -input file may be as simple as: SQL Person SQL Employee -sql=<sql entity list> Types and packages to be processed by JPub may also be listed using the -sql option For example, -sql=a,b:c,d:e:f is equivalent to the -input file entries: SQL a SQL b AS c SQL d GENERATE e AS f ... ...
Other options have been omitted; see Table 13.1 for a complete list of JPublisher options.
Table 13.1. JPublisher Command-Line Options Summary
Option Name | Description | Default Value | Category |
---|---|---|---|
| Determines the access modifiers that JPublisher includes in generated method definitions. |
|
|
| Appends an entry to the JPublisher default type-map. |
|
|
| Appends an entry to the JPublisher user type-map. |
|
|
| Specifies the data type mappings (jdbc or oracle), for built-in data types that are not numeric or large object (LOB). |
|
|
| Specifies the case of Java identifiers that JPublisher generates. |
|
|
| Adds to the Java CLASSPATH for JPublisher to resolve Java source and classes during translation and compilation. |
|
|
| Specifies a compatibility mode and modifies the behavior of - usertypes=oracle. |
|
|
| Determines whether to proceed with Java compilation or suppress it. This option also affects SQLJ translation for backward-compatibility modes. |
|
|
| Specifies a Java compiler version, in case you want a version other than the default. |
|
|
Specifies the class that JPublisher uses for SQLJ connection contexts. This can be the DefaultContext class, a user-specified class, or a JPublisher-generated inner class. |
|
| |
| Sets the default type-map that JPublisher uses. |
| |
| Specifies the root directory for placement of compiled class files. |
|
|
| Specifies the root directory for placement of generated source files. |
|
|
| Specifies the driver class that JPublisher uses for Java Database Connectivity (JDBC) connections to the database. |
|
|
| Specifies the Java encoding of JPublisher input and output files. |
|
|
| Specifies a Web service endpoint. This option is used in conjunction with the -proxywsdl option. |
|
|
| Filters code generation according to specified parameter modes. |
|
|
| Filters code generation according to specified parameter types. |
|
|
| Ensures that generated code conforms to the JavaBeans specification. |
|
|
Defines naming patterns for generated code. |
|
| |
| Specifies whether and how to generate stub code for user subclasses. |
|
|
| Specifies a proxy URL to resolve the URL of a Web Services Description Language (WSDL) document for access through a firewall. This option is used in conjunction with the -proxywsdl option. |
|
|
| Specifies a file that lists the types and packages that JPublisher translates. |
|
|
| Specifies server-side Java classes for which JPublisher generates client-side classes. |
|
|
| Specifies the data type mapping (jdbc or oracle) that JPublisher uses for BLOB and CLOB types. |
|
|
| Specifies the mapping that generated methods support for object attribute types and method argument types. Note: This option is deprecated in favor of the “XXXtypes” mapping options, but is supported for backward compatibility. |
|
|
Determines whether JPublisher generates wrapper methods for stored procedures of translated SQL objects and PL/SQL packages. This option also determines whether JPublisher generates SQLJ classes or non-SQLJ classes, and whether it generates PL/SQL wrapper classes at all. There are settings to specify whether overloaded methods are allowed. |
|
| |
| Specifies the data type mappings, such as jdbc, objectjdbc, bigdecimal, or oracle, that JPublisher uses for numeric data types. |
|
|
| Instructs JPublisher not to include the schema in SQL type name references in generated code. |
|
|
| Specifies the holder type, such as arrays, Java API for XML-based Remote Procedure Call (JAXRPC) holders, or function returns, for Java implementation of PL/SQL output parameters. |
|
|
| Specifies whether to ignore naming conflicts when creating SQL types. |
|
|
| Specifies the name of the Java package into which JPublisher generates Java wrapper classes. |
|
|
| Specifies a wrapper script to create and a dropper script to drop SQL conversion types for PL/SQL types and the PL/SQL package that JPublisher will use for generated PL/SQL code. |
|
|
Specifies whether to generate PL/SQL wrapper functions for stored procedures that use PL/SQL types. |
|
| |
| Specifies the PL/SQL package into which JPublisher generates PL/SQL code, such as call specifications, conversion functions, and wrapper functions. |
|
|
| Specifies a file that contains JPublisher options in addition to those listed on the command line. |
|
|
| Specifies Java classes for which JPublisher generates wrapper classes and PL/SQL wrappers according to the -proxyopts setting. For Web services, you will typically use -proxywsdl instead, which uses -proxyclasses behind the scenes. |
|
|
| Specifies required layers of Java and PL/SQL wrappers and additional related settings. Is used as input for the -proxywsdl and -proxyclasses options. |
|
|
| Specifies the URL of a WSDL document for which Web services client proxy classes and associated Java wrapper classes are generated along with PL/SQL wrappers. |
|
|
| Specifies whether the code generated for object types implements the java.io.Serializable interface. |
|
|
Specifies object types and packages, or subsets of packages, for which JPublisher generates Java classes, and optionally subclasses and interfaces. |
|
| |
| Specifies SQLJ option settings for the JPublisher invocation of the SQLJ translator. |
|
|
| Specifies SQL queries or data manipulation language (DML) statements for which JPublisher generates Java classes, and optionally subclasses and interfaces, with appropriate methods. |
|
|
| Specifies the name of a style file for Java-to-Java type mappings. |
|
|
| Specifies the name and password for a superuser account that can be used to grant permissions to execute wrappers that access Web services client proxy classes in the database. |
|
|
| Specifies whether to generate a toString() method for object types. |
|
|
| Specifies the JPublisher type-map. |
|
|
| Specifies object types for which JPublisher generates code. Note: This option is deprecated in favor of -sql, but is supported for backward compatibility. |
|
|
| Specifies the URL that JPublisher uses to connect to the database. |
|
|
| Specifies an Oracle user name and password for connection. |
|
|
| Specifies the type mapping (jdbc or oracle) that JPublisher uses for user-defined SQL types. |
|
|
| Specifies a Java version, in case you want a version other than the default. |
|
|
This section explains the key JPublisher options, summarized in Table 13.1. The options can be grouped into the following categories: Java environment, connection, Java code generation, runtime free code generation, style files for mapping Java in the database, type-maps, data type mapping (or type conversion), input files/items, input/output, style files, and backward compatibility. The Web services options will be covered in Part V dedicated to database Web services.
JPublisher shares the following options with the SQLJ environment (covered in Chapter 10):
The -classpath
option specifies the CLASSPATH to be used by JPublisher during translation and compilation for resolving classes:
$ jpub -user=scott/tiger -sql=PERSON:Person,EMPLOYEE:Employee -classpath=.:$ORACLE_HOME/jdbc/lib/ ojdbc14.jar:$CLASSPATH
The -compiler-executable
option can be used to specify a Java compiler other than the default for compiling the code generated by JPublisher:
-compiler-executable=<path to compiler executable>
The -vm
option can be used to specify a nondefault JVM for invoking JPublisher:
$ jpub -vm=$JAVA_HOME/bin/java -compiler-executable=$JAVA_HOME/bin/javac ...
JPublisher obviously requires a database connection; it also requires a data-source for Java proxies representing database entities that are deployed in the middle tier.
The -context
option specifies the SQLJ connection context class to be used by SQLJ classes generated by JPublisher (see Chapter 11 for more details on connection contexts).
-context={generated|DefaultContext|user_defined}
The -datasource
option specifies the datasource to be used for publishing SQL query and DML, PL/SQL, Streams AQ, and server-side Java classes. In the absence of an explicit JDBC connection, the generated code will look up the JNDI location specified by this option in order to get a connection.
-datasource=jndi_location
The -driver
option specifies the driver class to use for JDBC connections; the following default setting should satisfy most needs:
-driver=oracle.jdbc.OracleDriver
The -url
option specifies the URL of the target database.
The default is: -url=jdbc:oracle:oci:@
For JDBC-Thin, use: -url=jdbc:oracle:thin:@host:port/servicename
The -user
or –u
option is mandatory and specifies an Oracle database user name and password:
-user=username/password -u username/password
JPublisher shares the same mapping table with JDBC (Chapter 8) and SQLJ (Chapter 11); however, it groups SQL data types into the following four categories and, as summarized in Table 13.2, for each category, it lets you specify which style of mapping to perform.
Numeric types (-numbertypes
option): Anything stored as SQL type NUMBER
. The mapping choices are:
-numbertypes=objectjdbc|jdbc|bigdecimal|oracle
The default mapping is “objectjdbc.” See the next section for a more detailed description of numeric data types mapping.
Large object (LOB) types (-lobtypes
option): SQL BLOB
and CLOB.
The mapping choices are: -lobtypes=oracle|jdbc
. The default mapping is “oracle
.”
Built-in types (-builtintypes
option): Anything stored as a SQL type not covered by the preceding categories (e.g., CHAR
, VARCHAR2
, LONG
, and RAW
). The mapping choices are: -builtintypes=jdbc|oracle
. The default mapping is “jdbc
.”
User-defined types (-usertypes
option): An attribute of SQL object type. The mapping choices are: -usertypes=oracle|jdbc.
The default mapping is “oracle
.”
Table 13.2. JPublisher SQL Type Categories, Supported Settings, and Defaults
SQL Type Category | JPublisher Mapping Option | Mapping Settings | Default |
---|---|---|---|
UDT types |
|
|
|
Numeric types |
|
|
|
LOB types |
|
|
|
Built-in types |
|
|
|
JPublisher defined the following mapping of numeric types: JDBC mapping, Oracle maping, Object-JDBC mapping, and Big Decimal mapping.
JDBC mapping (setting jdbc
): Uses Java primitive types to map SQL types. This setting is valid for the -numbertypes
, -lobtypes
, and -builtintypes
options.
Oracle mapping (setting oracle
): Uses the oracle.sql
types for mapping SQL types. This setting is valid for the -numbertypes
, -lobtypes
, and -builtintypes
options.
Object-JDBC mapping (setting objectjdbc
): Is equivalent to JDBC mapping but uses numeric object types, or reference types (i.e., java.lang.Integer
, Float
, and Double
), instead of primitive Java types (i.e., int
, float
, and double
). Remember that the reference types may return null, unlike the primitive types. This setting is valid for the -numbertypes
option only.
BigDecimal mapping (setting bigdecimal): By default, large numeric attributes are mapped to java.math.BigDecimal
. You can instruct JPublisher to map to oracle.sql.NUMBER
instead, which is faster and more precise than java.math.BigDecimal
.
The JPublisher Input file lets you control the naming of the generated classes and packages and how to map them. The Input files include the INPUT
files (.in
) and properties files (.properties
), as specified, respectively, by the following options: -input, -props.
The general syntax of an INPUT
file (.in
) is as follows:
( SQL name_of_SQL_Type_or_PL/SQL_Package_to_be_translated | SQL [schema_name.]toplevel [(name_list)] | SQLSTATEMENTS_TYPE java_name | TYPE type_name) [GENERATE java_name_1 [IMPLEMENTS java_interface]] [AS java_name_2 [IMPLEMENTS java_interface]] [TRANSLATE database_member_name AS simple_java_name { , database_member_name AS simple_java_name}* ] (SQLSTATEMENTS_METHOD sql_statement AS java_method_name)* [VERSION [=] <version_name>]
AS
, GENERATE
, IMPLEMENTS
, SQLSTATEMENTS_TYPE
, SQL
, TYPE
, SQLSTATEMENTS_METHOD
, TRANSLATE
, TOPLEVEL
, and VERSION
are reserved words.
The VERSION
clause has no specific meaning at the moment.
Examples of input files:
Foo.in ====== SQL foo_pkg Rename.in ========= SQL original as newname Nested_Table.in =============== TYPE module_t as Module TYPE moduletbl_t as Modules TYPE employee_t as Employee
Assume the following user-defined object:
CREATE TYPE student AS OBJECT ( name VARCHAR2(30), empno INTEGER, deptno NUMBER, enroldate DATE, tuition REAL );
jpubinput.in ============ SQL student AS Student TRANSLATE NAME AS Name ENROLDATE AS EnrolDate
The input file may contain one or several TRANSLATE
statements:
$ jpub -user=scott/tiger -input=jpubinput -numbertypes=oracle -usertypes=oracle -dir=jpubdemo -d=jpubdemo -package=jpub -case=same
This JPublisher command will perform the following mapping:
NAME
: Column name mapped to Java Name
ENROLDATE
: Column name mapped to Java EnrolDate
STUDENT
: Type mapped by Student
class and written to the following files specified by the source and class directories (-dir
, and -d
) and the packagesettings (-package
):
jpubdemo/jpub/Student.java jpubdemo/jpub/Student.class
The –props
option (no jpub.props
in properties file) furnishes an alternative to command-line options and specifies the name of a JPublisher properties file (there is no default properties file in JPublisher). Each line in the property file corresponds to one command-line option (prefixed by jpub.
), with the associated value. Table 13.2 gives an explanatory summary of JPublisher options; you can take any option (but –props
) and prefix it with “jpub.
”
Here are samples of properties files:
foo.properties ============== jpub.sql=person_t:Person,student_t:Student, jpub.user=scott/tiger jpub.compatible=CustomDatum mapping2.properties ==================== jpub.filtermodes=in,out,inout,return jpub.filtertypes=1,.INDEXBY, .ORACLESQL-,.BLOB,.CLOB,.STRUCT,.BFILE jpub.generatebean=true jpub.compatible=ORAData jpub.methods=always jpub.out=return
The following options help you specify which items to publish, such as SQL objects, PL/SQL packages, SQL queries, SQL DML statements, or server-side Java classes:
-java, -sql, -sqlstatement.
The -sql
(or –s
, or jpub.sql
in properties file) is one of the most useful and most used options; it allows you to specify the user-defined SQL object types, user-defined SQL collection types, or PL/ SQL packages (whole package or a subset) to publish. It can also be used to request the generation of interfaces or subclasses.
The generic syntax is:
-sql={toplevel|<object type and package translation syntax>}
The following command-line syntaxes apply:
-sql=toplevel -sql=toplevel:MyClass
JPublisher generates a wrapper class named toplevel
or MyClass
:
-sql=<sql package or object type> -sql=<sql package(<proc1>+<proc2>+<proc3>+...)> -sql=<sql package or type>:<Java Class> -sql=<sql package or type>:<Java Class>#<Java Interface> -sql=<sql package or type>:<Class>:<Subclass>#<Sub Interface>
Alternatively, you may use the INPUT
file syntax, which gives you finer-grain control:
SQL toplevel SQL toplevel AS MyClass
The following command syntax specifies an interface for either the reference class (GENERATE
) or the customizable user subclass (AS
):
SQL name_a [GENERATE generated_class [ IMPLEMENTS intface_b] ] [AS user_class [ IMPLEMENTS intface_c ] ]
The –sql
option can occur several times within the same command line; however, you can use a single occurrence with several items separated by commas, without any white space.
Assume a package, MYPACKAGE
, object types STUDENT
and LOCATION.
The following command:
-sql=MYPACKAGE,STUDENT:Student,LOCATION:JLocation:MyLocation
is equivalent to the following INPUT
file syntax:
SQL MYPACKAGE SQL STUDENT AS Student SQL LOCATION GENERATE JLocation AS MyLocation
and performs the following actions:
Creates a wrapper class for the MYPACKAGE
; JPublisher maps a package with a single Java class (not a Java class per procedure or function).
The SQL STUDENT
type is mapped as Student
.
Generates an object reference class, StudentRefRef
.
The SQL LOCATION
type is mapped to JLocation
; in addition, JPublisher generates a user customizable class to represent LOCATION
objects by the MyLocation
class.
A MyLocation
stub is generated, which extends Jlocation
; you can customize the MyLocation
code.
Generates an object reference class, MyLocationRef
.
The -java
option (jpub.java
option in properties file) creates client-side stub classes for accessing server-side classes:
$ jpub -u scott/tiger -java=<Server-side Java>:<Stub Implementation>#<Stub interface>
JPublisher generates the client-side stub interface and its implementation. This option was covered in detail in Chapter 3. Depending on the database release (9.2 or 10g), it may require the sqljutl.jar
library (located in $ORACLE_HOME/sqlj/lib
) to be loaded in the database.
The -sqlstatement
option lets you publish SQL statements, including SELECT
, INSERT
, UPDATE
, or DELETE
statements, as Java methods in SQLJ classes. This is mainly for Web services needs; however, you can also use the generated class in traditional JDBC and/or SQLJ programs. You can specify multiple –sqlstatement.methodName
settings. The -sqlstatement.return
indicates whether a query result is returned as a result set, or an array, or both (as two methods).
Here is the generic syntax:
-sqlstatement.class=ClassName:UserClassName#UserInterfaceName -sqlstatement.methodName=sqlStatement -sqlstatement.return={both|resultset|beans}
The following JPublisher command will generate proxy classes that can be used in the middle tier to represent this SQL statement. See Part V for a how to publish a SQL statement as Web service.
$ jpub -u scott/tiger -sqlstatement.getEmpBySal="select ename, sal, hiredate from emp where sal >:{mysal NUMBER}" -compile=notranslate SQLStatements SQLStatements_getEmpBySalRow
JPublisher generates the following files: SQLStatements.sqlj
and SQLStatements_getEmpBySalRow.java
.
Alternatively, using the following properties file:
statement.properties ==================== jpub.sqlstatement.getEmpBySal="select ename, sal, hiredate from emp where sal>:{mysal NUMBER}"
you can issue the following JPublisher command:
$jpub -user=scott/tiger -props=statement.props -compile=notranslate
The following JPublisher options control how JPublisher performs Java code generation: -access
, -case
, -filtermodes
, -filtertypes
, -generatebean
, -genpattern
, -gensubclass
, -methods
, -omit_schema_names
, -outarguments
, -package
, -serializable
, and –tostring
.
Using these options, you can do the following:
Filter the generated code according to parameter modes or parameter types. The -filtermodes
option lets you filter generated code according to parameter modes; this is useful when not all parameter modes are supported in method signatures or attributes of the consumer of the generated code.
-filtermodes= <list of modes to filter out or filter in> (i.e., in, out, inout, return) 0: exclude all by default (total filtering) 1: include all by deault (no filtering) "-": the mode or type should be excluded "+": the mode or type should be included Examples: -filtermodes=0,in+,return+ -filtermodes=1,out-,inout-
The -filtertypes
option lets you filter generated code according to parameter types; this is useful when not all parameter types are supported in method signatures or attributes of the consumer of the generated code:
-filtertypes= l<ist of types to filter out or filter in> (i.e.,oracle.sql.XXX
types, any indexed-by table types, any SQLJ iterator types and java.sql.ResultSet
, any types that implement ORAData
or SQLData
, and so on).
Examples:
Allows ORAData
or SQLData
types except those with a typecode of ARRAY
or REF
:
-filtertypes=0,.ORADATA+,.ARRAY-,.REF-
Filters all types except .CURSOR
and .INDEXBY:
-filtertypes=0,.CURSOR+,.INDEXBY+
Ensure that the generated code conforms to the JavaBeans specification (e.g., getter method must return a bean property, and so on)
-generatebean={true|false} (default is false).
When -generatebean=true,
the names of methods that are not JavaBean property getter and setter are changed (i.e., prefixed with “_”) so as to avoid confusion.
Example:
public int getBaseType() throws SQLException;
becomes:
public int _getBaseType() throws SQLException;
Specify the naming patterns. The -genpattern
option can be used in conjunction with the –sql
or -sqlstatement
option, to define generic naming patterns:
-genpattern=<pattern specifications>
%1 refers to the default base names that JPublisher
generates
%2 refers to the output names if specified by -sql
For example, the following pattern specification:
-genpattern=%1Base:%1User#%1 -sql=PERSON,EMPLOYEE,EMPLOYEE_MGR
is equivalent to:
-sql=PERSON:PersonBase:PersonUser#Person -sql=EMPLOYEE:EmployeeBase:EmployeeUser#Employee -sql=EMPLOYEE_MGR:EmployeeMgrBase:EmployeeMgrUser#EmployeeMgr
Specifying how stubs are generated for user subclasses. The -gensubclass
option controls whether JPublisher generates source files for user-provided subclasses and their shape.
-gensubclass={true|false|force|call-super}
true:
JPublisher generates the subclass only if it does not already exist; this is the default
.
false:
JPublisher does not generate any subclass.
force:
JPublisher generates the subclasses, even if it already exists.
call-super:
JPublisher generates only constructors and methods for implementing an interface (i.e., ORAData);
getters, setters, and other methods are generated.
The –method
controls the generation of wrapper methods for stored procedures in SQL object types and methods in PL/SQL packages (JPublisher generates one Java class per PL/SQL package), as well as method overloading, and connection retry.
-methods={all|none|named|always,overload|unique ,noretry|retry}
-methods=all:
Generates SQLJ class if the underlying SQL object or package defines methods; otherwise a Java class. In pre-10g, SQLJ classes were always generated for this setting.
-methods=none:
No wrapper method is generated, and no class is generated for the PL/SQL packages.
-methods=named:
Wrapper methods are generated only for the methods explicitly named in the INPUT file.
-methods=always:
SQLJ classes are generated for all SQL object types, regardless of whether the types define methods.
-methods=overload:
Method in the generated code can be overloaded (i.e., same name different signatures).
Assume the following stored functions:
function func1 (a VARCHAR2(40)) return VARCHAR2; function func1 ( x int, y int) return int;
With –methods=overload
, JPublisher will generate the following method wrappers as part of the PL/SQL package mapping:
String func1 (String a); java.math.BigDecimal func1(java.math.BigDecimal x, java.math.BigDecimal y);
-methods=unique:
No overload, and various techniques are used to generate a unique method name, such as appending a number or the first letter of the return type and argument types.
Continuing the previous example, here is howJPublisher renamed function names:
String func1(String a); java.math.BigDecimal func1BBB(java.math.BigDecimal x, java.math.BigDecimal y);
-methods=retry:
This option is used for Web services call-in (see next chapter). An additional constructor is generated, which takes a DataSource
object as parameter to get a new connection at runtime. Extra code is generated to request a new JDBC connection and retry the method if the method encounters a JDBC connection error during execution.
The -outarguments
option is primarily used to support Web services; it lets you specify a method to use to return PL/SQL OUT or IN OUT parameters (Array, JAX-RPC holder or Function returns
).
-outarguments={array|holder|return}
Specifying whether generated code is serializable or not. The -serializable
option specifies whether the generated class (for SQL object types) implements the java.io.Serializable
interface. The default is false
.
-serializable={true|false}
The following options let you control how JPublisher generates PL/SQL code: -overwritedbtypes, -plsqlfile, -plsqlmap,
and -plsqlpackage
.
The –overwritedbtypes
option specifies if naming conflicts are checked before creating new SQL types. These are generated when publishing PL/SQL types and PL/SQL wrappers for server-side Java classes. The default is true; set this option to false (overwritedbtypes=false
.) if you do not want JPublisher to overwrite the existing type. In order to always generate the same type upon multiple invocation of the same operation, you must run the dropperx.sql
package before each run.
The -plsqlfile
option specifies scripts to use in creating and dropping SQL types and PL/SQL packages. The wrapper script creates new SQL types (to map to PL/SQL types) as well as the PL/SQL package that JPublisher uses for any PL/SQL wrappers or Call Specs, conversion functions, wrapper functions, and table functions. The dropper script drops these entities. JPublisher prints the following message on the standard output upon generating the scripts:
J2T-138, NOTE: Wrote PL/SQL package XXXXXXXX to file xxxxxxxxxx.sql. Wrote the dropping script to file zzzzzzzzzz.sql.
The –plsqlmap
option specifies whether PL/SQL wrapper functions are generated:
-plsqlmap={true|false|always}
true:
Only as needed, depending on the complexity of the mapping of the types, arguments, parameters, and function returns.
false:
PL/SQL wrapper functions are not generated, so JPublisher skips this step.
always:
A PL/SQL wrapper function is generated for every stored procedure that uses a PL/SQL type.
The –plsqlpackage
option specifies the name of the PL/SQL package in which JPublisher generates PL/SQL call specs, conversion functions, wrapper functions, and table functions. By default, JPublisher uses the JPUB_PLSQL_WRAPPER
package.
-plsqlpackage=<name of PLSQL package>
Style files let you specify the mapping of Java types and Oracle JDBC types that are not supported by the target consumer (e.g., Web services framework) into supported types; for example, java.sql.Clob
and oracle.sql.CLOB
are converted into java.lang.String
(see Database Web services in Part V).
JPublisher generates client-side Java subclasses or proxies that represent server-side Java, and the subclasses implement the mapping specified in the style files. Table 13.3 summarizes the Java-to-Java type mapping.
Table 13.3. Summary of Key Java-to-Java Type Mappings in Oracle Style Files
Source Type | Target Type |
---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
As of Oracle Database 10g, the following style files are furnished as part of translator.jar
:
/oracle/jpub/mesg/webservices-common.properties /oracle/jpub/mesg/webservices10.properties /oracle/jpub/mesg/webservices9.properties
The -style
option specifies the base name of a style file:
-style=<stylename>
Example:
-style = webservices10 (i.e., webservices10.properties) -style = local/mystyle (i.e., local/mystyle.properties)
JPublisher looks for a style file as follows:
It looks for the following resource in the CLASSPATH:
/oracle/jpub/mesg/<stylename>
It looks for the following resource in the CLASSPATH:
/oracle/jpub/mesg/<stylename>.properties
It looks for the file in the current directory:
<stylename>
Finally, it looks for the file in the current directory:
<stylename>.properties
If no style file can be found, JPublisher throws an exception.
In section 13.1, the –style=webservices10
is used to convert java.sql.Timestamp
into java.util.Date.
In this example:
The PL/SQL stored procedure definition includes a TIMESTAMP argument:
procedure proc1 (enrold timestamp, stud in out
studrec);
JPublisher first maps this as follows in the base class:
public void proc1(java.sql.Timestamp enrold,
StudRecSql[] stud_inout);
Then JPublisher maps it as follows in the user subclass:
public void proc1(java.util.Date enrold, StudRecSql[]
stud_inout);
XMLType mapping is a recurrent requirement. JPublisher takes a two-step approach in mapping XMLType into oracle.sql.SimpleXMLType
:
SYS.XMLTYPE
is mapped to oracle.xdb.XMLType
This can then be mapped to oracle.sql.SimpleXMLType
using style file.
A style file has a TRANSFORMATION
section and an OPTION
section.
The TRANSFORMATION
section is delimited by the TRANSFORMATION
and END_TRANSFORMATION
tags and describes the Java-to-Java mappings, to be applied to types used for object attributes or in method signatures.
The MAPPING
subsection within the TRANSFORMATION
section specifies source and target mapping, as follows:
MAPPING # TIMESTAMP columns uses java.sql.Timestamp. # For Web Services this is mapped to java.util.Date # SOURCETYPE java.sql.Timestamp TARGETTYPE java.util.Date OUT %2 = null; if (%1!=null) %2=new java.util.Date(%1.getTime() + %1.getNanos()/1000000) ; END_OUT IN # Converting Date to Timestamp %1 = null; if (%2!=null) %1 = new java.sql.Timestamp(%2.getTime()); END_IN END_MAPPING
The OPTIONS
section lets you specify additional JPublisher options and may be used in lieu of a JPublisher properties file.
The JPublisher user type-map or default type-map entries to influence code generation. Type-maps are used in JPublisher for the following:
JPublisher uses the following logic for mapping a given SQL or PL/SQL type to Java:
Checks the type-maps to see if a mapping already exists.
Checks the predefined Java mappings for the SQL and PL/SQL types.
Checks if the data type to be mapped is a PL/SQL RECORD
or an INDEX-BY-TABLE
.
If PL/SQL RECORD
type, JPublisher generates a corresponding SQL object type that can be mapped to Java.
If INDEX-BY-TABLE
type, JPublisher generates a corresponding SQL collection type that can be mapped to Java.
If none of these steps applies, then the data type must be a user-defined type. JPublisher generates an ORAData
or SQLData
class to map it accordingly.
The following options manage the type-map entries: -addtypemap
, -adddefaulttypemap
, -defaulttypemap
, and -typemap
.
-addtypemap
appends entries to the user-type map.
-typemap
replaces the existing user type-map with the specified entries.
-adddefaulttypemap
appends entries to the default type-map.
-defaulttypemap
replaces the existing default type-map with the specified entries.
These options can be specified instead in a JPublisher properties file.
A JPublisher type-map entry has one of the following formats:
-type_map_option=opaque_sql_type:java_type -type_map_option=numeric_indexed_by_table:java_numeric_type[max_length] -type_map_option=char_indexed_by_table:java_char_type[max_length](elem_size) -type_map_option= plsql_type:java_type:sql_type:sql_to_plsql_func:plsql_to_sql_func
sql_to_plsql_func
and plsql_to_sql_func
are conversion functions between SQL and PL/SQL (conversion functions are illustrated in the next chapter).
Here are examples of default type-maps with conversion functions (e.g., INT2BOOL, BOOL2INT):
jpub.defaulttypemap=SYS.XMLTYPE:oracle.xdb.XMLType jpub.adddefaulttypemap=BOOLEAN:boolean:INTEGER: SYS.SQLJUTL.INT2BOOL:SYS.SQLJUTL.BOOL2INT jpub.adddefaulttypemap=INTERVAL DAY TO SECOND:String:CHAR: SYS.SQLJUTL.CHAR2IDS:SYS.SQLJUTL.IDS2CHAR jpub.adddefaulttypemap=INTERVAL YEAR TO MONTH:String:CHAR: SYS.SQLJUTL.CHAR2IYM:SYS.SQLJUTL.IYM2CHAR
The following options control JPublisher input and output files and their locations.
The -compile
option prevents the compilation of the generated .java
files and the translation of generated .sqlj
files (for backward compatibility with previous releases, since by default JPublisher now removes the .sqlj
files).
-compile={true|false|notranslate}
true:
The generated SQLJ and/or Java files are translated and compiled.
false:
The generated SQLJ files are translated; however, the Java files are not compiled.
notranslate:
The generated SQLJ files are not translated and not removed, which is the behavior of the earlier JPublisher releases.
The -dir
option specifies the directory location for .java
and .sqlj
source files, while the -d
option specifies the directory location for .class
files.
$ jpub -user=scott/tiger -d=classdir -dir=sourcedir ...
The -encoding
option specifies the Java character encoding to be used for reading the INPUT
file and the output source files.
The -compatible
option:
-compatible={oradata|customdatum|both8i|8i|9i|10.1|sqlj}
can be used for:
Specifying the interface to implement in generated classes, for backward compatibility purposes. If -usertypes=oracle
, then -compatible=customdatum
instructs JPublisher to implement the CustomDatum
interface (supported for backward-compatibility purposes).
Skipping the translation step, the .sqlj
output files are kept.
-compatible=sqlj
instructs JPublisher to skip SQLJ translation of the generated .sqlj files.
Backward-compatibility with Oracle9i or Oracle8i environments -compatibility=9i
instructs JPublisher to generates .sqlj
files compatible with the Oracle 9i release. Oracle 8i compatibility is beyond the scope of this book.
This section describes succinctly how JPublisher maps supported database entities, including user-defined SQL object types, SQL object reference types (REF types), REF cursors, user-defined SQL collections, user-defined opaque types, XMLType, PL/SQL Boolean types, PL/SQL record types, PL/SQL associative arrays, PL/SQL packages, Java in the database, SQL queries, SQL DML, and streams AQ. The generated wrapper classes can be used as wrappers in your JDBC or SQLJ applications, as you used the handcrafted ones.
For a SQL object type, FOO_T
, JPublisher generates a foo_t.java
class that maps the object. This includes getters and setters methods to get and set each attribute of the object type. In addition, if the object type includes methods implemented as PL/SQL stored procedures, JPublisher will generate Java wrapper methods to invoke the corresponding methods on the SQL object instances. JPublisher also generates the object reference type.
Assume a user-defined NUMBERTYPE,
the ToReal, Plus,
and GCD Functions,
and Normalize
procedure:
Number.sql ========== drop table NumberTab; drop type NumberType force; CREATE TYPE NumberType AS OBJECT ( num INTEGER, denom INTEGER, MAP MEMBER FUNCTION toReal RETURN REAL, MEMBER PROCEDURE normalize, STATIC FUNCTION gcd(x INTEGER, y INTEGER) RETURN INTEGER, MEMBER FUNCTION plus ( x NumberType) RETURN NumberType ); / CREATE TYPE BODY NumberType AS MAP MEMBER FUNCTION toReal RETURN REAL IS -- convert rational number to real number BEGIN RETURN num / denom; END toReal; MEMBER PROCEDURE normalize IS g BINARY_INTEGER; BEGIN g := NumberType.gcd(num, denom); num := num / g; denom := denom / g; END normalize; STATIC FUNCTION gcd(x INTEGER, y INTEGER) RETURN INTEGER IS -- find greatest common divisor of x and y ans BINARY_INTEGER; BEGIN IF x < y THEN ans := NumberType.gcd(y, x); ELSIF (x MOD y = 0) THEN ans := y; ELSE ans := NumberType.gcd(y, x MOD y); END IF; RETURN ans; END gcd; MEMBER FUNCTION plus (x NumberType) RETURN NumberType IS BEGIN return NumberType(num * x.denom + x.num * denom, denom * x.denom); END plus; END; / CREATE TABLE NumberTab of NumberType; INSERT INTO NumberTab VALUES(2,3); COMMIT; EXIT; / $ sqlplus scott/tiger @Number Number.in ========= SQL NumberType AS NumberType Number.properties ================= jpub.methods=all jpub.numbertypes=jdbc jpub.lobtypes=jdbc jpub.builtintypes=jdbc jpub.usertypes=jdbc jpub.compile=notranslate ← do not translate/compile the sqlj file $ jpub -u scott/tiger -input=Number.in -props=Number.properties SCOTT.NUMBERTYPE
You can look at the generated Number.sqlj
file, and here are some highlights:
public class NumberType implements SQLData { public static final String _SQL_NAME = "SCOTT.NUMBERTYPE"; public static final int _SQL_TYPECODE = OracleTypes.STRUCT; ... public int gcd (int X,int Y) throws java.sql.SQLException ... #sql [getConnectionContext()] __jPt_result = { VALUES(SCOTT.NUMBERTYPE.GCD(:X,:Y)) }; ... public NumberType plus ( NumberType X) throws java.sql.SQLException #sql [getConnectionContext()] { BEGIN :OUT __jPt_result := :__jPt_temp.PLUS(:X); END;}; public NumberType normalize () NumberType __jPt_temp = this; #sql [getConnectionContext()] { BEGIN :INOUT __jPt_temp.NORMALIZE(); END;}; return __jPt_temp; public float toreal () #sql [getConnectionContext()] {BEGIN :OUT __jPt_result := :__jPt_temp.TOREAL(); END;};
The following test code uses the generated code (with proper environment). That’s all you have to do!
MuNumber.java ============= import sqlj.runtime.ref.DefaultContext; import oracle.sqlj.runtime.Oracle; public class MyNumber { public static void main(String[] args) throws java.sql.SQLException { int n = 5; int d = 10; NumberType r = new NumberType(5, 10); int g = r.gcd(n, d); System.out.println("gcd: " + g); float f = r.toReal(); System.out.println("real value: " + f); NumberType s = r.plus(r); System.out.println("sum: " + s); s.normalize(); System.out.println("sum: " + s); } }
For a SQL object reference type, REFFOO_T
, JPublisher generates a foo_tRef.java
class, which models references to the object type in Java, along with methods for accessing the actual object value. From JPublisher’s mode of operation perspective, this mapping is similar to the previous case. Let’s reuse XREFADTtab.sql
types defined in Chapter 3.
XREFADTTab.sql ============== drop table REFtab; drop table ADTtab; drop type ADTtyp; create type ADTtyp as OBJECT (a1 number, a2 varchar2(20), a3 date) / create table ADTtab of ADTtyp / create table REFtab (id number, refcol REF ADTtyp) / insert into ADTtab values (ADTtyp(1, 'One', '01-JAN-2001')); insert into ADTtab values (ADTtyp(2, 'Two', '02-JAN-2002')); insert into ADTtab values (ADTtyp(3, 'Three', '03-JAN- 2003')); insert into ADTtab values (ADTtyp(4, 'Four', '04-JAN-2004')); insert into ADTtab values (ADTtyp(5, 'Five', '05-JAN-2005')); insert into REFtab select 1, REF(R2) from ADTtab R2 where R2.a1 = 1; insert into REFtab select 2, REF(R2) from ADTtab R2 where R2.a1 = 2; insert into REFtab values (3, NULL); insert into REFtab select 4, REF(R2) from ADTtab R2 where R2.a1 = 4; insert into REFtab select 5, REF(R2) from ADTtab R2 where R2.a1 = 5; commit; $ sqlplus scott/tiger @XREFADTTab $ jpub -user scott/tiger -sql=ADTtyp:Adttyp SCOTT.ADTTYP $ ls Adttyp* Adttyp.class Adttyp.java AdttypRef.class AdttypRef.java
For a PL/SQL stored procedure or function that returns a REF CURSOR
, JPublisher generates a Java class, which maps the REF CURSOR
to java.sql.ResultSet
. For a SQL query that returns a REF CURSOR,
JPublisher maps the REF CURSOR
to an array of rows, each row being represented by a JavaBean instance. Assume the following script, which declares a PL/ SQL package that uses a ref cursor type:
Refcur.sql ========== set serveroutput on; create or replace package refcur as type curstype is ref cursor return emp%rowtype; FUNCTION getcurfunc RETURN curstype; PROCEDURE getcurproc(a curstype); end; / show errors create or replace package BODY refcur as FUNCTION getcurfunc RETURN curstype IS curs curstype; BEGIN open curs for select * from emp where ename='SCOTT'; return curs; END; PROCEDURE getcurproc(a curstype) IS name emp%rowtype; BEGIN LOOP FETCH a INTO name ; EXIT WHEN a%NOTFOUND; dbms_output.put_line(name.ENAME); END LOOP; close a; END; END; / show errors exit; $ sqlplus scott/tiger @Refcur SQL*Plus: Release 10.2.0.1.0 - Production on Sun Jan 22 19:00:01 2006 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning and Data Mining options Package created. No errors. Package body created. No errors. Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning and Data Mining options $
Invoke it through SQL*Plus first, as follows, for a sanity check:
$ sqlplus scott/tiger
SQL> set serveroutput on
SQL> declare
2 curs refcur.curstype;
3 begin
4 curs := refcur.getcurfunc();
5 refcur.getcurproc(curs);
6 end;
7 /
SCOTT
PL/SQL procedure successfully completed.
Map with JPublisher:
$ jpub –user scott/tiger –compile=notranslate –sql=REFCUR:Refcur SCOTT.REFCUR GetcurfuncRow
JPublisher generates:
Refcur.sqlj:
GetcurfuncRow.java:
containing the getters and setters for each column of the EMP
table
Generated code is not nice to read, but you can notice the following method in Refcur.sqlj
(preserved with -compile=notranslate
option):
public java.sql.ResultSet getcurfunc () {...}
getcurfunc()
places a SQLJ call to GETCURFUNC
, which returns a Ref Cursor into a Result Set
variable.
#sql [getConnectionContext()] __jPt_result_rs = { VALUES(SCOTT.REFCUR.GETCURFUNC()) };
However, if you want to map Ref Cursor
to String
instead, for use as a Web service parameter, for example, JPublisher allows you to subclass the generated class.
The following syntax generates a base class and a subclass that can be customized by the end user:
jpub -sql=REFCUR:RefcurBase:MyRefcur ...
The following classes are generated: RefcurBase.sqlj and MyRefcur.sqlj,
which extends RecurBase
. You can modify MyRefcur.sqlj
and add a new method, which returns the result set in the desired format, such as String
:
public String[] readRefCursorArray(String arg1, Integer arg2) { java.sql.ResultSet rs = getcurfunc(arg1,arg2); //... create a String[] from rs and return it ... }
You can perform a strongly typed custom mapping of user-defined collections. For each collection type (VARRAYs, Nested Tables
) COL_T
, JPublisher generates a col_t.java
class that implements ORAData
and represents the collection. For Nested Tables, it generates, in addition, methods to get and set the Nested Table as an entire array and methods to get and set individual elements of the table.
Assume the following database entities definition: STUDENT_T and MODULE_T object types, MODULETBL_T as NESTED TABLE of MODULE_T, STUDENT, and PROJECT tables.
NTab.sql (not shown entirely) ======== DROP TABLE projects / drop table student / DROP TYPE moduletbl_t / DROP TYPE module_t / DROP TYPE student_t / CREATE TYPE student_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 student_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 student_t, start_date DATE, duration NUMBER(3), modules moduletbl_t ) NESTED TABLE modules STORE AS modules_tab ; CREATE TABLE student OF student_t ; INSERT INTO STUDENT VALUES( STUDENT_T(7369,'BOB SMET','ANALYST',7902,'17-DEC-95',800,20)) ; ... INSERT INTO student VALUES ( student_t(7934,'SUE MILLER','SR ANALYST',7782,'23-JAN-02',1300,10)); INSERT INTO projects VALUES ( 111, 'P111', null, '10-JAN-02', 300, moduletbl_t( module_t ( 1111 , 'Physics ', null, '01-JAN-99', 100), module_t ( 1112, 'Advertizing', null, '05-FEB-04',20) , ... module_t ( 1115, 'Politics', null,'12-MAY-03',34) ) ) ; update projects set owner=(select ref(p) from student p where p.empno = 7839) where id=111 ; update the(select modules from projects a where a.id = 111) set module_owner = ( select ref(p) from student p where p.empno = 7844) where module_id = 1111 ; update the(select modules from projects a where a.id = 111) set module_owner = ( select ref(p) from student p where p.empno = 7844) where module_id = 1111 ; ... INSERT INTO projects VALUES ( 444, 'P444', null, '15-FEB-03', 555, moduletbl_t ( module_t ( 4441, 'Manufacturing', null, '01-MAR- 02', 120), ... module_t ( 4447, 'Budgets', null, '10-MAR-01',45))) ; update projects set owner=(select ref(p) from student p where p.empno = 7698) where id=444 ; update the ( select modules from projects where id = 444 ) set module_owner = ( select ref(p) from student p where p.empno = 7369) where module_id = 4441 ; ... commit; exit; $ jpub -user scott/tiger -compile=false -sql=MODULE_T:Module,MODULETBL_T:Modules,STUDENT_T:Student SCOTT.MODULE_T SCOTT.MODULETBL_T SCOTT.STUDENT_T
The following files are generated: Module.java, Modules.java, ModuleRef.java, Student.java,
and StudentRef.java.
Module.java public class Module implements ORAData, ORADataFactory { public static final String _SQL_NAME = "SCOTT.MODULE_T"; public static final int _SQL_TYPECODE = OracleTypes.STRUCT; ... // getters and setters Modules.java public class Modules implements ORAData, ORADataFactory { public static final String _SQL_NAME = "SCOTT.MODULETBL_T"; public static final int _SQL_TYPECODE = OracleTypes.ARRAY; ... public Module[] getArray(long index, int count) throws SQLException ... public void setArray(Module[] a) throws SQLException ... public Module getElement(long index) throws SQLException ... public void setElement(Module a, long index) throws SQLException ModuleRef.java public class ModuleRef implements ORAData, ORADataFactory { public static final String _SQL_BASETYPE = "SCOTT.MODULE_T"; public static final int _SQL_TYPECODE = OracleTypes.REF; ... Student.java public class Student implements ORAData, ORADataFactory { public static final String _SQL_NAME = "SCOTT.STUDENT_T"; public static final int _SQL_TYPECODE = OracleTypes.STRUCT; ... // getters and setters StudentRef.java public class StudentRef implements ORAData, ORADataFactory { public static final String _SQL_BASETYPE = "SCOTT.STUDENT_T"; public static final int _SQL_TYPECODE = OracleTypes.REF; ...
SQL OPAQUE
types are usually used internally by other types; however, these can be exposed to Java through custom wrapper classes, which implement the oracle.sql.ORAData
interface. Assume MYOPAQUE
type, for which there is no associated wrapper. JPublisher will generate a MyOpaque.java
wrapper class containing the following public, static fields, and methods:
public static String _SQL_NAME = "MYOPAQUE"; public static int _SQL_TYPECODE = OracleTypes.OPAQUE; public static ORADataFactory getORADataFactory() { ... } MYOPAQUE.sql ============ CREATE LIBRARY opqlib TRUSTED AS STATIC / CREATE OR REPLACE TYPE MYOPAQUE as OPAQUE FIXED (4) USING library opqlib ( STATIC FUNCTION CONSTRUCT(str IN VARCHAR2) return MYOPAQUE, MEMBER FUNCTION VALUE return BINARY_INTEGER, MAP MEMBER FUNCTION MAPFUN return BINARY_INTEGER ) / CREATE OR REPLACE TYPE BODY MYOPAQUE as STATIC FUNCTION CONSTRUCT(str IN VARCHAR2) return MYOPAQUE ... MEMBER FUNCTION VALUE return BINARY_INTEGER ... MAP MEMBER FUNCTION MAPFUN return BINARY_INTEGER ... end; / $ jpub -user=sys/<syspassword> -sql=MYOPAQUE:MyOpaque -methods=true
You may associate an existing Java class to the SQL OPAQUE
type using the JPublisher user type map as follows:
-addtypemap=MYOPAQUE:MyOpaque
As mentioned in JDBC and SQLJ subparts, SYS.XMLTYPE
is a named OPAQUE type that is mapped by default to the Java wrapper class oracle.xdb.XMLType
. For simple XMLTYPE operations you may use any JDBC driver (JDBC-Thin or JDBC-OCI), however, full support for oracle.xdb.XMLType
requires JDBC-OCI. Fortunately, JPublisher furnishes oracle.sql.SimpleXMLType
, a driver-independent alternative wrapper for SYS.XMLTYPE
(i.e., works for both JDBC-Thin and JDBC-OCI).
Assume xmlobj
is an object type based on XMLtype:
xmltype.sql =========== drop type xmlobj; create type xmlobj as object (xmldoc sys.xmltype); / show error exit
Here are the steps for generating the wrapper class xmlobj_t
and xmlobj_tRef
, using JPublisher:
Step#1: create the sql type $ sqlplus scott/tiger ... SQL> set echo on SQL> @xmltype SQL> drop type xmlobj; Type dropped. SQL> SQL> create type xmlobj as object (xmldoc sys.xmltype); 2 / Type created. SQL> show error No errors. SQL> SQL> exit $ jpub –u scott/tiger –compile=false –sql=xmlobj:xmlobj_t SCOTT.XMLOBJ
The following files are generated, for mapping SYS.XMLTYPE
to oracle.xdb.XMLType
.
xmlobj_t.java xmlobj_tRef.java
Let’s glance at xmlobj_t.java:
xmlobj_t.java ============= ... public class xmlobj_t implements ORAData, ORADataFactory { public static final String _SQL_NAME = "SCOTT.XMLOBJ"; public static final int _SQL_TYPECODE = OracleTypes.STRUCT; ... public static ORADataFactory getORADataFactory() ... /* ORAData interface */ public Datum toDatum(Connection c) throws SQLException ... /* ORADataFactory interface */ public ORAData create(Datum d, int sqlType) throws SQLException ... protected ORAData create(xmlobj_t o, Datum d, int sqlType) throws SQLException ... /* accessor methods */ public oracle.xdb.XMLType getXmldoc() throws SQLException ... public void setXmldoc(oracle.xdb.XMLType xmldoc) throws SQLException }
The xmlobj_tRef
is not displayed.
Using a style file, we can instruct JPublisher to map SYS.XMLTYPE
to oracle.sql.SimpleXMLType
.
$ jpub -u scott/tiger -style=webservices10 -compile=false -sql=XMLOBJ:xmlobj_t xmlobj_t.java xmlobj_tBase.java xmlobj_tUser.java xmlobj_tUserRef.java
When mapping a PL/SQL type not supported by JDBC (such as BOOLEAN
) as arguments or return value into a Java type, JPublisher lets you specify an intermediate supported SQL type (such as INTEGER
) and the RDBMS-side conversion functions.
Create a function that maps the PL/SQL type (PLTYP)
to the SQL type (SQLTYP)
:
FUNCTION PL2SQL(pl PLTYP) RETURN SQLTYP
Create a function that maps the SQL type (SQLTYP)
to the PL/SQL type (PLTYP)
:
FUNCTION SQL2PL(sql SQLTYP) RETURN PLTYP
Define a JPublisher typemap:
jpub -addtypemap=PLTYP:<java type>:SQLTYP:SQL2PL:PL2SQL ...
The SYS.SQLJUTL
package is required in the database where the generated code will run (it is installed by default in Oracle Database 10g).
This technique is used for mapping PL/SQL RECORD as well as PL/SQL Index-by-Table (see section 13.3.9). Note that PL/SQL BOOLEAN
typemap and conversion functions are predefined, so you don’t have to implement the conversion function or define the type map.
An example of PL/SQL RECORD
type mapping is furnished in section 13.1. JPublisher generates the corresponding SQL objects. An intermediate wrapper (the conversion functions for mapping between PL/SQL and SQL types) is used to map a RECORD
to a SQL type that JDBC supports.
As we have seen in Chapter 8, JDBC now supports index-by-table (or PL/ SQL associative). However, JPublisher can simplify the mapping of PL/ SQL index-by-table of scalar SQL types into Java arrays or custom Java wrapper class. JPublisher generates an intermediate SQL type (i.e., VARRAY
) as well as conversion functions (i.e., INDEX-BY-TABLE_to_VARRAY
and VARRAY_to_INDEX-BY-TABLE
) between the index-by-table type and the SQL type (i.e., VARRAY
).
The following example maps a PL/SQL INDEX-BY-TABLE
into a custom Java wrapper class, which implements ORAData
.
Create the PL/SQL package containing the index-by-table:
indexbytab.sql ============== create or replace package pkgidx is type plsidxtyp is table of varchar2(20) index by binary_integer; procedure procidx (p1 in out plsidxtyp); end; / $sqlplus scott/tiger @indexbytab
The following JPublisher command generates these objects: wrap1.sql
, drop1.sql
, Pkgidx.sqlj
, and PkgidxPlsidxtyp.java
(the -notranslate option prevents it from compiling and removing the .sqlj
files).
$ jpub –u scott/tiger –s PKGIDX:Pkgidx –plsqlpackage=wrap1 –plsqlfile=wrap1.sql,drop1.sql –compile=notranslate SCOTT.PKGIDX SCOTT."PKGIDX.PLSIDXTYP" J2T-138, NOTE: Wrote PL/SQL package wrap1 to file wrap1.sql. Wrote the dropping script to file drop1.sql $
The Java wrapper for the PL/SQL index-by-table type:
PkgidxPlsidxtyp.java ==================== public class PkgidxPlsidxtyp implements ORAData, ORADataFactory { public static final String _SQL_NAME = "SCOTT.PKGIDX_PLSIDXTYP"; public static final int _SQL_TYPECODE = OracleTypes.ARRAY; ...
The Java wrapper of the PL/SQL package (remember: JPublisher generates one Java class to map the entire PL/SQL package):
Pkgidx.sqlj =========== public class Pkgidx { /* connection management */ /* constructors */ // public void procidx (PkgidxPlsidxtyp P1[]) throws java.sql.SQLException { try { #sql [getConnectionContext()] { CALL wrap1.PKGIDX$PROCIDX(:INOUT (P1[0])) }; } catch(java.sql.SQLException _err) {...} ...
The conversion functions:
wrap1.sql ========= -- Declare the SQL type for the PL/SQL type PKGIDX.PLSIDXTYP CREATE OR REPLACE TYPE PKGIDX_PLSIDXTYP AS TABLE OF VARCHAR2(20); / show errors -- Declare package containing conversion functions between SQL and PL/SQL types CREATE OR REPLACE PACKAGE wrap1 AS -- Declare the conversion functions the PL/SQL type PKGIDX.PLSIDXTYP FUNCTION PL2PKGIDX_PLSIDXTYP (aPlsqlItem PKGIDX.PLSIDXTYP) RETURN PKGIDX_PLSIDXTYP; FUNCTION PKGIDX_PLSIDXTYP2PL(aSqlItem PKGIDX_PLSIDXTYP) RETURN PKGIDX.PLSIDXTYP; PROCEDURE PKGIDX$PROCIDX (P1 IN OUT PKGIDX_PLSIDXTYP); END wrap1; / show errors CREATE OR REPLACE PACKAGE BODY wrap1 IS FUNCTION PL2PKGIDX_PLSIDXTYP(aPlsqlItem PKGIDX.PLSIDXTYP) RETURN PKGIDX_PLSIDXTYP IS aSqlItem PKGIDX_PLSIDXTYP; BEGIN -- initialize the table aSqlItem := PKGIDX_PLSIDXTYP(); aSqlItem.EXTEND(aPlsqlItem.COUNT); FOR I IN aPlsqlItem.FIRST..aPlsqlItem.LAST LOOP aSqlItem(I + 1 - aPlsqlItem.FIRST) := aPlsqlItem(I); END LOOP; RETURN aSqlItem; END PL2PKGIDX_PLSIDXTYP; FUNCTION PKGIDX_PLSIDXTYP2PL(aSqlItem PKGIDX_PLSIDXTYP) RETURN PKGIDX.PLSIDXTYP IS aPlsqlItem PKGIDX.PLSIDXTYP; BEGIN FOR I IN 1..aSqlItem.COUNT LOOP aPlsqlItem(I) := aSqlItem(I); END LOOP; RETURN aPlsqlItem; END PKGIDX_PLSIDXTYP2PL; PROCEDURE PKGIDX$PROCIDX (P1 IN OUT PKGIDX_PLSIDXTYP) IS P1_ SCOTT.PKGIDX.PLSIDXTYP; BEGIN P1_ := wrap1.PKGIDX_PLSIDXTYP2PL(P1); SCOTT.PKGIDX.PROCIDX(P1_); P1 := wrap1.PL2PKGIDX_PLSIDXTYP(P1_); END PKGIDX$PROCIDX; END wrap1; / show errors exit
You need to add the following entry into a properties file:
jpub.addtypemap= SCOTT.PKGIDX.PLSIDXTYP:PkgidxPlsidxtyp:PKGIDX_PLSIDXTYP: WRAP1.PKGIDX_PLSIDXTYP2PL:WRAP1.PL2PKGIDX_PLSIDXTYP
The -plsqlindextable=<table size>
option has two functions:
On one hand, it instructs JPublisher to map PL/SQL index-by-table of scalar types into Java arrays.
On the other hand, this option specifies the capacity of the PL/SQL index-by-table, required by JDBC statements (see Chapter 8 for a description of JDBC methods to support PL/SQL index-by-table). In the following example, the generated Java program IndexbyTablePackage.java
uses these SQL statements:
For invoking TEST_TAB:
"BEGIN SCOTT.IDX_TAB_PKG.TEST_TAB1( :1 /*[32]*/) ; END;";
For invoking ECHO_IDX_TAB2:
"BEGIN :1 := /*[32]*/ SCOTT.IDX_TAB_PKG.ECHO_IDX_TAB2( n :2 /*[32]*/) ; END;";
and so on.
Create the PL/SQL package containing the index-by-table:
create or replace package idx_tab_pkg as type idx_tab1 is table of varchar2(111) index by binary_integer; type idx_tab2 is table of number index by binary_integer; type varray_tab is varray(100) of varchar2(20); type nested_tab is table of varchar2(20); type rec1 is record (a idx_tab1, b idx_tab2); function echo_idx_tab1(a idx_tab1) return idx_tab1; function echo_idx_tab2(a idx_tab2) return idx_tab2; function echo_varray_tab(a varray_tab) return varray_tab; function echo_nested_tab(a nested_tab) return nested_tab; function echo_rec1(a rec1) return rec1; procedure test_tab1(xxx idx_tab1); procedure test_tab2(yyy idx_tab2); end; / show errors create or replace package body idx_tab_pkg is function echo_idx_tab1(a idx_tab1) return idx_tab1 is begin return a; end; function echo_idx_tab2(a idx_tab2) return idx_tab2 is begin return a; end; function echo_varray_tab(a varray_tab) return varray_tab is begin return a; end; function echo_nested_tab(a nested_tab) return nested_tab is begin return a; end; function echo_rec1(a rec1) return rec1 is begin return a; end; procedure test_tab1(xxx idx_tab1) is begin null; end; procedure test_tab2(yyy idx_tab2) is begin null; end; end; / show errors
And here is the JPublisher invocation and the feedback of code generation:
$jpub -user=scott/tiger - sql=idx_tab_pkg:IndexbyTablePackage#IndexbyTableIntf - plsqlindextable=32 SCOTT.IDX_TAB_PKG SCOTT."IDX_TAB_PKG.VARRAY_TAB" SCOTT."IDX_TAB_PKG.NESTED_TAB" SCOTT."IDX_TAB_PKG.REC1" SCOTT."IDX_TAB_PKG.IDX_TAB1" SCOTT."IDX_TAB_PKG.IDX_TAB2" J2T-138, NOTE: Wrote PL/SQL package JPUB_PLSQL_WRAPPER to file plsql_wrapper.sql. Wrote the dropping script to file plsql_dropper.sql
The following Java wrapper classes and interfaces are generated, along with the Java wrapper class for the PL/SQL package (in the absence of the -notranslate
option, the intermediate .sqlj
files have been compiled and removed):
$ls *.java IndexbyTableIntf.java IndexbytableintfNestedTab.java IndexbytableintfRec1Ref.java Indexbytableintfrec1IdxTab1.java IndexbyTablePackage.java IndexbytableintfRec1.java IndexbytableintfVarrayTab.java Indexbytableintfrec1IdxTab2.java $ IndexbyTableIntf.java ===================== import java.sql.SQLException; import sqlj.runtime.ref.DefaultContext; import sqlj.runtime.ConnectionContext; import java.sql.Connection; public interface IndexbyTableIntf{ public IndexbytableintfVarrayTab echoVarrayTab ( IndexbytableintfVarrayTab A) throws java.sql.SQLException; public void testTab2 ( java.math.BigDecimal[] YYY) throws java.sql.SQLException; public void testTab1 ( String[] XXX) throws java.sql.SQLException; public java.math.BigDecimal[] echoIdxTab2 ( java.math.BigDecimal[] A) throws java.sql.SQLException; public IndexbytableintfNestedTab echoNestedTab ( IndexbytableintfNestedTab A) throws java.sql.SQLException; public IndexbytableintfRec1 echoRec1 ( IndexbytableintfRec1 A) throws java.sql.SQLException; public String[] echoIdxTab1 ( String[] A) throws java.sql.SQLException; }
Notice the plsql_dropper.sql
and plsql_wrapper.sql
; in the absence of -plsqlpackage
option JPublisher used the default JPUB_PLSQL_WRAPPER
name for the generated PL/SQL wrapper.
At this stage, the PL/SQL index-by-table type, the Java wrapper classes, the SQL collection type, and the conversion functions can be used for publishing PL/SQL packages that contain PL/SQL index-by-table types.
Streams AQ is a robust database-backed messaging and information integration system, which comprises queues, topics, and streams. A queue is a one-to-one message pipe with a specific payload type. A topic is a one-to-many message pipe with a specific payload type. A stream is a queue or topic with SYS.ANYDATA
payload type. JPublisher maps a queue, a topic, or a stream as a Java class, which uses the Java Message Service (JMS) API.
$ jpub -user=SCOTT/TIGER -sql=AQNAME:javaName
Java in the database (Java DB), can be exposed by JPublisher in many ways: to client/middle-tier Java, to Web Services, and to PL/SQL.
The –java
option publishes Java DB to client-side or middle-tier Java through a client-side stub—see Chapter 3. It supports JDBC types, Java Beans, arrays of supported types, and serializable types. This option is used under the covers by the Web Service Assembler utility (WSA) for publishing Java DB as Web service (see Chapter 15).
The -dbjava
option publishes static methods in Java DB to PL/SQL through the generation of Call Spec or PL/SQL wrappers on top of Java in the database. This option subsumes -java
, since it generates a client-side stub as well, supports for more types, and works with pre-10g database versions. When coupled with -proxyopts
, it also publishes instance methods at the expense of an additional server-side Java wrapper.
JPublisher is a versatile code generator, which offers many more possibilities than this chapter could cover. It shields the Java developer from the learning curve and many of the logistical details and inconveniences of manually creating Java database access programs. Furthermore, JPublisher simplifies the process of publishing database operations as Web services as well as the process of consuming external Web services from within the database, which is the topic of the next chapter.
3.16.135.36