NuoDB 2.0 Java Stored Procedures

amassari's picture

With NuoDB 2.0 the support for stored procedures gains a new exciting feature: the capability of running a procedure written in the Java language. Stored procedures written in SQL still achieve the maximum performance boost, but Java stored procedures have on their side a richer programming language, a huge number of third-party libraries and an almost effortless migration path from client-side code to server-side extension.

In order to create a stored procedure backed by Java code, the developer must wrap the code in a static method having a suitable signature, compile it and package it into a single JAR together with all the required third-party libraries. Note that such a JAR can include more than one extension methods. Then the code must be uploaded first to the server. In NuoSQL this is done by issuing the command:

CREATE JAVACLASS jExt FROM '/home/user/develop/nuodb-test.jar';

In this example, the JAR that has been built locally is uploaded to the server and named 'jExt'.

Running this command will overwrite a pre-existing extension having the same name, unless the IF NOT EXISTS clause is added to the command.

When the extension is not needed anymore, it can be deleted by issuing the command:

DROP JAVACLASS jExt;

If the extension doesn’t exist, an error will be reported, unless the IF EXISTS clause is added to the command.

There is no limit to the number of JARs that can be uploaded to the server: at runtime, when a Java stored procedure is invoked, the associated JAR is loaded by the Java Virtual Machine (JVM) embedded in the Transaction Engine (TE) into a private class loader that is not affected by the presence of the others.

Once the extension code is available in the server, the CREATE PROCEDURE statement can be used to bind a stored procedure with it:

CREATE PROCEDURE test LANGUAGE JAVA EXTERNAL 'jExt:testClass.testMethod';

The initial part, describing the signature of the stored procedure, is not influenced by the actual language used to implement it; what changes is the presence of LANGUAGE JAVA instead of LANGUAGE SQL (or nothing, as SQL is the default option) and the usage of the EXTERNAL keyword to specify the location of the code instead of embedding it using the AS <sql code> END_PROCEDURE option. The format of the reference string is <JAR name>:<class name>.<method name> where <JAR name> is the ID used in the CREATE JAVACLASS command that uploaded the code to the server and <class name> is the fully-qualified name of the class (e.g. com.nuodb.test.testClass, it testClass is placed in the package com.nuodb.test).

Having identified in this way the JAR containing the code, the class and the method name, what is left undefined is the signature of the method; this doesn’t need to be written in the binding string, as it is inferred from the signature of the stored procedure itself.

Method type

static

Return type

void

Argument #1

java.sql.Connection

Argument #2, #3 …

For each parameter of the stored procedure there must be a parameter in the Java method of the corresponding type as listed in this table:

SQL type

IN parameter

INOUT or OUT parameter

BOOLEAN

boolean

boolean[]

STRING, VARCHAR

java.lang.String

java.lang.String[]

SMALLINT

short

short[]

INT, INTEGER

int

int[]

BIGINT, DECIMAL, NUMBER

long

long[]

DECIMAL(scale), NUMBER(scale)

java.math.BigDecimal

java.math.BigDecimal[]

FLOAT, REAL

float

float[]

DOUBLE

double

double[]

DATE, DATEONLY

java.sql.Date

java.sql.Date[]

TIMESTAMP

java.sql.Timestamp

java.sql.Timestamp[]

TIME, TIMEONLY

java.sql.Time

java.sql.Time[]

BLOB, CLOB, BYTES, TEXT, VARBINARY

byte[]

byte[][]

Argument #N (if the stored procedure specifies the RETURNS option)

java.sql.ResultSet[]

 

When the stored procedure parameter is an INOUT or OUT parameter, the Java parameter is defined as an array that at runtime will be assigned to an array of 1 element only; this way, if the Java extension code reassigns the element of the array to a different object, the caller of the method can access the modified value.

