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:

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

Using jOOQ With Groovy

Alternative ways of writing SQL with Groovy

Like this:

Published by lukaseder

Leave a Reply Cancel reply