The Jodd SQL Generator

On the jOOQ blog, we never grow tired of comparing ourselves with other free or commercial SQL builders. One of the most interesting ones that we’ve seen in the past was the MyBatis SQL Statement Builder. The funny thing about some of these approaches is the fact that “typesafety” is understood in terms of merely offering a fluent API.
Jodd is a platform claiming to offer the The Unbearable Lightness of Java. That’s actually a very nice claim that we can only support. So let’s have a look at the unbearable lightness of SQL query building with Jodd’s SQL Generator:

Boy bb = new Boy();
Girl bg = new Girl();

DbSqlBuilder dsb = sql()
     ._("select")      // "select"
     .columnsAll("bb") // "bb.ID, bb.GIRL_ID, bb.NAME"
     .columnsIds("bg") // "bb.NAME, bg.ID"
     ._(" from")       // " from" (hardcoded string)
     .table(bb, "bb")  // "BOY bb"
     .table(bg, "bg")  // ", GIRL bg"
     ._()              // " " (single space)
     .match("bb", bb)  // "(1=1)"  since all bb fields are null
     ._(" and ")       // " and "
     .match("bg", bg); // "(1=1)" since all bg fields are null.

I must say, I’m intrigued! What is your take on Jodd’s SQL building?

Squel – A SQL Query Builder for JavaScript

… Yes! You’ve read correctly. For JavaScript. OK, there has been quite a bit traction around server-side JavaScript through node.js. The brave ones among you brave enough to actually write JavaScript, writing SQL in JavaScript might seem like a good idea, then. So I have discovered this library called squel.js, which has a nice-looking GitHub-style website and a big fat disclaimer almost at the top:

NOTE: It is recommended that you do NOT create queries browser-side to run on the server as this massively increases your exposure to SQL Injection attacks.

Again. If such a disclaimer needs to be added at the top of your website, is it really a good idea to proceed, then? But it may be for the node.js folks. So let’s have a look at the syntax of Squel.

squel.select().from("students")

Does this look familiar? So far, it could also be jOOQ code. With this SQL builder API, you can also create select from derived tables:

alert(
    squel.select()
        .from(squel.select().from('students'), 's')
        .field('s.id')
);
/* SELECT s.id FROM (SELECT * FROM students) `s` */

Or perform JOINs:

alert(
    squel.select()
        .field("students.id")
        .from("students")
        .left_join("teachers", null, 
             "students.id = teachers.student_id")
        .right_join("jailed", "j", 
             "j.student_id = students.id")
);
/*  SELECT students.id FROM students
        LEFT JOIN teachers 
        ON (students.id = teachers.student_id)
        RIGHT JOIN jailed `j` 
        ON (j.student_id = students.id)
*/

Obviously, unlike Java SQL builders, this API is not typesafe, but it’s still interesting to see fluent APIs in other languages as well.

Using jOOQ With Groovy

Some people may be using jOOQ with Groovy for easy scripting. As with the existing jOOQ / Scala integration, some Groovy language features can be leveraged. Take the following example, for instance:

package org.jooq.groovy

import static org.jooq.impl.DSL.*
import static org.jooq.groovy.example.h2.Tables.*

import groovy.sql.Sql
import org.jooq.*
import org.jooq.impl.DSL

sql = Sql.newInstance(
    'jdbc:h2:~/scala-test', 
    'sa', '', 'org.h2.Driver')

a = T_AUTHOR.as("a")
b = T_BOOK.as("b")

DSL.using(sql.connection)
   .select(a.FIRST_NAME, a.LAST_NAME, b.TITLE)
   .from(a)
   .join(b).on(a.ID.eq(b.AUTHOR_ID))
   .fetchInto ({ 
       r -> println(
           "${r.getValue(a.FIRST_NAME)} " +
           "${r.getValue(a.LAST_NAME)} " +
           "has written ${r.getValue(b.TITLE)}"
       )
   } as RecordHandler)

Groovy is not such a typesafe language. When we miss the .on() clause in the above query, Groovy’s Eclipse IDE integration would indicate that the subsequent call to fetchInto() might not work at run time. But Groovy cannot be sure, just as much as the getValue() calls cannot be guaranteed to work in Groovy’s interpretation of what closures are. This is how Eclipse displays the above code:

jOOQ code written in Groovy

jOOQ code written in Groovy

What’s also interesting to see is that Groovy cannot infer the SAM (Single Abstract Method) interface type that would best match the fetchInto() call. We have to explicitly tell Groovy to coerce the closure to a jOOQ RecordHandler, and inside that RecordHandler, we cannot access the well-known type of r, which would be:

Record3<String, String, String>

Using jOOQ with Groovy is certainly possible, but also not as powerful as with Scala or with Java 8.

Alternative ways of writing SQL with Groovy

Apart from using a SQL query builder like jOOQ (obviously, as this is the jOOQ blog, or a syndication thereof), you can also use other ways of writing SQL in Groovy. The standard way is to use Groovy’s own SQL support, which is a lot more convenient string-based approach than JDBC directly. In fact, Groovy SQL is how JDBC should have been implemented in the first place:

import groovy.sql.Sql

sql = Sql.newInstance(
    'jdbc:h2:~/scala-test', 
    'sa', '', 'org.h2.Driver')
sql.eachRow('select * from t_author') { 
    println "${it.first_name} ${it.last_name}" 
}

Another, interesting approach is to leverage Groovy’s advanced internal DSL capabilities. Here’s an example by Ilya Sterin where he created a DSL for SQL creation in Groovy

Select select = sql.select ("table1") {
    join("table2", type: "INNER") {
        using(table1: "col1", table2: "col1")
    }
    join("table3", type: "OUTER") {
        using(table1: "col2", table2: "col2")
        using(table1: "col3", table2: "col3")
    }
    where("table1.col1 = 'test'")
    groupBy(table1: "col1", table2: "col1")
    orderBy(table1: "col1", table2: "col1")
}

Read the full blog post here:
http://ilyasterin.com/blog/2009/07/groovy-sql-builder.html

MentaBean, the ORM/SQL Builder of Mentawai

I ran across an incredible discovery just now. I’m always closely observing jOOQ-related topics on Stack Overflow, so I immediately saw these somewhat advertising answers to a dedicated jOOQ user’s question:

MentaBean is an ORM/SQL Builder that has recently been made independent from Mentawai, a library building on top of the Servlet specification to simplify the lives of thousands of brazilian developers (counting the number of messages in the forum).

I’ve had a Stack Overflow chat with Sergio Oliveira Jr., one of the developers behind MentaBean, as I always find it interesting to chat with other people suffering from the heaviness and complexity of the Hibernate/JPA stack. His maxim is remarkable, if I may quote him quoting St. Exupéry: “Perfection is achieved, not when there is nothing more to add, but when there is nothing left to take away.” This is a very poetic way of saying the same thing in eXtreme Programming lingo: Refactor Mercilessly. With this being your primary paradigm, I believe great, fun software can evolve. No need to say that I started to like Sergio :-)

While it doesn’t look at first as though Mentawai (and its associated MentaBean) will outperform the currently hyped Play ! Framework, or the well-established Wicket library, I still find it nice to see how much effort is put in OSS, worldwide.

See the Mentawai homepage for more details:

http://www.mentaframework.org/quick-start.jsp