The jOOQ User Manual : SQL building : SQL Statements (DML) : The SELECT statement : SELECT clause | previous : next |
Available in versions: Dev (3.17) | Latest (3.16) | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10 | 3.9 | 3.8 | 3.7
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.
SELECT clause
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The SELECT clause lets you project your own record types, referencing table fields, functions, arithmetic expressions, etc. The DSL type provides several methods for expressing a SELECT clause:
-- The SELECT clause SELECT BOOK.ID, BOOK.TITLE SELECT BOOK.ID, TRIM(BOOK.TITLE)
// Provide a varargs Fields list to the SELECT clause: Select<?> s1 = create.select(BOOK.ID, BOOK.TITLE); Select<?> s2 = create.select(BOOK.ID, trim(BOOK.TITLE));
Some commonly used projections can be easily created using convenience methods:
-- Simple SELECTs SELECT COUNT(*) SELECT 0 -- Not a bind variable SELECT 1 -- Not a bind variable
// Select commonly used values Result<?> result1 = create.selectCount().fetch(); Result<?> result2 = create.selectZero().fetch(); Result<?> result3 = create.selectOne().fetch();
Which are short forms for creating Column expressions from the org.jooq.impl.DSL API
-- Simple SELECTs SELECT COUNT(*) SELECT 0 -- Not a bind variable SELECT ? -- A bind variable
// Select commonly used values Result<?> result1 = create.select(count()).fetch(); Result<?> result2 = create.select(inline(0)).fetch(); Result<?> result3 = create.select(val(1)).fetch();
See more details about functions and expressions in the manual's section about Column expressions
The SELECT DISTINCT clause
The DISTINCT keyword can be included in the method name, constructing a SELECT clause
SELECT DISTINCT BOOK.TITLE
Select<?> select1 = create.selectDistinct(BOOK.TITLE).fetch();
SELECT *
jOOQ supports the asterisk operator in projections both as a qualified asterisk (through Table.asterisk()) and as an unqualified asterisk (through DSL.asterisk()). It is also possible to omit the projection entirely, in case of which an asterisk may appear in generated SQL, if not all column names are known to jOOQ.
Whenever jOOQ generates an asterisk (explicitly, or because jOOQ doesn't know the exact projection), the column order, and the column set are defined by the database server, not jOOQ. If you're using generated code, this may lead to problems as there might be a different column order than expected, as well as too many or too few columns might be projected.
// Explicitly selects all columns available from BOOK - No asterisk create.select().from(BOOK).fetch(); // Explicitly selects all columns available from BOOK and AUTHOR - No asterisk create.select().from(BOOK, AUTHOR).fetch(); create.select().from(BOOK).crossJoin(AUTHOR).fetch(); // Renders a SELECT * statement, as columns are unknown to jOOQ - Implicit unqualified asterisk create.select().from(table(name("BOOK"))).fetch(); // Renders a SELECT * statement - Explicit unqualified asterisk create.select(asterisk()).from(BOOK).fetch(); // Renders a SELECT BOOK.* statement - Explicit qualified asterisk create.select(BOOK.asterisk()).from(BOOK).fetch(); create.select(BOOK.asterisk(), AUTHOR.asterisk()).from(BOOK, AUTHOR).fetch();
With all of the above syntaxes, the row type (as discussed below) is unknown to jOOQ and to the Java compiler.
It is worth mentioning that in many cases, using an asterisk is a sign of an inefficient query because if not all columns are needed, too much data is transferred between client and server, plus some joins that could be eliminated otherwise, cannot.
Typesafe projections with degree up to 22
Since jOOQ 3.0, records and row value expressions up to degree 22 are now generically typesafe. This is reflected by an overloaded SELECT
(and SELECT DISTINCT
) API in both DSL and DSLContext. An extract from the DSL type:
// Non-typesafe select methods: public static SelectSelectStep<Record> select(Collection<? extends Field<?>> fields); public static SelectSelectStep<Record> select(Field<?>... fields); // Typesafe select methods: public static <T1> SelectSelectStep<Record1<T1>> select(Field<T1> field1); public static <T1, T2> SelectSelectStep<Record2<T1, T2>> select(Field<T1> field1, Field<T2> field2); public static <T1, T2, T3> SelectSelectStep<Record3<T1, T2, T3>> select(Field<T1> field1, Field<T2> field2, Field<T3> field3); // [...]
Since the generic R type is bound to some Record[N], the associated T type information can be used in various other contexts, e.g. the IN predicate. Such a SELECT
statement can be assigned typesafely:
Select<Record2<Integer, String>> s1 = create.select(BOOK.ID, BOOK.TITLE); Select<Record2<Integer, String>> s2 = create.select(BOOK.ID, trim(BOOK.TITLE));
For more information about typesafe record types with degree up to 22, see the manual's section about Record1 to Record22.
Feedback
Do you have any feedback about this page? We'd love to hear it!