The DSL API
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The DSL API is the primary way to construct queries or query parts in jOOQ. See the model API for an alternative way to interact with the jOOQ query object model.
jOOQ ships with its own DSL (or Domain Specific Language) that emulates SQL in Java. This means, that you can write SQL statements almost as if Java natively supported it, just like .NET's C# does with LINQ to SQL.
Here is an example to illustrate what that means:
-- Select all books by authors born after 1920, -- named "Paulo" from a catalogue: SELECT * FROM author a JOIN book b ON a.id = b.author_id WHERE a.year_of_birth > 1920 AND a.first_name = 'Paulo' ORDER BY b.title
Result<Record> result =
create.select()
.from(AUTHOR.as("a"))
.join(BOOK.as("b")).on(a.ID.eq(b.AUTHOR_ID))
.where(a.YEAR_OF_BIRTH.gt(1920)
.and(a.FIRST_NAME.eq("Paulo")))
.orderBy(b.TITLE)
.fetch();
We'll see how the aliasing works later in the section about aliased tables
Many other frameworks have similar APIs with similar feature sets. Yet, what makes jOOQ special is its informal BNF notation modelling a unified SQL dialect suitable for many vendor-specific dialects, and implementing that BNF notation as a hierarchy of interfaces in Java. This concept is extremely powerful, when using jOOQ with IDE syntax auto completion. Not only can you code much faster, your SQL code will be compile-checked to a certain extent. An example of a DSL query equivalent to the previous one is given here:
DSLContext create = DSL.using(connection, dialect);
Result<?> result = create.select()
.from(AUTHOR)
.join(BOOK).on(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
.fetch();
Unlike other, simpler frameworks that use "fluent APIs" or "method chaining", jOOQ's BNF-based interface hierarchy will not allow bad query syntax. The following will not compile, for instance:
DSLContext create = DSL.using(connection, dialect);
Result<?> result = create.select()
.join(BOOK).on(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
// ^^^^ "join" is not possible here
.from(AUTHOR)
.fetch();
Result<?> result = create.select()
.from(AUTHOR)
.join(BOOK)
.fetch();
// ^^^^^ "on" is missing here
Result<?> result = create.select(rowNumber())
// ^^^^^^^^^ "over()" is missing here
.from(AUTHOR)
.fetch();
Result<?> result = create.select()
.from(AUTHOR)
.where(AUTHOR.ID.in(select(BOOK.TITLE).from(BOOK)))
// ^^^^^^^^^^^^^^^^^^
// AUTHOR.ID is of type Field<Integer> but subselect returns Record1<String>
.fetch();
Result<?> result = create.select()
.from(AUTHOR)
.where(AUTHOR.ID.in(select(BOOK.AUTHOR_ID, BOOK.ID).from(BOOK)))
// ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
// AUTHOR.ID is of degree 1 but subselect returns Record2<Integer, Integer>
.fetch();
References to this page
- Different use cases for jOOQ
- jOOQ as a SQL builder without code generation
- jOOQ and backwards-compatibility
- The model API
- Design
- Optional column expressions
- Optional conditional expressions
- Optional conditional tables
- SQL Parser API
- Record1 to Record22
- jOOQ: Implementing the DSL types
- jOOQ: Referencing the Step types
Feedback
Do you have any feedback about this page? We'd love to hear it!