As Published In January/February 2004

The improved Oracle JPublisher maps database entities to Java and Web services.

Programming client-side and middle-tier Java and J2EE applications to represent database entities, such as user-defined SQL object types and collection types, can be challenging and error-prone if you do all the coding manually. As an alternative, you can use the Oracle JPublisher utility, which produces the corresponding Java classes automatically according to your command-line input (or according to a few mouse clicks using the Oracle JDeveloper integrated development environment).

In this article, I will give you an overview of JPublisher and describe how it simplifies mapping database entities to Java, including types that you cannot use directly from Java Database Connectivity (JDBC). Then I'll show you how to map database operations and business logic to your Java client, J2EE components, and Web services clients with one or two JPublisher commands. Finally, I'll describe how JPublisher simplifies invoking external Web services from within the database.

Overview of Jpublisher

Oracle JPublisher is a utility for converting and exchanging structured object data between Java and SQL, and for accessing other database resources such as PL/SQL packages and Java stored procedures. It generates Java classes to represent database entities such as SQL objects and operations, PL/SQL packages and procedures, and server-side Java classes. You can use the generated classes through JDBC in your Java clients, servlets, JavaServer Pages (JSP), Enterprise JavaBeans (EJB), and Web services.

JPublisher generates classes representing the following database entities:

User-defined SQL object types. For each object type, JPublisher generates a type.java file for a class that represents the object. This includes accessor methods to get and set each attribute of the object type. Also, if your object types include methods implemented as stored procedures, JPublisher will generate wrapper methods to invoke these methods on object instances.

Object reference types (REF types). For a SQL object reference type, JPublisher generates a typeRef.java file to model references to the object type in Java. This includes methods to access the actual object value through an object reference.

User-defined SQL collection types. For each collection type, JPublisher generates a type.java file for a class that represents the collection. For nested tables, the generated class has methods to get and set the nested table as an entire array and methods to get and set individual elements of the table.

User-defined OPAQUE types. Whenever JPublisher encounters a SQL OPAQUE type for which no type correspondence has been provided, it will generate a type.java file to publish a Java wrapper class. The OPAQUE payload is represented as a byte array and may be processed from a subclass of the generated wrapper.

PL/SQL BOOLEAN. PL/SQL BOOLEAN is mapped into the Java Boolean type. This requires the presence of the SYS.SQLJUTL package, which gets installed by the sqljutil.sql script in the database where the JPublisher-generated code is to be run. The script is installed by default in Oracle Database 10g.

PL/SQL RECORD and TABLE types. For RECORD and TABLE types, JPublisher generates corresponding SQL objects (for RECORD types) or SQL VARRAY types (for TABLE types), along with conversion functions for mapping between PL/SQL and SQL types.

PL/SQL INDEXED-BY tables. If you use Oracle Database 10g JDBC Oracle Call Interface (OCI) drivers and require publishing of scalar indexed-by tables, you can use direct mapping between Java and such types (represented as Java arrays).

Running the JPublisher Utility

To run JPublisher on user-defined datatypes and PL/SQL packages in the database that you want to convert to Java classes, you must establish a database connection to allow access to the SQL types or PL/SQL packages you are mapping. Use the -user and -url options for this purpose, as in the following examples:

% jpub -user=scott/tiger -url=jdbc:oracle:oci:@ ...

% jpub -user=scott/tiger -url=jdbc:oracle:thin:@host:port/
servicename ...

The first example uses the Oracle JDBC OCI driver, and the second example uses the Oracle JDBC Thin driver. The following example is based on the Order Entry (OE) schema that is part of the Oracle Database 10g sample schemas. Assuming that the password for the OE schema is OE, use the following to publish the SQL object type CATEGORY_TYP, where CategoryTyp is the desired name of the corresponding Java class:

% jpub -user=OE/OE -url=jdbc:oracle:oci:@ 
-sql=CATEGORY_TYP:CategoryTyp

The -sql option specifies the types and packages to be published.

Oracle JPublisher generates and compiles the corresponding Java classes.

Mapping Database Operations

In this section, I will compare and contrast manual mapping of a PL/SQL package to Java with the JPublisher approach. My example includes the use of a PL/SQL-specific type that cannot be accessed directly from Java (in this case, PL/SQL RECORD), where JPublisher offers additional support.

