|The jOOQ User Manual : SQL building : Conditional expressions : Query By Example (QBE)||previous : next|
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
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).