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();
            cache.put(result);
        }

        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;
    }

    @Override
    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(
                ctx.sql(),
                sql -> DSL.using(connection).fetch(
                    ctx.sql(),
                    ctx.bindings()
                )
            );
        }

        // All other queries go to the database
        else {
            result = DSL.using(connection).fetch(
                ctx.sql(), 
                ctx.bindings()
            );
        }

        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);
lang.setName("German");
lang.store();

// 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
    .select(
        count(),
        count())
    .from(LANGUAGE)
    .fetchOne()
    .value1());

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

Conclusion

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:

gavin-king

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.

Opinion

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

java-8-trends

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 {
        task.call();
    }

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

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?

AFTER 2 MINUTES, THE WHOLE DARN BAR KNEW!

(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:
http://blog.jooq.org/2014/07/25/top-10-very-very-very-important-topics-to-discuss/

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:
http://blog.jooq.org/2013/02/26/the-golden-rules-of-code-documentation/

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:

Good:

  • 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

Bad:

  • 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.

Conclusion

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!

dzone-persistence

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:

Yak Shaving is a Good Way to Improve an API


Yak Shaving (uncountable):

  1. (idiomatic) Any apparently useless activity which, by allowing you to overcome intermediate difficulties, allows you to solve a larger problem.
  2. (idiomatic) A less useful activity done to consciously or unconsciously procrastinate about a larger but more useful task.

Both interpretations of the term Yak Shaving as explained by Wiktionary are absolutely accurate descriptions of most refactoring jobs. The Yak Shaving in refactoring itself can be described by this gif showing what happens when you want to change a light bulb:

light-bulb

However, when developing an API, it’s not such a bad idea to perform actual Yak Shaving (only the first interpretation, of course). Let’s look at an example why, from the daily work maintaining jOOQ.

The Task

For jOOQ 3.6, I wanted to implement a very simple feature. Feature #2639: Add stored procedure OUT values to DEBUG log output. This is not an important feature at all, but certainly very useful to a lot of jOOQ users. The idea is that every time you run a stored procedure with DEBUG logging activated, you’ll get the OUT parameters logged along with the procedure call. Here’s a visualisation:

debug-log-2

Now, the actual implementation would have been very easy. Just about 10 lines of code in the existing LoggerListener that already takes care of logging all the other things. But there were a couple of caveats, which reminded me of the above lightbulb changing gif:

The apparently useless activities

  1. There was no way to access the RETURN_VALUE meta information of a jOOQ Routine
  2. There was no easy way to access Routine IN and OUT values generically
  3. There was lifecycle event that modelled the moment when OUT parameters are fetched in jOOQ
  4. There was no way to format Routine OUT parameters in a nice way

Does this feel familiar? There is need for refactoring!

Now, this whole implementation is hidden in jOOQ’s internals. It wouldn’t matter too much for users, if this had been hacked together in one way or another. For instance, obviously the RETURN_VALUE meta information could be accessed through internal refactorings, the same is true for IN and OUT values. There are other lifecycle events that might have worked just as well, and formatting is easy to re-implement.

But this is a popular API that is used by many users who might profit from a cleaner solution. Thus, why don’t we simply refactor and implement:

  1. Add a public Routine.getReturnParameter() method
  2. Add public Routine.getValue() and setValue() methods
  3. Add ExecuteListener.outStart(ExecuteContext) and outEnd(ExecuteContext) to capture fetching of Routine OUT parameters
  4. Add Routine.outRecord() and Routine.inRecord() to view a Routine as a Record”

The thing is:

The API implementor is the first API consumer

It’s hard to foresee what API users really want. But if you’re implementing an API (or just a feature), and you discover that something is missing, always consider adding that missing thing to the public API. If it could be useful to yourself, internally, it could be even more useful to many others. This way, you turn one little nice feature into 5, amplifying the user love.

Don’t get me wrong. This doesn’t mean that every little piece of functionality needs to be exposed publicly, au contraire. But the fact that something is keeping you – as the maintainer from writing clean code might indicate that others implement the same hacky workarounds as you. And they won’t ask you explicitly for it!

Don’t believe it? Here’s an entirely subjective analysis of user feedback:

  • 0.2% – Hey, this is a cool product, I want to help the owner make it better, I’ll provide a very descriptive, constructive feature request and engage for the next 5 weeks to help implement it.
  • 0.8% – Whatever dudes. Make this work. Please.
  • 1.3% – Whatever dudes. Make this work. ASAP!
  • 4.0% – WTF is wrong with you guys? Didn’t you at least think about this once??
  • 4.7% – OK, I’m going to write this completely uninformed rant about this product now, which I hate so much. It makes my life completely miserable
  • 9.0% – Oh well, this doesn’t work. Let’s go home, it’s 17:00 anyways
  • 80.0% – Oh well, this didn’t work yesterday already. Let’s go home. It’s Friday, 16:00 anyways

Now, most of this list wasn’t meant entirely seriously, but you get the point. There may be those 0.2% of users / customers that love you and that actively engage with you. Others may still love you or at least like you, but they won’t engage. You have to guesstimate what they need.

So. Bottom line:

If you need it, they probably need it. Start Yak Shaving!

The Java Legacy is Constantly Growing


I’ve recently stumbled upon a very interesting caveat of the JDK APIs, the Class.getConstructors() method. Its method signature is this:

Constructor<?>[] getConstructors()

The interesting thing here is that Class.getConstructor(Class...) returns a Constructor<T>, with <T> being maintained:

Constructor<T> getConstructor(Class<?>... parameterTypes)

Why is there a difference, i.e. why doesn’t the first method return Constructor<T>[]?

Let’s consider the Javadoc:

Note that while this method returns an array of Constructor<T> objects (that is an array of constructors from this class), the return type of this method is Constructor<?>[] and not Constructor<T>[] as might be expected. This less informative return type is necessary since after being returned from this method, the array could be modified to hold Constructor objects for different classes, which would violate the type guarantees of Constructor<T>[].

59539500

That’s a tough one. Historically, here’s how this happened:

Java 1.0 / Oak: Arrays

In Java 1.0 (the immediate successor of the Oak programming language), arrays were already introduced. In fact, they have been introduced before the collections API, which was introduced in Java 1.2. Arrays suffer from all the problems that we know today, including them being covariant, which leads to a lot of problems at runtime, that cannot be checked at compile time:

Object[] objects = new String[1];
objects[0] = Integer.valueOf(1); // Ouch

Java 1.1: Reflection API

Short of a “decent” collections API, the only possible return type of the Class.getConstructors() method was Constructor[]. A reasonable decision at the time. Of course, you could do the same mistake as above:

Object[] objects = String.class.getConstructors();
objects[0] = Integer.valueOf(1); // Ouch

but in the addition to the above, you could also, rightfully, write this:

Constructor[] constructors  = String.class.getConstructors();
constructors[0] = Object.class.getConstructor();

// Muahahahahahahaha

Java 1.2: Collections API

Java has been backwards-compatible from the very early days, even from Oak onwards. There’s a very interesting piece of historic research about some of Oak’s backwards-compatibility having leaked into Java to this date in this Stack Overflow question.

While it would have been natural to design the reflection API using collections, now, it was already too late. A better solution might’ve been:

List getConstructors()

However, note that we didn’t have generics yet, so the array actually conveys more type information than the collection.

Java 1.5: Generics

In Java 5, the change from

Constructor[] getConstructors()

to

Constructor<?>[] getConstructors()

has been made for the reasons mentioned above. Now, the alternative API using a collection would definitely have been better:

List<Constructor<T>> getConstructors()

But the ship has sailed.

Java, the ugly wart

Java is full of these little caveats. They’re all documented in the Javadocs, and often on Stack Overflow. Just yesterday, we’ve documented a new caveat related to completely new API in Map and ConcurrentHashMap.

“Stewardship: the Sobering Parts,” a very good talk about all those caveats and how hard it is to maintain them by Brian Goetz can be seen here:

The summary of the talk:

When language designers talk about the language they're designing

When language designers talk about the language they’re designing

Avoid Recursion in ConcurrentHashMap.computeIfAbsent()


Sometimes we give terrible advice. Like in that article about how to use Java 8 for a cached, functional approach to calculating fibonacci numbers. As Matthias, one of our readers, noticed in the comments, the proposed algorithm may just never halt. Consider the following program:

public class Test {
    static Map<Integer, Integer> cache 
        = new ConcurrentHashMap<>();
 
    public static void main(String[] args) {
        System.out.println(
            "f(" + 25 + ") = " + fibonacci(25));
    }
 
    static int fibonacci(int i) {
        if (i == 0)
            return i;
 
        if (i == 1)
            return 1;
 
        return cache.computeIfAbsent(i, (key) -> {
            System.out.println(
                "Slow calculation of " + key);
 
            return fibonacci(i - 2) + fibonacci(i - 1);
        });
    }
}

It will run indefinitely at least on the following Java version:

C:\Users\Lukas>java -version
java version "1.8.0_40-ea"
Java(TM) SE Runtime Environment (build 1.8.0_40-ea-b23)
Java HotSpot(TM) 64-Bit Server VM (build 25.40-b25, mixed mode)

This is of course a “feature”. The ConcurrentHashMap.computeIfAbsent() Javadoc reads:

If the specified key is not already associated with a value, attempts to compute its value using the given mapping function and enters it into this map unless null. The entire method invocation is performed atomically, so the function is applied at most once per key. Some attempted update operations on this map by other threads may be blocked while computation is in progress, so the computation should be short and simple, and must not attempt to update any other mappings of this map.

The “must not” wording is a clear contract, which my algorithm violated, although not for the same concurrency reasons.

The Javadoc also reads:

Throws:

IllegalStateException – if the computation detectably attempts a recursive update to this map that would otherwise never complete

But that exception isn’t thrown. Neither is there any ConcurrentModificationException. Instead, the program just never halts.

The simplest use-site solution for this concrete problem would be to not use a ConcurrentHashMap, but just a HashMap instead:

static Map<Integer, Integer> cache = new HashMap<>();

Subtypes overriding super type contracts

The HashMap.computeIfAbsent() or Map.computeIfAbsent() Javadoc don’t forbid such recursive computation, which is of course ridiculous as the type of the cache is Map<Integer, Integer>, not ConcurrentHashMap<Integer, Integer>. It is very dangerous for subtypes to drastically re-define super type contracts (Set vs. SortedSet is greeting). It should thus be forbidden also in super types, to perform such recursion.

Further reference

While the contract issues are a matter of perception, the halting problem clearly is a bug. I’ve also documented this issue on Stack Overflow where Ben Manes gave an interesting answer leading to a previous (unresolved as of early 2015) bug report:

https://bugs.openjdk.java.net/browse/JDK-8062841

My own report (probably a duplicate of the above) was also accepted quickly, as:

https://bugs.openjdk.java.net/browse/JDK-8074374

While this is being looked at by Oracle, remember to:

Never recurse inside a ConcurrentHashMap.computeIfAbsent() method. And if you’re implementing collections and think it’s a good idea to write a possibly infinite loop, think again, and read our article:

Infinite Loops. Or: Anything that Can Possibly Go Wrong, Does)

Murphy is always right.

Follow

Get every new post delivered to your Inbox.

Join 2,572 other followers

%d bloggers like this: