jOOQ is an internal domain-specific language (DSL), modelling the SQL language (external DSL) in Java (the host language). The main mechanism of the jOOQ API is described in this popular article:
Anyone can implement an internal DSL in Java (or in most other host languages) according to the rules from that article.
An example SQL language feature: BOOLEANs
One of the nice things about the SQL language, however, is the
BOOLEAN type, which has been introduced late into the language as of SQL:1999. Sure, without booleans, you can just model
FALSE values via
0, and transform the predicates into the value using
CASE WHEN A = B THEN 1 ELSE 0 END
But with true
BOOLEAN support, you can do awesome queries like the following PostgreSQL query that is run against the Sakila database:
SELECT f.title, string_agg(a.first_name, ', ') AS actors FROM film AS f JOIN film_actor AS fa USING (film_id) JOIN actor AS a USING (actor_id) GROUP BY film_id HAVING every(a.first_name LIKE '%A%')
The above yields:
TITLE ACTORS ----------------------------------------------------- AMISTAD MIDSUMMER CARY, DARYL, SCARLETT, SALMA ANNIE IDENTITY CATE, ADAM, GRETA ANTHEM LUKE MILLA, OPRAH ARSENIC INDEPENDENCE RITA, CUBA, OPRAH BIRD INDEPENDENCE FAY, JAYNE ...
In other words, we’re looking for all the films where all the actors who played in the film contain the letter “A” in their first names. This is done via an aggregation on the boolean expression / predicate
first_name LIKE '%A%':
HAVING every(a.first_name LIKE '%A%')
Now, in the terms of the jOOQ API, this means we’ll have to provide overloads of the
having() method that take different argument types, such as:
// These accept "classic" predicates having(Condition... conditions); having(Collection<? extends Condition> conditions); // These accept a BOOLEAN type having(Field<Boolean> condition);
Of course, these overloads are available for any API method that accepts predicates / boolean values, not just for the
Condition condition1 = FIRST_NAME.like("%A%"); Field<Boolean> field = field(condition1); Condition condition2 = condition(field);
… and the overloads make conversion more conveniently implicit.
So, what’s the problem?
The problem is that we thought it might be a good idea to add yet another convenient overload, the
having(Boolean) method, where constant, nullable
BOOLEAN values could be introduced into the query, for convenience, which can be useful when building dynamic SQL, or commenting out some predicates:
DSL.using(configuration) .select() .from(TABLE) .where(true) // .and(predicate1) .and(predicate2) // .and(predicate3) .fetch();
The idea is that the
WHERE keyword will never be commented out, regardless what predicate you want to temporarily remove.
Unfortunately, adding this overload introduced a nuisance to developers using IDE auto-completion. Consider the following two method calls:
// Using jOOQ API Condition condition1 = FIRST_NAME.eq ("ADAM"); Condition condition2 = FIRST_NAME.equal("ADAM"); // Using Object.equals (accident) boolean = FIRST_NAME.equals("ADAM");
By (accidentally) adding a letter “s” to the
equal() method – mostly because of IDE autocompletion – the whole predicate expression changes semantics drastically, from a jOOQ expression tree element that can be used to generate SQL to an “ordinary” boolean value (which always yields
Prior to having added the last overload, this wasn’t a problem. The
equals() method usage wouldn’t compile, as there was no applicable overload taking a Java
// These accept "classic" predicates having(Condition condition); having(Condition... conditions); having(Collection<? extends Condition> conditions); // These accept a BOOLEAN type having(Field<Boolean> condition); // This method didn't exist prior to jOOQ 3.7 // having(Boolean condition);
After jOOQ 3.7, this accident started to go unnoticed in user code as the compiler no longer complained, leading to wrong SQL.
Conclusion: Be careful when designing an internal DSL. You inherit the host language’s “flaws”
Java is “flawed” in that every type is guaranteed to inherit from
java.lang.Object and with it, its methods:
In most APIs, this isn’t really that much of a problem. You don’t really need to re-use any of the above method names (please, don’t).
But when designing an internal DSL, these
Object method names (just like the language keywords) limit you in your design space. This is particularly obvious in the case of
We’ve learned, and we’ve deprecated and will remove the
having(Boolean) overload, and all the similar overloads again.