Using Embedded Java User Defined Functions

Like stored procedures, User Defined Functions (UDFs) written in SQL still achieve the maximum performance boost, but Java UDFs support a richer programming language, third-party libraries and an easy migration path from client-side code to server-side extension.

Overview

Java UDFs are very similar to Java stored procedures in that they are backed by a static method exposed by a Java class package in a JAR and uploaded to the server via the CREATE JAVACLASS command.

Stored Procedures versus User Defined Functions

The differences between stored procedures and UDFs are detailed in the following table.

Stored Procedures User Defined Functions

Invoked via a special SQL statement (EXECUTE or CALL)

Invoked as part of any DML statement (SELECT, INSERT, UPDATE or DELETE)

Can return zero or more values via INOUT or OUT parameters

Returns exactly one return value or table

Scalar and Table User Defined Functions

Like with SQL UDFs, Java UDFs come in two types - scalar and table. A scalar UDF accepts zero or more parameters and returns one simple data type. A table UDF accepts zero or more parameters and returns one result set.

Cache

Deterministic UDFs are stored in an in-memory cache. The size of this cache is limited by the System Property UDF_CACHE_SIZE (see SQL System Properties). The cache stores any type of UDF function, regardless of how it is implemented (Java or SQL). A deterministic UDF is one that is run with the same arguments. When the cache is full, the oldest entry gets removed, so if that combination of arguments is used again, the UDF code will be executed to compute the result. The default value for UDF_CACHE_SIZE is 50. For more information on the cache, see CREATE FUNCTION.

User Defined Function Format

A Java method used as a user fefined gunction must have the features in the following table to be successfully bound to the function via CREATE FUNCTION. The format is the same for both scalar and table UDFs, with the exception of Argument N.

Method Type

static

Return Type

void

Argument #1

java.Sql.Connection

Argument #2,3

For each parameter of the user defined function there must be a parameter in the Java method of the corresponding type as listed in this table:

SQL Type Parameter

BOOLEAN

boolean

STRING, VARCHAR

java.lang.String

SMALLINT

short

INT, INTEGER

int

BIGINT, DECIMAL, NUMBER

long

DECIMAL(scale), NUMBER(scale)

java.math.BigDecimal

FLOAT, REAL

float

DOUBLE

double

DATE

java.sql.Date

TIMESTAMP

java.sql.Timestamp

TIME

java.sql.Time

BLOB, CLOB, BYTES, TEXT, VARBINARY

byte[]

Argument #N

For scalar UDFs: type[] where type is the type of the return value.
For table UDFs:`java.sql.ResultSet[]`

For scalar UDFs, the base data type of the array return value depends on the data type that the UDF is expected to return.

For table UDFs, the corresponding Java method must have an Nth java.sql.ResultSet[] argument that can be used to populate the returned table.

Example: Scalar User Defined Function

A scalar user defined function accepts zero or more parameters and returns a simple data type.

Step 1 Create Java Class

package com.mycompany;

import java.sql.Connection;
import java.sql.SQLException;
/*
 * MyFirstJavaUDF#my_java_greater_function()
 * returns 1 if left is gt right
 * returns -1 if left is lt right
 * returns 0 if left == right
 */
public class MyFirstJavaUDF {
    public static void my_java_greater_function(Connection conn, int left, int right, int[] ret) throws SQLException {
        System.out.println(String.format("my_java_func: left=" + left + " right=" + right));
        if (conn == null)
            return;
        int result;
        if (left > right)
            result = 1;
        else if (left < right)
            result = -1;
        else
            result = 0;
        ret[0]=result;
    }
}

Step 2 Compile the File

Compile the file created in Step 1 and package it into a JAR archive.

$ javac com/mycompany/MyFirstJavaUDF.java
$ jar cvf myfirstjavaudf.jar com/mycompany/MyFirstJavaUDF.class

Step 3 CREATE the Java class in nuosql

To load the Java class into memory, run the following command in nuosq l (authenticated as a user with the DBA role), where myfirstjavaudf.jar is the JAR file compiled in Step 2.

SQL> CREATE JAVACLASS myfirstjavaudf FROM 'path/myfirstjavaudf.jar';

