JDK8 Based Nashorn JavaScript & Java to interact with MySQL Database. (part-2)

Nov/15

JDK8 Based Nashorn JavaScript & Java to interact with MySQL Database. (part-2)

by JaySenSharma under Database, Java8, JavaScript, WildFly

Hi,

As part of out previous article “Why Nashorn Java Scripting in Java8, Is it Poisonous for Java? (part-1)”, We discussed some very basics of JDK8, Nashorn Java Script and about the “$JAVA_HOME/bin/jjs” command line tool. We also learned that how we can invoke a JavaScript snippet or script file from inside a java code and Also how to use the Java APIs inside a simple Java Script code.

So now lets move a step ahead and see how to interact with the Database using Nashorn Java Script. In this demo we will extensively focus on how to use various java APIs and java packages inside the Nashorn Script.

What this demo is about?

As part of this demo we will learn about the following things:

1. How to create a Database and some sample tables in MySQL database.

2. How to write a simple Nashorn Java Script to interact with MySQL.

3. How to execute the Nashorn Script using jjs.

4. How to Invoke the Nashorn Java Script from inside a java code.

Setting up MySQL Database

Start the MySQL database and then do the following steps which will basically do the following things:
A. Creating a simple Database with name “TestJavaScriptDB”
B. Creating a table with name “CUSTOMER”.
C. Insert some dummy records inside the “CUSTOMER” table.

mysql -u root -p 
password:


mysql> create database TestJavaScriptDB;
Query OK, 1 row affected (0.00 sec)


mysql> use TestJavaScriptDB;
Database changed


mysql> CREATE TABLE CUSTOMER (custId INT(10) NOT NULL, custName CHAR(20) NOT NULL);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO CUSTOMER VALUES (1000, "MiddlewareMagic");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO CUSTOMER VALUES (2000, "Customer-2");
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO CUSTOMER VALUES (3000, "Customer-3");
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM CUSTOMER;
+--------+-----------------+
| custId | custName        |
+--------+-----------------+
|   1000 | MiddlewareMagic |
|   2000 | Customer-2      |
|   3000 | Customer-3      |
+--------+-----------------+
3 rows in set (0.01 sec)

Writing Nashorn JavaScript

Now lets start writing the Java Script to interact with the Database. So lets create a file “testDB.js” somewhere in the filesystem as following:

//  ## JDBC Based JavaScript  ##

// Declaring the getConnection function
var getConnection = function() {
    var Properties = Java.type("java.util.Properties");
    var Driver = Java.type("com.mysql.jdbc.Driver");
    var driver = new Driver();
    var properties = new Properties();
    var conn = null;
    try {
        properties.setProperty("user", "root");
        properties.setProperty("password", "testpwd");
        conn = driver.connect("jdbc:mysql://localhost:3306/TestJavaScriptDB", properties);
        return conn;
    } finally {
    
    }
}

print("****** conn : " + getConnection());

// Declaring the runQuery function
function runQuery(conA, query) {
   try {
       var stmt = conA.prepareStatement(query);
       var resultSet = stmt.executeQuery();
       print("      --------------------------- ");
       while (resultSet.next()) {
          print("\t" + resultSet.getString("custId") + " - "+ resultSet.getString("custName"))
       }
       print("      --------------------------- ");
       
    } finally {
      if (resultSet)
         try {
            resultSet.close();
            print("\nResultSet Closed.");
         }
         catch(e) {}
      if (stmt)
         try {
            stmt.close();
            print("Statement Closed.");            
         } catch (e) { print( e );  }
      if (conA)        
         try { 
            conA.close();
            print( "Connection Closed." );
         } catch (e) { print( e );  }          
      }  
 } 


// Invoking the getConnection and runQuery() functions.
var con1 = getConnection();
runQuery(con1, "select * from CUSTOMER");

Running testDB.js script using jjs

As we know that JDK8 provides the “jjs” utility as part of “$JAVA_HOME/bin” directory which is a command line utility to execute the Nashorn Java Script. Here we will be using that utility to run the testDB.js script. Open a terminal and then run the following commands in order to set the JDK8 in the path.

Unix Based OS

export JAVA_HOME=/PATH/TO/jdk1.8.0_60
export PATH=$JAVA_HOME/bin/jjs:$PATH

Windows Based OS

set JAVA_HOME=C:\jdk1.8.0_60
set PATH=%JAVA_HOME%\bin;%PATH%

After setting the path lets run the Java Script.

$ jjs -cp /PATH/TO/mysql-connector-java-5.1.37-bin.jar:.: testDB.js 

****** conn : com.mysql.jdbc.JDBC4Connection@384ad17b
      --------------------------- 
	1000 - MiddlewareMagic
	2000 - Customer-2
	3000 - Customer-3
      --------------------------- 

ResultSet Closed.
Statement Closed.
Connection Closed.

NOTICE: while running the “testDB.js” script make sure to set the Classpath using the “-cp” option provided by “jjs” utility.
Windows users need to use “;” semi colon rather than using the “:” Colon while defining the classpath. as following:
-cp C:\PATH\TO\mysql-connector-java-5.1.37-bin.jar;.;

If users will not set the classpath properly then it will throw the following kind of error because it won’t be able to find the MySQL JDBC Driver.

$ jjs testDB.js 

Exception in thread "main" java.lang.RuntimeException: java.lang.ClassNotFoundException: com.mysql.jdbc.Driver
	at jdk.nashorn.internal.runtime.ScriptRuntime.apply(ScriptRuntime.java:397)
	at jdk.nashorn.tools.Shell.apply(Shell.java:397)
	at jdk.nashorn.tools.Shell.runScripts(Shell.java:326)
	at jdk.nashorn.tools.Shell.run(Shell.java:172)
	at jdk.nashorn.tools.Shell.main(Shell.java:136)
	at jdk.nashorn.tools.Shell.main(Shell.java:112)
Caused by: java.lang.ClassNotFoundException: com.mysql.jdbc.Driver
	at java.net.URLClassLoader.findClass(URLClassLoader.java:381)
	at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
	at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:331)
	at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
	at java.lang.Class.forName0(Native Method)
	at java.lang.Class.forName(Class.java:348)
	at jdk.nashorn.internal.runtime.Context.findClass(Context.java:1051)
	at jdk.nashorn.internal.objects.NativeJava.simpleType(NativeJava.java:493)
	at jdk.nashorn.internal.objects.NativeJava.type(NativeJava.java:322)
	at jdk.nashorn.internal.objects.NativeJava.type(NativeJava.java:314)
	at jdk.nashorn.internal.objects.NativeJava.type(NativeJava.java:310)
	at jdk.nashorn.internal.scripts.Script$Recompilation$1$65$testDB.getConnection(testDB.js:5)
	at jdk.nashorn.internal.scripts.Script$testDB.:program(testDB.js:19)
	at jdk.nashorn.internal.runtime.ScriptFunctionData.invoke(ScriptFunctionData.java:640)
	at jdk.nashorn.internal.runtime.ScriptFunction.invoke(ScriptFunction.java:228)
	at jdk.nashorn.internal.runtime.ScriptRuntime.apply(ScriptRuntime.java:393)
	... 5 more

Invoking testDB.js script from Java code

Just now we have seen that how we can use the “$JAVA_HOME/bin/jjs” command line utility in order to execute the Nashorn JavaScript. Now we will write a simple java code using the “javax.script.ScriptEngineManager” and “javax.script.ScriptEngine” APIs.
Hence lets write a java program “ExampleNashornJdbc.java” somewhere in the filesystem as following:

import javax.script.*;
import java.io.*;

public class ExampleNashornJdbc {
	public static void main(String ar[]) throws Exception {
		ScriptEngineManager enginManager = new ScriptEngineManager();
		ScriptEngine engine = enginManager.getEngineByName("Nashorn");
		try {
			// Executing a simple java script file
			FileReader javaScriptFileReader = new FileReader(new File("testDB.js"));
            engine.eval(javaScriptFileReader);
            
		 } catch(final ScriptException se) {
			se.printStackTrace();
		 }
	}
}

Running Java ScriptEngine to execute testDB.js

Lets run the above code which will internally evaluate the “testDB.js” script.

$ java -cp /PATH/TO/mysql-connector-java-5.1.37-bin.jar:.: ExampleNashornJdbc
****** conn : com.mysql.jdbc.JDBC4Connection@7c729a55
      --------------------------- 
	1000 - MiddlewareMagic
	2000 - Customer-2
	3000 - Customer-3
      --------------------------- 

ResultSet Closed.
Statement Closed.
Connection Closed.

Do not forget to append the “.” in the classpath “-cp” else java virtual machine will search for the ExampleNashornJdbc class also inside the same “mysql-connector-java-5.1.37-bin.jar” and it wont find that class there ;).

If the Classpath is not set properly with the JDBC driver jar included in it then you may see the following kind of exception:

$ java ExampleNashornJdbc


