Available in versions: Dev (3.19) | Latest (3.18) | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10 | 3.9

Query By Example (QBE)

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

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);

Result<BookRecord> books =
DSL.using(configuration)
   .selectFrom(BOOK)
   .where(condition(book))
   .fetch();

Feedback

Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo