Tag Archive | jooq

A Wonderful SQL Feature: Quantified Comparison Predicates (ANY, ALL)

Have you ever wondered about the use-case behind SQL’s ANY (also: SOME) and ALL keywords?

You have probably not yet encountered these keywords in the wild. Yet they can be extremely useful. But first, let’s see how they’re defined in the SQL standard. The easy part:

8.7  <quantified comparison predicate>


    Specify a quantified comparison.


    <quantified comparison predicate> ::=
        <row value constructor> <comp op> 
            <quantifier> <table subquery>

    <quantifier> ::= <all> | <some>
    <all> ::= ALL
    <some> ::= SOME | ANY

Intuitively, such a quantified comparison predicate can be used as such:

-- Is any person of age 42?
42 = ANY (SELECT age FROM person)

-- Are all persons younger than 42?
42 > ALL (SELECT age FROM person)

Let’s keep with the useful ones. Observe that you have probably written the above queries with a different syntax, as such:

-- Is any person of age 42?
42 IN (SELECT age FROM person)

-- Are all persons younger than 42?
42 > (SELECT MAX(age) FROM person)

In fact, you’ve used the <in predicate>, or a greater than predicate with a <scalar subquery> and an aggregate function.

The IN predicate

It’s not a coincidence that you might have used the <in predicate> just like the above <quantified comparison predicate> using ANY. In fact, the <in predicate> is specified just like that:

8.4 <in predicate>

Syntax Rules

2) Let RVC be the <row value constructor> and let IPV 
   be the <in predicate value>.

3) The expression


   is equivalent to

     NOT ( RVC IN IPV )

4) The expression


   is equivalent to

     RVC = ANY IPV

Precisely! Isn’t SQL beautiful? Note, the implicit consequences of 3) lead to a very peculiar behaviour of the NOT IN predicate with respect to NULL, which few developers are aware of.

Now, it’s getting awesome

So far, there is nothing out of the ordinary with these <quantified comparison predicate>. All of the previous examples can be emulated with “more idiomatic”, or let’s say, “more everyday” SQL.

But the true awesomeness of <quantified comparison predicate> appears only when used in combination with <row value expression> where rows have a degree / arity of more than one:

-- Is any person called "John" of age 42?
(42, 'John') = ANY (SELECT age, first_name FROM person)

-- Are all persons younger than 55?
-- Or if they're 55, do they all earn less than 150'000.00?
(55, 150000.00) > ALL (SELECT age, wage FROM person)

See the above queries in action on PostgreSQL in this SQLFiddle.

At this point, it is worth mentioning that few databases actually support…

  • row value expressions, or…
  • quantified comparison predicates with row value expressions

Even if specified in SQL-92, it looks as most databases still take their time to implement this feature 22 years later.

Emulating these predicates with jOOQ

But luckily, there is jOOQ to emulate these features for you. Even if you’re not using jOOQ in your project, the following SQL transformation steps can be useful if you want to express the above predicates. Let’s have a look at how this could be done in MySQL:

-- This predicate
(42, 'John') = ANY (SELECT age, first_name FROM person)

-- ... is the same as this:
  SELECT 1 FROM person 
  WHERE age = 42 AND first_name = 'John'

What about the other predicate?

-- This predicate
(55, 150000.00) > ALL (SELECT age, wage FROM person)

