SQL in Scala, where jOOQ could go

I have recently blogged about how simple it is to integrate jOOQ into Scala. See the full blog post here:

https://lukaseder.wordpress.com/2011/12/11/the-ultimate-sql-dsl-jooq-in-scala/

I’m more and more thrilled by that option, as Scala is one of the fastest emerging JVM languages nowadays. The plain integration of a Java library in Scala leaves some open questions. jOOQ knows the “val()” operator or method, to create bind values. See the manual here:

http://www.jooq.org/manual/JOOQ/BindValues/

This operator cannot be used in Scala, as Scala declares “val” as a reserved word. I’ve had similar issues in Java before, when trying to use “case” or “else” in the API, which is not possible either. The path of least resistance is to overload or re-name those methods. “val” was overloaded as “value” in jOOQ 2.0.1. “case” and “else” were re-named a long time ago as “decode” (from Oracle’s DECODE function), and “otherwise” (as in XSL).

So for a full-fledged integration in Scala, jOOQ should be wrapped in a new API called scOOQ ;-). This new API should take Scala’s language features into account in order to make working with jOOQ a lot easier. This could be the chance to re-engineer some of the API and make all API methods uppercase, as is usual with SQL. With Scala’s ability of omitting syntax elements, such as “.” and “()”, the API could then declare one-word methods, such as in this Java example:

MERGE().INTO(MY_TABLE)
       .USING(SOURCE_TABLE)
       .ON(MY_TABLE.ID.equal(SOURCE_TABLE.ID))
       .WHEN().MATCHED().THEN().UPDATE()
         .SET(ID, 1)
         .SET(DATA, "Data")
       .WHEN().NOT().MATCHED().THEN().INSERT(MY_TABLE.ID, MY_TABLE.DATA)
       .VALUES(1, "Data")

While in Java, this looks quite nasty and verbose, in Scala it could be very nice! The below statement should compile in Scala if the API was declared as such:

MERGE INTO MY_TABLE
      USING (SOURCE_TABLE)
      ON (MY_TABLE.ID equal SOURCE_TABLE.ID)
      WHEN MATCHED THEN UPDATE
        SET (ID, 1)
        SET (DATA, "Data")
      WHEN NOT MATCHED THEN INSERT (MY_TABLE.ID, MY_TABLE.DATA)
      VALUES (1, "Data")

Convinced? Contributions very welcome! :-)

Op4j and Lambda-J. For more fluency in Java

I recently blogged about simple constructs, such as Java’s Arrays.asList() and the fact that it is not used often enough:

https://lukaseder.wordpress.com/2011/10/28/javas-arrays-aslist-is-underused/

I like to work with fluent API’s, which are still quite a rare thing in the Java world, compared to other languages that support features such as language extensions, operator overloading, true generics, extension methods, closures, lambda expressions, functional constructs etc etc. But I also like Java’s JVM and the general syntax. And the many libraries that exist. I now came across Op4j, a really nice-looking library:

http://www.op4j.org/

It features exactly the kind of constructs I’d like to use every day. Some examples (taken from the documentation):

// Always static import Op.* as the main entry point
import static org.op4j.Op.*;
import static org.op4j.functions.FnString.*;

// Transform an array to uppercase
String[] values = ...;
List upperStrs =
  on(values).toList().map(toUpperCase()).get();

// Convert strings to integers
String[] values = ...;
List intValueList =
  on(values).toList().forEach().exec(toInteger()).get();

There are many more examples on their documentation page, and the API is huge and looks quite extensible:

http://www.op4j.org/apidocs/op4j/index.html

This library reminds me of Lambda-J, another attempt to bring more fluency to Java by introducing closure/lambda-like expressions in a static way:

http://code.google.com/p/lambdaj/

From a first look, Op4j looks more object oriented and straight-forward, though, whereas Lambda-J seems to depend on instrumentation and some advanced usage of reflection. A sample of some non-trivial Lambda-J usage:

Closure println = closure(); {
  of(System.out).println(var(String.class));
}

The above syntax is not easy to grasp. “closure()” seems to modify some static (ThreadLocal) state of the library, which can be used thereafter by the static method “of()”. “of()” in turn can take any type of parameter assuming its identity and type (!). Somehow, you can then “apply” objects of type String to the defined closure:

println.apply("one");
println.each("one", "two", "three");

SQL DSL’s in other languages

Like jOOQ, there are many other tools out there, that aim to implement SQL as an internal DSL in other languages. This one is particularly nice-looking. It’s called sqlkorma, a SQL DSL for Clojure. A sample SQL statement taken from their documentation:

(select users
  (with address) ;; include other entities based on
                 ;; their relationship
  (fields :first :last :address.state)
      ;; you can alias a field using a vector of [field alias]
  (aggregate (count :*) :cnt :status) 
      ;; You specify alias and optionally a field to group by
      ;; available aggregates:
      ;; sum, first, last, min, max, avg, count
  (where {:first "john"
          :last [like "doe"]}) 
  (order :id :ASC)
  (group :status)
  (limit 3)
  (offset 3))

These nice languages that support closures and/or lambda expressions make for much more intuitive aliasing than jOOQ can do today. I’m really looking forward to Java 8!

See more examples in the documentation:

http://sqlkorma.com/docs#db