The extension code can use the provided java.sql.Connection object to interact with the database engine: it looks like a normal client-side JDBC connection, but it’s a direct connection with the internal engine. The connection is already set up in a transaction managed by the server, so commit() and rollback() methods are no-ops; if you need to rollback the transaction, simply throw a java.sql.SQLException and the server will abort the transaction for you.

Finally, if the stored procedure specifies the RETURNS statement, the java.sql.ResultSet object that is stored as the first element of the provided array can be filled with the data to be returned via the moveToInsertRow()/updateString()/updateInt()/…/insertRow() methods. Alternatively, if the data is collected by a JDBC method call like Statement.executeQuery(), the returned ResultSet can be assigned to the first element of the provided array.

As an example, let’s try rewriting the stored procedures used in the DBT-2 benchmark to check the level of the stock. The SQL version is:

 

CREATE PROCEDURE stock_level(in_w_id INT, in_d_id INT, in_threshold INT, OUT low_stock INT) 
AS 
    VAR tmp_d_next_o_id = (SELECT d_next_o_id 
                           FROM district 
                           WHERE d_w_id = in_w_id AND d_id = in_d_id); 
    low_stock = (SELECT count(*) 
                 FROM order_line, stock, district 
                 WHERE d_id = in_d_id 
                   AND d_w_id = in_w_id 
                   AND d_id = ol_d_id 
                   AND d_w_id = ol_w_id 
                   AND ol_i_id = s_i_id 
                   AND ol_w_id = s_w_id 
                   AND s_quantity < in_threshold 
                   AND ol_o_id BETWEEN (tmp_d_next_o_id - 20) 
                                   AND (tmp_d_next_o_id - 1)); 
END_PROCEDURE;

 

To obtain the Java version of the procedure we start from this code, in the JavaProcs.java file:

 

import java.sql.*; 

class JavaProcs 
{ 
    static void stock_level(Connection conn, int in_w_id, int in_d_id, int in_threshold, int[] low_stock) throws SQLException 
    { 
        PreparedStatement nextID = conn.prepareStatement("SELECT d_next_o_id FROM district WHERE d_w_id = ? AND d_id = ?"); 
        nextID.setInt(1, in_w_id); 
        nextID.setInt(2, in_d_id); 
        ResultSet rset = nextID.executeQuery(); 
        if (rset.next()) 
        {
            int tmp_d_next_o_id = rset.getInt(1); 
            PreparedStatement lowStock = conn.prepareStatement("SELECT count(*) FROM order_line, stock, district WHERE d_id = ? AND d_w_id = ? AND d_id = ol_d_id AND d_w_id = ol_w_id AND ol_i_id = s_i_id AND ol_w_id = s_w_id AND s_quantity < ? AND ol_o_id BETWEEN (? - 20) AND (? - 1)"); 
            lowStock.setInt(1, in_d_id); 
            lowStock.setInt(2, in_w_id); 
            lowStock.setInt(3, in_threshold); 
            lowStock.setInt(4, tmp_d_next_o_id); 
            lowStock.setInt(5, tmp_d_next_o_id); 
            rset = lowStock.executeQuery(); 
            if (rset.next()) 
                low_stock[0] = rset.getInt(1); 
        }
    }
}

 

The Java code is compiled, packaged and finally installed:

 

user$ javac JavaProcs.java 
user$ jar cf dbt2-jext.jar JavaProcs.class
user$ nuosql test@localhost --user cloud --password user
SQL> CREATE JAVACLASS dbt2 FROM 'dbt2-jext.jar';
SQL> CREATE PROCEDURE stock_level(in_w_id INT, in_d_id INT, in_threshold INT, OUT low_stock INT) LANGUAGE JAVA EXTERNAL 'dbt2:JavaProcs.stock_level';

 

Happy coding!

Jiri Cincura (not verified)
Anonymous's picture

Are there any plans to restrict/manage uploads to server? Currently I can create potentially harmful jar and freely upload it from my machine to server and execute the code - in server's context and from the machine (i.e. doing network requests).

Add new comment

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.
Go to top