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!