Practical Nashorn, Part 3: Introducing JavaScript, ECMAScript, and Nashorn

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.

• Part 1: Introducing JavaScript, ECMAScript, and Nashorn
• Part 2: The Java in JavaScript
• Part 3: Database Scripting

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.

A 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.

When 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:

• Apache HttpComponents: consuming REST APIs and web resources
• Java Secure Channel (JSch): accessing servers over SSH
• StringTemplate: template engine
• Jackson: JSON processing

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.