create or replace package action 
as type emprec 
is record (name varchar2(10), 
age number);
procedure foo (hired timestamp, 
emp in out emprec);
end;
/

Suppose you want to map the ACTION package into a Java class Action. JDBC supports mapping from TIMESTAMP to java.sql.TimeStamp; however, instead of using the JDBC-supported mapping, you want to map from TIMESTAMP to Date. The corresponding Java foo method would have the following signature:

public void foo(java.util.Date hired, EmpRecSql[] emp_inout)

The manual approach. The following steps outline how you would manually set up the PL/SQL-to-Java mapping for this ACTION package example:

1. Create a SQL object type—for example, EMPREC_SQL—for the PL/SQL RECORD type, EMPREC.
2. Create a Java type for the SQL type you created. For example, create the Java type EmpRecSql for the SQL type EMPREC_SQL.
3. In Java, convert each argument to a desired Java type. For example, convert SQL TIMESTAMP to Java Date.
4. Pass each IN or IN OUT argument to a PL/SQL block via JDBC. For example, pass EmpRecSql and TimeStamp to foo.
5. In the PL/SQL block, convert each IN or IN OUT argument to the correct type for the PL/SQL stored procedure. For example, convert EMPREC_SQL to EMPREC.
6. Call the PL/SQL stored procedure.
7. In PL/SQL, convert each OUT argument, IN OUT argument, or function result from the type that JDBC does not support to the corresponding type that JDBC does support. For example, convert EMPREC to EMPREC_SQL.
8. Return each OUT argument, IN OUT argument, or function result from the PL/SQL block.
9. In Java, convert each OUT argument, IN OUT argument, or function result from the type JDBC does support to the type it does not support. For example, convert from TimeStamp to Date.

The Oracle JPublisher approach. The following JPublisher command directly publishes the ACTION package:

%  jpub -u scott/tiger -s action:Action
   -style=webservices9 
   -outarguments=array 
   -plsqlfile=actionWrap.sql

Note that the -style=webservices9 option specifies mappings from TimeStamp to Date. The -outarguments= array indicates that OUT arguments are passed via one-element arrays.

JPublisher generates the PL/SQL wrapper script that you must execute before using the JPublisher-generated code. The script contains the definition of the SQL type EMPREC_SQL for the PL/SQL EMPREC type and conversion functions between EMPREC and EMPREC_SQL. Also, the script contains a wrapper for the PL/SQL stored procedure foo.

The following command executes the generated script:

% sqlplus scott/tiger @actionWrap.sql

That's all. Compare JPublisher's two commands for the PL/SQL-to-Java mapping for the ACTION package to the many commands required in the manual approach.

Here is a brief description of each file JPublisher generates for the ACTION PL/SQL-package-to-Java mapping:

• Action.java, the Java base class generated for the ACTION package
• ActionBase.java, the Java user subclass extended from ActionBase
• ActionUser.java, the Java classes extended from ActionBase, with Java type mappings, such as from TimeStamp to Date
• ActionEmprec.java, the Java interface generated for EMPREC_SQL
• ActionEmprecBase.java, the Java base class generated for EMPREC_SQL
• ActionEmprecUser.java, the Java user subclass extending ActionEmprecBase
• ActionEmprecUserRef.java, the Java type for the REF type of EMPREC_SQL
• actionWrap.sql, the SQL and PL/SQL scripts defining the SQL type for EMPREC and the conversion function between EMPREC and its SQL type

Database as Web Services Provider

Oracle Database 10g includes new JPublisher features that support database Web services. The combination of Oracle Database 10g and Oracle Application Server 10g allows you to publish PL/SQL packages and stored procedures, SQL queries, SQL data manipulation language (DML) statements, and server-side Java classes as Web services. JPublisher-generated classes can be exposed as Web services by the Oracle Application Server Web Services Assembler tool.

Publishing SQL queries or DML statements. JPublisher is able to publish a specified SELECT, UPDATE, INSERT, or DELETE statement as a method of a Java class. This class, in turn, can be published as a Web service.

Style files. In publishing server-side Java classes, JPublisher uses style files that specify Java-to-Java type mappings to ensure that generated classes can be used in Web services.

Generating Java interfaces. JPublisher can generate Java interfaces as well as classes. This eliminates the need to manually generate Java interfaces that represent the API from which Web Services Description Language (WSDL) content is to be generated.

Supported SQL types. The following SQL types can be published in database Web services signatures: simple SQL types, including DATE; SQL object types; SQL VARRAY types; SYS.XMLTYPE; and REF CURSOR.

Database as Web Services Consumer

When the database is a Web services consumer, Web services client code is written in SQL, PL/SQL, or Java and run inside the database, invoking external Web services. For code running in the database to access other Web services, you need to be able to call out to these services. You can use the manual approach with Simple Object Access Protocol (SOAP) programming, or you can use JPublisher. The following steps will help you build your first database Web services consumer using JPublisher.

Step 1: Verify the database setup. In Oracle Database 10g, the libraries sqljutl.jar and utl_dbws_jserver.jar from [Oracle_Home]/sqlj/lib, and the package SYS.UTL_DBWS, come preloaded in the database at installation time.

Step 2: Generate and load the client proxy and Java and PL/SQL wrappers. JPublisher fully automates the tasks of generating Web services client proxy code by compiling it, putting it into a Java archive (JAR) file, and loading it into the database. Use the following format to generate the proxy code:

% jpub 
-proxywsdl=URL_of_Web_Service_WSDL 
-user=username/password

Additionally, you can specify

-endpoint=external_Web_Services_URL

if this is different from what is provided in the WSDL itself, and -url=JDBC_database_URL if different from the default database instance accessed with the JDBC OCI driver. The following example uses several options:

% jar xvf dist/javacallout.jar 
  META-INF/HelloServiceEJB.wsdl 

% jpub -proxywsdl=META-INF/
HelloServiceEJB.wsdl 
-dir=genproxy 
-package=javacallout 
-user=scott/scott 
-endpoint=http://localhost:8888/
javacallout/javacallout 

• The -proxywsdl option specifies the WSDL file, for which JPublisher will generate the static Java proxy classes, Java and PL/SQL wrappers, and PL/SQL scripts.
• The -dir option specifies the root directory for generated code and script.
• The -package option specifies the package name for the generated Java code.

After code generation, JPublisher automatically loads the following files into the database:

• genproxy/javacallout. Java classes under this directory are generated by the Web Services WSDL compilation tool called by JPublisher.
• genproxy/HelloServiceEJBJPub.java. The HelloServiceEJBJPub.java class defines the method sayHello() as a static Java method.
• genproxy/plsql_wrapper.sql. This is the PL/SQL wrapper for the Web service client (HelloServiceEJBJPub.java).
• genproxy/plsql_dropper.sql. This is to remove PL/SQL types and packages defined by plsql_wrapper.sql.
• genproxy/plsql_grant.sql. This (run under SYS) is to grant necessary privileges for the Java client proxy to call the Web service.
• genproxy/plsql_revoke.sql. This is to revoke the privileges granted by plsql_grant.sql.
• genproxy/jpub_proxyload.log. This is the log file for loading the generated Java files and installing the plsql_wrapper.sql script.

Step 3: Grant privileges. The script plsql_grant.sql is for SYS to grant necessary privileges for SCOTT to execute the loaded client proxy:

SQL> conn / as sysdba 
SQL> @genproxy/plsql_grant.sql

Step 4: Call out to the external Web service. To invoke an external Web service from the database, declare and run the run_plsql_proxy.sql script or run the PL/SQL block below:

SQL> set serveroutput on 
SQL> declare x varchar2(100); 
     begin 
       x:=JPUB_PLSQL_WRAPPER.sayHello(
               'Hello from database'); 
     dbms_output.put_line(x); 
     end; 
    /

Step 5: Clean up. Remove the PL/SQL wrapper and revoke the granted privileges, using SQL*PLUS.

SQL> conn scott/tiger 
SQL> @genproxy/plsql_dropper.sql 
SQL> conn / as sysdba 
SQL> @genproxy/plsql_revoke.sql

Remove the loaded Java classes from the database, using the dropjava tool:

dropjava -u scott/tiger 
genproxy/wsdlGenerated.jar 

Conclusion

JPublisher 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.

Kuassi Mensah ( kuassi.mensah@oracle.com) is a group product manager in the Server Technologies division at Oracle Corporation.