| The jOOQ User Manual. Multiple Pages : SQL building : SQL Statements : The SELECT statement | previous : next |
All versions: 3.11 | 3.10 | 3.9 | 3.8 | 3.7 | Development versions: 3.12 | Unsupported versions: 3.6 | 3.5 | 3.4 | 3.3 | 3.2 | 2.6
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.
- The SELECT clause
- 4.2.2.2.
- The FROM clause
- 4.2.2.3.
- The JOIN clause
- 4.2.2.4.
- The WHERE clause
- 4.2.2.5.
- The CONNECT BY clause
- 4.2.2.6.
- The GROUP BY clause
- 4.2.2.7.
- The HAVING clause
- 4.2.2.8.
- The ORDER BY clause
- 4.2.2.9.
- The LIMIT .. OFFSET clause
- 4.2.2.10.
- The FOR UPDATE clause
- 4.2.2.11.
- UNION, INTERSECTION and EXCEPT
- 4.2.2.12.
- Oracle-style hints
| The jOOQ User Manual. Multiple Pages : SQL building : SQL Statements : The SELECT statement | previous : next |
