Java 8 Friday: JavaScript goes SQL with Nashorn and jOOQ

Posted on by lukaseder
At Data Geekery, we love Java. And as we’re really into jOOQ’s fluent API and query DSL, we’re absolutely thrilled about what Java 8 will bring to our ecosystem.

Java 8 Friday

Every Friday, we’re showing you a couple of nice new tutorial-style Java 8 features, which take advantage of lambda expressions, extension methods, and other great stuff. You’ll find the source code on GitHub.

JavaScript goes SQL with Nashorn and jOOQ

This week, we’ll look into some awesome serverside SQL scripting with Nashorn and Java 8. Only few things can be found on the web regarding the use of JDBC in Nashorn. But why use JDBC and take care of painful resource management and SQL string composition, when you can use jOOQ? Everything works out of the box! Let’s set up a little sample JavaScript file as such: 

var someDatabaseFun = function() {
    var Properties = Java.type("java.util.Properties");
    var Driver = Java.type("org.h2.Driver");

    var driver = new Driver();
    var properties = new Properties();

    properties.setProperty("user", "sa");
    properties.setProperty("password", "");

    try {
        var conn = driver.connect(
            "jdbc:h2:~/test", properties);

        // Database code here
    }
    finally {
        try { 
            if (conn) conn.close();
        } catch (e) {}
    }
}

someDatabaseFun();
This is pretty much all you need to interoperate with JDBC and a H2 database. So we could be running SQL statements with JDBC like so: 

try {
    var stmt = conn.prepareStatement(
        "select table_schema, table_name " + 
        "from information_schema.tables");
    var rs = stmt.executeQuery();

    while (rs.next()) {
        print(rs.getString("TABLE_SCHEMA") + "."
            + rs.getString("TABLE_NAME"))
    }
}
finally {
    if (rs)
        try {
            rs.close();
        }
        catch(e) {}

    if (stmt)
        try {
            stmt.close();
        }
        catch(e) {}
}
Most of the bloat is JDBC resource handling as we unfortunately don’t have a try-with-resources statement in JavaScript. The above generates the following output: 
INFORMATION_SCHEMA.FUNCTION_COLUMNS
INFORMATION_SCHEMA.CONSTANTS
INFORMATION_SCHEMA.SEQUENCES
INFORMATION_SCHEMA.RIGHTS
INFORMATION_SCHEMA.TRIGGERS
INFORMATION_SCHEMA.CATALOGS
INFORMATION_SCHEMA.CROSS_REFERENCES
INFORMATION_SCHEMA.SETTINGS
INFORMATION_SCHEMA.FUNCTION_ALIASES
INFORMATION_SCHEMA.VIEWS
INFORMATION_SCHEMA.TYPE_INFO
INFORMATION_SCHEMA.CONSTRAINTS
...
Let’s see if we can run the same query using jOOQ: 

var DSL = Java.type("org.jooq.impl.DSL");

print(
    DSL.using(conn)
       .fetch("select table_schema, table_name " +
              "from information_schema.tables")
);
This is how you can execute plain SQL statements in jOOQ, with much less bloat than with JDBC. The output is roughly the same: 
+------------------+--------------------+
|TABLE_SCHEMA      |TABLE_NAME          |
+------------------+--------------------+
|INFORMATION_SCHEMA|FUNCTION_COLUMNS    |
|INFORMATION_SCHEMA|CONSTANTS           |
|INFORMATION_SCHEMA|SEQUENCES           |
|INFORMATION_SCHEMA|RIGHTS              |
|INFORMATION_SCHEMA|TRIGGERS            |
|INFORMATION_SCHEMA|CATALOGS            |
|INFORMATION_SCHEMA|CROSS_REFERENCES    |
|INFORMATION_SCHEMA|SETTINGS            |
|INFORMATION_SCHEMA|FUNCTION_ALIASES    |
 ...
But jOOQ’s strength is not in its plain SQL capabilities, it lies in the DSL API, which abstracts away all the vendor-specific SQL subtleties and allows you to compose queries (and also DML) fluently. Consider the following SQL statement: 

// Let's assume these objects were generated
// by the jOOQ source code generator
var Tables = Java.type(
    "org.jooq.db.h2.information_schema.Tables");
var t = Tables.TABLES;
var c = Tables.COLUMNS;

// This is the equivalent of Java's static imports
var count = DSL.count;
var row = DSL.row;

