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?
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.
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:
/* SELECT s.id FROM (SELECT * FROM students) `s` */
Or perform JOINs:
"students.id = teachers.student_id")
"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.
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:
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:
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.