Integrating jOOQ with Grails Featuring the UWS-jOOQ Plugin


Introduction

Grails is a web framework aimed to boost development productivity. One of the main features is domain centric database schema generation. Applications built with Grails are able to update existing schema just before they start. To do this, Grails is using built-in domain mappers or migrations in more advanced cases. The goal of the UWS-jOOQ Grails-plugin is to integrate jOOQ into the existing Grails lifecycle in order to leverage features of jOOQ without compromising the ones provided by Grails.

This article is part of a series brought to you by the Germany based jOOQ integration partner UWS Software Service (UWS). UWS is specialised in custom software development, application modernisation and outsourcing with a distinct focus on the Java Enterprise ecosystem.

Why should I use jOOQ with Grails?

In enterprise applications we often face issues with Hibernate performance, lack of support of some statements or just too much hassle caused by the Hibernate model. Hibernate’s Query Language HQL often is not sophisticated enough to cope with some requirements. This forces us to use plain SQL, which is not bad and helps solving specific business problems. However in big projects where a larger group of people is involved and a product continuously evolves, type-safety is very precious but is thrown away when using plain SQL. That’s the moment where the jOOQ framework excels and the UWS-jOOQ Grails-Plugin comes into the game.

How can I Integrate jOOQ into Grails?

We tried to provide a simple integration of jOOQ into Grails using Grails built-in dependency resolution. Just add the following line to the plugins section of your BuildConfig.groovy:

compile ':uws-jooq:0.1.1'

Add plugin configuration to your Config.groovy:

jooq.dataSource = ['dataSource']
jooq.xmlConfigDir = "src/resources/jooq/"
jooq.generatedClassOutputDirectory = 'src/java'
jooq.generatedClassPackageName.dataSource = 'ie.uws.example'

As in this example the plugin allows to configure the datasources to be used and also some of the key-paths. Eventually you want to also take a look at a customized version of the Config.groovy in our sample integration project.

Next jOOQ needs an xml configuration file, which can be generated by the plugin using the following command. The plugin will use your existing datasources and their configuration to generate the jOOQ configuration:

grails jooq-generate-config

Now all the configuration is ready and it is time to get to one of the main features of jOOQ which is type-safe SQL. With the following command jOOQ will generate Java-classes which you want to use when writing SQL as they will give you compiler-based autocompletion:

grails jooq-init

Now that everything is in place, let’s say you would like to insert a new record into your database via jOOQ in one of your Controllers. It’s as simple as that:

class ExampleController {
  JooqService jooqService

  def insert() {
    DSLContext dsl = jooqService.dataSource	
    BookRecord record = dsl.newRecord(Tables.BOOK)
    record.author = "John"
    record.name = "Uws"
    record.version = 1
    record.store()
  }
}

How does the integration of jOOQ with Grails work under the hood?

In the example above you noticed the JooqService which is dependency-injected by Grails. The JooqService is your entrypoint when it comes to using jOOQ within Grails as it is able to pick your datasource and provide the jOOQ DSL context for you. When you have multiple datasources it also allows you to select a different datasource just by providing the name of it:

DSLContext dsl = jooqService.dataSource_custom

Note that autocompletion won’t tell you about the existence of a dataSource_custom field but JooqService will handle that for you.

In version 0.1 we added JooqService but DSLContext have to generated based on your databases schema. So it is important to execute jooq-init command every time you change your Grails domain model since this command compiles your code and executes all migrations so that latest Java-classes are generated on the latest database-schema. Thanks to this approach it is possible to generate structures even from an in-memory H2 database which will not be available right after the application will be shut down.

Best-practices for using jOOQ with Grails

Integrate legacy databases

You might face the situation where you have to connect to some legacy database using the Grails framework. It’s doable, for sure, but you have to create the right Hibernate mapping first or – with some luck – let Hibernate generate the right one for you. With this plugin you can just let jOOQ generate its Java-classes and you’re ready to communicate with the database using a fully type-safe DSL.

Let a database schema change break your code

It’s one of the most precious gifts when you know that something will break early. With using jOOQ in Grails it will happen during compilation time. When you are executing jooq-init, your application is compiled and the Java-classes are regenerated using the latest database schema. If the generated classes change, you will be notified that your code is not able to compile anymore. You can fix your SQL statements and ensure that your application won’t break during runtime.

Keep generated classes in your version control system

We recommend you to check in jOOQs generated classes into your VCS along with the rest of your applications source code. When you are using jOOQ classes to communicate with the database it’s mandatory for compilation to have those classes already defined. Do you use a different approach? Please let us know!

Roadmap

We’re planning to simplify integration even more and attach jooq-init into regular grails compilation process. Also we’d like to make our plugin harder to misuse (see jooqService section) and add the possibility to use jOOQ not only in services or controllers but also in plain Java classes.

Contribution to UWS-jOOQ Grails-Plugin

This software is distributed under the Apache License, Version 2.0. We want to keep this software free and provide services for people
who integrate jOOQ and Grails. If you’re interested in this project feel free to submit issues or pull requests to the project’s git repository.

Further reading

The following links provide additional information about the UWS-jOOQ Grails-Plugin :

General information about UWS or jOOQ can be found here:

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