Where myfirstjavaudf is an arbitrary string specified to identify this class and path is the folder in which the JAR was created. For a full description of syntax and parameters, see CREATE JAVACLASS.

Step 4 Create a NuoDB SQL User Defined Function

SQL> CREATE FUNCTION mygt(l INTEGER, r INTEGER) RETURNS INTEGER
    LANGUAGE JAVA EXTERNAL 'myfirstjavaudf:com.mycompany.MyFirstJavaUDF.my_java_greater_function';

For a full description of syntax and parameters, see CREATE FUNCTION.

Step 5 Invoke the Java User Defined Function

SQL> CREATE TABLE t (x INTEGER, y INTEGER);
SQL> INSERT INTO t VALUES (1,2),(3,4),(6,5),(8,7),(9,9);
SQL> SELECT * FROM t;
 X  Y
 -- --
 1  2
 3  4
 6  5
 8  7
 9  9
SQL> SELECT mygt(x,y) FROM t;
 MYGT
 -----
  -1
  -1
   1
   1
   0

Step 6 Unload the Java Class

When the JAR file isn’t needed anymore, you can unload it unload by running the following NuoDB SQL command:

SQL> DROP JAVACLASS myfirstjavaudf IF EXISTS;

See DROP JAVACLASS.

Example: Table User Defined Function

A table user defined function accepts zero or more parameters and returns one result set.

Step 1 Create Java Class

package com.mycompany;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;

public class MySecondJavaUDF {
    public static void my_java_returns_rset(Connection conn, ResultSet[] ret) throws SQLException {

        if (conn == null)
            return;

        ret[0].moveToInsertRow();
        ret[0].updateInt(1,1);
        ret[0].updateString(2,"foo");
        ret[0].insertRow();
        ret[0].moveToInsertRow();
        ret[0].updateInt(1,2);
        ret[0].updateString(2,"bar");
        ret[0].insertRow();
    }
}

Step 2 Compile the File

Compile the file created in Step 1 and package it into a JAR archive.

$ javac com/mycompany/MySecondJavaUDF.java
$ jar cvf mysecondjavaudf.jar com/mycompany/MySecondJavaUDF.class

Step 3 CREATE Java class in nuosql

To load the Java class into memory, run the following command in nuosql (authenticated as a user with the DBA role), where mysecondjavaudf.jar is the JAR file compiled in Step 2.

SQL> CREATE JAVACLASS mysecondjavaudf FROM 'path/mysecondjavaudf.jar';

Where mysecondjavaudf is an arbitrary string specified to identify this class and path is the folder in which the JAR was created. For a full description of syntax and parameters, see CREATE JAVACLASS.

Step 4 Create a NuoDB SQL User Defined Function

SQL> CREATE FUNCTION my_second_udf() RETURNS TABLE mytable(id INTEGER, value STRING)
    LANGUAGE JAVA EXTERNAL 'mysecondjavaudf:com.mycompany.MySecondJavaUDF.my_java_returns_rset';

For a full description of syntax and parameters, see CREATE FUNCTION.

Naming the table as in, “mytable” is meaningful only with SQL user defined functions. With Java, the table is in memory and this table name is not used.

Step 5 Invoke the Java User Defined Function

SQL> select * from my_second_udf();
 ID  VALUE
 --- ------
  1   foo
  2   bar

SQL> select my_second_udf.value as v from my_second_udf();
  V
 ---
 foo
 bar

Step 6 Unload the Java Class

When the JAR file isn’t needed anymore, you can unload it unload by running the following NuoDB SQL command:

SQL> DROP JAVACLASS mysecondjavaudf IF EXISTS;

See DROP JAVACLASS.

SecurityManager Limitations: In general, a Java UDF could perform any operation that Java allows. In practice though, because the Java code is being hosted by the Transaction Engine (TE) process, a few operations are prohibited via a custom SecurityManager. These types of operations do not work with Java UDFs:

  • Loading native code killing the current process

  • Launching new processes

  • Defining classes dynamically or creating new class loaders

  • Accessing files from the file system

  • Modifying system properties

  • Accessing the printer or the clipboard

  • Creating server sockets

See Unsupported Methods in Embedded Java API for important information information about differences between the NuoDB JDBC driver API and the Embedded Java API used by Java stored procedures, triggers, and user defined functions.