Latest content
Explore and discover our latest tutorials
Tech Articles from your friends at Oracle and the developer community.
The Nashorn engine has been deprecated in JDK 11 as part of JEP 335 and and has been removed from JDK15 as part of JEP 372. To learn more, please read Migration Guide from Nashorn to GraalVM JavaScript.
This series of articles introduces Nashorn, a blazing fast JavaScript runtime engine that shipped with Java SE 8 to provide a lightweight environment for extending, supplementing, and often even replacing Java code. Full Java Virtual Machine (JVM) capabilities and dynamic typing represent an effective tooling that will appeal to developers and admins alike.
Year 2017 marks the 20th anniversary since JDBC was introduced in JDK 1.1. That new JDBC API has proven exceptionally successful and has revolutionized the way applications connect to database—and continues to thrive in the upcoming JDBC 4.3, which is a part of Java 9. Nashorn proves to be excellent language to remove much of the verbosity from the JDBC API, especially when combined with new features of Java 8.
Building on the regular driver manager in java.sql
and the datasource in javax.sql
, Nashorn can often position itself as a glue language between different database
engines, operating systems, and applications. Database context has been made much more relevant with the
announcement of Oracle Database 12c Release 2, where Nashorn plays an integral part of the dbms_java
and dbms_javascript
subsystems.
Oracle Universal Connection Pool is a mission-critical pool implementation that allows massive optimization of connection traffic, and it is focused on reducing the database footprint while providing a sufficient number of application endpoints. Nashorn can be a perfect tool for experimenting with and fine-tuning the pools, validating their state, and imitating real application traffic for troubleshooting purposes.
Oracle SQL Developer Command Line (Oracle SQLcl) is a free command-line interface for Oracle Database and a
next-generation SQL*Plus replacement implemented in Java. Besides thorough compatibility with its predecessor, the
tool embeds ScriptEngine
directly so that new extensions can be implemented in any Java
Virtual Machine (JVM) language. Nashorn, being part of the JDK, makes an excellent choice for extending Oracle
SQLcl by allowing out-of-the box use of ScriptEngine
.
Oracle Database 12c Release 2 was the first release to ship with a JDBC 4.2–compatible driver
(ojdbc8.jar
) and it included enhancements to type support, ref cursors, and large
datasets. Using Nashorn to interact with a database guarantees the same level of compatibility and functionality
as if full-fledged Java code was applied.
Oracle Database drivers can be downloaded from the Oracle Technology Network JDBC/UCP Download Page (see Figure 1) and the MySQL Connector/J driver can be obtained from the MySQL Developer Zone.
Figure 1. Oracle Technology Network JDBC/UCP download page.
Because JDBC is designed for compatibility, Oracle Database drivers and JDBC drivers are all compatible with each other between Oracle Database releases 11.2, 12.1, and 12.2. It is recommended, though, to use a JDBC driver of at least the version of the database to have access to new features. Listing 1 illustrates an easy way to check the exact version of the JDBC driver.
$ java -jar ojdbc8.jar
Oracle 12.2.0.1.0 JDBC 4.2 compiled with javac 1.8.0_91 on Tue_Dec_13_06:08:31_PST_2016
#Default Connection Properties Resource
#Tue Sep 19 16:58:25 CEST 2017
Listing 1. Checking the JDBC driver version.
JDBC covers all aspects of accessing a database in a generic and unified way, enduring as the primary database
API of all major database engines. The consistency and portability of this API can be often used for integrating
systems, and Nashorn proves to be a very capable player on this front. Listing 2 demonstrates a sample
implementation of a helper JDBC module that allows easy database access, support for named bind parameters, IN/OUT
parameters, and JDBC 4.2 large updates. Named binds are tokenized with a private parse
function, whereas the ndbc.run
function handles routing of different types of SQL
statements. The implementation responsible for handling queries illustrates a straightforward way of implementing
streams on top of existing APIs by converting the result set iterator to a stream, using spliterator.
/* ndbc.js */
(function() {
this.config = new java.util.HashMap() {
setAutoCommit: false,
setLoginTimeout: java.time.Duration.ofSeconds(5),
setQueryTimeout: java.time.Duration.ofSeconds(30),
setFetchSize: 5000
};
this.connect = function(url, user, password, driver) {
driver != null && java.lang.Class.forName(driver);
java.sql.DriverManager.setLoginTimeout(config.setLoginTimeout.getSeconds());
var conn = java.sql.DriverManager.getConnection(url, user, password);
conn.setAutoCommit(config.setAutoCommit);
return conn;
};
var parse = function(sql, params) {
var token = /[:]([A-Za-z_]+)/g;
var matches = sql.match(token);
var sqla = (matches == null) ? sql : sql.replace(token, "?");
var binds = { in : {}, out: {} };
for (var i = 0; matches != null && i < matches.length; i++)
binds[matches[i][1] != "_" ? "in" : "out"][i + 1] = matches[i].substring(1);
return {sql: sqla, binds: binds };
};
var prepare = function(conn, sql, params, isproc) {
var parsed = parse(sql, params);
var stmt = conn[isproc ? "prepareCall" : "prepareStatement"](parsed.sql);
for (var k in parsed.binds.in) {
stmt.setObject(parseInt(k), params[parsed.binds.in[k]]);
}
for (var k in parsed.binds.out) {
stmt.registerOutParameter(parseInt(k), params[parsed.binds.out[k]]);
}
return stmt;
};
this.run = function(conn, sql, params) {
var stmt, keyword = sql.toLowerCase().split(" ").shift();
switch (keyword) {
case "select":
var stmt = prepare(conn, sql, params);
stmt.setFetchSize(config.setFetchSize);
stmt.setQueryTimeout(config.setQueryTimeout.getSeconds());
return stream(stmt).collect(java.util.stream.Collectors.toList());
case "insert":
case "update":
case "delete":
var stmt = prepare(conn, sql, params);
var exec = ("executeLargeUpdate" in stmt) ? "executeLargeUpdate" : "executeUpdate";
return stmt[executeUpdate]();
default:
var stmt = prepare(conn, sql, params, true);
var parsed = parse(sql, params);
stmt.execute();
var ret = new java.util.HashMap();
for (var k in parsed.binds.out) {
var result = stmt.getObject(parseInt(k));
if (result instanceof java.sql.ResultSet) {
var rows = stream(null, result).collect(java.util.stream.Collectors.toList());
ret[parsed.binds.out[k]] = rows;
} else {
ret[parsed.binds.out[k]] = result;
}
}
return ret;
}
};
this.stream = function(stmt, rs) {
var rs = (rs == null) ? stmt.executeQuery() : rs;
var iter = new java.util.Iterator() {
hasNext: function() {
var next = rs.next();
if (!next) {
stmt != null && stmt.close();
rs.close();
}
return next;
},
next: function() {
var cols = new java.util.LinkedHashMap();
var meta = rs.getMetaData();
for (var i = 1, type = meta.getColumnTypeName(i); i <= meta.getColumnCount(); i++) {
if (type.equals("CLOB")) {
var clob = rs.getClob(i);
var value = clob.getSubString(1, parseInt(clob.length()));
} else {
var value = rs.getObject(i);
}
cols[meta.getColumnLabel(i)] = value;
}
return cols;
}
};
var spl = java.util.Spliterators.spliteratorUnknownSize(iter, java.util.Spliterator.ORDERED);
return java.util.stream.StreamSupport.stream(spl, false);
};
return this;
})();
Listing 2. Generic Nashorn JDBC helper module with JDBC 4.2 extensions and Java 8 streams support.
Output PL/SQL parameters for the NDBC module must start with an underscore and also define the return JDBC type.
In Listing 3, output parameter _list
is mapped to CURSOR
so
that multiple rows can be returned from the procedure. All queries are fetched as lists of maps so that
Nashorn's attribute access can be used in addition to the standard [] operator. The in Listing 3 runs on
Docker image store/oracle/enterprise:12.2.0.1
and, therefore, Docker machine IP 192.168.99.100
is provided in the Transparent Network Substrate (TNS) address. The last
variable displays the newest user in a pluggable database with CON_ID=3
.
$ jjs -J-Djava.class.path=ojdbc8.jar
jjs> var ndbc = load('ndbc.js');
jjs> var conString = 'jdbc:oracle:thin:@192.168.99.100:1521/ORCLCDB.localdomain';
jjs> var c1 = ndbc.connect(connString, 'system', '****', 'oracle.jdbc.pool.OracleDataSource');
jjs> var last = ndbc.run(c1, 'select max(created) created from cdb_users where con_id=:pdb', {pdb: 3});
jjs> last
[{CREATED=2017-09-16 22:36:39.0}]
jjs> var cursor = Packages.oracle.jdbc.OracleTypes.CURSOR;
jjs> var pdbs = ndbc.run(c1, 'begin open :_list for select pdb_name from cdb_pdbs; end;', {_list: cursor});
jjs> pdbs
{_list=[{PDB_NAME=PDB$SEED}, {PDB_NAME=ORCLPDB1}]}
Listing 3. Using NDBC with named binds and OUT parameters on Oracle Database 12c Release 2.
Scripting Oracle Universal Connection PoolA connection pool is vital for healthy database connection management, with failing or excessive connections often ranked as the number-one issue on the troubleshooting list. Oracle Universal Connection Pool (Oracle UCP) provides a robust pool implementation that supports all kinds of connections, whether they are JDBC, LDAP, or others that can be load balanced, recycled, and efficiently maintained during their lifetime.
For diagnosing connection pools, continuous rebuilding of Java programs might simply become insufficient
if many multiple features and capabilities are put into use. This in turn offers a perfect opportunity for
experimentation with Nashorn to achieve the desired pool behavior and configuration. Listing 4 provides a
definition of an Oracle UCP ping program called ucp.js
with a sample configuration
file called pool.properties
containing a definition of the pool parameters. All
requests made to the pool are measured implicitly by the Oracle UCP driver and can be queried during or after
program is run. This client uses the ExecutorService
framework for asynchronous calls
on the pool.
/* pool.properties */
# connection
URL = jdbc:oracle:thin:@192.168.99.100:1521/ORCLCDB.localdomain
user = system
password = Oradoc_db1
threads = 15
connections = 100
sleep = 5
query = select sys_context('userenv', 'instance_name') from dual
# ucp
connectionFactoryClassName = oracle.jdbc.pool.OracleDataSource
connectionWaitTimeout = 10
inactiveConnectionTimeout = 10
initialPoolSize = 10
maxConnectionReuseCount = 1000
maxConnectionReuseTime = 0
maxPoolSize = 20
maxStatements = 5000
minPoolSize = 20
minPoolSize = 5
timeoutCheckInterval = 5
timeToLiveConnectionTimeout = 0
validateConnectionOnBorrow = false
/* ucp.js */
var pds = Packages.oracle.ucp.jdbc.PoolDataSourceFactory.getPoolDataSource();
var props = new java.util.Properties();
props.load(new java.io.FileInputStream(arguments[0]));
pds.setConnectionFactoryClassName(props.connectionFactoryClassName);
pds.setURL(props.URL);
pds.setConnectionProperties(props);
var format = java.lang.String.format;
print(format("\nPinging %s using %s:", props.URL, arguments[0]));
var worker = function() {
try {
var conn = pds.getConnection();
var stmt = conn.createStatement();
stmt.setQueryTimeout(5);
stmt.setFetchSize(500);
var before = java.time.Instant.now();
var rs = stmt.executeQuery(props.query);
while (rs.next()) var inst = rs.getObject(1);
var duration = java.time.Duration.between(before, java.time.Instant.now()).toMillis();
java.lang.Thread.sleep(parseInt(props.sleep) * Math.random() * 1000);
print(format("Reply from %s time=%sms result=%s", java.lang.Thread.currentThread(), duration, inst));
rs.close();
stmt != null && stmt.close();
conn.close();
} catch (e) {
throw java.lang.RuntimeException(e);
}
};
var exec = java.util.concurrent.Executors.newFixedThreadPool(parseInt(props.threads));
for (var t = 0; t < parseInt(props.connections); t++) {
exec.submit(new java.lang.Runnable() {
run: worker
});
}
exec.shutdown();
exec.awaitTermination(60, java.util.concurrent.TimeUnit.SECONDS);
var stats = pds.getStatistics();
print(format("\nPing statistics for %s:\n Connections = %s, Borrowed = %s, Closed = %s", props.URL, stats.getTotalConnectionsCount(), stats.getBorrowedConnectionsCount(), stats.getConnectionsClosedCount()));
print(format("Connection creation wait times in milli-seconds:\n Average = %sms, Maximum = %sms", stats.getAverageConnectionWaitTime(), stats.getPeakConnectionWaitTime()));
Listing 4. Oracle UCP "ping" utility for diagnosing correct connection pool behavior.
Aside from the PoolDataSource
and ExecutorFramework
, the code in Listing 4 also introduces a reference to a new Java 8
calendar framework introduced under JEP 150: Date &
Time API. This new API has been redesigned from the ground up and brings seamless date/time handling to
all JVM languages, Nashorn included.
The invocation and result of the sample test of the Oracle UCP pool is shown in Listing 5.
$ jjs "-J-Djava.class.path=lib/ojdbc8.jar:lib/ucp.jar" ucp.js -- pool.properties
Pinging jdbc:oracle:thin:@192.168.99.100:1521/ORCLCDB.localdomain using
pool.properties:
Reply from Thread[pool-1-thread-3,5,main] time=85ms result=ORCLCDB
Reply from Thread[pool-1-thread-5,5,main] time=2ms result=ORCLCDB
Reply from Thread[pool-1-thread-6,5,main] time=91ms result=ORCLCDB
Reply from Thread[pool-1-thread-7,5,main] time=86ms result=ORCLCDB
Reply from Thread[pool-1-thread-8,5,main] time=22ms result=ORCLCDB
Reply from Thread[pool-1-thread-1,5,main] time=3ms result=ORCLCDB
Reply from Thread[pool-1-thread-10,5,main] time=7ms result=ORCLCDB
Reply from Thread[pool-1-thread-2,5,main] time=8ms result=ORCLCDB
Reply from Thread[pool-1-thread-9,5,main] time=90ms result=ORCLCDB
Reply from Thread[pool-1-thread-4,5,main] time=2ms result=ORCLCDB
Ping statistics for jdbc:oracle:thin:@192.168.99.100:1521/ORCLCDB.localdomain:
Connections = 10, Borrowed = 0, Closed = 0
Connection creation wait times in milli-seconds:
Average = 606ms, Maximum = 856ms
Listing 5. Testing Oracle UCP with Nashorn.
Scripting Oracle DatabaseWhen Oracle Database 12c Release 2 came along, Nashorn earned yet another credit of trust by becoming the language of choice for scripting the JVM that runs inside Oracle Database. This functionality was enabled by the introduction of Oracle's Java 8–based JVM.
Before running the code, the JVM release that's running inside Oracle Database can be confirmed with select dbms_java.get_ojvm_property(propstring=>'java.version') from dual
. With
the database version 12.2.0.1 used in this article, version 1.8.0_121
is returned.
Listing 6 shows the deployment of a Java procedure responsible for invoking Nashorn inside Oracle Database with
the required privileges on the DEMO
user. Aside from the comprehensive DBJAVASCRIPT
role that allows execution of Nashorn code, a getClassLoader
permission makes the code generic in a way that the script name can be
provided at runtime.
SQL> connect sys/****@orclpdb1 as sysdba
SQL> create user demo identified by demo;
SQL> grant create session, resource, dbjavascript to demo;
SQL> begin
2 dbms_java.grant_permission('DEMO', 'SYS:java.lang.RuntimePermission',
3 'getClassLoader', '');
4 end;
5 /
-- runscript.sql
create or replace and compile java source named "RunScript" as
import javax.script.*;
import java.net.*;
import java.io.*;
public class RunScript {
public static String eval(String script, String code) throws Exception {
String output = new String();
ClassLoader loader = Thread.currentThread().getContextClassLoader();
try (
InputStream is = loader.getResourceAsStream(script);
Reader reader = new InputStreamReader(is)
) {
ScriptEngineManager factory = new ScriptEngineManager();
ScriptEngine engine = factory.getEngineByName("javascript");
engine.eval(reader);
output = engine.eval(code).toString();
} catch(Exception e) {
output = e.getMessage();
}
return output;
}
}
/
create or replace function runscript(script in varchar2, code in varchar2)
return varchar2 as language java
name 'RunScript.eval(java.lang.String, java.lang.String) return java.lang.String'
/
Listing 6. Helper ScriptEngine
objects in Java and
PL/SQL.
At this point, Nashorn scripts could be loaded into the database by using the loadjava
facility, for example, loadjava -v -u demo/demo@orclpdb1 runscript.js
uploads runscript.js
into the Nashorn schema on pluggable database ORCLPDB1
. The newly created runscript
SQL function takes the
name of a loaded script and code to execute as two VARCHAR2
parameters.
For procedures without a return value, the even simpler API of dbms_javascript.run()
can be used to quickly invoke Nashorn scripts without the need for intermediary functions. This method takes the
name of a loaded script as the first and only argument. To enable the output of such scripts, both server output
and Java output must be enabled (that is, set serveroutput on
and dbms_java.set_ouput()
must be invoked prior to executing dbms_javascript.run scripts
).
The task of porting SQL*Plus to Java to modernize the tool and provide new capabilities brought one feature
bigger than all the others combined: extensibility via ScriptEngine
. Nashorn, being a
part of Java SE became a natural fit for implementing these extensions and has already received a lot of traction
from both vendors and the community.
Seamless integration of Nashorn into Oracle SQLcl is based on the top-level SCRIPT
command, which loads the script under the first argument, assuming .js
if only the base
file name is given. Execution of external scripts would make little sense if the current connection context was
not accessible, so Oracle SQLcl exposes the command, connection, and terminal to the script. Oracle SQLcl scripts
can be executed directly as external processors or they can implement new functionality through one of the oracle.dbtools.raptor
subtypes.
Listing 7 exhibits the implementation of a new top-level Oracle SQLcl function called JSON
that transforms a SQL result set into valid JSON representation using JavaScript's
built-in JSON.stringify
function. Oracle SQLcl's CommandRegistry
and CommandListener
are used to bind the jsonCommand
handler to the JSON command. To load this script in Oracle SQLcl, a simple
script json.js
command will enable the extended functionality.
/* json.js */
var jsonCommand = function(ctx, conn, sql) {
print(sql);
var ret = util.executeReturnList(sql, null);
var rows = [];
for (var r in ret) {
var cols = {};
for (var k in ret[r]) cols[k] = ret[r][k];
rows.push(cols);
}
print(JSON.stringify(rows, null, 4));
}
var handle = function(conn, ctx, cmd) {
if (cmd.getSql().toLowerCase().startsWith('json')) {
jsonCommand(ctx, conn, cmd.getSql().replace(/^json\s/i, ''));
return true;
}
return false;
}
var CommandRegistry = Java.type("oracle.dbtools.raptor.newscriptrunner.CommandRegistry");
var CommandListener = Java.type("oracle.dbtools.raptor.newscriptrunner.CommandListener");
var JsonCommand = Java.extend(CommandListener, {
handleEvent: handle,
beginEvent: function() {},
endEvent: function() {}
});
CommandRegistry.addForAllStmtsListener(JsonCommand.class);
Listing 7. Implementing the new Oracle SQLcl command to output results as valid JSON.
Many more ways of using the SCRIPT
command exist. Because Oracle SQLcl exposes the
Connection
object to the script, it is possible to use it for comparing, migrating,
unloading, exporting, or crunching data in various ways. Because all Oracle SQLcl scripts have access to its
classpath, built-in libraries that ship with Oracle SQLcl can be used, for example:
Thanks to the ubiquity and popularity of JDBC, Nashorn turns out to be an excellent language for database work. Without the need for compile-time dependencies that require the setup of Maven or Gradle, all that is needed for running high-performing JVM bytecode is a JAR driver and a script. Combined with the portability of code, Nashorn scripts will work on every operating system and every JDBC-compatible database, without the need for porting or recompiling. That agility is often sought after in today's dynamic software projects, especially when it doesn't sacrifice the performance and reliability of the JVM.