All versions: 3.11 | 3.10 | 3.9 | 3.8 | Development versions: 3.12

A popular approach to querying database tables is called Query by Example, meaning that an "example" of a result record is provided instead of a formal query:

-- example book record:
ID          : 
AUTHOR_ID   : 1
TITLE       :
PUBLISHED_IN: 1970
LANGUAGE_ID : 1
-- Corresponding query
SELECT *
FROM book
WHERE author_id = 1
AND published_in = 1970
AND language_id = 1

The translation from an example record to a query is fairly straight-forward:

  • If a record attribute is set to a value, then that value is used for an equality predicate
  • If a record attribute is not set, then that attribute is not used for any predicates

jOOQ knows a simple API called DSL.condition(Record), which translates a org.jooq.Record to a org.jooq.Condition:

BookRecord book = new BookRecord();
book.setAuthorId(1);
book.setPublishedIn(1970);
book.setLanguageId(1);

// Using the explicit condition() API
Result<BookRecord> books1 =
DSL.using(configuration)
   .selectFrom(BOOK)
   .where(condition(book))
   .fetch();
   
// Using the convenience API on DSLContext
Result<BookRecord> books2 = DSL.using(configuration).fetchByExample(book);

The latter API call makes use of the convenience API DSLContext.fetchByExample(TableRecord).

The jOOQ Logo