Over a million developers have joined DZone.

Using jOOQ With Groovy

· Java Zone

What every Java engineer should know about microservices: Reactive Microservices Architecture.  Brought to you in partnership with Lightbend.

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(
    'sa', '', 'org.h2.Driver')
a = T_AUTHOR.as("a")
b = T_BOOK.as("b")
   .select(a.FIRST_NAME, a.LAST_NAME, b.TITLE)
   .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 jOOQRecordHandler, 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 thejOOQ 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(
    '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:

Microservices for Java, explained. Revitalize your legacy systems (and your career) with Reactive Microservices Architecture, a free O'Reilly book. Brought to you in partnership with Lightbend.


Published at DZone with permission of Lukas Eder, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}