Per the Redmonk ranking (http://redmonk.com/sogrady/2016/07/20/language-rankings-6-16/) and the TIOBE index (http://www.tiobe.com/tiobe-index), JavaScript is one the most popular programming languages, disputing the top seat with Java.
This blog post is an update to
a previous post which discussed the rationales and a proof of concept of Nashorn (JavaScript on the JVM) and Avatar.js (Node.js APIs on the JVM. This update discusses the real production implementation of Nashorn on the JVM in rhe database.
Rationales for JavaScript in the Database
The embedded JVM in Oracle Database 12c Release 2 (a.k.a. OJVM) supports Java 8 and the Nashorn JavaScript engine.Running JavaScript in the database stemmed from the same motivations for running Java in the database namely: reuse of skills, libraries and code for implementing data-bound modules or applications and performance.
However, JavaScript in the database makes sense only when it is processing data stored in the database thereby - and this is another major motivation -- yielding better performance by the virtue of running close to the data. The beauty of the Nashorn JavaScript engine on the JVM is the ability to call Java APIs from JavaScript e.g., calling JDBC APIs from JavaScript, for database access.
Let's create an EMPLOEES table storing employees data as JSON documents
CREATE TABLE employees (
id RAW(16) NOT NULL,
data CLOB,
CONSTRAINT employees_pk PRIMARY KEY (id),
CONSTRAINT employees_json_chk CHECK (data IS JSON)
);
INSERT INTO employees (id, data)
VALUES (SYS_GUID(),
'{
"EmpId" : "100",
"FirstName" : "Kuassi",
"LastName" : "Mensah",
"Job" : "Manager",
"Email" : "kuassi@oracle.com",
"Address" : {
"City" : "Redwood",
"Country" : "US"
}
}');
...
...
INSERT INTO employees (id, data)
VALUES (SYS_GUID(),
'{
"EmpId" : "300",
"FirstName" : "Suresh",
"LastName" : "Mohan",
"Job" : "Developer",
"Email" : "Suresh@oracle.com",
"Address" : {
"City" : "Bangalore",
"Country" : "India"
}
}');
COMMIT;
Here is a JavaScript code which uses JDBC for querying JSON documents stored in the EMPLOYEE table.
var selectQuery = function(id)
{
var Driver = Packages.oracle.jdbc.OracleDriver;
var oracleDriver = new Driver();
var url = "jdbc:default:connection:";
var output = "";
var connection = oracleDriver.defaultConnection();
var prepStmt;
// Prepare statement if(id == 'all') {
prepStmt = connection.prepareStatement("SELECT a.data FROM employees a");
} else {
prepStmt = connection.prepareStatement("SELECT a.data FROM employees a WHERE a.data.EmpId = ?");
prepStmt.setInt(1, id);
}
// execute Query var resultSet = prepStmt.executeQuery();
// display results while(resultSet.next()) {
output = output + resultSet.getString(1) + "
";
}
// cleanup resultSet.close();
prepStmt.close();
connection.close();
return output;
}
Fluent JavaScript API using SODA for Java with Nashorn
For accessing JSON Collections and documents, Oracle furnishes SODA for Java; this API allows No SQL and dot notation access to JSON.
How to Run JavaScript in Oracle Database
Your schema (or database user) must be granted the DBJAVASCRIPT role (check with your DBA).
Your JavaScript source code (select.js) may originate from strings, files or Java resources.
function hello()
{
/*
*This is a sample Javascript file that prints "Hello World".
*/
var hellow = "Hello World";
return hellow;
}
var output = hello();
print(output);
Oracle recommends loading your JavaScript source code from a file into the database, as Java resource, using the loadjava utility
loadjava -v -u yourschema hello.js
Upon loading your JavaScrit code as a Java resource you may invoke it using: (i) either the dbms_java.javascript.run() procedure from SQL or PL/SQL, or (ii) DbmsJavaScript.run() Java call or the javax.script package.
Make sure that your database schema has been granted the
DBJAVASCRIPT role.
Running JavaScript in the Database using DBMS_JAVA.JAVASCRIPT.RUN Procedure
From SQL or PL/SQL
SQL>set serveroutput on
SQL>call dbms_java.set_output(20000);
SQL>call dbms_javascript.run("hello.js");
Running JavaScript in the Database using DbmsJavaScript.run Java call
From Java running in the database
import oracle.aurora.rdbms.DbmsJavaScript;
…
DbmsJavaScript.run("hello.js");
Notes:
Running JavaScript in the Database using the javax.script API
Invoking JavaScript in the database using the javax.script API requires the following steps:
Instantiate a script manager
Create an engine
Pass your resource stream reader as the argument to the eval method of the engine
import javax.script.*;
import java.net.*;
import java.io.*;
...
// create a script engine manager
ScriptEngineManager factory = new ScriptEngineManager();
// create a JavaScript engine
ScriptEngine engine =
factory.getEngineByName("javascript");
// create schema resource URL
URL url = Thread.currentThread()
.getContextClassLoader().getResource("hello.js");
engine.eval(new InputStreamReader(url.openStream()));
...
These steps can be turned into a wrapper class (the signature may differ depending on your input parameters and return value(s).
Here is a javax.script wrapper for invoking select.js JavaScript function (shown earlier).
create or replace and compile java resource named "InvokeScript" as
import javax.script.*;
import java.net.*;
import java.io.*;
public class InvokeScript {
public static String eval(String inputId) throws Exception {
String output = new String();
try {
// create a script engine manager
ScriptEngineManager factory = new ScriptEngineManager();
// create a JavaScript engine
ScriptEngine engine = factory.getEngineByName("javascript");
//read the script as a java resource
engine.eval(new InputStreamReader(InvokeScript.class.getResourceAsStream("select.js")));
// Alternative approach
//engine.eval(Thread.currentThread().getContextClassLoader().getResource("select.js"));
Invocable invocable = (Invocable) engine;
Object selectResult = invocable.invokeFunction("selectQuery", inputId);
output = selectResult.toString();
} catch(Exception e) {
output =e.getMessage();
}
return output;
}
}
/
Finally, invoke SelectQuery function (in select.js), using Javax API thru SQLDEMO procedure