// We can now execute the following query:
print(
    DSL.using(conn)
       .select(
           t.TABLE_SCHEMA, 
           t.TABLE_NAME, 
           c.COLUMN_NAME)
       .from(t)
       .join(c)
       .on(row(t.TABLE_SCHEMA, t.TABLE_NAME)
           .eq(c.TABLE_SCHEMA, c.TABLE_NAME))
       .orderBy(
           t.TABLE_SCHEMA.asc(),
           t.TABLE_NAME.asc(),
           c.ORDINAL_POSITION.asc())
       .fetch()
);
Note that there is obviously no typesafety in the above query, as this is JavaScript. But I would imagine that the IntelliJ, Eclipse, or NetBeans creators will eventually detect Nashorn dependencies on Java programs, and provide syntax auto-completion and highlighting, as some things can be statically analysed. Things get even better if you’re using the Java 8 Streams API from Nashorn. Let’s consider the following query: 

DSL.using(conn)
   .select(
       t.TABLE_SCHEMA,
       t.TABLE_NAME,
       count().as("CNT"))
   .from(t)
   .join(c)
   .on(row(t.TABLE_SCHEMA, t.TABLE_NAME)
       .eq(c.TABLE_SCHEMA, c.TABLE_NAME))
   .groupBy(t.TABLE_SCHEMA, t.TABLE_NAME)
   .orderBy(
       t.TABLE_SCHEMA.asc(),
       t.TABLE_NAME.asc())

// This fetches a List<Map<String, Object>> as
// your ResultSet representation
   .fetchMaps()

// This is Java 8's standard Collection.stream()
   .stream()

// And now, r is like any other JavaScript object
// or record!
   .forEach(function (r) {
       print(r.TABLE_SCHEMA + '.' 
           + r.TABLE_NAME + ' has ' 
           + r.CNT + ' columns.');
   });
The above generates this output: 
INFORMATION_SCHEMA.CATALOGS has 1 columns.
INFORMATION_SCHEMA.COLLATIONS has 2 columns.
INFORMATION_SCHEMA.COLUMNS has 23 columns.
INFORMATION_SCHEMA.COLUMN_PRIVILEGES has 8 columns.
INFORMATION_SCHEMA.CONSTANTS has 7 columns.
INFORMATION_SCHEMA.CONSTRAINTS has 13 columns.
INFORMATION_SCHEMA.CROSS_REFERENCES has 14 columns.
INFORMATION_SCHEMA.DOMAINS has 14 columns.
...
If your database supports arrays, you can even access such array columns by index, e.g. 

r.COLUMN_NAME[3]
So, if you’re a server-side JavaScript aficionado, download jOOQ today, and start writing awesome SQL in JavaScript, now! For more Nashorn awesomeness, consider reading this article here. Stay tuned for more awesome Java 8 content on this blog.

Published by lukaseder

I made jOOQ

4 thoughts on “Java 8 Friday: JavaScript goes SQL with Nashorn and jOOQ”

  1. We are using jOOQ 3.2.2. When trying this with Nashorn, if I use the dsl with raw SQL, everything works fine. If I use the dsl with the jOOQ generated class, it seems to quote-escape all of the table names, causing errors such as:

    SQL [select * from “example_table”]; You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘”example_table”‘ at line 1

    Again, the SQL version of using the dsl class works fine: dsl.fetch('select * from test_plan_filter')

    I also tried explicitly setting the render name style to be as_is but still get these added quotes around the table name.

    @Provides
    @Named("sqlSettings")
    public Settings provideDefaultJooqSettings()
    {
        Settings s = new Settings();
        s.setExecuteLogging(true);
        s.setRenderNameStyle(RenderNameStyle.AS_IS);
        return s;
    }

    Have you guys run into this problem before? Any suggestions? Thanks!

    Reply

    1. On MySQL, jOOQ should be quoting tables using backticks, as in select * from `example_table`. This indicates that you probably did not correctly specify the SQLDialect. More info here.

      On the other hand, given that also the AS_IS name style is not applied correctly to your SQL, I wonder if you’re applying those settings at all?

      Reply

  2. The code all works outside of Nashorn. Once it’s implemented within Nashorn, the renderNameStyle doesn’t seem to be used when adding the table name to the query. For example, if I set the renderNameStyle to upper, I get the following SQL error:

    SQL [select * from “example_table” order by EXAMPLE_TABLE.NAME asc limit ? offset ?]; You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘”example_table” order by EXAMPLE_TABLE.NAME asc limit 100 offset 0’ at line 1

    If I set it to LOWER or AS_IS, the order by gets modified correctly; however, the table_name is still stuck with the surrounding quotes. Therefore, it looks like within Nashorn, when I do something like the following, the renderNameStyle doesn’t get applied to the from() clause and is only applied on everything else.

    function query() {
	settings.setRenderNameStyle(AS_IS);
	var dsl = jooq.getAtomicDSLContextWithSetting(settings);
	
	return dsl.select().from(Tables.TEST_PLAN_FILTER)
	.orderBy(Tables.TEST_PLAN_FILTER.NAME)
	.limit(100)
	.offset(0)
	.fetch()
	.intoMaps();
}
    Reply

Leave a Reply