Creating Java External Procedures

This chapter explains how to configure the basic environment for Java external procedures and create Java external procedures.

Basic Environment Configuration

This section describes how to specify the basic environment configuration to execute the tbEPA processes, before creating the Java External Procedure.

Initialization Parameters

The initialization parameters that users can define for the JEPA process are as follows:

<<$TB_SID.tip>>

_PSM_BOOT_JEPA=Y
JAVA_CLASS_PATH=/home/tibero/tibero7/instance/tibero/java

Initialization Parameter	Description
_PSM_BOOT_JEPA	Specifies whether to start the JEPA process. Y: Must be set to 'Y' to start the JEPA process. N: If set to N, the database server is started but the JEPA process is not.
JAVA_CLASS_PATH	Path where the compilation output for Java objects will reside. If not specified, Java class files are created in the sub directory specified in the initialization parameter, DB_CREATE_FILE_DEST.

JEPA Connection Information

Specify JEPA's connection information in the tbdsn.tbr file located in the $TB_HOME/client/config directory.

The following example shows the connection information specified in the tbdsn.tbr file.

<<tbdsn.tbr>>

epa=(
         (EXTPROC=(LANG=JAVA)
         (LISTENER=(HOST=localhost)
                   (PORT=9390)
         )
     )
)

The Tibero server connects to the JEPA processing using this connection information.

JEPA Environment Configuration

Modify the epa.cfg file located in the $TB_HOME/client/epa/java/config directory to customize the user environment. After the environment setting is applied, the Java external procedure can be created.

The following example illustrates how to specify an .cfg file.

<<epa.cfg>>

# listener port 
LISTENER_PORT=9390

# initial thread pool size 
INIT_POOL_SIZE=10

# max thread pool size 
MAX_POOL_SIZE=1000

# gateway encoding "ASCII", "EUC-KR", "MSWIN949", "UTF-8", "UTF-16", "SHIFT-JIS"
ENCODING=UTF-8

#STATIC_LOADING_CLASSES=ex.StaticClass1, ex.st.Class2
 

Configuration Parameter	Description
LISTENER_PORT	Must be the same as the port number specified in the tbdsn.tbr file.
INIT_POOL_SIZE	Number of threads to create when the JEPA process is first started.
MAX_POOL_SIZE	Maximum number of threads to be created by the JEPA process.
ENCODING	Encoding method to be used by the JEPA process. For synchronization, the specified encoding must be the same as the database encoding.
STATIC_LOADING_ CLASSES	Required for using a class library that is not appropriate for Dynamic Class Loading (Example: a library that uses JNI).

The port number specified in the tbdsn.tbr file must be the same as that in the .cfg file.

---

Creating Java External Procedures

The following steps must be taken to create a Java external procedure.

1. Create a Java object

2. Compile the created Java object

3. Create a PSM function

4. Execute the Java class

1. Creating a Java Object

To create a Java external procedure, users must register a user Java class in DBMS, which can then be dynamically called in Java. This is the process of registering a user Java class as a database object.

When a PSM object is mapped to a user Java class, users can call the user Java class by using the same interface they would use to call PSM.

The following example creates a Java object.

SQL> CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "JavaExtproc" AS
     public class SimpleMath {
         public static int findMax(int x, int y) { 
             if (x >= y) return x;
             else return y;
         }
     }
     /

Java Source 'JavaExtproc' created.

SQL>

When the DDL statement described in the previous example is executed, a Java object named JavaExtproc is created in the database. To view the created Java object, you can use the USER_OBJECTS view or the LS command.

The following example retrieves a Java object created via the LS command.

SQL> LS

NAME                          OBJECT_TYPE
----------------------------- --------------
JavaExtproc                   JAVA             

1 row selected.

SQL>

Since a nullary constructor is called when a Java External Procedure calls a Java object, an empty nullary constructor must be specified to omit a constructor or to use another constructor.

2. Compiling a Java Object

The Java object source code created in a DDL statement is created as a separate file, then saved in the path specified in the initialization parameter, JAVA_CLASS_PATH. To create a Java object as a package, create a package path in the default location and store it as a class file.

The following is an example.

SQL> CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "JavaExtproc" AS
     package com.tmax;
     public class SimpleMath {
         public static int findMax(int x, int y) { 
             if (x >= y) return x;
             else return y;
         }
     }

Upon execution of the statement of the previous example, the SimpleMath.class file is created in the JAVA_CLASS_PATH/com/tmax directory. Compile the created class file using the external Java compiler.

The external Java compiler can use the psmjavac script located in the $TB_HOME/bin directory.

The following example executes a compile command using the psmjavac script

javac -classpath ${classpath} ${src} 

Item	Description
classpath	Path specified in the initialization parameter, JAVA_CLASS_PATH, and the $TB_HOME/client/lib/jar/tibero6-jdbc.jar path.
src	Java source code to compile.

To create a Java object using an external library, modify the psmjavac script file.

The following example executes a command to create a Java object that uses htmlconverter.jar which is provided by JVM.

javac -classpath ${classpath}:$JAVA_HOME/lib ${src}

JEPAs are run in a separate VM. When a client program calls a PSM function referencing an object that uses an external library, JEPAs must reference the library as well. JEPAs are run by the tbjavaepa script located in the $TB_HOME/client/bin directory.

Users must add the path to the library in the -classpath option of the exec java command, as shown in the following example.

exec java -verbose:gc -Xms128m -Xmx512m -Djavaepa="$TB_HOME"
      -Dlog4j.configuration=$log4jfile
      -classpath $pool:$collections:$log4j:
      $:$:$config $mainclass CONFIG=$configfile

Deleting a Java object requires the following command.

SQL> DROP JAVA SOURCE "JavaExtproc";
Java Source 'JavaExtproc' dropped.

SQL>

3. Creating a PSM function

Executing the CREATE JAVA statement requires the CREATE PROCEDURE privilege.

After mapping a Java object to a PSM as shown below, users can use the interface of the PSM program.

SQL> CREATE OR REPLACE FUNCTION find_max(x PLS_INTEGER, y PLS_INTEGER) 
     RETURN PLS_INTEGER IS
     LANGUAGE JAVA NAME 'SimpleMath.findMax(int, int) return int';
     /
     
Function 'FIND_MAX' created. 

SQL>

The following table details type conversions between PSM and Java.

Type in PSM	Type in Java
CHAR, LONG, VARCHAR2	java.lang.String java.sql.Date java.sql.Time java.sql.Timestamp java.lang.Byte java.lang.Short java.lang.Integer java.lang.Long java.lang.Float java.lang.Double java.math.BigDecimal byte short int long float double
DATE	java.sql.Date java.sql.Time java.sql.Timestamp java.lang.String
NUMBER	java.lang.Byte java.lang.Short java.lang.Integer java.lang.Long java.lang.Float java.lang.Double java.math.BigDecimal byte short int long float double
RAW, LONG RAW	byte[ ]
ROWID	java.lang.String

4. Executing a Java Class

Users can execute any SQL statement in the same way as executing a PSM function.

The following example executes a PSM statement using an SQL statement

SQL> select find_max(4,60) from dual; 

FIND_MAX(4,60)
--------------
           60

1 row selected.

SQL>