7.6.1. The jOOQ DSL

jOOQ comes with its own DSL (for Domain Specific Language) that emulates SQL in Java. It allows you to write SQL statements almost as though Java actually supported them. Its effect is similar to what .NET in C# does with LINQ to SQL.

jOOQ uses an informal BNF notation modelling a unified SQL dialect suitable for most database engines. Unlike other, simpler frameworks that use the Fluent API or the chain method, the jOOQ-based BNF interface does not permit bad query syntax.

A simple SQL query:

  1. SELECT *
  2.   FROM author a
  3.   JOIN book b ON a.id = b.author_id
  4.  WHERE a.year_of_birth > 1920
  5.    AND a.first_name = 'Paulo'
  6.  ORDER BY b.title

In jOOQ it looks like this:

  1. Result<Record> result =
  2. dsl.select()
  3.    .from(AUTHOR.as("a"))
  4.    .join(BOOK.as("b")).on(a.ID.equal(b.AUTHOR_ID))
  5.    .where(a.YEAR_OF_BIRTH.greaterThan(1920)
  6.    .and(a.FIRST_NAME.equal("Paulo")))
  7.    .orderBy(b.TITLE)
  8.    .fetch();

The AUTHOR and BOOK classes describing the corresponding tables must be generated beforehand. The process of generating jOOQ classes according to the specified database schema was described earlier.

We specified table aliases for the AUTHOR and BOOK tables using the AS clause. Here is the same query in DSL without aliases:

  1. Result<Record> result =
  2. dsl.select()
  3.    .from(AUTHOR)
  4.    .join(BOOK).on(AUTHOR.ID.equal(BOOK.AUTHOR_ID))
  5.    .where(AUTHOR.YEAR_OF_BIRTH.greaterThan(1920)
  6.    .and(AUTHOR.FIRST_NAME.equal("Paulo")))
  7.    .orderBy(BOOK.TITLE)
  8.    .fetch();

Now we take a more complex query with aggregate functions and grouping:

  1. SELECT AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, COUNT(*)
  2. FROM AUTHOR
  3.   JOIN BOOK ON AUTHOR.ID = BOOK.AUTHOR_ID
  4. WHERE BOOK.LANGUAGE = 'DE'
  5.   AND BOOK.PUBLISHED > '2008-01-01'
  6. GROUP BY AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME
  7.   HAVING COUNT(*) > 5
  8. ORDER BY AUTHOR.LAST_NAME ASC NULLS FIRST
  9.   OFFSET 1 ROWS
  10.   FETCH FIRST 2 ROWS ONLY

In jOOQ:

  1. dsl.select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, count())
  2.    .from(AUTHOR)
  3.    .join(BOOK).on(BOOK.AUTHOR_ID.equal(AUTHOR.ID))
  4.    .where(BOOK.LANGUAGE.equal("DE"))
  5.    .and(BOOK.PUBLISHED.greaterThan("2008-01-01"))
  6.    .groupBy(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
  7.    .having(count().greaterThan(5))
  8.    .orderBy(AUTHOR.LAST_NAME.asc().nullsFirst())
  9.    .limit(2)
  10.    .offset(1)
  11.    .fetch();

‘Dialect’ in the jOOQ context represents not just the SQL dialect of the database but also the major version number of the database engine. The field ‘limit’, limiting the number of records returned, will be generated according to the SQL syntax available to the database engine. The example above used FIREBIRD_3_0, which supports OFFSET …​ FETCH. If we had specified the FIREBIRD_2_5 or just the FIREBIRD dialect, the ROWS clause would have been used instead.

You can build a query in parts. This will allow you to change it dynamically, to change the sort order or to add additional filter conditions.

  1. SelectFinalStep<?> select
  2.     = dsl.select()
  3.          .from(PRODUCT);
  5. SelectQuery<?> query = select.getQuery();
  6. switch (searchOper) {
  7.     case "eq":
  8.          query.addConditions(PRODUCT.NAME.eq(searchString));
  9.          break;
  10.     case "bw":
  11.          query.addConditions(PRODUCT.NAME.startsWith(searchString));
  12.          break;
  13.     case "cn":
  14.          query.addConditions(PRODUCT.NAME.contains(searchString));
  15.          break;
  16. }
  17. switch (sOrd) {
  18.     case "asc":
  19.          query.addOrderBy(PRODUCT.NAME.asc());
  20.          break;
  21.     case "desc":
  22.          query.addOrderBy(PRODUCT.NAME.desc());
  23.          break;
  24. }
  25. return query.fetchMaps();