jOOQ has been around for a while – since around 2009 as a publicly available library, and since 2013 as a commercially licensed product.
A lot of things have happened in 12 years. Here are 10 things that you maybe didn’t know about jOOQ.
1. eq, ne, gt, ge, lt, le are inspired by XSLT
What’s harder than naming a local variable?
Naming public API! The original jOOQ had methods like these to construct predicates:
But words such as greaterOrEqual() and others are kinda “heavy” in the middle of what is supposed to be a very readable, SQL style DSL, so a short version needed to be added. But what to name the short version? There are different opinions.
Since I love XSLT almost as much as I love SQL, it was natural to choose eq, ne, lt, le, gt, ge for these operators, just like in XSLT. Well, XPath, actually, to be precise. These are also available as HTML entities.
Fun fact, starting with jOOQ, we support also parsing these abbreviations in the SQL parser to support alternative Teradata syntax, see https://github.com/jOOQ/jOOQ/issues/11844 (yes, the almighty Teradata!)
-- Valid in Teradata, I mean, why not?
SELECT *
FROM t
WHERE id EQ 1
Other comparable libraries use eq, ne, lt, loe, gt, goe. But I could never sleep at night knowing the inconsistent length of names of these very similar operators.
2. API naming regrets
Some names, I wish I hadn’t chosen. The three most prominent ones are:
Field(it’s really more of a column or column expression. Field sounds so MS Excel-ish)Condition(The SQL standard calls it predicate)Record(the name is sound, but it now conflicts withjava.lang.Record, which is a pain. If only I had called itRow)
Alas, there’s no way these terms are ever going to be changed without breaking every jOOQ application out there for no good reason. Such is life of an API developer.
3. Overloads in jOOQ’s API are really untagged union types
Oh, if only Java were more like TypeScript. They have these beautiful first-class untagged union types, which we poor Java folks only know from exception catch blocks, where they don’t exist as a first-class language feature, but just as syntactic sugar:
type F<T> = String | Name | Field<T> | Select<? extends Record1<T>>
If “just” we had those in Java… (along with the above type aliases). Then all the troubles of maintaining a vast API like jOOQ would be gone, namely the ever present set of overloaded methods.
Just look at the overloads for DSL::substring. Just look:
And that isn’t covering all possible permutations, by far. A pragmatic decision was made that it is not too likely for the first argument to be a string bind variable. We hardly ever support Name unless the argument is really about a column reference, not an arbitary column expression, and the scalar subquery case (Select<? extends Record1<T>>) well, that’s just convenience.
If users still need the bind variable, they can wrap it with DSL.val("value"). If Java did have untagged union types, however, the API would look more like this:
static Field<String> substring(
F<String> string,
F<? extends Number> startingPosition,
F<? extends Number> length = null
) { ... }
Where F is the above union type. This would add a lot of convenience to a DSL like jOOQ because now, every permutation of argument types is supported. Alas, here we are, hand rolling the union type emulation through heavily overloaded methods, hoping IDE auto-completion doesn’t fail or become too slow (hello IntelliJ / Kotlin) 😅
4. Some SQL things are incredibly hard to abstract
Even after all those 12 years, there are still hard to solve bugs when trying to translate some SQL feature combinations to all 30 currently supported RDBMS.
I mean, try this on the almighty PostgreSQL, which isn’t even impressed by my lame attempts of creating not-everyday-SQL:
with t (a) as (
with u (b) as (
values (1)
)
select c
from (
with v (c) as (select b from u)
select c from v
union (
select c from v
union all
select c from v
order by c
)
order by c
) v
)
select a from t
It’s just an obfuscated way to write
values (1)
A few things here:
- We’re nesting CTE in CTE declarations
- We’re nesting CTE in derived tables
- We’re nesting unions in derived tables
- We’re nesting unions in union subqueries
- We’re ordering derived tables
- We’re ordering union subqueries
I mean, why not? But what about other dialects? Try converting this to alternative dialects on https://www.jooq.org/translate/, if you dare. Each one of those bullets (or several at a time), don’t work on some dialect. And not all of our translations work yet, for so many reasons.
These aren’t the most important bugs. They’re usually edge cases (e.g. the ORDER BY clauses are meaningless), but even then you want as much SQL to work on all of your dialects as possible, so we’re being kept busy, that’s for sure.
5. The pronunciation
Now it’s official (it always has been)
It’s pronounced dʒuːk (as in juke)
jOOQ is a recursive acronym that stands for jOOQ Object Oriented Querying. The “Object Oriented” stands for the API design, not how you’re supposed to use it. You’re supposed to use it in a functional programming style, duh. The jOOQ expression tree is following a composite pattern, if you will, and the SQL generation is implemented using a visitor pattern style approach, everything is encapsulated, etc. Just like you shouldn’t be tempted to say ess queue ell, you shouldn’t be tempted to say jay o o queue. It’s just dʒuːk for sequel. Hah!
6. RDBMS Bugs
jOOQ has helped discover a ton of RDBMS bugs maybe even more than the awesome https://github.com/sqlancer/sqlancer. When jOOQ integrates with a new dialect (e.g. EXASOL, recently), we discover a ton of bugs. See a list here: https://github.com/jOOQ/jOOQ/issues/1985, or for the recently supported Apache Ignite: https://github.com/jOOQ/jOOQ/issues/10551.
That’s because our integration tests are vast and cover all sorts of weird combinations of syntax that hardly anyone ever worries about such as the previous item 4. I always document each bug I find, either on the RDBMS issue tracker, if it’s public, or on Stack Overflow.
So, if you’re an RDBMS vendor and want us to test your SQL implementation, let us know! We’re for hire.
7. Mutability was a mistake
One of the biggest API design mistakes in jOOQ was mutability of the DSL, which can now hardly be removed. It’s even harder to change behaviour incompatibly than API. When API changes incompatibly, there are compilation errors. They’re a pain, but at least there aren’t any surprises.
Changing behaviour is a big no-go for a library. Here’s what I’m talking about:
SelectWhereStep<?> s =
ctx.select(T.A, T.B)
.from(T);
// Dynamic SQL how you shouldn't do it:
if (something)
s.where(T.C.eq(1));
if (somethingElse)
s.where(T.D.eq(2));
Result<?> result = s.fetch();
Yes, the DSL API is mutable, which is why the above works. It shouldn’t work, and you shouldn’t use this, but here we are. Our own little sun.misc.Unsafe disaster. It’s too late. Everyone is using it already.
Not all DSL elements are mutable, for example, expressions are not:
Condition c = noCondition();
// Has no effect
if (something)
c.and(T.C.eq(1));
if (somethingElse)
c.and(T.D.eq(2));
The above doesn’t work. You’ll notice soon enough, and fix it accordingly:
Condition c = noCondition();
if (something)
c = c.and(T.C.eq(1));
if (somethingElse)
c = c.and(T.D.eq(2));
So, there’s still mutation, but only of your local variable, not any jOOQ objects. That’s how the entire DSL ought to work. Or even better, you could use a functional programming style to implement dynamic SQL.
In any case, it will be very hard to change this behaviour without breaking everything, in very subtle ways, because you can’t easily detect mutable API usage. In jOOQ 3.15, we’ve started annotating the DSL API with a @CheckReturnValue annotation, which is getting picked up by some tools and IDEs, see e.g. https://youtrack.jetbrains.com/issue/IDEA-265263.
Luckily, this annotation will also cause a warning when you use the DSL API in a mutable fashion, because you’re supposed to consume the return value of that where(T.C.eq(1)) call. Perhaps there is a way to change this, after all, though chances are slim. Probably not worth the damage caused.
Yeah, such is the fate of every “lightweight library”, once it reaches maturity. It’s almost impossible to change its fundamental flaws anymore
8. Source and behavioural compatibility is very important in jOOQ
Behavioural incompatibilities
Behavioural incompatibilities are an absolute no-go in almost all libraries / products.
Source incompatibilities
Source incompatibilities are sometimes inevitable, at least in major releases, if there is a very compelling reason. Since we haven’t released a major release in almost a decade, we treat our minor releases as major.
An example of such an incompatibility in jOOQ was when we removed the convenience overloads that accepted Condition|Field<Boolean>|Boolean. The three things are the same in jOOQ:
Conditionis a SQL predicate (see item 2)Field<Boolean>is aConditionwrapped asField<Boolean>, which is quite nice in dialects with native support for theBOOLEANtypeBooleanis just a boolean bind variable, wrapped inDSL.val(boolean)
So, what’s wrong? The problem was the Boolean overload. It was intended for these kinds of usages:
// Turn off the entire query or subquery, dynamically
boolean featureFlag = ...;
.where(featureFlag)
So, rarely useful, only in edge cases. Again, what’s the problem? The problem was that users accidentally wrote this. All. The. Time.
.where(USER.NAME.equals(userName))
Can you spot the bug? It would not have happened if they had written USER.NAME.eq(userName), i.e. if they had used the XSLT style abbreviations.
Yes, they wrote equals() (as in Object::equals), not equal() (as in Field::equal). Simply typo. Still compiled. Chose the wrong overload. And looked almost correct. These would have been correct.
.where(USER.NAME.equal(userName))
.where(USER.NAME.eq(userName))
So, we deprecated, and then removed the overload, such that there is now a compilation error when using Object::equals. Seems a reasonable case for breaking source code, because that API’s usage was almost exclusively accidental.
How to test these things?
In order to make sure we don’t break behavioural or source compatibility, we have a ton (and I mean a ton) of tests. Behavioural compatibility is checked via unit and integration tests, making sure complex, and weird usages of jOOQ API continue to produce the same results on all of our supported RDBMS.
Source compatibility is checked via a ton of hand-written jOOQ API usage, which may be a bit more verbose than your average usage. For example, we don’t use var much in our own tests, though we heavily recommend you use it in your client code!
var result = ctx.select(T.A, multiset(..).as("fancy stuff")).fetch();
Instead, even in the most fancy statements using the new MULTISET operator, which can be quite heavy on the structural typing abuse of jOOQ, we always assign everything to explicitly typed variables that may look as threatening as this:
Result<Record4<
String, // FILM.TITLE
Result<Record2<
String, // ACTOR.FIRST_NAME
String // ACTOR.LAST_NAME
>>, // "actors"
Result<Record1<String>>, // CATEGORY.NAME
Result<Record4<
String, // CUSTOMER.FIRST_NAME
String, // CUSTOMER.LAST_NAME
Result<Record2<
LocalDateTime, // PAYMENT.PAYMENT_DATE
BigDecimal // PAYMENT.AMOUNT
>>,
BigDecimal // "total"
>> // "customers"
>> result =
dsl.select(...)
Even if that type is not actually consumed or used in any way, e.g. when calling result.formatXML() on it. More tests = better. Any source incompatibility would immediately cause our tests to stop compiling, so we can be confident not to run into any surprises.
There are always weird things, nonetheless. It’s hard to achieve perfection. An example is this issue where rawtype compatibility was overlooked. I wonder if anyone actually makes sure their generics can be used safely and compatibly with raw types? Seems very hard in jOOQ’s case…
9. Binary compatibility is almost impossible to provide in jOOQ
But binary incompatibilities? In a DSL like jOOQ’s they’re probably completely impossible. An example is when we introduced support for the Teradata QUALIFY clause in jOOQ 3.12. Here’s a blog post explaining how our DSL works. Before supporting QUALIFY, the jOOQ WINDOW clause, supported via the SelectWindowStep had several window() method overloads like this:
// "extends SelectOrderByStep", because is window() methods are
// obviously optional. It's a hardly used feature
interface SelectWindowStep<R extends Record>
extends SelectOrderByStep<R> {
SelectOrderByStep<R> window(WindowDefinition... w);
}
Example usage:
select(T.A, count().over(w))
.from(T)
.window(w)
.orderBy(T.A)
.fetch();
Now, the QUALIFY clause comes after WINDOW (Teradata doesn’t support WINDOW, but if they did, they’d have to prepend it to QUALIFY, because WINDOW declares named window definitions, and QUALIFY consumes them, though you never know with SQL)
// "extends SelectQualifyStep" is a compatible change
interface SelectWindowStep<R extends Record>
extends SelectQualifyStep<R> {
// But these changes are not
SelectQualifyStep<R> window(WindowDefinition... w);
}
While the JVM allows overloading by return type (and that feature is used to implement generics and covariant overloads since Java 5), the Java language does not allow this. There’s no way a new version of this API can maintain the old byte code, at least not in Java. Kotlin supports such things, i.e. the ability to emit (synthetic?) methods in byte code that cannot be called from source code directly, purely for backwards compatibility reasons.
So, if you’re upgrading from jOOQ 3.11 to 3.12, and you were using the WINDOW clause, tough luck. You have to recompile your client code, otherwise your friend NoSuchMethodError will have a word at runtime only (because at compile time, everything still compiles).
I guess that binary compatibility is not such a huge problem anymore in most cases. We run CI/CD jobs all the time, and recompile everything all the time for various reasons. It’s unlikely that you keep your own internal libraries binary compatible for other teams to consume. Nevertheless, it would be nice if it weren’t a problem. Ultimately, pragmatism dictates that we can’t offer this service in jOOQ (such as the JDK does), and try really hard not to break binary compatibility at least in patch releases, but minor releases do not have any such guarantee.
10. The Logo
The current logo has been designed when jOOQ went commercial
Fun fact, I designed both of these logos myself. My wife never liked the old one. It looked like some bloody samurai to her with all bloody eyes 😅
The new one went through several iterations. All weird and colourful. When finally, a friend who is working in design told me a few very simple tricks:
- Black and white can be applied to all media (screens, print, etc.) very easily, compared to coloured logos.
- It can even be inverted to white and black.
- Squares are easier to manage in icons, headers, thumbnails, etc. than rectangles.
I took this a step further and made the entire logo quadratic, including the font, such that it doesn’t even require an SVG format to scale. It’s essentially a 20×20 bitmap. Can’t go more low budget yet effective than this! 😁