Available in versions: Dev (3.19) | Latest (3.18) | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10 | 3.9
SQL to DSL mapping rules
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
jOOQ takes SQL as an external domain-specific language and maps it onto Java, creating an internal domain-specific language. Internal DSLs cannot 100% implement their external language counter parts, as they have to adhere to the syntax rules of their host or target language (i.e. Java). This section explains the various problems and workarounds encountered and implemented in jOOQ.
SQL allows for "keywordless" syntax
SQL syntax does not always need keywords to form expressions. The UPDATE .. SET clause takes various argument assignments:
UPDATE t SET a = 1, b = 2
update(t).set(a, 1).set(b, 2)
The above example also shows missing operator overloading capabilities, where "=" is replaced by "," in jOOQ. Another example are row value expressions, which can be formed with parentheses only in SQL:
(a, b) IN ((1, 2), (3, 4))
row(a, b).in(row(1, 2), row(3, 4))
In this case, ROW is an actual (optional) SQL keyword, implemented by at least PostgreSQL.
SQL contains "composed" keywords
As most languages, SQL does not attribute any meaning to whitespace. However, whitespace is important when forming "composed" keywords, i.e. SQL clauses composed of several keywords. jOOQ follows standard Java method naming conventions to map SQL keywords (case-insensitive) to Java methods (case-sensitive, camel-cased). Some examples:
GROUP BY ORDER BY WHEN MATCHED THEN UPDATE
groupBy() orderBy() whenMatchedThenUpdate()
Future versions of jOOQ may use all-uppercased method names in addition to the camel-cased ones (to prevent collisions with Java keywords):
GROUP BY ORDER BY WHEN MATCHED THEN UPDATE
GROUP_BY() ORDER_BY() WHEN_MATCHED_THEN_UPDATE()
SQL contains "superfluous" keywords
Some SQL keywords aren't really necessary. They are just part of a keyword-rich language, the way Java developers aren't used to anymore. These keywords date from times when languages such as ADA, BASIC, COBOL, FORTRAN, PASCAL were more verbose:
-
BEGIN .. END -
REPEAT .. UNTIL -
IF .. THEN .. ELSE .. END IF
jOOQ omits some of those keywords when it is too tedious to write them in Java.
CASE WHEN .. THEN .. END
decode().when(.., ..)
The above example omits THEN and END keywords in Java. Future versions of jOOQ may comprise a more complete DSL, including such keywords again though, to provide a more 1:1 match for the SQL language.
SQL contains "superfluous" syntactic elements
Some SQL constructs are hard to map to Java, but they are also not really necessary. SQL often expects syntactic parentheses where they wouldn't really be needed, or where they feel slightly inconsistent with the rest of the SQL language.
LISTAGG(a, b) WITHIN GROUP (ORDER BY c)
OVER (PARTITION BY d)
listagg(a, b).withinGroupOrderBy(c)
.over().partitionBy(d)
The parentheses used for the WITHIN GROUP (..) and OVER (..) clauses are required in SQL but do not seem to add any immediate value. In some cases, jOOQ omits them, although the above might be optionally re-phrased in the future to form a more SQLesque experience:
LISTAGG(a, b) WITHIN GROUP (ORDER BY c)
OVER (PARTITION BY d)
listagg(a, b).withinGroup(orderBy(c))
.over(partitionBy(d))
SQL uses some of Java's reserved words
Some SQL keywords map onto Java Language Keywords if they're mapped using camel-casing. These keywords currently include:
-
CASE -
ELSE -
FOR
jOOQ replaces those keywords by "synonyms":
CASE .. ELSE PIVOT .. FOR .. IN ..
decode() .. otherwise() pivot(..).on(..).in(..)
There is more future collision potential with:
-
BOOLEAN -
CHAR -
DEFAULT -
DOUBLE -
ENUM -
FLOAT -
IF -
INT -
LONG -
PACKAGE
SQL operators cannot be overloaded in Java
Most SQL operators have to be mapped to descriptive method names in Java, as Java does not allow operator overloading:
= <>, != || SET a = b
equal(), eq() notEqual(), ne() concat() set(a, b)
For those users using jOOQ with Scala or Groovy, operator overloading and implicit conversion can be leveraged to enhance jOOQ:
= <>, != ||
=== <>, !== ||
SQL's reference before declaration capability
This is less of a syntactic SQL feature than a semantic one. In SQL, objects can be referenced before (i.e. "lexicographically before") they are declared. This is particularly true for aliasing
SELECT t.a FROM my_table t
MyTable t = MY_TABLE.as("t");
select(t.a).from(t)
A more sophisticated example are common table expressions (CTE), which are currently not supported by jOOQ:
WITH t(a, b) AS ( SELECT 1, 2 FROM DUAL ) SELECT t.a, t.b FROM t
Common table expressions define a "derived column list", just like table aliases can do. The formal record type thus created cannot be typesafely verified by the Java compiler, i.e. it is not possible to formally dereference t.a from t.
Feedback
Do you have any feedback about this page? We'd love to hear it!