A DSL is a nice thing to have, it feels "fluent" and "natural", especially if it models a well-known language, such as SQL. But a DSL is always expressed in a host language (Java in this case), which was not made for exactly the same purposes as its hosted DSL. If it were, then jOOQ would be implemented on a compiler-level, similar to LINQ in .NET. But it's not, and so, the DSL is limited by language constraints of its host language. We have seen many functionalities where the DSL becomes a bit verbose. This can be especially true for:

You'll probably find other examples. If verbosity scares you off, don't worry. The verbose use-cases for jOOQ are rather rare, and when they come up, you do have an option. Just write SQL the way you're used to!

jOOQ allows you to embed SQL as a String into any supported statement in these contexts:

Apart from the general factory methods, plain SQL is also available in various other contexts. For instance, when adding a .where("a = b") clause to a query. Hence, there exist several convenience methods where plain SQL can be inserted usefully. This is an example displaying all various use-cases in one single query:

// You can use your table aliases in plain SQL fields
// As long as that will produce syntactically correct SQL
Field<?> LAST_NAME = create.field("a.LAST_NAME");
// You can alias your plain SQL fields
Field<?> COUNT1 = create.field("count(*) x");
// If you know a reasonable Java type for your field, you
// can also provide jOOQ with that type
Field<Integer> COUNT2 = create.field("count(*) y", Integer.class);
// Use plain SQL as select fields
create.select(LAST_NAME, COUNT1, COUNT2)
// Use plain SQL as aliased tables (be aware of syntax!)
.from("author a")
.join("book b")
// Use plain SQL for conditions both in JOIN and WHERE clauses
.on("a.id = b.author_id")
// Bind a variable in plain SQL
.where("b.title != ?", "Brida")
// Use plain SQL again as fields in GROUP BY and ORDER BY clauses
.groupBy(LAST_NAME)
.orderBy(LAST_NAME)
.fetch();