Share jOOQ on Facebook
Share jOOQ on Twitter

jOOQ ships with its own DSL (or Domain Specific Language) that simulates 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.equal(b.AUTHOR_ID))
      .where(a.YEAR_OF_BIRTH.greaterThan(1920)
      .and(a.FIRST_NAME.equal("Paulo")))
      .orderBy(b.TITLE)
      .fetch();

We'll see how the aliasing works later in the section about aliased tables

jOOQ as an internal domain specific language in Java (a.k.a. the DSL-API)

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 in modern IDEs with syntax 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:

Factory create = new Factory(connection, dialect);
Result<?> result = create.select()
                         .from(AUTHOR)
                         .join(BOOK).on(BOOK.AUTHOR_ID.equal(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:

Factory create = new Factory(connection, dialect);
Result<?> result = create.select()
                         .join(BOOK).on(BOOK.AUTHOR_ID.equal(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();

History of SQL building and incremental query building (a.k.a. the non-DSL API)

Historically, jOOQ started out as an object-oriented SQL builder library like any other. This meant that all queries and their syntactic components were modeled as so-called QueryParts, which delegate SQL rendering and variable binding to child components. This part of the API will be referred to as the non-DSL API, which is still maintained and used internally by jOOQ for incremental query building. An example of incremental query building is given here:

Factory create = new Factory(connection, dialect);
SelectQuery query = create.selectQuery();
query.addFrom(AUTHOR);

// Join books only under certain circumstances
if (join) {
    query.addJoin(BOOK, BOOK.AUTHOR_ID.equal(AUTHOR.ID));
}

Result<?> result = query.fetch();

This query is equivalent to the one shown before using the DSL syntax. In fact, internally, the DSL API constructs precisely this QueryObject. Note, that you can always access the SelectQuery object to switch between DSL and non-DSL APIs:

Factory create = new Factory(connection, dialect);
SelectFinalStep select = create.select().from(AUTHOR);

// Add the JOIN clause on the internal QueryObject representation
SelectQuery query = select.getQuery();
query.addJoin(BOOK, BOOK.AUTHOR_ID.equal(AUTHOR.ID));

Mutability

Note, that for historic reasons, the DSL API mixes mutable and immutable behaviour with respect to the internal representation of the QueryPart being constructed. While creating conditional expressions, column expressions (such as functions) assumes immutable behaviour, creating SQL statements does not. In other words, the following can be said:

// Conditional expressions (immutable)
// -----------------------------------
Condition a = BOOK.TITLE.equal("1984");
Condition b = BOOK.TITLE.equal("Animal Farm");

// The following can be said
a       != a.or(b); // or() does not modify a
a.or(b) != a.or(b); // or() always creates new objects

// Statements (mutable)
// --------------------
SelectFromStep s1 = create.select();
SelectJoinStep s2 = s1.from(BOOK);
SelectJoinStep s3 = s1.from(AUTHOR);

// The following can be said
s1 == s2; // The internal object is always the same
s2 == s3; // The internal object is always the same

Mutability may be removed in a future version of jOOQ.

The jOOQ Logo