Hack up a Simple JDBC ResultSet Cache Using jOOQ’s MockDataProvider

Some queries shouldn’t hit the database all the time. When you query for master data (such as system settings, languages, translations, etc.), for instance, you may want to avoid sending the same silly query (and the results) over the wire all the time. For example:

SELECT * FROM languages

Most databases maintain buffer caches to accelerate these queries, so you don’t always hit the disk. Some databases maintain result set caches per cursor, or their JDBC drivers might even implement result set caches directly in the driver – a little known feature in Oracle, for instance:

SELECT /*+ RESULT_CACHE */ * FROM languages

But you may not be using Oracle, and because patching JDBC is a pain, you might have resorted to implementing the cache one or two layers up in the data access or service layer:

class LanguageService {
    private Cache cache;

    List<Language> getLanguages() {
        List<Language> result = cache.get();

        if (result == null) {
            result = doGetLanguages();

        return result;

Doing it in the JDBC layer, instead

While this might work fine on a per-service-and-method level, it might quickly become tedious when you query only parts of those results. E.g. what happens when you add an additional filter? Should you cache that query as well? Should you perform the filter on the cache, or hit the database at least once per filter?

class LanguageService {
    private Cache cache;

    List<Language> getLanguages() { ... }
    List<Language> getLanguages(Country country) {
        // Another cache?
        // Query the cache only and delegate to
        //     getLanguages()?
        // Or don't cache this at all?

wouldn’t it be nice if we had a cache of the form:

Map<String, ResultSet> cache;

… which caches re-usable JDBC ResultSets (or better: jOOQ Results) and returns the same results every time an identical query string is encountered.

Use jOOQ’s MockDataProvider for this

jOOQ ships with a MockConnection, which implements the JDBC Connection API for you, mocking all other objects, such as PreparedStatement, ResultSet, etc. We’ve already introduced this useful tool for unit testing in a previous blog post.

But you can “mock” your connection also in order to implement a cache! Consider the following, very simple MockDataProvider:

class ResultCache implements MockDataProvider {
    final Map<String, Result<?>> cache = 
        new ConcurrentHashMap<>();
    final Connection connection;

    ResultCache(Connection connection) {
        this.connection = connection;

    public MockResult[] execute(MockExecuteContext ctx)
    throws SQLException {
        Result<?> result;

        // Add more sophisticated caching criteria
        if (ctx.sql().contains("from language")) {

            // We're using this very useful new Java 8
            // API for atomic cache value calculation
            result = cache.computeIfAbsent(
                sql -> DSL.using(connection).fetch(

        // All other queries go to the database
        else {
            result = DSL.using(connection).fetch(

        return new MockResult[] { 
            new MockResult(result.size(), result)

Obviously, this is a very simplistic example. A real cache would involve invalidation (time-based, update-based, etc.) as well as more selective caching criteria than just matching on from language.

But the fact is that using the above ResultCache, we can now wrap all JDBC connections and prevent hitting the database more than once for all queries that query from the language table! An example using jOOQ API:

DSLContext normal = DSL.using(connection);
DSLContext cached = DSL.using(
    new MockConnection(new ResultCache(connection))

// This executs a select count(*) from language query
assertEquals(4, cached.fetchCount(LANGUAGE));
assertEquals(4, normal.fetchCount(LANGUAGE));

// Let's add another language (using normal config):
LanguageRecord lang = normal.newRecord(LANGUAGE);

// Checking again on the language table:
assertEquals(4, cached.fetchCount(LANGUAGE));
assertEquals(5, normal.fetchCount(LANGUAGE));

The cache works like a charm! Note that the current cache implementation is merely SQL string based (as it should be). If you modify the SQL string even only slightly, you’ll experience another cache miss and the query goes back to the database:

// This query is not the same as the cached one, it
// fetches two count(*) expressions. Thus we go back
// to the database and get the latest result.
assertEquals(5, (int) cached

// This still has the "stale" previous result
assertEquals(4, cached.fetchCount(LANGUAGE));


Caching is hard. Very hard. Apart from concurrency, naming things and off-by-one errors, it’s one of the three hardest problems in software.

This article doesn’t recommend to implement a cache at the JDBC level. You may or may not make that decision yourself. But when you do, then you can see how easy it is to implement such a cache using jOOQ.

jOOQ is the best way to write SQL in Java

And the best thing is that you don’t have to use jOOQ in all of your application. You can use it just for this particular use-case (and for mocking JDBC), and continue using JDBC, MyBatis, Hibernate, etc, as long as you patch other framework’s JDBC Connections with the jOOQ MockConnection.

jOOQ vs. Hibernate: When to Choose Which

Hibernate has become a de-facto standard in the Java ecosystem, and after the fact, also an actual JavaEE standard implementation if standards matter to you, and if you put the JCP on the same level with ISO, ANSI, IEEE, etc.

This article does not intended to discuss standards, but visions. Hibernate shares JPA’s vision of ORM. jOOQ shares SQL’s vision of powerful querying, so for the sake of the argument, let’s use Hibernate / JPA / ORM interchangeably, much like jOOQ / JDBC / SQL.

The question why should anyone not use Hibernate these days always shows up frequently – precisely because Hibernate is a de-facto standard, and the first framework choice in many other frameworks such as Grails (which uses GORM, which again uses Hibernate).

However, even Gavin King, the creator of Hibernate, doesn’t believe that Hibernate should be used for everything:


If that’s the case, are there any objective decision helping points that you could consider, when to use an ORM and when to use SQL?

Discussing on a high level

First off, let’s bring this discussion to a higher level. Instead of deciding between Hibernate and jOOQ as concrete implementations of their own domains, let’s think about ORM vs. SQL, and their different use-cases.

When deciding between an ORM (e.g. Hibernate) and SQL (e.g. jOOQ), the driving question that you should ask yourself is not the question of project complexity. Some of our most demanding customers are using jOOQ on medium-sized schemas with thousands of tables / views. Often, those schemas are extremely normalised and sometimes even deployed on as many as six different RDBMS. jOOQ was specifically designed to work in these scenarios, while keeping the simple use-case in mind as well.

So, instead of thinking about project complexity, ask yourself the following questions:

  • 1. Will your data model drive your application design, or will your application design drive your data model(s)?

    A main aspect here is the question whether you “care” about your database in the sense of whether it might survive your application. Very often, applications come and go. They may be re-written in Python / JavaScript, etc. 5 years down the line. Or you have multiple applications accessing the same database: Your Java application, some Perl scripts, stored procedures, etc. If this is the case, database design is a priority in your project, and jOOQ works extremely well in these setups.

    If you don’t necessarily “care” about your database in the sense that you just want to “persist” your Java domain somewhere, and this happens to be a relational database, then Hibernate might be a better choice – at least in early stages of your project, because you can easily generate your database schema from your Entity model.

  • 2. Will you do mostly complex reading and simple writing, or will you engage in complex writing?

    SQL really shines when reading is complex. When you join many tables, when you aggregate data in your database, when you do reporting, when you do bulk reading and writing. You think of your data in terms of set theory, e.g. your data as a whole. Writing CRUD with SQL is boring, though. This is why jOOQ also provides you with an ActiveRecord-style API that handles the boring parts, when you’re operating on single tables (Jason mentioned this).

    If, however, your writing becomes complex, i.e. you have to load a complex object graph with 20 entities involved into memory, perform optimistic locking on it, modify it in many different ways and then persist it again in one go, then SQL / jOOQ will not help you. This is what Hibernate has originally been created for.


I believe that data is forever. You should *always* assume that your database survives your application. It is much easier to rewrite (parts of) an application than to migrate a database. Having a clean and well-designed database schema will always pay off down the line of a project, specifically of a complex project. See also our previous article about the fallacy of “schemaless” databases.

Also, most projects really do 90% reading and 10% writing, writing often not being complex (2-3 tables modified within a transaction). This means that most of the time, the complexity solved by Hibernate / JPA’s first and second level caches is not needed. People often misunderstand these features and simply turn off caching, flushing Hibernate’s cache to the server all the time, and thus using Hibernate in the wrong way.

If, however, you’re undecided about the above two axes of decision, you can go the middle way and use jOOQ only for reporting, batch processing, etc. and use Hibernate for your CRUD – in a CQRS (Command Query Responsibility Segregation: http://martinfowler.com/bliki/CQRS.html) style. There are also quite a few jOOQ users who have chosen this path.

Further reading

One Year After Java 8’s Release, IDEs and Compilers are not Fully Ready Yet

One year ago, on March 18, 2014, Java SE 8 was released, and with it, the bliss of functional programming through lambda expressions and the streams API. These were great news for all of our Java ecosystem, and many people have already upgraded to Java 8. Stack Overflow already yields almost 2500 questions about Java 8 (with Brian Goetz himself answering). Also, Google Trends shows a massive increase in search volume


But even after one year, neither the javac compiler, nor the three major IDE compilers are fully Java 8 ready yet. This question by user meriton was asked on Stack Overflow, recently:

Lambda type inference infers an exception type not thrown by the lambda

The question shows the following piece of code:

class TestException extends Exception {

interface Task<E extends Exception> {
    void call() throws E;

public class TaskPerformer {

    private <E extends Exception> void perform(Task<E> task) throws E {

    public static void main(String[] args) {
        // compilation error
        new TaskPerformer().perform(() -> {
            try {
                throw new TestException();
            } catch (TestException e) {

The false positive compilation error has probably been fixed with issue 429430. In any case, it is not reproducible with Eclipse 4.5.0 M5, available as a developer build

With Java 8, compiling Java code hasn’t really become any easier than before. The above bug has been produced by a very subtle combination of:

  • Checked vs. unchecked exceptions
  • Generics (and exceptions)
  • Lambda expressions
  • Type inference
  • Flow analysis

If you’ve ever had a look at compiler source code, you cannot help but be glad that someone else is doing that job for you (the same is true when you look at jOOQ’s or Hibernate’s sources, by the way).

Where are we at with our compilers?

We’re getting there. My personal feeling is that early access releases of javac work best. For instance, I’m using

build 1.8.0_40-ea-b23

(disclaimer: this article was written before it was published. many problems are now gone with 1.8.0_40)

… although, you probably don’t want to go to production with such an early access release. IDEs building with javac, and Maven, of course, work equally well. Eclipse is lagging a little bit – which can be annoying at times.

Some of you non-Eclipse users might smirk and get your Eclipse vs. IntelliJ rants ready, and you know… there’s a saying about that:

A vegan, an IntelliJ user, a Mac user, and a Linux user walked into a bar.

How do I know?


(We actually have a whole article on that topic)

The fact is, all of the compiler teams are working hard to fix loads of bugs. IntelliJ, even while using javac to compile, may still display some false positives, visually in your IDE. Geertjan from NetBeans has just recently fixed a whole pile of bugs that we’ve reported. And Eclipse, well, Eclipse ships with their own very sophisticated incremental Java compiler. It’s a great compiler for rapid prototyping, but the drawback is that it compiles stuff slightly differently from others.

While developing jOOQ and also jOOλ, we’ve discovered quite a few bugs in Eclipse – many of them having been fixed already in Eclipse Mars. For instance:

We’re getting there. If you can, make use of lambdas and streams, and apply as much type inference in your code as possible. And please, if you do discover a bug, report it. We’re probably all using one of those three IDEs. Every bug that you report, is one less obstacle towards Java 8 adoption.

Here are the links to start registering bugs:

The 10 Things Everyone does Wrong when Committing Pull Requests

So, you’ve found a nice Open Source project that has added great value to your own work and you want to give back.

Before we move on, let me stress that this isn’t anything personal. This article doesn’t criticise anyone particular, and the ranty tone is just for your reading entertainment. I do not want to discourage you from contributing at all, neither to our own work (e.g. jOOQ, jOOλ, jOOX, etc.), nor to any other product. Open Source works also because of your enthusiasm.

Also, do not take this article as a discouragement of criticism. You can criticise software (like any other product) all you want. There is in fact an established measurement of such criticism, and we’ve done so excessively ourselves. But in this article, we’re assuming that you’ve already made a pull request, and why you did that wrong.

After all, be a sport. Take this article with humour and say to yourself…

The following “sins” are committed by so many people, I just had to write this down. Here they are:

The 10 Pull Request Commandments

1. Thou Shalt not reformat

OMG! Please! Never ever hit that sweet Ctrl-Shift-F (or whatever keys your IDE binds that command of doom to). There are only two exceptions to this rule, and that’s

  • When there’s a auto-format commit-hook anyway, if someone with a lot of power and ego insists on such rules (and millions of other extremely useful code lint rules).
  • If there’s an open issue in the bug tracker that reads “Re-format all teh codez”

But let’s face it. You’re not in any of the above situation.

We’re all adults and we all work with programming languages (which all suck), which is why we accept the fact that the code is poorly formatted.

2. Thou shalt absolutely not fix whitespace

In case you didn’t get #1, here’s a specific case of it: Whitespace. Yes, I know. This horrible OSS project has mixtures of "\t" and "  ", "    ", "        " scattered all over the place. It suffers from severe indecisiveness with respect to "\n", "\r" (damn mac users), and "\r\n".

It suffers severely from:

if (a == b) {
    if (c == d)
    } else {

It even has occurrences of

if (     a ==      b)

    if (

c ==               d
) {


So what? Swallow your pride. Don’t fix that. Does your pull request read “Implement a sophisticated algorithm to calculate the significance of the difference between the mean and median distance between stars in our universe” or does it read “Fix the darn formatting”?

The answer is in this interesting article here:

3. Thou Shalt not refactor

I know that this series of if-else could better be represented by a strategy pattern, and that we should really finally apply the chain of responsibility pattern for a cleaner separation of behaviour, and hey, if we can add a little command pattern, we can even undo the whole thing, and that the world would be a better place if we removed all static (so incredibly evil) and final.

But you’re not maintaining that code, and your pull request reads “Fix Javadoc typo”. So, no. You do not try to inject your superior design skills into the project that you won’t maintain any bit right after that clever pull request of yours.

4. Thou Shalt not move

Oh, this one is the worst of all. Why even think of this? Why does path.to.MyClass have to be moved over to other.path.to.MyClass? Or worse, to other.path.to.BetterClassName? If you get this right (and you’re lucky enough to be relying on git’s black magic file moving discovery), then it might not matter that much, but what if you’re using SVN or TFS or some other version control system where moving is an explicit action? You’ll screw up all history, you’ll produce diffs (even with git) that are hard to review. You’ll just help all hell break loose. So don’t move stuff. Leave it there. It sucks, yes, but… No!

5. Thou Shalt not rename

And why would you want to do that? OK, the variable name was called thing and grammatically correct, it should really be called things, but in fact the word thing doesn’t yet fully describe what the thing really is, so you rename it to objects

Added value? Zero.

Added version control history? Tons.

Added regression risk? Some. (have you thought of reflection, backwards compatibility, serialisation, etc. etc. whatnot)

This is called noise and you shouldn’t do it.

6. Thou shalt document

This is an absolute no-brainer. I’ve written a whole post about that alone:

To be fair, this one is highly maintainer-specific. Please ask about how to do documentation, if in doubt. Usually, just mimick the level of documentation that is already present, e.g. any of these:

  • None at all (what the hell)
  • Only Javadoc / API doc
  • API doc + lots of references to issues in the code
  • The whole code repeated as prosa
  • The whole code repeated as prosa in Shakespeare-English

7. Thou shalt not implement more than one thing in a single commit

This isn’t about whether you should have one or several commits per task. You should have only one.

This is about whether you should have several tasks in a single commit. You shouldn’t.

Imagine the code review process of the maintainer of the software you’re contributing to, when exposed to the following alternatives:


  • Fixed Javadoc typo
  • Fixed formatting (agreed with maintainer. I swear, I checked)
  • Added tests (note <- test driven development! Tests go first!)
  • Implemented tough complex algorithm
  • Implemented new API
  • Fixed typo


  • Heres teh codez (5 million files touched)

You get the point.

8. Thou shalt ask the vendor / community first

I know you really need this and that feature and you know exactly how to implement it, and are sooo ready to contribute, man this is exciting.

But here’s the ugly truth: No one has expected you, let alone waited for you and your contribution. Your contribution will cause a bit of work for the maintainer(s), who will have to maintain it for years down the line, while you’ll probably leave and never contribute again.

You can, of course, use a pull request as a documentation of what you plan to do, but if it’s not just a small typo or other trivial change, or something that’s on the roadmap anyway, just please ask first if you can do it, and if your implementation idea is along the lines of the project. You can save yourself and the maintainer a lot of unnecessary work.

9. Thou shalt not demand

If #8 above doesn’t resonate with you, here’s an additional point of view. You do not demand that your feature be implemented or that your pull request be merged. There are millions of reasons why things are done in one or another way in matured, popular OSS projects. You don’t understand them (yet), and you’ve based a lot of design decisions on your own local use-case. This use-case is very interesting and greatly appreciated, but it has to be planned and integrated thoroughly, not with the first implementation draft that came to mind.

So, if your pull request gets rejected (chances are high), don’t be angry. You might have just needed to consider #8 first.

10. Thou shalt accept the license terms

Here’s the thing. All Open Source projects / products out there are Open Source for a reason. Many of them (like jOOQ, for instance) are Open Source merely because Open Source allows the vendor to very easily distribute their software, getting a lot of marketing traction for the upselling offering. In the SaaS world and in many other worlds, this idea is called Freemium (someone actually went through the hassle of creating a website about “freemium”: http://www.freemium.org). Others want software to be free as in freedom. Again, others simply don’t care and use whatever license they find (often, ironically, the wrong one).

When you contribute, you will need to accept contributor license agreements, or rights and title transfer agreements. Some licenses consider their CLA to be implicit (e.g. read this article by Red Hat about why CLAs are unnecessary when contributing to Apache Licensed software). Others have more important reasons why they need a CLA or transfer agreement.

This is important to the maintainer of the software, and it should be important to you. If you care about free as in freedom (for instance), go back to #8 and ask the vendor / maintainer, if you can keep the copyright for your contribution, becoming a licensor of the software. If you do not follow #8 and provide your pull request before signing any agreements, well then just sign the thing, please. I mean, your ideology and dogma is important, but then again, not that important.


By now, you either had a laugh or you’re completely mad at me and this article. It wasn’t meant to be taken too seriously. But here’s the serious part to it. Open Source is everywhere and it is inevitable. GitHub gave Open Source an even bigger boost. Contributing is (technically) very easy. This has pros and cons.

Most often, contributions to projects that are hosted on GitHub are very welcome. But don’t take contributions for granted. Don’t take appreciation for your work for granted. Don’t take Open Source for granted. GitHub is only a hosting platform (before, we used to host on SourceForge). It is not an ideology. It doesn’t mean you can do anything you want with what’s hosted on GitHub.

TL;DR: Don’t just send a pull request. Engage with your vendor and with the vendor’s community. It will be a much more rewarding experience for everyone.

10 Java Articles Everyone Must Read

One month ago, we’ve published a list of 10 SQL Articles Everyone Must Read. A list of articles that we believe would add exceptional value to our readers on the jOOQ blog. The jOOQ blog is a blog focusing on both Java and SQL, so it is only natural that today, one month later, we’re publishing an equally exciting list of 10 Java articles everyone must read.

Note that by “must read”, we may not specifically mean the particular linked article only, but also other works from the same authors, who have been regular bloggers over the past years and never failed to produce new interesting content!

Here goes…

1. Brian Goetz: “Stewardship: the Sobering Parts”

The first blog post is actually not a blog post but a recording of a very interesting talk by Brian Goetz on Oracle’s stewardship of Java. On the jOOQ blog, we’ve been slightly critical about 1-2 features of the Java language in the past, e.g. when comparing it to Scala, or Ceylon.

Brian makes good points about why it would not be a good idea for Java to become just as “modern” as quickly as other languages. A must-watch for every Java developer (around 1h)

2. Aleksey Shipilёv: The Black Magic of (Java) Method Dispatch

In recent years, the JVM has seen quite a few improvements, including invokedynamic that arrived in Java 7 as a prerequisite for Java 8 lambdas, as well as a great tool for other, more dynamic languages built on top of the JVM, such as Nashorn.

invokedynamic is only a small, “high level” puzzle piece in the advanced trickery performed by the JVM. What really happens under the hood when you call methods? How are they resolved, optimised by the JIT? Aleksey’s article sub-title reveals what the article is really about:

“Everything you wanted to know about Black Deviously Surreptitious Magic in low-level performance engineering”

Definitely not a simple read, but a great post to learn about the power of the JVM.

Read Aleksey’s “The Black Magic of (Java) Method Dispatch

3. Oliver White: Java Tools and Technologies Landscape for 2014

We’re already in 2015, but this report by Oliver White (at the time head of ZeroTurnaround’s RebelLabs) had been exceptionally well executed and touches pretty much everything related to the Java ecosystem.

Read Oliver’s “Java Tools and Technologies Landscape for 2014

4. Peter Lawrey: Java Lambdas and Low Latency

When Aleksey has introduced us to some performance semantics in the JVM, Peter takes this one step further, talking about low latency in Java 8. We could have picked many other useful little blog posts from Peter’s blog, which is all about low-latency, high performance computing on the JVM, sometimes even doing advanced off-heap trickery.

Read Peter’s “Java Lambdas and Low Latency

5. Nicolai Parlog: Everything You Need To Know About Default Methods

Nicolai is a newcomer in the Java blogosphere, and a very promising one, too. His well-researched articles go in-depth about some interesting facts related to Java 8, digging out old e-mails from the expert group’s mailing list, explaining the decisions they made to conclude with what we call Java 8 today.

Read Nicolai’s “Everything You Need To Know About Default Methods

6. Lukas Eder: 10 Things You Didn’t Know About Java

This list wouldn’t be complete without listing another list that we wrote ourselves on the jOOQ blog. Java is an old beast with 20 years of history this year in 2015. This old beast has a lot of secrets and caveats that many people have forgotten or never thought about. We’ve uncovered them for you:

Read Lukas’s “10 Things You Didn’t Know About Java

7. Edwin Dalorzo: Why There Is Interface Pollution in Java 8

Edwin has been responding to our own blog posts a couple of times in the past with very well researched and thoroughly thought through articles, in particular about Java 8 related features, e.g. comparing Java 8 Streams with LINQ (something that we’ve done ourselves, as well).

This particular article explains why there are so many different and differently named functional interfaces in Java 8.

Read Edwin’s “Why There Is Interface Pollution in Java 8

8. Vlad Mihalcea: How Does PESSIMISTIC_FORCE_INCREMENT Lock Mode Work

When Java talks to databases, many people default to using Hibernate for convenience (see also 3. Oliver White: Java Tools and Technologies Landscape for 2014). Hibernate’s main vision, however, is not to add convenience – you can get that in many other ways as well. Hibernate’s main vision is to provide powerful means of navigating and persisting an object graph representation of your RDBMS’s data model, including various ways of locking.

Vlad is an extremely proficient Hibernate user, who has a whole blog series on how Hibernate works going. We’ve picked a recent, well-researched article about locking, but we strongly suggest you read the other articles as well:

Read Vlad’s “How Does PESSIMISTIC_FORCE_INCREMENT Lock Mode Work

9. Petri Kainulainen: Writing Clean Tests

This isn’t a purely Java-related blog post, although it is written from the perspective of a Java developer. Modern development involves testing – automatic testing – and lots of it. Petri has written an interesting blog series about writing clean tests in Java – you shouldn’t miss his articles!

Read Petri’s “Writing Clean Tests

10. Eugen Paraschiv: Java 8 Resources Collection

If you don’t already have at least 9 open tabs with interesting stuff to read after this list, get ready for a browser tab explosion! Eugen Paraschiv who maintains baeldung.com has been collecting all sorts of very interesting resources related to Java 8 in a single link collection. You should definitely bookmark this collection and check back frequently for interesting changes:

Read Eugen’s “Java 8 Resources Collection

Many other articles

There are, of course, many other very good articles providing deep insight into useful Java tricks. If you find you’ve encountered an article that would nicely complement this list, please leave a link and description in the comments section. Future readers will appreciate the additional insight.

3 Reasons why It’s Okay to Stick with SQL

The past decade has been an extremely exciting one in all matters related to data. We have had:

  • An ever increasing amount of data produced by social media (once called “Web 2.0”)
  • An ever increasing amount of data produced by devices (a.k.a. the Internet of Things)
  • An ever increasing amount of database vendors that explore “new” models

Marketers, publishers, and evangelists coined the term “Big Data” and “NoSQL” to wrap up the fuzzy ideas around persistence technology that have emerged in the last decade. But how big is “Big Data”? Do you do “Big Data”? Do you need to do “Big Data”? The truth is… you don’t. You can keep using the same architecture and relational databases you’ve always had, for these three reasons.

1. RAM prices are crumbling
2. SQL is the best language for querying
3. Procedural languages are ideal for computing

RDBMS have won in the past & will win again.

3 Reasons why It’s Okay to Stick with SQL is an article that we’ve published in collaboration with our content marketing partners at DZone. You will find the full article below in the free DZone guide to database and persistence management along with lots of other great content. Enjoy!


Integrating jOOQ with Grails Featuring the UWS-jOOQ Plugin


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

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!


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: