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.
| Reference: | Java 8 Friday: JavaScript goes SQL with Nashorn and jOOQ from our JCG partner Lukas Eder at the JAVA, SQL, AND JOOQ blog. |
