PL/Java Functions

With the GaussDB(DWS) PL/Java functions, you can choose your favorite Java IDE to write Java methods and install the JAR files containing these methods into the GaussDB(DWS) database before invoking them. GaussDB(DWS) PL/Java is developed based on open-source PL/Java 1.5.5 and uses JDK 1.8.0_292.

Constraints

Java UDF can be used for some Java logical computing. You are not advised to encapsulate services in Java UDF.

Examples

Before using PL/Java, you need to pack the implementation of Java methods into a JAR package and deploy it into the database. Then, create functions as a database administrator. For compatibility purposes, use JDK 1.8.0_262 for compilation.

  1. Compile a JAR package.

    Java method implementation and JAR package archiving can be achieved in an integrated development environment (IDE). The following is a simple example of compilation and archiving through command lines. You can create a JAR package that contains a single method in the similar way.

    First, prepare an Example.java file that contains a method for converting substrings to uppercase. In the following example, Example is the class name and upperString is the method name:

    1
2
3
4
5
6
7
    public class Example 
{
    public static String upperString (String text, int beginIndex, int endIndex) 
    {
        return text.substring(beginIndex, endIndex).toUpperCase();
    }
}

    Then, create a manifest.txt file containing the following content:

    1
2
3
4
5
6
    Manifest-Version: 1.0
Main-Class: Example
Specification-Title: "Example"
Specification-Version: "1.0"
Created-By: 1.6.0_35-b10-428-11M3811
Build-Date: 08/14/2018 10:09 AM

    Manifest-Version specifies the version of the manifest file. Main-Class specifies the main class used by the .jar file. Specification-Title and Specification-Version are the extended attributes of the package. Specification-Title specifies the title of the extended specification and Specification-Version specifies the version of the extended specification. Created-By specifies the person who created the file. Build-Date specifies the date when the file was created.

    Finally, archive the .java file and package it into javaudf-example.jar.

    1
2
    javac Example.java
jar cfm javaudf-example.jar manifest.txt Example.class

    JAR package names must comply with JDK rules. If a name contains invalid characters, an error occurs when a function is deployed or used.

  2. Deploy the JAR package.

    First store the JAR package on an OBS server. For details, see "Uploading a File" in Object Storage Service Console Operation Guide. Then, create the access key AK/SK. For details about how to create access keys, see "Creating an Access Key (AK and SK)" in Data Warehouse Service User Guide. After that, log in to the database, run the gs_extend_library function, and import the package to GaussDB(DWS).

    1
    SELECT gs_extend_library('addjar', 'obs://bucket/path/javaudf-example.jar accesskey=access_key_value_to_be_replaced  secretkey=secret_access_key_value_to_be_replaced  region=region_name libraryname=example');

    For details about how to use the gs_extend_library function, see Manage JAR packages and files. Change the values of AK and SK as needed. Replace region_name with an actual region name.

  3. Use a PL/Java function.

    Log in to the database as a user who has the sysadmin permission (for example, dbadmin) and create the java_upperstring function:

    1
2
3
4
    CREATE FUNCTION java_upperstring(VARCHAR, INTEGER, INTEGER)
    RETURNS VARCHAR
    AS 'Example.upperString'
LANGUAGE JAVA;
    • The data type defined in the java_upperstring function should be a type in GaussDB(DWS) and match the data type defined in 1 in the upperString method in Java. For details about the mapping between GaussDB(DWS) and Java data types, see Table 1.
    • The AS clause specifies the class name and static method name of the Java method invoked by the function. The format is Class name.Method name. The class name and method name must match the Java class and method defined in 1. In this example, no packages are specified. If a package has been specified, specify the complete class name when using CREATE FUNCTION.
    • To use PL/Java functions, set LANGUAGE to JAVA.
    • For details about CREATE FUNCTION, see Create functions.

    Execute the java_upperstring function.

    1
    SELECT java_upperstring('test', 0, 1);

    The expected result is as follows:

    1
2
3
4
     java_upperstring
---------------------
 T
(1 row)

  4. Authorize a common user to use the PL/Java function.

    Create a common user named udf_user.

    1
    CREATE USER udf_user PASSWORD 'password';

    This command grants user udf_user the permission for the java_upperstring function. Note that the user can use this function only if it also has the permission for using the schema of the function.

    1
2
    GRANT ALL PRIVILEGES ON SCHEMA public TO udf_user;
GRANT ALL PRIVILEGES ON FUNCTION java_upperstring(VARCHAR, INTEGER, INTEGER) TO udf_user;

    Log in to the database as user udf_user.

    1
    SET SESSION SESSION AUTHORIZATION udf_user PASSWORD 'password';

    Execute the java_upperstring function.

    1
    SELECT public.java_upperstring('test', 0, 1);

    The expected result is as follows:

    1
2
3
4
     java_upperstring
---------------------
 T
(1 row)

  5. Delete the function.

    If you no longer need this function, delete it.
    1
    DROP FUNCTION java_upperstring;

  6. Uninstall the JAR package.

    Use the gs_extend_library function to uninstall the JAR package.

    1
    SELECT gs_extend_library('rmjar', 'libraryname=example');

SQL Definition and Usage

Mapping for Basic Data Types

Table 1 PL/Java mapping for default data types

GaussDB(DWS)

Java

BOOLEAN

boolean

"char"

byte

bytea

byte[]

SMALLINT

short

INTEGER

int

BIGINT

long

FLOAT4

float

FLOAT8

double

CHAR

java.lang.String

VARCHAR

java.lang.String

TEXT

java.lang.String

name

java.lang.String

DATE

java.sql.Timestamp

TIME

java.sql.Time (stored value treated as local time)

TIMETZ

java.sql.Time

TIMESTAMP

java.sql.Timestamp

TIMESTAMPTZ

java.sql.Timestamp

Array Type Processing

GaussDB(DWS) can convert basic array types. You only need to append a pair of square brackets ([]) to the data type when creating a function.

CREATE FUNCTION java_arrayLength(INTEGER[])
    RETURNS INTEGER
    AS 'Example.getArrayLength'
LANGUAGE JAVA;

Java code is similar to the following:

public class Example
{
    public static int getArrayLength(Integer[] intArray)
    {
        return intArray.length;
    }
}

Invoke the following statement:

SELECT java_arrayLength(ARRAY[1, 2, 3]);

The expected result is as follows:

java_arrayLength
---------------------
3
(1 row)

NULL Handling

NULL values cannot be handled for GaussDB(DWS) data types that are mapped and can be converted to simple Java types by default. If you use a Java function to obtain and process the NULL value transferred from GaussDB(DWS), specify the Java encapsulation class in the AS clause as follows:

CREATE FUNCTION java_countnulls(INTEGER[])
    RETURNS INTEGER
    AS 'Example.countNulls(java.lang.Integer[])'
LANGUAGE JAVA;

Java code is similar to the following:

public class Example
{
    public static int countNulls(Integer[] intArray)
    {
        int nullCount = 0;
        for (int idx = 0; idx < intArray.length; ++idx)
        {
            if (intArray[idx] == null)
            nullCount++;
        }
        return nullCount;
    }
}

Invoke the following statement:

SELECT java_countNulls(ARRAY[null, 1, null, 2, null]);

The expected result is as follows:

java_countNulls
--------------------
3
(1 row)

Overloaded Functions

PL/Java supports overloaded functions. You can create functions with the same name or invoke overloaded functions from Java code. The procedure is as follows:

  1. Create overloaded functions.

    For example, create two Java methods with the same name, and specify the methods dummy(int) and dummy(String) with different parameter types.

    public class Example
{
    public static int dummy(int value)
    {
        return value*2;
    }
    public static String dummy(String value)
    {
        return value;
    }
}

    In addition, create two functions with the same names as the above two functions in GaussDB(DWS).

    CREATE FUNCTION java_dummy(INTEGER)
    RETURNS INTEGER
    AS 'Example.dummy'
LANGUAGE JAVA;

CREATE FUNCTION java_dummy(VARCHAR)
    RETURNS VARCHAR
    AS 'Example.dummy'
LANGUAGE JAVA;

  2. Invoke the overloaded functions.

    GaussDB(DWS) invokes the functions that match the specified parameter type. The results of invoking the above two functions are as follows:

    SELECT java_dummy(5);
 java_dummy
-----------------
            10
(1 row)

SELECT java_dummy('5');
 java_dummy
---------------
5
(1 row)

    Note that GaussDB(DWS) may implicitly convert data types. Therefore, you are advised to specify the parameter type when invoking an overloaded function.

    SELECT java_dummy(5::varchar);
 java_dummy
----------------
5
(1 row)

    In this case, the specified parameter type is preferentially used for matching. If there is no Java method matching the specified parameter type, the system implicitly converts the parameter and searches for Java methods based on the conversion result.

    SELECT java_dummy(5::INTEGER);
 java_dummy
-----------------
10
(1 row)

DROP FUNCTION java_dummy(INTEGER);

SELECT java_dummy(5::INTEGER);
 java_dummy
----------------
5
(1 row)

    Data types supporting implicit conversion are as follows:

    • SMALLINT: It can be converted to the INTEGER type by default.
    • SMALLINT and INTEGER: They can be converted to the BIGINT type by default.
    • TINYINT, SMALLINT, INTEGER, and BIGINT: They can be converted to the BOOL type by default.
    • CHAR, NAME, BIGINT, INTEGER, SMALLINT, TINYINT, RAW, FLOAT4, FLOAT8, BPCHAR, VARCHAR, NVARCHAR2, DATE, TIMESTAMP, TIMESTAMPTZ, NUMERIC, and SMALLDATETIME: They can be converted to the TEXT type by default.
    • TEXT, CHAR, BIGINT, INTEGER, SMALLINT, TINYINT, RAW, FLOAT4, FLOAT8, BPCHAR, DATE, NVARCHAR2, TIMESTAMP, NUMERIC, and SMALLDATETIME: They can be converted to the VARCHAR type by default.

  3. Delete the overloaded functions.

    To delete an overloaded function, specify the parameter type for the function. Otherwise, the function cannot be deleted.

    DROP FUNCTION java_dummy(INTEGER);

GUC Parameters

Exception Handling

If there is an exception in a JVM, PL/Java will export JVM stack information during the exception to a client.

Logging

PL/Java uses the standard Java Logger. Therefore, you can record logs as follows:

Logger.getAnonymousLogger().config( "Time is " + new 
Date(System.currentTimeMillis()));

An initialized Java Logger class is set to the CONFIG level by default, corresponding to the LOG level in GaussDB(DWS). In this case, log messages generated by Java Logger are all redirected to the GaussDB(DWS) backend. Then, the log messages are written into server logs or displayed on the user interface. MPPDB server logs record information at the LOG, WARNING, and ERROR levels. The SQL user interface displays logs at the WARNING and ERROR levels. The following table lists mapping between Java Logger levels and GaussDB(DWS) log levels.

Table 2 PL/Java log levels

java.util.logging.Level

GaussDB(DWS) Log Level

SERVER

ERROR

WARINING

WARNING

CONFIG

LOG

INFO

INFO

FINE

DEBUG1

FINER

DEBUG2

FINEST

DEBUG3

You can change Java Logger levels. For example, if the Java Logger level is changed to SEVERE by the following Java code, log messages (msg) will not be recorded in GaussDB(DWS) logs during WARNING logging.

Logger log = Logger.getAnonymousLogger();
Log.setLevel(Level.SEVERE);
log.log(Level.WARNING, msg);

Security Issues

In GaussDB(DWS), PL/Java is an untrusted language. Only user sysadmin can create PL/Java functions. The user can grant other users the permission for using the PL/Java functions. For details, see Authorize permissions for functions.

In addition, PL/Java controls user access to file systems, forbidding users from reading most system files, or writing, deleting, or executing any system files in Java methods.

Parent topic: User-Defined Functions