Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10
This documentation is for the unreleased development version of jOOQ. Click on the above version links to get this documentation for a supported version of jOOQ.
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); // 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).
Feedback
Do you have any feedback about this page? We'd love to hear it!