Exception in thread "main" java.lang.RuntimeException: java.lang.ClassNotFoundException: com.mysql.jdbc.Driver
	at jdk.nashorn.internal.runtime.ScriptRuntime.apply(ScriptRuntime.java:397)
	at jdk.nashorn.api.scripting.NashornScriptEngine.evalImpl(NashornScriptEngine.java:446)
	at jdk.nashorn.api.scripting.NashornScriptEngine.evalImpl(NashornScriptEngine.java:403)
	at jdk.nashorn.api.scripting.NashornScriptEngine.evalImpl(NashornScriptEngine.java:399)
	at jdk.nashorn.api.scripting.NashornScriptEngine.eval(NashornScriptEngine.java:150)
	at javax.script.AbstractScriptEngine.eval(AbstractScriptEngine.java:249)
	at ExampleNashornJdbc.main(ExampleNashornJdbc.java:11)
Caused by: java.lang.ClassNotFoundException: com.mysql.jdbc.Driver
	at java.net.URLClassLoader.findClass(URLClassLoader.java:381)
	at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
	at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:331)
	at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
	at java.lang.Class.forName0(Native Method)
	at java.lang.Class.forName(Class.java:348)
	at jdk.nashorn.internal.runtime.Context.findClass(Context.java:1051)
	at jdk.nashorn.internal.objects.NativeJava.simpleType(NativeJava.java:493)
	at jdk.nashorn.internal.objects.NativeJava.type(NativeJava.java:322)
	at jdk.nashorn.internal.objects.NativeJava.type(NativeJava.java:314)
	at jdk.nashorn.internal.objects.NativeJava.type(NativeJava.java:310)
	at jdk.nashorn.internal.scripts.Script$Recompilation$1$65$\^eval\_.getConnection(<eval>:5)
	at jdk.nashorn.internal.scripts.Script$\^eval\_.:program(<eval>:19)
	at jdk.nashorn.internal.runtime.ScriptFunctionData.invoke(ScriptFunctionData.java:640)
	at jdk.nashorn.internal.runtime.ScriptFunction.invoke(ScriptFunction.java:228)
	at jdk.nashorn.internal.runtime.ScriptRuntime.apply(ScriptRuntime.java:393)
	... 6 more

Source code for this demo is present in :
https://github.com/jaysensharma/MiddlewareMagicDemos/tree/master/WildFly/Java_8/ServerSideJavaScript/Part-2-Jdbc

.
.
Regards
Jay SenSharma

If you enjoyed this post, please consider leaving a comment or subscribing to the RSS feed to have future articles delivered to your feed reader.

java8, javascript, MySQL, WildFly

3 Comments for this entry

akray
March 22nd, 2016 on 3:56 pm
Hi jay san sarma ..

i need to configure secutiry features in jboss AS 6.0.0 APp server..
1. ssl configuraton

plz suggest me..

Log in to Reply
- JaySenSharma
  March 22nd, 2016 on 5:26 pm
  Hello Akray,
  
  Following is a simple demo of configuring the HTTPs on JBossAS6 using self signed certificates.
  
  Step-1). To Create “jbossweb.keystore” please do the following inside the “$PROFILE/conf” cirectory:
```
$JAVA_HOME/bin/keytool -genkey -v -alias jbossalias -keyalg RSA -keysize 1024 -keystore jbossweb.keystore -validity 3650 -keypass JbossPassword -storepass JbossPassword -dname "CN=127.0.0.1, OU=MiddlewareMagic Unit, O=MiddlewareMagic, L=Bangalore, S=Karnataka, C=IN"
```
  Step-2). “$PROFILE/deploy/jbossweb.sar/server.xml” Enable the HTTPS Listen Port like following:
```
      <Connector protocol="HTTP/1.1" SSLEnabled="true" 
           port="8443" address="${jboss.bind.address}"
           scheme="https" secure="true" clientAuth="false" 
           keystoreFile="${jboss.server.home.dir}/conf/jbossweb.keystore"
           keystorePass="JbossPassword" sslProtocol = "TLS" />
```
  Step-3). Restart JBoss and then try accessing the applications over ssl using HTTPS
  https://localhost:8443/jmx-console
  
  Regards
  Jay SenSharma
  Log in to Reply
harishmunurai
September 22nd, 2016 on 3:59 pm
Hi Jay,

I deployed my application as ROOT.war and made required changes in standalone.xml. Application is deployed and able to access the application. But moment i login, it throws me “HTTP Status 404 – /favicon.ico” error. I have no idea whats going wrong. I hope you can help me in this to resolve. Yes, am sorry to post this on current article, i had no other options to reach you. Please, revert back with your findings.

Log in to Reply