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:

Suis-je Groovy? No! What Pivotal’s Decision Means for Open Source Software

Today there was great news in the JVM ecosystem.

Pivotal, the company who is committed to OSS has become a bit less committed:

committed

The reaction in the community were largely summarised by the hashtag #jesuisgroovy:

The interesting part in Pivotal’s announcement is this one:

The decision to conclude its sponsorship of Groovy and Grails is part of Pivotal’s larger strategy to concentrate resources on accelerating both commercial and open source projects that support its growing traction in Platform-as-a-Service, Data, and Agile development. Pivotal has determined that the time is right to let further development of Groovy and Grails be led by other interested parties in the open source community who can best serve the goals of those projects.

The official announcement can be read here.

Groovy is not a viable business (for Pivotal)

In other words, Groovy is not a viable business for Pivotal. And it’s hard to disagree here. Groovy has never been created with any commercial interests. Like many Open Source projects, Groovy was created in order to make something “better”, mostly for the sake of it being better. Of course it was useful as it introduced a lot of nice features into the Java ecosystem at a time before all these new JVM languages popped up. And before all these new JVM languages finally had an effect on Java-the-language itself.

On the other hand, the Groovy website’s rather geeky look-and-feel has never made it seem as though virtually anyone had any commercial interests in the language or the platform for that matter. I’m not trying to be harsh here, Groovy is an awesome language, created with love. But maintaining an Open Source ecosystem is hard work. It costs a lot of money and effort. And in the case of Groovy, it is just very hard to disagree with the fact that there is probably little money to be made out of it.

How to make money out of Open Source

When we moved on from a purely Open Source jOOQ to a dual-licensed one, we were criticised a lot by people who realised that they might fall into the dual-licensing category who no longer gets to ride for free. This was of course a disappointing evolution for those people. We see it as one step forward for a product that doesn’t just want to implement l’art pour l’art. We believe that we’re adding value on a small scale to a select set of customers with real SQL problems, and we want to continue to do so. Thus, commercial interests are now the driving force behind our developments, and dual-licensing is the easiest way to achieve that on our own small scale.

Many of those who had criticised us claimed that we should create a support-based Open Source business model instead (like Pivotal!). In other words: Let “them” pay for support – whoever “they” are. But that is not a viable model in the long run.

We create fishing poles. We don’t want to compete with our customers, the fishermen.

In software, the vendor of some product shouldn’t commoditize the main driver for innovation: The product. They should sell the product and create an ecosystem and a market for consultants that will be much better at applying the product to some concrete customer’s business. It is a win-win-win situation for everyone:

  • Vendors get money from licenses
  • Consultants get money from their specialist knowledge
  • End-users get a better, cheaper solution with a lower cost of ownership

Although, the consultant is always the one whose work is commoditized in the long run as demand for the product increases, and more consultants pop up trying to make money from their consulting business.

Joel Spolsky has written an extremely interesting Strategy Letter on the idea of commoditizing a complementary product (support, in this case) to increase the demand for the primary product (license, in this case). In the case of a PaaS company like Pivotal, however, we can only guess that even the commoditization of a whole programming language and ecosystem is no longer sustainable enough to increase demand for their PaaS offerings.

If that is the truth, then other platforms like Spring are at the stake as well! If Groovy is not sustainable, why should Spring be?

Am I Groovy?

Suis-je Groovy? To get back to the original claim:

No, I’m not Groovy. Groovy and every other piece of Open Source software that does not in any direct way produce a commercial value for both the vendor and the consumer is doomed to fail in the long run. Open Source software has created a tremendous amount of value in our industry. Companies like ourselves wouldn’t be possible if we’d still pay millions for an operating system license. The question is not whether software is free as in beer or as in freedom. The question is whether anyone has any viable commercial interests in making a particular software element cheap or even free. If they don’t, well, the joke will be on you as the vendor might just stop doing it. Open Source or not.