27 Using Java in the Database
This chapter includes the following sections:
About Using Java in the Database
Use Java with a database. You can embed SQL in Java programs using SQLJ or JDBC. You can access database objects and packages from Java programs. Use Java stored procedures, which are Java methods 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.
Choosing SQLJ or JDBC
Choose whether to use SQLJ or JDBC to embed SQL in Java programs.
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.
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. See Oracle® Database SQLJ Developer's Guide.
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.
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.
-
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.
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.
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.
Embedding SQL in Java Programs with SQLJ
You have to perform a number of tasks to embed SQL in Java programs with SQLJ.
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:
An empty SQL file is added to the current project and opened for editing.
How to Create SQLJ Classes
Create a new SQLJ (.sqlj) file and add it to the current project.
To create a new SQLJ file:
A skeleton SQLJ class will be added to the current project and be opened for editing.
How to Compile SQLJ Classes
You can compile SQLJ classes into Java .class
files.
To compile a SQLJ class:
- Set the project's SQLJ translator options to control how the file is compiled in the Compiler > SQLJ page of the Project Properties dialog.
- In the Applications window, locate and select the SQLJ class.
- 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.
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.
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:
After you have declared a context class, create a context object.
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:
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. See SQLJ Debugging Features.
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:
You can set SQLJ translator properties for all projects by choosing Default Project Properties from the Application menu
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 typeCtx2
, 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 isjdbc: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 isoracle.jdbc.driver.OracleDriver
. For example:-driver=sun.jdbc.odbc.JdbcOdbcDriver,oracle.jdbc.driver.OracleDriver
This option cannot be tagged with a connection context type.
Embedding SQL in Java Programs with JDBC
JDBC provides Java programs with low-level access to databases. See Oracle® Database SQLJ Developer's Guide.
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.
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:
How to Code a JDBC Connection
You can establish a database connection in pure JDBC code.
A summary is given here. See Oracle® Database SQLJ Developer's GuideOracle® Database SQLJ Developer's Guide.
To code a JDBC Connection:
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:
- 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. - Use JPublisher to generate source code — Java and SQLJ files — that represents the Oracle objects, PL/SQL packages, user-defined types, and REF types.
- Import these classes into your application code.
- Use the methods in the generated classes to access and manipulate the Oracle Objects and their attributes.
- 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. - Run your compiled application.
This process is illustrated in the following figure:
Figure 27-3 Oracle Objects and PL/SQL Packages
![Description of Figure 27-3 follows Description of Figure 27-3 follows](img/jpuba.png)
Description of "Figure 27-3 Oracle Objects and PL/SQL Packages"
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 27-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:
|
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 generatesCustomDatum
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 27-2 Built In Mapping Types
PL/SQL Data Type | Oracle Mapping Class | JDBC Mapping |
---|---|---|
|
|
|
|
|
|
|
|
|
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 27-3 LOB Type Mapping
PL/SQL Data Type | Oracle Mapping Class | JDBC Mapping Class |
---|---|---|
|
|
|
|
|
|
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 asint
and float,and
mapsDECIMAL
andNUMBER
tojava.math.BigDecimal
. -
The
objectjdbc
mapping (the default) maps most numeric database types to Java wrapper classes such asjava.lang.Integer
andjava.lang.Float,
and mapsDECIMAL
andNUMBER
tojava.math.BigDecimal
. -
The
bigdecimal
mapping maps all numeric database types tojava.math.BigDecimal
. The oracle mapping maps all numeric database types tooracle.sql.NUMBER
. -
The
oracle
mapping maps all numeric database types tooracle.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 27-4 Numeric Type Mapping
PL/SQL Data type | JDBC Mapping Class | Object JDBC Mapping |
---|---|---|
|
|
|
|
|
|
|
|
|
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 generatesCustomDatum
classes forobject
,collection
, andREF
types. -
When
usertypes=jdbc
, JPublisher generates SQLData classes for object types. JPublisher does not generate anything forcollection
orREF
types. Usejava.sql.Array
for allcollection
types, andjava.sql.Ref
for allREF
types.
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.
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
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.
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 thanJAddressRef
. -
JPublisher uses the
MyAddress
class, instead of theJAddress
class, to represent attributes whose database type isADDRESS
. This situation occurs in classes generated by JPublisher, or in classes written by the user. -
JPublisher uses the
MyAddress
class, instead of theJAddress
class to representVARRAY
and nested table elements whose database type isADDRESS
. -
JPublisher will use the
MyAddress
factory, instead of theJAddress
factory, when theCustomDatumFactory
interface is used to construct Java objects whose database type isADDRESS
. 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 thetoDatum()
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 classEmployee
that implementsCustomDatum
and a classEmployeeFactory
that implementsCustomDatumFactory
.
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
See Oracle® Database JPublisher User's Guide.
To set JPublisher options for PL/SQL subprograms in a schema:
-
In the Connection Manager, navigate a schema to find and select the node for the subprogram type
-
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:
- Set the JPublisher package option in the JPublisher wizard by providing a name in the Package field.
Using Java Stored Procedures
Java stored procedures are Java methods that reside and run 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. See Developing Java Stored Procedures.
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 27-4 Java Stored Procedure Deployment
![Description of Figure 27-4 follows Description of Figure 27-4 follows](img/spdeploy.png)
Description of "Figure 27-4 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:
-
If necessary, create a database connection in Jdeveloper.
-
If necessary, create a deployment profile for Loadjava and Java stored procedures.
-
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:
-
In the Applications window, select the project in which you want to create the deployment profile.
-
Choose File > New to open the New Gallery.
-
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. -
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. -
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. -
Select and right-click
storedProc.dbexport
in the Applications window. Choose from the available context menu options. -
(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.
-
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.
-
Configure the Method Settings as required. These settings allow you to customize the parts of the
CREATE PROCEDURE
andCREATE FUNCTION SQL
statements that are used to customize the stored procedure. -
(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:
-
Right-click
storedProc.deploy
which appears in the Applications window below the specified project. -
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. -
(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:
-
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:
-
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.
-
-
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 anSQL INTEGER
(which maps to a Javaint
), and the third is aSQL VARCHAR
(which maps to a JavaString
), then:newCustomer.registerOutParameter(2, Types.INTEGER); newCustomer.registerOutParameter(3, Types.VARCHAR);
-
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:
-
Declare and initialize input and in-out variables. For example, if the first parameter is an int input parameter:
int bookID = scanID();
-
Declare output variables. For example:
int daysLate; String title;
-
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
-
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!
How to Debug Java Stored Procedures
Debug Java stored procedures through a database connection.
To debug PL/SQL:
- Choose Window > Database > Databases window.
- Expand IDE Connections or application, and select a database connection.
- Expand a schema, and find a node with the name of the object type (for example, Package), and expand the node.
- In the node, right-click the PL/SQL program, and choose Debug.
- A Debug PL/SQL window opens. Select a target and parameter(s), and click OK.
- Jdeveloper debugs the program. Check status windows for progress and information.
Additional information is available in Debugging PL/SQL Programs and Java Stored Procedures.