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.

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

Tell us a little bit about yourself and join our newsletter (examples)

The SELECT statement

SELECT from single tables

Table of contents

Community

Support

Legal

Documentation