-- ... is the same as these:
-- No quantified comparison predicate with
-- Row value expressions available
(55, 150000.00) > (
  SELECT age, wage FROM person

-- No row value expressions available at all
  SELECT 1 FROM person
  WHERE (55 < age)
  OR    (55 = age AND 150000.00 <= wage)

Clearly, the EXISTS predicate can be used in pretty much every database to emulate what we’ve seen before. If you just need this for a one-shot emulation, the above examples will be sufficient. If, however, you want to more formally use <row value expression> and <quantified comparison predicate>, you better get SQL transformation right.

Read on about SQL transformation in this article here.

The “Free”, “Standard”, “Open” Software Heresy

There are those people that have a strong, dogmatic belief in what they call “Free” or “Standard” or “Open” software. One of those individuals is Jimmie (let’s call him Jimmie in this article) who has responded to an article about Java persistence by Marco Behler on TheServerSide.

Let me cite Jimmie’s response here:

JPA is difficult but complete. It has a learning curve, and you’ll have surprises if you try to shortcut its complexities. But they mostly are there for a reason. Difficult stuff is difficult using JPA, that’s true.

JOOQ is quick to learn. And is proprietary stuff. Not free. Only one implementation. No public review, only one body involved in its evolution. SQL-oriented, not OO (ok, they say it’s a feature).
As a serious professional, learn JPA. Fully. There is no excuse for not knowing which sql queries are generated in your production app. Replacing it with a more basic framework is no solution.

Let’s not go deeply into the concrete difference between JPA and jOOQ / SQL. That topic has been discussed already in lengths on Reddit. Let’s consider the essence of the comparison as perceived by Jimmie. Because, Jimmie would probably say exactly the same thing when comparing

  • JSF with Ext.JS or ZK
  • PostgreSQL with Oracle
  • MS Office or Google Docs (probably OK cause “gratis”) with LibreOffice
  • Linux with Windows or MacOSX (although he might perform some doublethink as a Mac user)

Software not being free

Jimmie, Is YOUR software free and “not proprietary”? If so, how do you finance it? How do you earn a living? And why are you doing it? What really motivates you? What really motivates your customers and why?

Only one implementation

How many people actually do use alternatives to Hibernate and why? Are they using EclipseLink mainly because they used to use TopLink for the last 20 years and the learning curve (or benefit) to switch to Hibernate is too high? How often do you actually switch implementations? What keeps you from implementing the jOOQ API, and open-source its implementation?

And most importantly: Do you always adhere to the JPA API, even if Hibernate has lots of awesome, proprietary extensions that just happen to work so much better / easier?

No public review

Who exactly is “public”, and what are their main interests? Did you know that one of the major driving force for the JDK is Credit Suisse, being a large customer for Oracle in the Java environment, for instance? What is your stake and relation with Credit Suisse as your “public” representative?

Only one body involved in its evolution

Do you say that to YOUR customers also, about your own software as well?

SQL-oriented vs “a serious professional”

What’s not serious about SQL? In fact, SQL is reviewed by more entities than the JLS, let alone the JPA specs. Have you ever thought about that?

More basic

Fair enough. But don’t forget: You probably replaced your sophisticated EJB 2.0 framework (still a standard!) from the early 2000’s by a more basic one, which was (at the time) proprietary, had only one implementation, had no public review, nor multiple bodies involved in its evolution. It was, at the time, called Hibernate. And let me take the opportunity to cite Gavin King (creator of Hibernate) about when to use Hibernate:


My reply to you, Jimmie

According to you, JPA has to be learned fully. So I challenge you to also FULLY learn SQL, including all the SQL:2011 clauses, including

  • window functions
  • grouping sets
  • common table expressions
  • distinct/match/type/submultiset/unique predicates
  • time periods
  • partitioned outer joins
  • lateral joins
  • standard OFFSET pagination
  • contextually typed value specifications
  • quantified comparison predicates

… and of course all the details of interoperation between SQL and XQuery, one of the most popular aspects of the SQL:2011 standard!

And please, learn this FULLY, regardless of whether these things are part of your specific implementation. Because as a serious professional, you shall fully learn SQL. And while you’re at that, learn also everything about execution plans, and join, fetch, buffer caching, cursor caching and all other sorts of algorithms. Because there is no excuse for not knowing which SQL transformations are generated by your database’s CBO.

I know you like standards, Jimmie. But beware of the fact that there are some people out there who cannot wait for a standard to evolve to solve their problems. They may have more immediate problems. More specific problems. Simpler problems. Problems that might be solved only by proprietary software, so far. Or problems that are solved by proprietary software, that can be put into production with much less effort than your standards, Jimmie.

Lower time-to-market is what your customer might consider “professional”. Not whether this or that tech is used.

Someone always invents something proprietary at some time. It might just evolve into a standard. It might have been a bad idea and not evolve into anything. Or it might evolve into a standard and then be the worst standard ever. See again: EJB 2.0. I think we all agree on that, today.

No, Jimmie, the world isn’t black and white. It isn’t just about standards vs. proprietary. About free (libre) vs. commercial. About free (gratis) vs. “closed”. It’s about creating value for your customer.

Oh, and Jimmie. I sincerely hope you’re neither a Windows, nor a Mac user, because that wouldn’t be free, and there is only one implementation of each OS, and no public review, and only one body involved in their evolutions. And yet, the whole world runs on one of them.

Thanks for your attention, Jimmie.

Using Oracle AQ in Java Won’t Get Any Easier Than This

As recently announced in our newsletter, the upcoming jOOQ 3.5 will include an awesome new feature for those of you using the Oracle database: Native support for Oracle AQ! And your client code will be so easy to write, you’ll be putting those AQs all over your database immediately.

How does it work?

jOOQ rationale

The biggest reason why many of our users love jOOQ is our code generator. It generates a Java representation of your database schema, with all the relevant objects that you need when writing SQL. So far, this has included tables, sequences, user-defined-types, packages, procedures.

What’s new is that AQ objects are now also generated and associated with the generated object type.

A simple schema

Let’s consider writing this simple schema (all sources available on GitHub)

  ID         NUMBER(7),
  title      VARCHAR2(100 CHAR),
  language   VARCHAR2(2 CHAR)

  AS VARRAY(32) OF book_t

  ID         NUMBER(7),
  first_name VARCHAR2(100 CHAR),
  last_name  VARCHAR2(100 CHAR),
  books      books_t

  AS VARRAY(32) OF author_t

    queue_table => 'new_author_aq_t',
    queue_payload_type => 'author_t'

    queue_name => 'new_author_aq',
    queue_table => 'new_author_aq_t'

    queue_name => 'new_author_aq'

So, essentially, we have both OBJECT and VARRAY types for books and authors. You might prefer using TABLE types rather than VARRAY types, but for the sake of simplicity, we stick with VARRAY (as it isn’t so easy to use nested TABLE types with AQs in Oracle).

We have also created a queue that notifies listeners every time a new author is added to the database – along with their books. Imagine enqueue operations being done in a trigger on either the author or the book table.

jOOQ-generated code

When you run the jOOQ codegenerator (version 3.5 upwards) against the above schema, you’ll get a new Queues.java file, which contains:

public class Queues {
    public static final Queue<AuthorT> NEW_AUTHOR_AQ 
      = new QueueImpl<AuthorT>(
         "NEW_AUTHOR_AQ", SP, AUTHOR_T);

Obviously, also the previously shown OBJECT and VARRAY types are also generated by jOOQ, just like lables.

(of course, the actual naming patterns for generated Java code are completely configurable)

Using the generated artefacts

The above code is not really nicely formatted on this blog, but you don’t see any of this in your every day work. Because when you want to enqueue a message to this queue, you can simply write:

// Create a new OBJECT type with nested
// VARRAY type
AuthorT author = new AuthorT(
    new BooksT(
        new BookT(1, "1984", "en"),
        new BookT(2, "Animal Farm", "en")

// ... and simply enqueue that on NEW_AUTHOR_AQ
DBMS_AQ.enqueue(configuration, NEW_AUTHOR_AQ, author);

Seriously? That easy? Yes!

Compare the above to anything you’ve written before through JDBC, or using Oracle’s native APIs. You’ll find a couple of examples about how to serialise / deserialise RAW types, but frankly, queues are awesome because you can send OBJECT types through the database, and we don’t see those examples from Oracle. In fact, trust us, you don’t want to serialise OBJECT, VARRAY, or TABLE types through JDBC. You don’t. That’s our job. We’re hacking JDBC so you don’t have to.

Of course, you can also pass MESSAGE_PROPERTIES_T, ENQUEUE_OPTIONS_T, and DEQUEUE_OPTIONS_T types as arguments to the enqueue() and dequeue() methods.

Dequeuing is just as easy. The following will generate a blocking call and wait for the next AUTHOR_T message to arrive:

AuthorT author =
  DBMS_AQ.dequeue(configuration, NEW_AUTHOR_AQ);

That’s it. Can’t be that hard, can it?

jOOQ: The best way to use Oracle AQ in Java

Goodie: Java 8 and Oracle AQ

With the above simple API and Java 8, we can do what Oracle must’ve known long ago, when they renamed Oracle AQ’s marketing name to Oracle Streams. Let’s create a Java 8 Stream of AQ-produced OBJECT types with jOOQ. Easy as pie. Just write:

static <R extends UDTRecord<R>> Stream<R> stream(
    Configuration c, 
    Queue<R> queue
) {
    return Stream.generate(() -> 
        DBMS_AQ.dequeue(c, queue)

And now, use this beauty like so:

stream(configuration, NEW_AUTHOR_AQ)
    .forEach(author -> {
            author.getFirstName() + " " +

The above statement takes the next 10 messages dequeued this way and prints them to the console.

jOOQ Tip of the Day: Reuse Bind Values

jOOQ implements your SQL statements as AST (Abstract Syntax Tree). This means that your SQL statement is modelled in a non-text form prior to serialising it as a textual SQL statement to your JDBC driver.

One advantage of this is that you can freely manipulate this AST any way you want. This can be done using jOOQ’s SQL transformation capabilities, or in some cases much more simply directly in your client code.

Imagine, for instance, that you have a SQL statement where several bind values should be identical. This is a frequent problem in SQL, as SQL is verbose and repetitive. For instance:

-- Both "1" should in fact be the same value

-- Both "2" should in fact be the same value

With jOOQ, what you can do is this:

// Create a single bind value reference
Param<Integer> value = val(1);

// And use that reference several times in your query:
Select<?> query =

assertEquals(1, (int) query.fetchOne("a"));

// Now, for the second query, simply change the value

// ... and execute the query again
assertEquals(2, (int) query.fetchOne("a"));

As a jOOQ developer, you’re directly manipulating your SQL statement’s AST. Nothing keeps you from turning that AST into a directed graph (beware of cycles, of course), to improve your SQL expressiveness.

jOOQ: The best way to write SQL in Java

Top 10 Very Very VERY Important Topics to Discuss

Some things are just very very very VERY very important. Such as John Cleese.

The same is true for Whitespace:

Yes. 1080 Reddit Karma points (so urgently needed!) in only 23 hours. That’s several orders of magnitudes better than any of our – what we wrongfully thought to be – very deep and interesting technical insight about Java and SQL has ever produced.

The topic of interest was a humourous treatise about whether this:

for (int i=0; i<LENGTH; i++)

… or this:

for (int i = 0; i < LENGTH; i++)

… should be preferred. Obviously both options are completely wrong. The right answer is:

(   int i = 0
;   i < LENGTH
;   i++

Read the full treatise here.

But at some point, the whitespace discussion is getting stale. We need new very very very important topics to discuss instead of fixing them bugs. After all, the weekend is imminent, and we don’t know what else to talk about.

This is why we are now publishing…

Top 10 Very Very VERY Important Topics to Discuss

Here we go…

0. Whitespace

OK, that was a no-brainer. We’ve already had that. Want to participate? The very interesting Reddit discussion is still hot.

1. The Vietnam of Computer Science

In case you haven’t heard of this highly interesting discussion, there are some people who believe that ORMs are outdated, because ORMs don’t work as promised. And they’re totally right. And the best thing is, all the others are totally right as well.

Why is that great? Because that means we get to discuss it. Endlessly!

While everyone keeps talking about ORMs like that, no one cares what Gavin King (creator of Hibernate) had said from the beginning:

Why should we care about his opinion? We have our own, far superior opinion! Let’s have another discussion about why ORMs are evil!

2. Case-sensitivity

Unfortunately, us Java folks cannot have any of those very very very very very important discussions about casing, because unfortunately, Java is a case-sensitive language.

But take SQL (or PL/SQL, T-SQL for that sake). When writing SQL, we can have awesome discussions about whether we should

-- Upper case it all

-- Upper case keywords, lower case identifiers
SELECT tab.col1, tab.col2 FROM tab

-- Lower case keywords, upper case identifiers
select TAB.COL1, TAB.COL2 from TAB

-- Lower case it all
select tab.col1, tab.col2 from tab

-- Add some PascalCase (awesome SQL Server!)
SELECT Tab.Col1, Tab.Col2 FROM Tab

-- Mix case-sensitivity with case-insensitivity
-- (Protip to piss off your coworkers: Name your
-- table "FROM" or "INTO" and let them figure out
-- how to query that table)

-- PascalCase keywords (wow, MS Access)
Select TAB.COL1, TAB.COL2 From TAB

Now that is really incredibly interesting. And because this is so interesting and important, you can only imagine the number of interesting discussions we’ve had on the jOOQ User Group, for instance, about how to best generate meta data from the database. With jOOQ, we promise that you can extend these enticing discussions from the SQL area to the Java area by overriding the code generator’s default behaviour:

  • Should classes be PascalCased and literals be UPPER_CASED?
  • Should everything be PascalCased and camelCased as in Java?
  • Should everything be generated as named in the database?

Endless interesting discussions!

jOOQ: The Best Way to Write SQL in Java

We have so many options to SQL casing, which brings us to

3. SQL formatting

Unlike C-style general-purpose languages such as C, Java, Scala, C#, or even keyword-heavy ones Delphi, Pascal, Ada, SQL has one more awesome grounds for numerous discussions. It is not only keyword-heavy, but it also has a very complex and highly irregular syntax. So we’re lucky enough to get to choose (after long discussions and settlements) between:

-- All on one line. Don't tell me about print margins,
-- Or I'll telefax you my SQL!
SELECT table1.col1, table1.col2 FROM table1 JOIN table2 ON table1.id = table2.id WHERE id IN (SELECT x FROM other_table)

-- "Main" keywords on new line
SELECT table1.col1, table1.col2 
FROM table1 JOIN table2 ON table1.id = table2.id 
WHERE id IN (SELECT x FROM other_table)

-- (almost) all keywords on new line
SELECT table1.col1, table1.col2 
FROM table1 
JOIN table2 
ON table1.id = table2.id 
WHERE id IN (SELECT x FROM other_table)

-- "Main" keywords on new line, others indented
SELECT table1.col1, table1.col2 
FROM table1 
  JOIN table2 
  ON table1.id = table2.id 
  FROM other_table

-- "Main" keywords on new line, others heavily indented
SELECT table1.col1, table1.col2 
FROM table1 JOIN table2 
              ON table1.id = table2.id 
             FROM other_table)

-- Doge formatting
SUCH table1.col1,
    MUCH table1
JOIN table2 WOW table1.id
            = table2.id
WHICH              id IN
   (SUCH x

WOW other_table
Doge SQL Formatting

Doge SQL Formatting

And so on and so forth. Now any project manager should reserve at least 10 man-weeks in every project to agree on rules about SQL formatting.

4. The end of the DBA

Now THAT is a very interesting topic that is not only interesting for developers who are so knowledgeable about productive systems, no it’s also very interesting for operations teams. Because as we all know, the DBA is dead (again).

For those of you who have been missing out on this highly interesting topic, do know that all of this started (again) when the great NoSQL vs. SQL debate was initiated by brilliant minds and vendors of truly alternative systems. Which are now starting to implement SQL, because apparently, well… SQL isn’t all that bad:

Please, do engage in some more discussions about the best and only true way to tackle database problems. Because your opinion counts!

5. New lines and comments

Remember our own blog post about putting some keywords on new lines? Yes, we prefer:

// If this
if (something) {

// Else something else
else {

Exactly. Because this allows comments to be written where they belong: Next to the appropriate keyword, and always aligned at the same column. This leads us to the next very interesting question: Should we put comments in code at all? Or is clean code self-documenting?

And we say, why yes, of course we should comment. How on earth will anyone ever remember the rationale behind something like this??

// [#2744] DB2 knows the SELECT .. FROM FINAL 
// TABLE (INSERT ..) syntax
case DB2:

// Firebird and Postgres can execute the INSERT 
// .. RETURNING clause like a select clause. JDBC
// support is not implemented in the Postgres JDBC
// driver
case POSTGRES: {
    try {
        rs = ctx.statement().executeQuery();
    finally {
        consumeWarnings(ctx, listener);


Taken from our “hacking JDBC” page.

6. JSON is totally better than XML

Of course it is! Because… because… errr. Because it allows me to structure data hierarchically. Waaaait a second…


You’re saying, JSON and XML are the SAME THING!?

But MongoDB and PostgreSQL allow me to store JSON. Oh wait. They tried to store XML in databases, back in the 90s, too!? And it failed? Well, of course it failed, because XML sucks, right? (which is essentially another way of saying that I’ve never understood XSLT or XQuery or XPath or didn’t even hear about XProc, and I’m just ranting about angle brackets and namespaces)

Let’s further discuss this matter. I feel that we’re close to the very ultimate solution on that topic.

Speaking of JSON…

7. Curly braces

OMG! This is the most interesting of all topics. Should we put the opening brace:

  • On the same line?
  • On a NEW line??

The right answers are 1) and 3). 1) only if we absolutely have to, as in try or switch statements. We’re not paid by the number of lines of code, so we don’t add meaningless lines with only opening braces. And if we can omit the braces entirely, fine. Here’s an awesome statement, if you ask me:

if (something)
    for (String thing : list)
            if (somethingElse)
                break inner;
                continue outer;
        while (true);

That ought to teach them juniors not to touch my code. Which brings us to:

8. Labels

Nothing wrong with them. I’ll break out of my loops any time I want. Don’t tell me labels are Java’s way of saying GOTO, they’re much more sophisticated than that. (Besides, goto is a reserved word in Java, and it is an actual bytecode instruction). So I’ll happily do my jumping forward:

label: {
  // do stuff
  if (check) break label;
  // do more stuff

Or my jumping backward:

label: do {
  // do stuff
  if (check) continue label;
  // do more stuff
  break label;
} while(true);

(observe how the above example used two spaces for indentation instead of four (or tabs). Another great topic for further discussion)

9. emacs vs. vim vs. vi vs. Eclipse vs. IntelliJ vs. Netbeans

Can we please, PLEASE, have another very interesting discussion about which one of these is better? Please!

10. Last but not Least: Is Haskell better than [your language]?

According to TIOBE, Haskell ranks 38.

And as we all know, the actual market share (absolutely none in the case of Haskell) of any programming language is inversely proportional to the amount of time spent on reddit discussing the importance of said language, and how said language is totally superior to the one ranking 1-2 above on TIOBE, for instance. Which would be Lua.

So, I would love to invite you to join our blogging friends below to a very very interesting discussion about…

Now, of course, we could enlargen the debate and compare functional programming with OO programming in general before delving into why Scala is NOT a functional programming language, let alone Java 8.

Oh, and you think your dialect of Haskell or Lisp is not good enough, so you should roll your own? Go ahead (right after checking this checklist!)

Such great topics. So little time.


The great thing about these social networks like Reddit, Hackernews, and all the others is the fact that we can finally spend all day to discuss really really intersting topics instead of fixing them boring bugs our boss wants us to fix. After all, this is IMPORTANT.

Or as Randall Munroe would say: “Duty calls!”

Further reading

If you’re now all hot and ready to discuss things, please consider also reading these very interesting and insightful articles on how to best format and style code:

Or add your own. There’s still much much important writing to do!

jOOQ Newsletter: jOOLY 23, 2014 – Only 8 Days Left in jOOLY

subscribe to this newsletter here

Only 8 Days Left in jOOLY

Time is running so fast! The month of jOOLY is almost over – have you taken advantage of our limited-time promotional discount of 20% that we’re offering to all of your purchases in July 2014? And that’s not it, you will also get a free copy of the popular e-book SQL Performance Explained by Markus Winand, a book that we believe belongs on the shelf of every SQL developer.

Act now to get 20% off your next jOOQ purchase!

Tweet of the Day

Our customers, users, and followers are sharing their love for jOOQ with the world and we can hardly catch up with them! Here are:

Álvaro Hernández Tortosa, who has the final word on frameworks that hide SQL, because SQL is really powerful

Calvin Thomas, who Has come to an end of his search for the stack he direly needs. And that consists of AngularJS, Bootstrap, Play, Scala, jOOQ. Well done!

The famous Adam Bien, who explains how to properly use jOOQ in a Java EE context.

Thanks for the shouts, guys! You make the jOOQ experience rock!

New tiered pricing model

In the recent months, we have been having a lot of interesting discussions about our workstation-based pricing model, and how that fits in larger organisations with more fluctuation among team members.

We think of our workstation-based model as particularly fair because the price increases when more value is added – but we have heard the various concerns about simplifying the administration effort for large volumes. This is why we’re now officially offering a tiered pricing model on all subscriptions larger than 10 workstations.

If this is interesting for your organisation, please consider the updated license textcontaining prices (on page 17), or contact us directly.

Of course, if you act quickly, this offering can be combined with the “jOOLY” promotional discount to help you get even more value out of your next purchase!

jOOQ 3.5: Oracle AQ Support

The upcoming jOOQ 3.5 will ship with an extension to the code generator and the API that will make using Oracle AQ with jOOQ as easy as everything else!

Oracle AQ is a very powerful feature when you need to notify your database clients of data changes. Typical use-cases include triggers on updates needing to invalidate a UI cache for an “expensive” value.

If you’re using Oracle AQ with JDBC directly, however, you might be put off by the complexity of binding / loading OBJECT types from CallableStatements. Not with jOOQ.

This is what an enqueue call will look like:

DBMS_AQ.enqueue(conf, QUEUE_NAME, object);

And this is what a dequeue call will look like:

MyObjectType object = DBMS_AQ.dequeue(conf, QUEUE_NAME);

Both the MyObjectType and the QUEUE_NAME reference are generated objects with type information associated with them. This means, you can enqueue / dequeue just as if Java were the same as PL/SQL. Excited? We are!

Community Zone – The jOOQ aficionados have been active!

The jOOQ community has been very active again in the last month. We’re happy to point out these editor’s picks from our radar:

Tired of building with Maven? We’re very happy to announce Etienne Studer’s publication of a fully-functional gradle-jooq-plugin. This is a great community effort for those of you working with jOOQ and Gradle – or even Groovy in general.

Bert van Langen is a passionate DB2 DBA who has given us this excellent introduction to jOOQ on his blog. An alternative tutorial that should get new users started very quickly.

Marco Behler has published a treaties about the Java persistence ghetto (and how jOOQ might change that). We’re very glad to see that Marco is also coming to the same conclusion that we try to repeat time and again: Nothing keeps you from using JPA and SQL (e.g. in the form of jOOQ) in the same project.

Feedback zone

You’ve read to the end of this newsletter, that’s great! Did you like it? What did we do great? What can we improve? What other subjects would you like us to cover?

We’d love to hear from you, so if you want to reach out to us, just drop a message tocontact@datageekery.com. Looking forward to hearing from you!

Java 8 Friday: More Functional Relational Transformation

In the past, we’ve been providing you with a new article every Friday about what’s new in Java 8. It has been a very exciting blog series, but we would like to focus again more on our core content, which is Java and SQL. We will still be occasionally blogging about Java 8, but no longer every Friday (as some of you have already notice).

In this last, short post of the Java 8 Friday series, we’d like to re-iterate the fact that we believe that the future belongs to functional relational data transformation (as opposed to ORM). We’ve spent about 20 years now using the object-oriented software development paradigm. Many of us have been very dogmatic about it. In the last 10 years, however, a “new” paradigm has started to get increasing traction in programming communities: Functional programming.

Functional programming is not that new, however. Lisp has been a very early functional programming language. XSLT and SQL are also somewhat functional (and declarative!). As we’re big fans of SQL’s functional (and declarative!) nature, we’re quite excited about the fact that we now have sophisticated tools in Java to transform tabular data that has been extracted from SQL databases. Streams!

SQL ResultSets are very similar to Streams

As we’ve pointed out before, JDBC ResultSets and Java 8 Streams are quite similar. This is even more true when you’re using jOOQ, which replaces the JDBC ResultSet by an org.jooq.Result, which extends java.util.List, and thus automatically inherits all Streams functionality. Consider the following query that allows fetching a one-to-many relationship between BOOK and AUTHOR records:

Map<Record2<String, String>, 
    List<Record2<Integer, String>>> booksByAuthor =

// This work is performed in the database
// --------------------------------------

// This work is performed in Java memory
// -------------------------------------

   // Group BOOKs by AUTHOR

        // This is the grouping key      
        r -> r.into(AUTHOR.FIRST_NAME, 

        // This is the target data structure

        // This is the value to be produced for each
        // group: A list of BOOK
            r -> r.into(BOOK.ID, BOOK.TITLE),

The fluency of the Java 8 Streams API is very idiomatic to someone who has been used to writing SQL with jOOQ. Obviously, you can also use something other than jOOQ, e.g. Spring’s JdbcTemplate, or Apache Commons DbUtils, or just wrap the JDBC ResultSet in an Iterator…

What’s very nice about this approach, compared to ORM is the fact that there is no magic happening at all. Every piece of mapping logic is explicit and, thanks to Java generics, fully typesafe. The type of the booksByAuthor output is complex, and a bit hard to read / write, in this example, but it is also fully descriptive and useful.

The same functional transformation with POJOs

If you aren’t too happy with using jOOQ’s Record2 tuple types, no problem. You can specify your own data transfer objects like so:

class Book {
    public int id;
    public String title;

    public String toString() { ... }

    public int hashCode() { ... }

    public boolean equals(Object obj) { ... }

static class Author {
    public String firstName;
    public String lastName;

    public String toString() { ... }

    public int hashCode() { ... }

    public boolean equals(Object obj) { ... }

With the above DTO, you can now leverage jOOQ’s built-in POJO mapping to transform the jOOQ records into your own domain classes:

Map<Author, List<Book>> booksByAuthor =

        // This is the grouping key      
        r -> r.into(Author.class),

        // This is the grouping value list
            r -> r.into(Book.class),

Explicitness vs. implicitness

At Data Geekery, we believe that a new time has started for Java developers. A time where Annotatiomania™ (finally!) ends and people stop assuming all that implicit behaviour through annotation magic. ORMs depend on a huge amount of specification to explain how each annotation works with each other annotation. It is hard to reverse-engineer (or debug!) this kind of not-so-well-understood annotation-language that JPA has brought to us.

On the flip side, SQL is pretty well understood. Tables are an easy-to-handle data structure, and if you need to transform those tables into something more object-oriented, or more hierarchically structured, you can simply apply functions to those tables and group values yourself! By grouping those values explicitly, you stay in full control of your mapping, just as with jOOQ, you stay in full control of your SQL.

This is why we believe that in the next 5 years, ORMs will lose relevance and people start embracing explicit, stateless and magicless data transformation techniques again, using Java 8 Streams.

PostgreSQL’s Table-Valued Functions

Table-valued functions are an awesome thing. Many databases support them in one way or another and so does PostgreSQL. In PostgreSQL, (almost) everything is a table. For instance, we can write:

    f_1 (v1 INTEGER, v2 OUT INTEGER)
AS $$
    v2 := v1;
$$ LANGUAGE plpgsql;

… and believe it or not, this is a table! We can write:

select * from f_1(1);

And the above will return:

| v2 |
|  1 |

It’s kind of intuitive if you think about it. We’re just pushing out a single record with a single column. If we wanted two columns, we could’ve written:

AS $$
    v2 := v1;
    v3 := v1 + 1;
$$ LANGUAGE plpgsql;

… and then:

select * from f_2(1);

And the above will return:

| v2 | v3 |
|  1 |  2 |

That’s useful, but those are just single records. What if we wanted to produce a whole table? It’s easy, just change your functions to actually return TABLE types, instead of using OUT parameters:

AS $$
    SELECT *
    FROM (
        VALUES(v1, v1 + 1), 
              (v1 * 2, (v1 + 1) * 2)
    ) t(a, b);
$$ LANGUAGE plpgsql;

When selecting from the above very useful function, we’ll get a table like so:

select * from f_3(1);

And the above will return:

| v2 | v3 |
|  1 |  2 |
|  2 |  4 |

And we can LATERAL join that function to other tables if we want:

select *
from book, lateral f_3(book.id)

… which might yield, for example:

| id | title        | v2 | v3 |
|  1 | 1984         |  1 |  2 |
|  1 | 1984         |  2 |  4 |
|  2 | Animal Farm  |  2 |  4 |
|  2 | Animal Farm  |  4 |  6 |

In fact, it appears that the keyword LATERAL is optional in this case, at least for PostgreSQL.

Table-valued functions are very powerful!

Discovering table-valued functions

From jOOQ’s schema reverse-engineering perspective, things might get a bit tricky as can be seen in this Stack Overflow question. PostgreSQL deals with OUT parameters in a very similar way as with TABLE return types. This can be seen in the following query against the INFORMATION_SCHEMA:

SELECT r.routine_name, r.data_type, p.parameter_name, p.data_type
FROM   information_schema.routines r
JOIN   information_schema.parameters p
USING (specific_catalog, specific_schema, specific_name);

… and the output:

routine_name | data_type | parameter_name | data_type
f_1          | integer   | v1             | integer
f_1          | integer   | v2             | integer
f_2          | record    | v1             | integer
f_2          | record    | v2             | integer
f_2          | record    | v3             | integer
f_3          | record    | v1             | integer
f_3          | record    | v2             | integer
f_3          | record    | v3             | integer

As you can see, the output is really indistinguishable from that perspective. Luckily, we can also join the pg_catalog.pg_proc table, which contains the relevant flag to indicate if a function returns a set or not:

SELECT   r.routine_name, 
FROM     information_schema.routines r
JOIN     information_schema.parameters p
USING   (specific_catalog, specific_schema, specific_name)
JOIN     pg_namespace pg_n
ON       r.specific_schema = pg_n.nspname
JOIN     pg_proc pg_p
ON       pg_p.pronamespace = pg_n.oid
AND      pg_p.proname = r.routine_name
ORDER BY routine_name, parameter_name;

Now, we’re getting:

routine_name | data_type | parameter_name | data_type | proretset
f_1          | integer   | v1             | integer   | f
f_1          | integer   | v2             | integer   | f
f_2          | record    | v1             | integer   | f
f_2          | record    | v2             | integer   | f
f_2          | record    | v3             | integer   | f
f_3          | record    | v1             | integer   | t
f_3          | record    | v2             | integer   | t
f_3          | record    | v3             | integer   | t

We can see that f_3 is the only function actually returning a set of record, unlike f_1 and f_2, which only return a single record.

Now, remove all those parameters that are not OUT parameters, and you have your table type:

SELECT   r.routine_name, 
         row_number() OVER (
           PARTITION BY r.specific_name 
           ORDER BY p.ordinal_position
         ) AS ordinal_position
FROM     information_schema.routines r
JOIN     information_schema.parameters p
USING   (specific_catalog, specific_schema, specific_name)
JOIN     pg_namespace pg_n
ON       r.specific_schema = pg_n.nspname
JOIN     pg_proc pg_p
ON       pg_p.pronamespace = pg_n.oid
AND      pg_p.proname = r.routine_name
WHERE    pg_p.proretset
AND      p.parameter_mode = 'OUT'
ORDER BY routine_name, parameter_name;

Which will give us:

routine_name | parameter_name | data_type | position |
f_3          | v2             | integer   |        1 |
f_3          | v3             | integer   |        2 |

How to run such queries in jOOQ?

Once the above code is generated, you can easily call the table-valued function in any jOOQ query. Consider again the BOOK example (in SQL):

select *
from book, lateral f_3(book.id)

… and with jOOQ:

   .from(BOOK, lateral(F_3.call(BOOK.ID)))

The returned records then contain values for:


All that typesafety is only available in the upcoming jOOQ 3.5, for free! (SQL Server, Oracle, and HSQLDB table-valued functions are already supported!)

jOOQ is the best way to write SQL in Java

jOOQ Newsletter: July 2, 2014 – jOOLY 20% Discount Offering

Subscribe for this newsletter here

jOOLY 2014 20% Discount Offering

Have you been evaluating jOOQ for a while now, still hesitating to purchase licenses? Or are you an existing customer and looking into licensing more workstations for your team? This is your chance!

Get 20% off all your jOOQ purchases in the month of jOOLY 2014!

Google has their summer of code, we have our month of jOOLY, where you get 20% off all your purchases for jOOQ Professional and Enterprise Edition licenses. Just enter the “jOOLY” discount code with your next purchase and start coding awesome Java / SQL code! Click here to download and buy jOOQ now! The discount code is only valid until the end of jOOLY (July 31, 2014), so act quickly!

In fact, if you purchase jOOQ in the month of jOOLY (July), we’ll offer you a free PDF e-book copy of SQL Performance Explained with your purchase to help you get even more out of your jOOQ experience.

This discount cannot be combined with other discounts. Only the first period of a monthly or yearly subscription is discounted. Existing subscriptions are not eligible for this discount

Tweet of the Day

Our customers, users, and followers are sharing their love for jOOQ with the world and we can hardly catch up with them! Here are:

“Henk”, who challenges the JavaEE folks to add jOOQ to the standards. It’s about time!

Roland Tepp, who sees the added value of jOOQ in “database-first” applications. That’s very true

Chris Raastad, who … well. Who puts it bluntly and is looking forward to use the “last missing features” from .NET also in Java. We’re honoured to be compared to LINQ. Thanks Chris!

Thanks for the shouts, guys! You make the jOOQ experience rock!

Community Zone – The jOOQ aficionados have been active!

The jOOQ community has been very active again in the last month. We’re happy to point out these editor’s picks from our radar:

Speaking of LINQ, you may have heard of JINQ. JINQ now also has a jOOQ integration, allowing you to query the awesome the Java 8 Streams API as if it were a database. With jOOQ being the backing SQL implementation, you get all the advantages like typesafety, SQL transformation and standardisation, etc. So, let’s hear it for Dr. Ming-Yee Iu, from whom we’ll certainly hear more in the near future.

Instil Software from Belfast is hosting this great jOOQ and Flyway workshop on July 16, 2014. “Unfortunately,” it is already sold out, but we’re sure that this killer productivity combination will be interesting for numerous future workshops. If you’re missing out on it, read this blog post to get an overview of jOOQ and Flyway. If you’re hosting such a workshop yourself, let us know. We’ll be more than happy to advertise it in our events section of the jOOQ website.

If you haven’t seen it already, consider reading the ZeroTurnaround Java Tools and Technologies Landscape for 2014, a survey of 2164 Java professionals (slightly biased towards ZeroTurnaround’s RebelLabs audience). jOOQ is listed and compared to other Java / SQL integration patterns. We’re clearly catching up with well-established brands like MyBatis – so stay tuned for more community news as we keep growing.

Feedback zone

You’ve read to the end of this newsletter, that’s great! Did you like it? What did we do great? What can we improve? What other subjects would you like us to cover?

We’d love to hear from you, so if you want to reach out to us, just drop a message to contact@datageekery.com. Looking forward to hearing from you!

Flyway and jOOQ for Unbeatable SQL Development Productivity

When performing database migrations, we at Data Geekery recommend using jOOQ with Flyway – Database Migrations Made Easy. In this post, we’re going to look into a simple way to get started with the two frameworks.


There are a variety of ways how jOOQ and Flyway could interact with each other in various development setups. In this tutorial we’re going to show just one variant of such framework team play – a variant that we find particularly compelling for most use cases.

The general philosophy and workflow behind the following approach can be summarised as this:

  • 1. Database increment
  • 2. Database migration
  • 3. Code re-generation
  • 4. Development

The four steps above can be repeated time and again, every time you need to modify something in your database. More concretely, let’s consider:

  • 1. Database increment – You need a new column in your database, so you write the necessary DDL in a Flyway script
  • 2. Database migration – This Flyway script is now part of your deliverable, which you can share with all developers who can migrate their databases with it, the next time they check out your change
  • 3. Code re-generation – Once the database is migrated, you regenerate all jOOQ artefacts (see code generation), locally
  • 4. Development – You continue developing your business logic, writing code against the udpated, generated database schema

0.1. Maven Project Configuration – Properties

The following properties are defined in our pom.xml, to be able to reuse them between plugin configurations:


0.2. Maven Project Configuration – Dependencies

While jOOQ and Flyway could be used in standalone migration scripts, in this tutorial, we’ll be using Maven for the standard project setup. You will also find the source code of this tutorial on GitHub, and the full pom.xml file here.

These are the dependencies that we’re using in our Maven configuration:

<!-- We'll add the latest version of jOOQ 
     and our JDBC driver - in this case H2 -->

<!-- For improved logging, we'll be using 
     log4j via slf4j to see what's going
     on during migration and code generation -->

<!-- To esnure our code is working, we're
     using JUnit -->

0.3. Maven Project Configuration – Plugins

After the dependencies, let’s simply add the Flyway and jOOQ Maven plugins like so. The Flyway plugin:


    <!-- Note that we're executing the Flyway
         plugin in the "generate-sources" phase -->

    <!-- Note that we need to prefix the db/migration
         path with filesystem: to prevent Flyway
         from looking for our migration scripts
         only on the classpath -->

The above Flyway Maven plugin configuration will read and execute all database migration scripts from src/main/resources/db/migrationprior to compiling Java source code. While the official Flyway documentation suggests that migrations be done in the compile phase, the jOOQ code generator relies on such migrations having been done prior to code generation.

After the Flyway plugin, we’ll add the jOOQ Maven Plugin. For more details, please refer to the manual’s section about the code generation configuration.


    <!-- The jOOQ code generation plugin is also 
         executed in the generate-sources phase,
         prior to compilation -->

    <!-- This is a minimal working configuration. 
         See the manual's section about the code
         generator for more details -->

This configuration will now read the FLYWAY_TEST schema and reverse-engineer it into the target/generated-sources/jooq-h2 directory, and within that, into the org.jooq.example.flyway.db.h2 package.

1. Database increments

Now, when we start developing our database. For that, we’ll create database increment scripts, which we put into thesrc/main/resources/db/migration directory, as previously configured for the Flyway plugin. We’ll add these files:

  • V1__initialise_database.sql
  • V2__create_author_table.sql
  • V3__create_book_table_and_records.sql

These three scripts model our schema versions 1-3 (note the capital V!). Here are the scripts’ contents

-- V1__initialise_database.sql

CREATE SCHEMA flyway_test;
-- V2__create_author_table.sql
CREATE SEQUENCE flyway_test.s_author_id START WITH 1;

CREATE TABLE flyway_test.author (
  first_name VARCHAR(50),
  last_name VARCHAR(50) NOT NULL,
  date_of_birth DATE,
  year_of_birth INT,
  address VARCHAR(50),

-- V3__create_book_table_and_records.sql
CREATE TABLE flyway_test.book (
  author_id INT NOT NULL,
  title VARCHAR(400) NOT NULL,

  CONSTRAINT pk_t_book PRIMARY KEY (id),
  CONSTRAINT fk_t_book_author_id FOREIGN KEY (author_id) REFERENCES flyway_test.author(id)

INSERT INTO flyway_test.author VALUES (next value for flyway_test.s_author_id, 'George', 'Orwell', '1903-06-25', 1903, null);
INSERT INTO flyway_test.author VALUES (next value for flyway_test.s_author_id, 'Paulo', 'Coelho', '1947-08-24', 1947, null);

INSERT INTO flyway_test.book VALUES (1, 1, '1984');
INSERT INTO flyway_test.book VALUES (2, 1, 'Animal Farm');
INSERT INTO flyway_test.book VALUES (3, 2, 'O Alquimista');
INSERT INTO flyway_test.book VALUES (4, 2, 'Brida');

2. Database migration and 3. Code regeneration

The above three scripts are picked up by Flyway and executed in the order of the versions. This can be seen very simply by executing:

mvn clean install

And then observing the log output from Flyway…

[INFO] --- flyway-maven-plugin:3.0:migrate (default) @ jooq-flyway-example ---
[INFO] Database: jdbc:h2:~/flyway-test (H2 1.4)
[INFO] Validated 3 migrations (execution time 00:00.004s)
[INFO] Creating Metadata table: "PUBLIC"."schema_version"
[INFO] Current version of schema "PUBLIC": <>
[INFO] Migrating schema "PUBLIC" to version 1
[INFO] Migrating schema "PUBLIC" to version 2
[INFO] Migrating schema "PUBLIC" to version 3
[INFO] Successfully applied 3 migrations to schema "PUBLIC" (execution time 00:00.073s).

… and from jOOQ on the console:

[INFO] --- jooq-codegen-maven:3.5.0-SNAPSHOT:generate (default) @ jooq-flyway-example ---
[INFO] Using this configuration:
[INFO] Generating schemata      : Total: 1
[INFO] Generating schema        : FlywayTest.java
[INFO] ----------------------------------------------------------
[INFO] GENERATION FINISHED!     : Total: 337.576ms, +4.299ms

4. Development

Note that all of the previous steps are executed automatically, every time someone adds new migration scripts to the Maven module. For instance, a team member might have committed a new migration script, you check it out, rebuild and get the latest jOOQ-generated sources for your own development or integration-test database.

Now, that these steps are done, you can proceed writing your database queries. Imagine the following test case

import org.jooq.Result;
import org.jooq.impl.DSL;
import org.junit.Test;

import java.sql.DriverManager;

import static java.util.Arrays.asList;
import static org.jooq.example.flyway.db.h2.Tables.*;
import static org.junit.Assert.assertEquals;

public class AfterMigrationTest {

    public void testQueryingAfterMigration() throws Exception {
        try (Connection c = DriverManager.getConnection("jdbc:h2:~/flyway-test", "sa", "")) {
            Result<?> result =

            assertEquals(4, result.size());
            assertEquals(asList(1, 2, 3, 4), result.getValues(BOOK.ID));

If you run the mvn clean install again, the above integration test will now compile and pass!


The power of this approach becomes clear once you start performing database modifications this way. Let’s assume that the French guy on our team prefers to have things his way (no offense intended ;-) ):

-- V4__le_french.sql
ALTER TABLE flyway_test.book 
  ALTER COLUMN title RENAME TO le_titre;

They check it in, you check out the new database migration script, run

mvn clean install

And then observe the log output:

[INFO] --- flyway-maven-plugin:3.0:migrate (default) @ jooq-flyway-example ---
[INFO] --- flyway-maven-plugin:3.0:migrate (default) @ jooq-flyway-example ---
[INFO] Database: jdbc:h2:~/flyway-test (H2 1.4)
[INFO] Validated 4 migrations (execution time 00:00.005s)
[INFO] Current version of schema "PUBLIC": 3
[INFO] Migrating schema "PUBLIC" to version 4
[INFO] Successfully applied 1 migration to schema "PUBLIC" (execution time 00:00.016s).

So far so good, but later on:

[INFO] -------------------------------------------------------------
[ERROR] C:\...\AfterMigrationTest.java:[24,19] error: cannot find symbol
[INFO] 1 error

When we go back to our Java integration test, we can immediately see that the TITLE column is still being referenced, but it no longer exists:

public class AfterMigrationTest {

    public void testQueryingAfterMigration() throws Exception {
        try (Connection c = DriverManager.getConnection("jdbc:h2:~/flyway-test", "sa", "")) {
            Result<?> result =
                   //   ^^^^^ This column no longer exists. 
                   //   We'll have to rename it to LE_TITRE

            assertEquals(4, result.size());
            assertEquals(asList(1, 2, 3, 4), result.getValues(BOOK.ID));


This tutorial shows very easily how you can build a rock-solid development process using Flyway and jOOQ to prevent SQL-related errors very early in your development lifecycle – immediately at compile time, rather than in production!

Visit the Flyway website and the jOOQ website.


Get every new post delivered to your Inbox.

Join 1,964 other followers

%d bloggers like this: