28 Using Java in the Database

JDeveloper supports features that allow you to write and execute Java programs that access Oracle Databases.

This chapter includes the following sections:

28.1 About Using Java in the Database

There are three aspects to using Java in the database:

  • Using SQLJ or JDBC, both of which can be used to embed SQL in Java programs.

  • Accessing database objects and PL/SQL packages from Java programs.

  • Using Java stored procedures, which are Java methods that reside and run inside the database.

28.2 Choosing SQLJ or JDBC

JDeveloper supports two mechanisms for embedding SQL in Java programs:

  • SQLJ: If you know the PL/SQL tables and columns involved at compile time (static application), you can use SQLJ. SQLJ is an industry standard for defining precompiled SQL code in Java programs.

    SQLJ allows you to code at a higher level than JDBC, by embedding SQL statements directly in your Java code. The SQLJ precompiler that is integrated into JDeveloper translates the SQL into Java plus JDBC code for you. SQLJ with JDeveloper lets you write and debug applications much faster than you can using just JDBC.

  • JDBC: If you require fine-grained control over database access, or if you are developing an application that requires precise information about database (or instance) metadata, you can code your application entirely in Java using the JDBC API.

You can mix JDBC calls with SQLJ statements in your program. One way to do this is through connection context sharing.

28.2.1 Using SQLJ

SQLJ is a standard way to embed static SQL statements in Java programs. SQLJ applications are portable and can communicate with databases from multiple vendors using standard JDBC drivers.

SQLJ provides a way to develop applications both on the client side and on the middle-tier that access databases using Java. Developing in SQLJ is fast and efficient, and JDeveloper completely supports SQLJ development. You can create or include SQLJ files in your JDeveloper projects. When you compile a project that contains SQLJ source files, JDeveloper automatically calls the SQLJ translator, or precompiler. The translator produces completely standard Java source code, with calls to JDBC methods to provide the database support. JDeveloper then compiles the Java that the SQLJ translator generates.

For more information, see the Oracle Database SQLJ Developer's Guide.

28.2.2 Using Oracle JDBC Drivers

JDBC provides Java programs with low-level access to databases.

Oracle JDBC drivers can be grouped into two main categories with the following attributes:

  • Java-based drivers (thin client / Type 4 driver):

    • are implemented entirely in Java

    • are highly portable

    • can be downloaded from the server system to a web browser

    • can connect using the TCP/IP protocol

    • are the only option for applets (due to security restrictions)

  • OCI-based drivers (Type 2 driver):

    • are implemented using native method libraries (OCI DLLs)

    • have OCI libraries that must be available on the client system

    • cannot be downloaded to a browser

    • can connect using any Net8 protocol

    • deliver high performance

    The following figure illustrates how JDBC components and the driver run in the same memory space as an applet.

    Figure 28-1 JDBC Components

    JDBC components running in same memory space as an applet

The following figure illustrates how the Oracle JDBC OCI drivers run in a separate memory space from your Java application. These JDBC drivers make OCI calls to a separately loaded file.

Figure 28-2 Oracle JDBC OCI Drivers

JDBC OCI drivers run in a separate memory space

Note:

Take care not to confuse the terms JDBC and JDBC drivers. All Java applications, no matter how they are developed or where they execute, ultimately use the JDBC-level drivers to connect to Oracle. However, coding using the pure JDBC API is low-level development, akin to using the Oracle Call Interface (OCI) to develop a database application. Like the OCI, the JDBC API provides a very powerful, but also very code-intensive, way of developing an application.

28.2.3 SQLJ versus JDBC

How does SQLJ compare to JDBC? Here are some of the advantages that SQLJ offers over coding directly in JDBC:

  • SQLJ programs require fewer lines of code than JDBC programs. They are shorter, and hence easier to debug.

  • SQLJ can perform syntactic and semantic checking on the code, using database connections at compile time.

  • SQLJ provides strong type-checking of query results and other return parameters, while JDBC values are passed to and from SQL without having been checked at compile time.

  • SQLJ provides a simplified way of processing SQL statements. Instead of having to write separate method calls to bind each input parameter and retrieve each select list item, you can write one SQL statement that uses Java host variables. SQLJ takes care of the binding for you.

However, JDBC provides finer-grained control over the execution of SQL statements and offers true dynamic SQL capability. If your application requires dynamic capability (discovery of database or instance metadata at runtime), then you should use JDBC.

28.2.4 Embedding SQL in Java Programs with SQLJ

You have to perform a number of tasks to embed SQL in Java programs with SQLJ.

28.2.4.1 How to Create SQL Files

You can create a new SQL (.sql) file and add it to the current project.

To create a SQL file:

  1. In the Applications window, select the project.

  2. From the main menu, choose File > New to open the New Gallery.

  3. In the New Gallery, in the Categories tree, select Database Tier then Database Files. In the Items list, double-click SQL File.

  4. In the Create SQL File dialog, provide the details to describe the new file.

    For more information at any time, press F1 or click Help from within the dialog.

  5. Click OK.

An empty SQL file is added to the current project and opened for editing.

28.2.4.2 How to Create SQLJ Classes

Create a new SQLJ (.sqlj) file and add it to the current project.

To create a new SQLJ file:

  1. In the Applications window, select the project.

  2. From the main menu, choose File > New to open the New Gallery.

  3. In the Categories tree, expand Database Tier and select Database Files.

    For more information at any time, press F1 or click Help from within the dialog.

  4. In the Items list, double-click SQLJ Class to open the Create SQLJ Class dialog.

  5. In the Create SQLJ File dialog, provide the details to describe the new file.

    For more information at any time, press F1 or click Help from within the dialog.

  6. Click OK.

A skeleton SQLJ class will be added to the current project and be opened for editing.

28.2.4.3 How to Compile SQLJ Classes

You can compile SQLJ classes into Java .class files.

To compile a SQLJ class:

  1. Set the project's SQLJ translator options to control how the file is compiled in the Compiler > SQLJ page of the Project Properties dialog.

  2. In the Applications window, locate and select the SQLJ class.

  3. Right-click the class, and choose Make.

The status bar at the bottom of the JDeveloper window shows the result of the compilation. Errors, if any, are listed in the log window.

28.2.4.4 How to Use Named SQLJ Connection Contexts

A SQLJ executable statement can designate a connection context object that specifies the database connection where the SQL operation in that clause will execute. If the SQLJ statement omits the connection context clause, then the default connection context is used.

28.2.4.5 How to Declare a SQLJ Connection Context Class

A connection context is an object of a connection context class, which you define using a SQLJ connection declaration.

To declare a context class:

  1. Declare a context class.

    Named connection contexts are not required: SQLJ statements that omit the connection context name use the default connection context.

    For example, this statement declares the context class MyConnectionContext:

    #sql context MyConnectionContext;

    Context classes extend sqlj.runtime.ref.ConnectionContextImpl and implement sqlj.runtime.ConnectionContext.

After you have declared a context class, create a context object.

28.2.4.6 How to Create a Connection Context Object

Before it can be used in an SQLJ statement, a declared connection context must be created.

To create a context object:

  1. Named connection contexts are not required: SQLJ statements that omit the connection context name use the default connection context.

    For example, use this statement to create an instance thisCtx for the connection context class MyConnectionContext:

    MyConnectionContext thisCtx = new MyConnectionContext (myPath, myUID, myPasswd, autocommit

28.2.4.7 How to Debug SQLJ Classes

You debug SQLJ code by debugging the SQLJ source directly, not the generated Java code.

SQLJ is debugged in JDeveloper in the same manner as other source code.

For more information, see the Oracle Database SQLJ Developer's Guide.

28.2.4.8 How to Set SQLJ Translator Options

You can control the translation of SQLJ classes through the controls in the Project Properties dialog:

  • Provide syntactic as well as semantic checking of SQL code.

  • Provide syntax and type checking on the SQL statements.

  • Test the compatibility of Java and SQL expressions at compile time.

  • Specify a connection to a database server.

  • Check the semantics of your SQL statements against the database schemas specified by connection contexts.

To set the SQLJ translator options:

  1. In the Applications window, select the project that contains the SQLJ file.

  2. Choose Application > Project Properties > Compiler and select SQLJ.

  3. In the SQLJ panel, set the compilation options. These include:

    • The level at which translater warnings should be set.

    • Type of code generation.

    • Whether you want to perform SQL semantic checking against a database schema.

    • Additional options to be used in the SQLJ translator.

  4. Click OK.

You can set SQLJ translator properties for all projects by choosing Default Project Properties from the Application menu

28.2.4.9 How to Use SQLJ Connection Options

SQLJ connection options specify the database connection for online checking. The general form for connection options is

-option@context=value

where option is one of the four options listed below.

The context tag is a connection context type, which permits the use of separate exemplar schemas for each of the connection contexts. If you omit the connection context type, the value will be used for any SQL statements that use the default connection context. The driver option does not allow a context tag.

The options are:

  • user This option specifies the user name for connecting to a database in order to perform semantic analysis of the SQL expressions embedded in a SQLJ program. It contains the user name, for example:

    -user=hr

    The user command line option may include a connection context type. For example:

    -user@Ctx1=hr

    Whenever a user name is required for the connection to a database context Ctx1, SQLJ uses the user option that was tagged with Ctx1. If it can not find one, SQLJ issues a message and looks for an untagged user option to use instead.

    Specifying a user value indicates to SQLJ that online checking is to be performed. If you do not specify the user option, SQLJ does not connect to the database for semantic analysis. There is no default value for the user option.

    If you have turned on online checking by default (by specifying, for example, -user=hr), then in order to disable online checking for a particular connection context type Ctx2, you must explicitly specify an empty user name, for example:

    -user@Ctx2Z

  • password This option specifies a password for the user. The password will be requested interactively if it is not supplied. This option can be tagged with a connection context type. Examples of the two forms are:

    -password=hr
-password@Ctx1=hr

  • url This option specifies a JDBC URL for establishing a database connection. The default is jdbc:oracle:oci9:@. This option can be tagged with a connection context type. For example:

    -url=jdbc:oracle:oci8:@ -url@Ctx1=jdbc:oracle:thin:@<local_host>:1521:orcl

  • driver This option specifies a list of JDBC drivers that should be registered in order to interpret JDBC connection URLs for online analysis. The default is oracle.jdbc.driver.OracleDriver. For example:

    -driver=sun.jdbc.odbc.JdbcOdbcDriver,oracle.jdbc.driver.OracleDriver

    This option cannot be tagged with a connection context type.

28.2.5 Embedding SQL in Java Programs with JDBC

JDBC provides Java programs with low-level access to databases.

For more information, see the Oracle Database SQLJ Developer's Guide.

28.2.5.1 How to Choose a JDBC Driver

JDBC uses a driver manager to support different drivers, so that you can connect to multiple database servers. To connect your database application to a data server, you must have available the appropriate JDBC driver. JDeveloper provides the Oracle Thin and OCI JDBC drivers. OCI for Oracle is the default driver. If you wish you may install a non-default JDBC driver.

Consider the following when choosing a JDBC driver to use for your application or applet:

  • If you are writing an applet, you must use the JDBC Thin driver. JDBC OCI-based driver classes will not work inside a Web browser, because they call native (C language) methods.

    Note:

    When the JDBC Thin driver is used with an applet, the client browser must have the capability to support Java sockets.

  • If you are writing a client application for an Oracle client environment and need maximum performance, then choose the JDBC OCI driver.

  • For code that runs in an Oracle server acting as a middle tier, use the server-side Thin driver.

    Note:

    JDeveloper does not supply the server-side Thin driver.

  • If your code will run inside the target Oracle server, then use the JDBC server-side internal driver to access that server. You can also access remote servers using the server-side Thin driver.

    Note:

    JDeveloper does not supply the server-side Thin driver.

  • If performance is critical to your application, you want maximum scalability of the Oracle server, or you need the enhanced availability features like TAF (Transparent Application Failover) or the enhanced proxy features like middle-tier authentication, then choose the OCI driver.

28.2.5.2 How to Modify a Project to Use a Non-Default JDBC Driver

If your JDeveloper programming environment has been modified to allow the use of a non-default JDBC driver, you can modify the current project to use the new driver by performing these steps.

To modify the project:

  1. In the Applications window, select the project.

  2. Choose Application > Project Properties > Profiles > Development > Libraries.

  3. Select the driver's library from the list displayed, and transfer it to the Selected Libraries list. The driver's library was created when you registered the driver.

  4. If necessary, order the list of selected libraries so that the library you have just added appears before other driver libraries, or libraries that pull in other driver libraries. These include:

    • Oracle JDBC

    • Enterprise Java Beans

    If necessary, select the library you added and drag it up to the top of the list.

  5. Click OK to save your changes and close the dialog.

28.2.5.3 How to Code a JDBC Connection

You can establish a database connection in pure JDBC code.

A summary is given here, but for more information, see "Getting Started" in the Oracle Database JDBC Developer's Guide.

To code a JDBC Connection:

  1. Import the JDBC classes using the statement

    import java.sql.*;

    This statement is required for all JDBC programming.

  2. Register the JDBC drivers. If you are using an Oracle JDBC driver and use a constructor that uses the static Oracle.connect() method to set the default connection, the Oracle JDBC drivers are automatically registered.

    Alternatively, if you are using an Oracle JDBC driver, but do not use Oracle.connect(), then you must manually register the Oracle Driver class using the statement

    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

    If you are not using an Oracle JDBC driver, then you must register an appropriate driver class:

    DriverManager.registerDriver(new mydriver.jdbc.driver.MyDriver());

    In any case, you must also set your connection URL, user name, and password.

  3. Get a connection to a data server using a getConnection() method, for example

    Connection conn = DriverManager.getConnection(parameters...);

28.3 Accessing Oracle Objects and PL/SQL Packages using Java

Use Oracle JPublisher to access Oracle objects and PL/SQL packages from your Java programs. Oracle JPublisher lets you specify and customize the mapping of Oracle object types, reference types, and collection types to Java classes in a strongly typed paradigm

You can use JPublisher to access Oracle objects and PL/SQL packages from your Java programs. JPublisher lets you specify and customize the mapping of Oracle object types, reference types, and collection types to Java classes in a strongly typed paradigm.

Also, SQLJ programmers who want to call stored procedures declared in PL/SQL packages can use JPublisher to generate SQLJ wrapper classes for the packages. The SQLJ wrapper classes let you invoke the PL/SQL stored procedures, and pass and return values from them, directly from your SQLJ program.

To access Oracle objects and PL/SQL packages using Java:

  1. Create the desired object data types (Oracle objects) and PL/SQL packages in the database. It is recommended that any custom classes or interfaces you use in Oracle Database implement the oracle.sql.CustomDatum interface.

  2. Use JPublisher to generate source code — Java and SQLJ files — that represents the Oracle objects, PL/SQL packages, user-defined types, and REF types.

  3. Import these classes into your application code.

  4. Use the methods in the generated classes to access and manipulate the Oracle Objects and their attributes.

  5. Compile all classes (the code generated by Oracle JPublisher and your code). The SQLJ compiler compiles the .sqlj files, and the Java or SQLJ compiler compiles the .java files.

  6. Run your compiled application.

This process is illustrated in the following figure:

Figure 28-3 Oracle Objects and PL/SQL Packages

Oracle objects and PL/SQL packages

28.3.1 How to Use JPublisher

Oracle JPublisher increases your productivity by letting you access Oracle objects and PL/SQL packages from your Java programs. Oracle JPublisher lets you specify and customize the mapping of Oracle object types, reference types, and collection types (VARRAYs or nested tables) to Java classes in a strongly typed paradigm

JPublisher

JPublisher increases your productivity by letting you access Oracle objects and PL/SQL packages from your Java programs. JPublisher lets you specify and customize the mapping of Oracle object types, reference types, and collection types (VARRAYs or nested tables) to Java classes in a strongly typed paradigm.

SQLJ programmers who want to call stored procedures declared in PL/SQL packages can use JPublisher to generate SQLJ wrapper classes for the packages. The SQLJ wrapper classes let you invoke the PL/SQL stored procedures, and pass and return values from them, directly from your SQLJ program.

For more information, see the Oracle Database JPublisher User's Guide.

Object Types and JPublisher

JPublisher allows your Java language applications to use user-defined object types in Oracle Database. These objects can be user-defined objects, VARRAYs, nested tables, index-by tables, or REFs to object types. If you intend to have your Java-language application access object data, then it must represent the data in a Java format. JPublisher helps you do this by creating the mapping between object types and Java classes, and between object attribute types and their corresponding Java types.

The mapping is determined by both:

  • The selected Java mapping option.

  • The object's data type category.

Additionally, JPublisher generates get and set accessor methods for each of the object's attributes, and optionally generates a wrapper method for each of the object's stored procedures. A wrapper method is a method that invokes a stored procedure that executes in the database. Wrapper methods generated by JPublisher are always instance methods even when the original object methods are static.

The following table summarizes the types of Java classes that JPublisher generates for objects.

Table 28-1 Mapping SQL Type to Java Class

SQL type Java class mapping

user-defined object type

Java class with accessor methods to get and set each attribute of the object, and optional wrapper methods to call the object's stored procedures.

VARRAY,

nested table,

index-by table.

Java classes that can get and set the following:

  • The entire array

  • A subset of the array

  • An individual element of the array

REF to an object type

Java class to get and set the object to which the REF refers.

Classes generated by JPublisher implement either the oracle.sql.CustomDatum interface or the java.sql.SQLData interface. Either interface makes it possible to transfer object type instances between the database and your Java program. It is recommended that you use the oracle.sql.CustomDatum interface.

PL/SQL Packages and JPublisher

You might want to call stored procedures in a PL/SQL package from your Java application. The stored procedure can be implemented in PL/SQL, or it can be a Java method that has been published to PL/SQL. Java arguments and functions are passed to and returned from the stored procedure.

To help you do this, you can direct JPublisher to create a class containing a wrapper method for each subprogram in the package. Like object methods, the wrapper methods generated for each subprogram are always instance methods even when the original method is static. The wrapper methods that JPublisher generates provide a convenient way to invoke PL/SQL stored procedures from Java code or to invoke a Java stored procedure from a client Java program.

JPublisher lets you generate Java wrappers by selecting an individual package, or by selecting the Packages node to select all of the packages in the schema. If you call PL/SQL code that includes subprograms at the top-level, JPublisher generates a single class containing a wrapper method for each top-level subprogram.

For PL/SQL functions, whether you generate Java for a single PL/SQL function or multiple functions, JPublisher generates a single class. For a single function, the class contains a single wrapper method for the function. For multiple functions, the class contains a wrapper method for each function.

For PL/SQL procedures, whether you generate Java for a single PL/SQL procedure or multiple procedures, JPublisher generates a single class. For a single procedure, the class contains a single wrapper method for the procedure. For multiple procedures, the class contains a wrapper method for each procedure

Java Mapping Options

The mapping options you select for data type categories determine the set of type mappings that JPublisher uses to translate object types and PL/SQL packages into Java classes:

  • For object types, JPublisher applies the mappings to the object's attributes and to the arguments and results of any methods included with the object. The mappings control the types that the generated accessor methods should support, that is, what types the get methods should return and the set methods should require.

  • For PL/SQL packages, JPublisher applies the mappings to the arguments and results of the methods.

  • For a collection type (that is, nested tables and VARRAYs), JPublisher applies the mappings to the element type of the collection.

  • For user-defined types (usertypes category) JPublisher generates CustomDatum classes or SQLData classes and generates code for collection and REF types.

You may select from the following mapping options:

  • Oracle Mapping represents data in PL/SQL format.

  • JDBC Mapping represents simple data types as Java primitive types.

  • Object JDBC Mapping represents simple data types as Java wrapper classes.

  • BigDecimal Mapping uses a common class to represent all numeric types.

For more information, see the Oracle Database JPublisher User's Guide.

Mapping Built-in Types

Syntax: jpub.builtintypes={jdbc|oracle}

The builtintypes parameter (and its JPublisher wizard equivalent Built-in Types) controls type mappings for all the built-in database types except the LOB and BFILE types (controlled by the lobtypes parameter) and the different numeric types (controlled by the numbertypes parameter). The following table lists the database types affected by the builtintypes parameter, and shows their Java type mappings for builtintypes=oracle and for builtintypes=jdbc (the default).

Table 28-2 Built In Mapping Types

PL/SQL Data Type Oracle Mapping Class JDBC Mapping

CHAR

CHARACTER

LONG

STRING

VARCHAR

VARCHAR2

oracle.sql.CHAR

java.lang.String

RAW

LONG RAW

oracle.sql.RAW

byte[ ]

DATE

oracle.sql.DATE

java.sql.Timestamp

Mapping LOB Types

Syntax: lobtypes={jdbc|oracle}

The lobtypes parameter (and its JPublisher wizard equivalent LOB Types) controls type mappings for the LOB types. The following table shows how these types are mapped for lobtypes=oracle (the default) and for lobtypes=jdbc.

Table 28-3 LOB Type Mapping

PL/SQL Data Type Oracle Mapping Class JDBC Mapping Class

CLOB

oracle.sql.CLOB

java.sql.CLOB

BLOB

oracle.sql.BLOB

java.sql.BLOB

The BFILE type does not appear in this table, because it has only one mapping. It is always mapped to oracle.sql.BFILE, because there is no java.sql.BFILE class.

Mapping Numeric Types

Syntax: jpub.numbertypes={jdbc|objectjdbc|bigdecimal|oracle}

The numbertypes parameter (and its JPublisher wizard equivalent Number Types) controls type mappings for numeric PL/SQL types. Four choices are available:

  • The jdbc mapping maps most numeric database types to Java primitive types such as int and float, and maps DECIMAL and NUMBER to java.math.BigDecimal.

  • The objectjdbc mapping (the default) maps most numeric database types to Java wrapper classes such as java.lang.Integer and java.lang.Float, and maps DECIMAL and NUMBER to java.math.BigDecimal.

  • The bigdecimal mapping maps all numeric database types to java.math.BigDecimal. The oracle mapping maps all numeric database types to oracle.sql.NUMBER.

  • The oracle mapping maps all numeric database types to oracle.sql.NUMBER.

The following table lists the PL/SQL types affected by the numbertypes option, and shows their Java type mappings for numbertypes=jdbc and numbertypes=objectjdbc (the default).

Table 28-4 Numeric Type Mapping

PL/SQL Data type JDBC Mapping Class Object JDBC Mapping

BINARY_INTEGER

INT

INTEGER

NATURAL

NATURALN

PLS_INTEGER

POSITIVE

POSITIVEN

SIGNTYPE

int

java.lang.Integer

SMALLINT

short

java.lang.Float

REAL

float

java.lang.Double

Mapping User-Defined Types

Syntax: jpub.usertypes={oracle|jdbc}

The usertypes parameter (and its JPublisher wizard equivalent User Types) controls whether JPublisher generates CustomDatum classes or SQLData classes for user-defined types:

  • When usertypes=oracle (the default), JPublisher generates CustomDatum classes for object, collection, and REF types.

When usertypes=jdbc, JPublisher generates SQLData classes for object types. JPublisher does not generate anything for collection or REF types. Use java.sql.Array for all collection types, and java.sql.Ref for all REF types.

28.3.2 JPublisher Output

JPublisher generates a Java class for each object type that it translates. For each object type, JPublisher generates a type.java file (or a type.sqlj file if wrapper methods were requested) for the class code and a typeRef.java file for the code for the REF class of the Java type. For example, if you define an EMPLOYEE PL/SQL object type, JPublisher generates an employee.java file and an employeeRef.java file.

For each collection type (nested table or VARRAY) it translates, JPublisher generates a type.java file. For nested tables, the generated class has methods to get and set the nested table as an entire array and to get and set individual elements of the table. JPublisher translates collection types when generating CustomDatum classes but not when generating SQLData classes. JPublisher does not generate a typeRef.java file for nested tables or VARRAYs. This is because PL/SQL does not allow a REF to be made to these types.

For PL/SQL packages, JPublisher generates classes containing wrapper methods as SQLJ files. JPublisher also generates method wrappers in your class that invoke the associated package methods executing in the server. This is specified by the Include Methods option.

Note:

Since version 8.1.6, the wrapper methods that JPublisher generates to invoke stored procedures are in SQLJ only. Classes generated by JPublisher that contain wrapper methods must be compiled by SQLJ.

28.3.3 Properties Files

A properties file is an optional text file where you can specify frequently used parameters or parameters that you cannot specify in the JPublisher wizard. Note that if you need only the default output of JPublisher, then you do not need a properties file.

The properties file is designated in the JPublisher wizard.

In a properties file, you enter one (and only one) parameter and its associated value on each line. Each parameter name must be preceded with the prefix "jpub." and you cannot use any white space within a line. You can enter any parameter except the props parameter in the properties file. JPublisher processes the parameters, in order, from the top of the list to the bottom. If you specify a parameter more than once, JPublisher uses the last encountered value.

A properties file might contain the following:

jpub.case=lower
jpub.package=package1
jpub.numbertypes=jdbc
jpub.lobtypes=jdbc
jpub.builtintypes=jdbc
jpub.usertypes=jdbc
jpub.omit_schema_names
jpub.methods=true
jpub.input=mySchema.txt
jpub.sql=employee:oracleEmployee

28.3.4 How to Enhance JPublisher-Generated Classes

You can enhance the functionality of a custom Java class generated by JPublisher by adding methods and transient fields to it. For example:

  • Extend the class. That is, treat the JPublisher-generated class as a superclass, write a subclass to extend its functionality, and then map the object type to the subclass.

  • Write a new class that delegates the functionality provided by the JPublisher-generated class to a field whose type is the generated class.

  • Add methods to the class. This is not recommended if you anticipate running JPublisher at some future time to regenerate the class. If you regenerate a class that you have modified in this way, your changes (that is, the methods you have added) will be overwritten. Even if you direct JPublisher output to a separate file, you will still need to merge your changes into the file.

28.3.5 How to Extend JPublisher-Generated Classes

The Declaration Name and Use Name fields in the JPublisher wizard give you the flexibility of extending generated classes. In the Declaration Name field, enter the name of the class that you want JPublisher to generate from the given database object. In the Use Name field, enter the name of the class that your Java program will use to represent the database object.

When publishing an object type where Use Name is different from Declaration Name, JPublisher creates a declaration_name.sqlj file and a use_nameRef.java file, where use_name represents the object type in your Java program.

JPublisher expects that you have written the class use_name, which extends declaration_name. If you do not provide this class, then the use_nameRef.java file will not compile.

For example, suppose you want JPublisher to generate the class JAddress from the PL/SQL object type ADDRESS. You have also written a class, MyAddress, to represent ADDRESS objects, where MyAddress either extends the functionality provided by JAddress or has a JAddress field.

Under this scenario, select ADDRESS in the Database Browser and right-click Generate Java. In the JPublisher wizard, enter JAddress in the Declaration Name field and MyAddress in the Use Name field. JPublisher will generate the custom Java class JAddress, and map the ADDRESS object to the MyAddress class—not to the JAddress class. JPublisher will also produce a reference class for MyAddress, not JAddress.

This is how JPublisher will alter the code it generates:

  • JPublisher generates the REF class MyAddressRef rather than JAddressRef.

  • JPublisher uses the MyAddress class, instead of the JAddress class, to represent attributes whose database type is ADDRESS. This situation occurs in classes generated by JPublisher, or in classes written by the user.

  • JPublisher uses the MyAddress class, instead of the JAddress class to represent VARRAY and nested table elements whose database type is ADDRESS.

  • JPublisher will use the MyAddress factory, instead of the JAddress factory, when the CustomDatumFactory interface is used to construct Java objects whose database type is ADDRESS. This situation will occur both in classes generated by JPublisher, and in classes written by the user.

The class that you create (for example, MyAddress.java) must have the following features:

  • The class 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.

  • The class must implement the CustomDatum interface. The simplest way to do this is to inherit the toDatum() method from the superclass.

  • You must also implement the CustomDatumFactory interface, either in the same class or in a different one. For example, you could have a class Employee that implements CustomDatum and a class EmployeeFactory that implements CustomDatumFactory.

28.3.6 JPublisher Options

JPublisher options can be set for these types of PL/SQL subprograms in your schema.

How to Set JPublisher Options

JPublisher options can be set for these types of PL/SQL subprograms in your schema:

  • Functions

  • Package Bodies

  • Packages

  • Procedures

For more information, see the Oracle Database JPublisher User's Guide.

To set JPublisher options for PL/SQL subprograms in a schema:

  1. In the Connection Manager, navigate a schema to find and select the node for the subprogram type

  2. Right-click and choose Generate Java to launch the JPublisher wizard. For more help at any time, press F1 or click Help in the wizard.

How to Generate Classes for Packages and Wrapper Methods for Methods

Set the JPublisher methods option in the JPublisher wizard by checking Include Methods

The value of the methods option determines whether JPublisher generates classes for PL/SQL packages and wrapper methods for methods in packages and object types.

If selected, JPublisher generates PL/SQL classes and methods. This is default behavior.

If not selected, JPublisher does not generate PL/SQL classes and methods.

How to Omit the Schema Name from Generated Names

Set the JPublisher omit_schema_names option in the JPublisher wizard by checking the Omit Schema Names box.

The value of the omit_schema_names option determines whether certain object type and PL/SQL wrapper names generated by JPublisher include the schema name. If an object type or wrapper name generated by JPublisher does not include the schema name, the type or wrapper is looked up in the schema associated with the current connection when the code generated by JPublisher is executed. This makes it possible for you to use classes generated by JPublisher with a connection other than the one used when JPublisher was invoked. However, the type or package must be declared identically in the two schemas.

If selected, an object type or wrapper name generated by JPublisher is qualified with a schema name only if either:

  • You declare the object type or wrapper in a schema other than the one to which JPublisher is connected; or

  • You declare the object type or wrapper with a schema name in the properties file or INPUT file.

That is, an object type or wrapper from another schema requires a schema name to identify it, and the use of a schema name with the type or package in the properties file or INPUT file overrides the omit_schema_names option.

If not selected, every object type or wrapper name generated by JPublisher is qualified with a schema name. This is default behavior.

How to Set the Package Name for Generated Classes

The package option specifies the name of the Java package JPublisher generates. The name of the package appears in a package declaration in each Java file. The directory structure also reflects the package name. An explicit name in the INPUT file, after the sql option, overrides the value given to the package option.

To set the package option:

  1. Set the JPublisher package option in the JPublisher wizard by providing a name in the Package field.

28.4 Using Java Stored Procedures

A Java stored procedure is a Java method that resides and runs in a database. Stored procedures can help improve the performance of database applications because they are efficient: they are stored in the RDBMS in executable form, and run in the RDBMS (rather than the client) memory space.

Use JDeveloper to write methods in Java for new stored procedures and deploy them to Oracle Database. When you deploy a Java class to Oracle, you can select the methods that you want to publish to PL/SQL for use as stored procedures. Methods can be deployed together in a package or separately.

For more information, see "Developing Java Stored Procedures" in the Oracle Database JPublisher User's Guide.

A stored procedure is a program that resides and runs in a database. Application developers can use stored procedures to help improve the performance of a database application. Procedure calls are quick and efficient because a stored procedure is compiled once and stored in an executable form. Because a stored procedure runs in the RDBMS memory space, complex functions run faster than a routine run by a client. You can also use stored procedures to group PL/SQL statements so that they are executed in a single call. This reduces network traffic and improves round-trip response times. By designing applications around a common set of stored procedures, you can avoid redundant coding and increase your productivity.

A Java stored procedure contains Java public static methods that are published to PL/SQL and stored in Oracle Database for general use. To publish Java methods, you write call specifications, that map Java method names, parameter types, and return types to their PL/SQL counterparts. This allows a Java stored procedure to be executed from an application as if it were a PL/SQL stored procedure. When called by client applications, a Java stored procedure can accept arguments, reference Java classes, and return Java result values.

Figure 28-4 Java Stored Procedure Deployment

Java Stored Procedure deployment

Any Java class can be deployed to Oracle Database and the conforming methods of the class can be published to PL/SQL as stored procedures. These Java stored procedures can then be executed from an application as if they were PL/SQL stored procedures. Java stored procedures can be an entry point for your application into other (Java and non-Java) procedures deployed to Oracle Database.

Deploying and publishing Java stored procedures to Oracle Database generates call specifications that act as PL/SQL wrappers for each of the methods selected for publishing. The PL/SQL wrappers allow the stored procedures to be accessible from SQL*Plus, JDBC, or any other Oracle application environment.

The call specifications (the PL/SQL wrappers) for Java stored procedure packages and methods deployed to a database schema can be inspected through Oracle Database connection. Only published Java stored procedures appear as PL/SQL blocks, and only public static methods in Java classes can be published to PL/SQL when deployed. Java classes can be deployed without being published, in which case they are not seen in the PL/SQL nodes.

Depending on how Java stored procedures were published, they appear in one of the following nodes under a schema:

  • Packages include call specs for Java stored procedures deployed in packages.

  • Functions include call specs for Java stored procedures deployed as functions (that return a value).

  • Procedures include call specs for Java stored procedures deployed as procedures (that do not return a value).

To view a Java stored procedure's call specification, find its node in the schema's hierarchy, and double-click it.

How to Create Java Stored Procedures

You create Java stored procedures by first developing business application logic in a Java class file. Declare methods that are to become stored procedures as public static.

Use the editor in JDeveloper to add and edit business logic in the Java class. During deployment to Oracle Database, all public static methods included in the class file are available to be published to PL/SQL as stored procedures. You can choose which public static methods in the class to be published to PL/SQL.

There are different JDeveloper Java stored procedure creation scenarios:

  • Use an existing Java class and make any necessary edits to the public static methods in the class that will be deployed to Oracle Database. The existing class could include public static methods used for validation or database triggers. The methods in the class might also be in local use by several applications. These methods could be deployed to Oracle Database and used by multiple applications from the database. The deployed methods could also supplement existing PL/SQL stored procedures and functions.

  • Create a new class with methods designed for publishing as stored procedures. Use the editor in JDeveloper to create the public static methods that will be exposed as stored procedures. Write in industry-standard Java and use the original Java class for other application deployments. In Oracle Database, this programming could supplement existing PL/SQL stored procedures.

For example, assume the following Java package Welcome was created with the public class Greeting and the public static method Hello().

package Welcome;
 public class Greeting {
 public static String Hello() {
 return "Hello World!";
 }

When this package is deployed to Oracle Database and the Hello() method is published there, the call spec for the package as viewed in the source editor looks like this:

PACKAGE WELCOME AS
FUNCTION HELLO RETURN VARCHAR2
AS LANGUAGE JAVA
NAME 'Welcome.Greeting.Hello() return java.lang.String'
END WELCOME;

How to Deploy Java Stored Procedures

You create a deployment profile for Java stored procedures, then deploy the classes and, optionally, any public static methods in JDeveloper using the settings in the profile.

Deploying to the database uses the information provided in the Deployment Profile wizard and two Oracle Database utilities:

  • loadjava loads the Java class containing the stored procedures to Oracle Database.

  • publish generates the PL/SQL call spec wrappers for the loaded public static methods. Publishing enables the Java methods to be called as PL/SQL functions or procedures.

To deploy Java stored procedures in JDeveloper:

  1. If necessary, create a database connection in JDeveloper.

  2. If necessary, create a deployment profile for Loadjava and Java stored procedures.

  3. Deploy the objects.

How to Create a Deployment Profile for Loadjava and Java Stored Procedures

The Loadjava and Java stored procedure deployment profile is very similar to the simple archive profile, except that the selected contents of the profile will be uploaded into Oracle Database via the command-line tool loadjava or in the case of Java stored procedures, they are stored in Oracle Database for general use.

Note:

Make sure that you have configured a database connection in JDeveloper before you complete this task.

To create a deployment profile for Loadjava or Java stored procedures in JDeveloper:

  1. In the Applications window, select the project in which you want to create the deployment profile.

  2. Choose File > New to open the New Gallery.

  3. In the Categories tree, expand Database Tier and select Database Files. In the Items list, double-click Loadjava and Java Stored Procedures.

    If the category or item is not found, make sure the correct project is selected, and select All Technologies in the Filter By dropdown list.

  4. In the Create Deployment Profile dialog, specify a location for the deployment profile or accept the defaults. The deployment profile is named with a .dbexport filename extension.

  5. Click Save to display the Loadjava and Java Stored Procedures Deployment Profile Settings dialog. Configure the settings for each page as appropriate, and click OK when you are done.

    The newly created storedProc.dbexport deployment profile appears in the Applications window below the specified project.

  6. Select and right-click storedProc.dbexport in the Applications window. Choose from the available context menu options.

  7. (Optional) If you choose Add Stored Procedure Package, choose the methods you want to load as a stored procedure. For each Java method callable from SQL a call spec is required, which exposes the method's top-level entry point to the database. Typically, only a few call specs are needed. JDeveloper generates the call spec for you from this page.

  8. Select a method and click Settings.

    If a method on the list is dimmed, this indicates a problem with deploying this method as a Java stored procedure. Click Why not? for an explanation.

    For more information, see "Developing Java Stored Procedures" in the Oracle Database JPublisher User's Guide.

  9. Configure the Method Settings as required. These settings allow you to customize the parts of the CREATE PROCEDURE and CREATE FUNCTION SQL statements that are used to customize the stored procedure.

  10. (Optional) Right-click and choose Preview SQL Statements to display a dialog that shows the SQL statements used to load the specifically selected item in the Applications window. In the case of top-level procedures or functions and packages, you will see complete SQL statements. In the case of packaged procedures or functions, you will only see fragments of SQL statements which represent the portion of the CREATE PACKAGE BODY statement corresponding to the packaged procedure or function.

    • (Optional) If you choose Add PL/SQL Package, enter the name of a PL/SQL package that you want to start building.

    • (Optional) Right-click and choose Preview SQL Statements to display a dialog that shows the SQL statements used to load the specifically selected item in the Applications window. In the case of top-level procedures or functions and packages, you will see complete SQL statements. In the case of packaged procedures or functions, you will only see fragments of SQL statements which represent the portion of the CREATE PACKAGE BODY statement corresponding to the packaged procedure or function.

    • To deploy the profile, see Deploying Loadjava and Java Stored Procedures Profile.

How to Deploy to Oracle Databases

If necessary:

  • Create a database connection in JDeveloper.

  • Create a deployment profile for Loadjava and Java stored procedures.

Note:

If you are deploying to Oracle9i Database release 2 (9.2) or later, set the compiler's target to 1.1 or 1.2. in the Project Properties dialog, available from the Application menu.

To deploy Loadjava and Java stored procedures in JDeveloper:

  1. Right-click storedProc.deploy which appears in the Applications window below the specified project.

  2. From the context menu, choose Export to and select one of the already existing database connections; the Java application's source files are uploaded directly into the selected database.

    Or, choose New Connection to display the Create Database Connection Wizard.

  3. (Optional) If you want to edit the deployment profile, right-click storedProc.deploy in the Applications window below the specified project and choose Settings.

Note:

If you are deploying your files as both compiled files and source files and you have selected either -resolve or -andresolve in the Resolver page, then the deployment profile will only upload the source files. The reason is that when loadjava resolves the uploaded .java source files, loadjava also compiles the .java source files into .class files. You will only see the source files when previewing the loadjava deployment profile settings.

How to Invoke Java Stored Procedures

The SQL CALL statement lets you call Java stored procedures.

To invoke a Java Stored Procedure using SQL:

  1. In SQL*Plus, execute the CALL statement interactively to invoke a Java stored procedure, using the syntax:

    CALL [schema_name.][{package_name | object_type_name}][@dblink_name]
 { procedure_name ([param[, param]...])
 | function_name ([param[, param]...]) INTO :host_variable};
where param represents this syntax: 
{literal | :host_variable}

    Host variables, that is variables declared in a host environment, must be prefixed with a colon. The following examples show that a host variable cannot appear twice in the same CALL statement, and that a subprogram with no parameters must be called with an empty parameter list:

    CALL swap(:x, :x); -- illegal, duplicate host variables
 
CALL balance() INTO :current_balance; -- () required

To invoke a Java stored procedure using JDBC:

  1. Java stored procedures invoked from JDBC must be encapsulated in CallableStatement objects.

    Create a callable statement object:

    • Declare a callable statement object. For example:

      private CallableStatement checkIn;

    • Initialize the callable statement object by calling prepareCall on the connection with a SQL CALL statement for the stored procedure. For example:

      checkIn = connection.prepareCall(quot;{call NPL.CHECKIN(?, ?, ?)}");

    Note:

    The number of parameters in the stored procedure is represented by the number of place-holders in the SQL call.

  2. Register the callable statement object's output parameters. Call registerOutParameter for each output parameter, identifying it by position, and declaring its type. For example, if the second parameter is an SQL INTEGER (which maps to a Java int), and the third is a SQL VARCHAR (which maps to a Java String), then:

    newCustomer.registerOutParameter(2, Types.INTEGER); 
newCustomer.registerOutParameter(3, Types.VARCHAR);

  3. Execute the callable statement object:

    • Provide the callable statement object's input parameters by calling a set method, identifying the parameter by position, and assigning it a value. For example, if the first parameter is an int input parameter:

      checkIn.setInt(1, bookID);

    • Execute the callable statement object. For example:

      checkIn.execute();

    • Extract the callable statement object's output parameters. Call a get method for each output parameter, identifying the parameter by position. The get methods return values of corresponding Java types. For example:

      int daysLate = checkIn.getInt(2); 
String title = checkIn.getString(3);

To invoke a Java stored procedure using SQLJ:

  1. Declare and initialize input and in-out variables. For example, if the first parameter is an int input parameter:

    int bookID = scanID();

  2. Declare output variables. For example:

    int daysLate; String title;

  3. Invoke the stored procedure in a SQLJ statement. In the statement identify the parameters by name, and designate them as :in, :out, or :inout. For example:

    #sql { call NPL.CHECKIN (:in bookID, :out daysLate, :out title)}

    Return values will be assigned to output and input variables.

To Invoke a Java Stored Procedure using PL/SQL

  1. Use a CALL statement in the trigger body clause of a PL/SQL statement to invoke a stored procedure, and pass arguments to it.

    The CALL statement's arguments can be:

    • Literal values.

    • SQL expressions, but not bind variables.

    • Column references, qualified by correlation names.

    Correlation names are prefixes to column references. Use these names to qualify whether the reference applies to the existing column value of the row being processed by the trigger or the value being written by the triggering event:

    • OLD refers to the value of the column prior to the triggering operation.

    • NEW refers to the value being assigned to the column by the triggering operation. It is possible for the trigger body to redefine this value before the triggering operation occurs.

    An example of a complete trigger definition:

    CREATE TRIGGER check_salary
BEFORE UPDATE OF salary ON employee
CALL salaryCheck(:new.job, :old.salary, :new.salary, :old.employee

    CREATE TRIGGER check_salary

    BEFORE UPDATE OF salary ON employee

    CALL salaryCheck(:new.job, :old.salary, :new.salary, :old.employeeID)

How to Test Java Stored Procedures

For stored procedures deployed in packages, access the stored procedure by the package name and/or the stored procedure name set during deployment. The package name may be the default name taken from the project or another name entered during deployment. The stored procedure name may be the default name taken from the method name or a name chosen for the stored procedure during deployment. Stored Procedures may also be deployed without packages.

For example, assume a public static method hello()is in the Java package Welcome and the public class Greeting. Further assume it is deployed in a package Openings.

You could execute a PL/SQL query to the deployed stored procedure that executes the public static method deployed there and returns the result. To invoke SQL*Plus from within JDeveloper, right-click a connection or select it from the Tools menu.

With a working connection to the database, your SQL*Plus client could execute the following:

package Welcome;
 public class Greeting {
 public static String Hello() {
 return "Hello World!";
 }
 }

You could execute a PL/SQL query to the deployed stored procedure that executes the public static method deployed there and returns the result. To invoke SQL*Plus from within JDeveloper, right-click a connection or select it from the Tools menu.

With a working connection to the database, your SQL*Plus client could execute the following:

select Hello() from dual;Hello()

Executing the code displays:

Hello World!

Note:

The reference to the stored procedure call spec uses package.method syntax; the name of the class from which the method originated is not part of the call.

For stored procedures deployed separately (not in packages), access the stored procedure by the stored procedure name set during deployment. The stored procedure name may be the default name taken from the method name or a name chosen for the stored procedure during deployment.

For example, for a public static method hello() that was deployed as hello from a class greeting and package welcome, you could execute a PL/SQL query to the deployed stored procedure that returns the result.

Assume the above hello() method as the example method, but this time assume it was deployed without a package.

With a working connection to the database, your SQL*Plus client could execute the following:

select Openings.Hello() from dual;
 
Openings.Hello()

The executed code displays:

Hello World!

28.4.1 How to Debug Java Stored Procedures

Debug Java stored procedures through a database connection.

To debug PL/SQL:

  1. Choose Window > Database > Databases window.

  2. Expand IDE Connections or application, and select a database connection.

  3. Expand a schema, and find a node with the name of the object type (for example, Package), and expand the node.

  4. In the node, right-click the PL/SQL program, and choose Debug.

  5. A Debug PL/SQL window opens. Select a target and parameter(s), and click OK.

  6. JDeveloper debugs the program. Check status windows for progress and information.

Additional information is available in Section 29.3, "Debugging PL/SQL Programs and Java Stored Procedures."

28.4.2 How to Remove Java Stored Procedures

To drop a stored procedure:

  1. Choose Window > Database > Databases window.

  2. Expand IDE Connections or application, and select a database connection.

  3. Expand the connection and select a schema.

  4. Expand the schema and locate the object you wish to remove. Depending on how Java stored procedures were published, they appear in one of these nodes:

    • Packages includes call specs for Java stored procedures deployed in packages.

    • Functions includes call specs for Java stored procedures deployed as functions (that return a value).

    • Procedures includes call specs for Java stored procedures deployed as procedures (that do not return a value).

  5. Select the object and right-click to display the context menu and choose Drop.