New versions: Dev (3.15) | Latest (3.14) | 3.13 | 3.12 | 3.11 | 3.10 | 3.9 | 3.8 | Old versions: 3.7 | 3.6 | 3.5 | 3.4 | 3.3 | 2.6
The SELECT statement
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
When you don't just perform CRUD (i.e. SELECT * FROM your_table WHERE ID = ?), you're usually generating new record types using custom projections. With jOOQ, this is as intuitive, as if using SQL directly. A more or less complete example of the "standard" SQL syntax, plus some extensions, is provided by a query like this:
-- get all authors' first and last names, and the number -- of books they've written in German, if they have written -- more than five books in German in the last three years -- (from 2011), and sort those authors by last names -- limiting results to the second and third row, locking -- the rows for a subsequent update... whew! SELECT AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, COUNT(*) FROM AUTHOR JOIN BOOK ON AUTHOR.ID = BOOK.AUTHOR_ID WHERE BOOK.LANGUAGE = 'DE' AND BOOK.PUBLISHED > '2008-01-01' GROUP BY AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME HAVING COUNT(*) > 5 ORDER BY AUTHOR.LAST_NAME ASC NULLS FIRST LIMIT 2 OFFSET 1 FOR UPDATE
// And with jOOQ... Factory create = new Factory(connection, dialect); create.select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, count()) .from(AUTHOR) .join(BOOK).on(BOOK.AUTHOR_ID.eq(AUTHOR.ID)) .where(BOOK.LANGUAGE.eq("DE")) .and(BOOK.PUBLISHED.gt("2008-01-01")) .groupBy(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME) .having(count().gt(5)) .orderBy(AUTHOR.LAST_NAME.asc().nullsFirst()) .limit(2) .offset(1) .forUpdate();
Details about the various clauses of this query will be provided in subsequent sections
SELECT from single tables
A very similar, but limited API is available, if you want to select from single tables in order to retrieve TableRecords or even UpdatableRecords. The decision, which type of select to create is already made at the very first step, when you create the SELECT statement with the Factory:
public <R extends Record> SimpleSelectWhereStep<R> selectFrom(Table<R> table);
As you can see, there is no way to further restrict/project the selected fields. This just selects all known TableFields in the supplied Table, and it also binds <R extends Record> to your Table's associated Record. An example of such a Query would then be:
BookRecord book = create.selectFrom(BOOK) .where(BOOK.LANGUAGE.eq("DE")) .orderBy(BOOK.TITLE) .fetchAny();
The simple SELECT API is limited in the way that it does not support any of these clauses:
In most parts of this manual, it is assumed that you do not use the simple SELECT API. For more information about the simple SELECT API, see the manual's section about fetching strongly or weakly typed records.
Table of contents
- 4.2.2.1.
- SELECT clause
- 4.2.2.2.
- FROM clause
- 4.2.2.3.
- JOIN operator
- 4.2.2.4.
- WHERE clause
- 4.2.2.5.
- CONNECT BY clause
- 4.2.2.6.
- GROUP BY clause
- 4.2.2.7.
- HAVING clause
- 4.2.2.8.
- ORDER BY clause
- 4.2.2.9.
- LIMIT .. OFFSET clause
- 4.2.2.10.
- FOR UPDATE clause
- 4.2.2.11.
- UNION, INTERSECTION and EXCEPT
- 4.2.2.12.
- Oracle-style hints
The jOOQ User Manual : SQL building : SQL Statements : The SELECT statement | previous : next |
Feedback
Do you have any feedback about this page? We'd love to hear it!