3 Invoking Java in the Database

3.1 Overview

In Oracle Database, you utilize Java in one of the following ways:

• Invoking Java Methods—Invoke Java methods in classes that are loaded within the database, such as Java stored procedures.

• Utilizing SQLJ and JDBC for Querying the Database—You can query the database from a Java client through utilizing JDBC.

We recommend that you approach Java development in Oracle Database incrementally, building on what you learn at each step.

1. You should master the process of writing simple Java stored procedures, as explained in "Preparing Java Class Methods for Execution". This includes writing the Java class, deciding on a resolver, loading the class into the database, and publishing the class.

2. You should understand how to access and manipulate SQL data from Java. Most Java server programs, and certainly Java programs executing on Oracle Database, interact with database-resident data. The two standard APIs for accomplishing this is JDBC and SQLJ. Because JDBC forms the foundation for SQLJ, you should understand how the two work together, even though you might be using only SQLJ in your code.

Java is a simple, general purpose language for writing stored procedures. JDBC and SQLJ allow Java to access SQL data. They support SQL operations and concepts, variable bindings between Java and SQL types, and classes that map Java classes to SQL types. You can write portable Java code that can execute on a client or a server without change. With JDBC and SQLJ, the dividing line between client and server is usually obvious—SQL operations happen in the server, and application program logic resides in the client.

As you write more complex Java programs, you can gain performance and scalability by controlling the location where the program logic executes. You can minimize network traffic and maximize locality of reference to SQL data. JDBC and SQLJ furnishes ways to accomplish these goals. However, as you tend to leverage the object model in your Java application, a more significant portion of time is spent in Java execution, as opposed to SQL data access and manipulation. It becomes more important to understand and specify where Java objects reside and execute in an Internet application.

3.2 Invoking Java Methods

The way your client calls a Java method depends on the type of Java application. The following sections discuss each of the Java APIs available for creating a Java class that can be loaded into the database and accessed by your client:

• Utilizing Java Stored Procedures

• Utilizing Java Native Interface (JNI) Support

• Utilizing SQLJ and JDBC for Querying the Database

// (Presume you already have a JDBC Connection object conn)

// Define Java variables

String name;

int id=37115;

float salary=20000;


// Set up JDBC prepared statement.

PreparedStatement pstmt = conn.prepareStatement

   ("select ename from emp where empno=? and sal>?");

pstmt.setInt(1, id);

pstmt.setFloat(2, salary);


// Execute query; retrieve name and assign it to Java variable.

ResultSet rs = pstmt.executeQuery();

while (rs.next()) {

   name=rs.getString(1);

   System.out.println("Name is: " + name);

}


// Close result set and statement objects.

rs.close()

pstmt.close();

3.3 Debugging Server Applications

Oracle Database furnishes a debugging capability that is useful for developers who use the JDK's jdb debugger. The interfaces that is provided is the Java Debug Wire Protocol, which is supported by JDK 1.3 and later versions of the Sun Microsystems JDB debugger (http://java.sun.com/j2se/1.3/docs/guide/jpda/, http://java.sun.com/j2se/1.4/docs/guide/jpda/.) The use of this interface is documented on OTN. The JDWP protocol supports many new features, including the ability to listen for connections (no more DebugProxy), change the values of variables while debugging, and evaluate arbitrary Java expressions, including method evaluation.

Oracle's JDeveloper provides a user-friendly integration with these debugging features. See the JDeveloper documentation for more information on how to debug your Java application through JDeveloper. Other independent IDE vendors will be able to integrate their own debuggers with Oracle Database.

3.4 How To Tell You Are Executing in the Server

You might want to write Java code that executes in a certain way in the server and another way on the client. In general, Oracle does not recommend this. In fact, JDBCand SQLJ goes to some trouble to enable you to write portable code that avoids this problem, even though the drivers used in the server and client are different.

If you must determine whether your code is executing in the server, use the System.getProperty method, as follows:

System.getProperty ("oracle.jserver.version")

The getProperty method returns the following:

• If executing in the server, it returns a String that represents the Oracle Database release.

• If executing on the client, it returns null.

3.5 Redirecting Output on the Server

System.out and System.err print to the current trace files. To redirect output to the SQL*Plus text buffer, use this workaround:

SQL> SET SERVEROUTPUT ON

SQL> CALL dbms_java.set_output(2000);

The minimum (and default) buffer size is 2,000 bytes; the maximum size is 1,000,000 bytes. In the following example, the buffer size is increased to 5,000 bytes:

SQL> SET SERVEROUTPUT ON SIZE 5000

SQL> CALL dbms_java.set_output(5000);

Output prints at the end of the call.

For more information about SQL*Plus, see the SQL*Plus User's Guide and Reference.

3.6 Support for Calling Java Stored Procedures Directly

Oracle Database 10g introduces new convenience features for calling Java stored procedures and functions.

In previous releases, calling Java stored procedures and functions from a database client required JDBC calls to associated PL/SQL wrappers. Each wrapper had to be manually published with a SQL signature and a Java implementation. This had the following disadvantages:

• A separate step was required for publishing the SQL signatures for Java methods.

• The signatures permitted only Java types with SQL equivalents.

• Exceptions issued in Java were not properly returned.

• Only a single method invocation could be performed for each database round trip.

To remedy these deficiencies, a simple API has been implemented for direct invocation of static Java stored procedures and functions. The new functionality is useful for Web services, but is more generally useful as well.

Classes for the simple API are located in the package oracle.jpub.reflect, so you must import this into the client-side code.

Here is the Java interface for the API:

public class Client

{

   public static String getSignature(Class[]);

   public static Object invoke(Connection, String, String, 

                               String, Object[]);

   public static Object invoke(Connection, String, String, 

                               Class[], Object[]);

}

As an example, consider a call to the following method in the server:

public String oracle.sqlj.checker.JdbcVersion.to_string();

You can now accomplish this as follows:

Connection conn = ...;

String serverSqljVersion = (String)

           Client.invoke(conn, "oracle.sqlj.checker.JdbcVersion",

           "to_string", new Class[]{}, new Object[]{});

The Class[] array is for the method parameter types and the Object[] array is for the parameter values. In this case, because to_string has no parameters, the arrays are empty.

Note the following:

• Any serializable type (such as int[] and String[], for example) can be passed as an argument.

• As an optimization, parameter values can be represented in a string:

  String sig = oracle.jpub.reflect.Client.getSignature(new Class[]{});
  
  ... 
  
  Client.invoke(conn, "oracle.sqlj.checker.JdbcVersion", "to_string",
  
               sig, new Object[]{});
  
  

(This is offered as a general note; in this example it is a moot point because to_string has no parameters.)

• The semantics of this API are different than semantics for invoking stored procedures or functions through a PL/SQL wrapper, in the following ways:

  • Arguments cannot be OUT or IN OUT. Returned values must all be part of the function result.

  • Exceptions are properly returned.

  • The method invocation uses invoker's rights. (There is no tuning to obtain definer's rights.)