|The jOOQ User Manual. Multiple Pages : SQL building : Column expressions : Aliased columns||previous : next|
Available in ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
Just like tables, columns can be renamed using aliases. Here is an example:
SELECT FIRST_NAME || ' ' || LAST_NAME author, COUNT(*) books FROM AUTHOR JOIN BOOK ON AUTHOR.ID = AUTHOR_ID GROUP BY FIRST_NAME, LAST_NAME;
Here is how it's done with jOOQ:
Record record = create.select( concat(AUTHOR.FIRST_NAME, inline(" "), AUTHOR.LAST_NAME).as("author"), count().as("books")) .from(AUTHOR) .join(BOOK).on(AUTHOR.ID.eq(BOOK.AUTHOR_ID)) .groupBy(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME) .fetchAny();
When you alias Fields like above, you can access those Fields' values using the alias name:
System.out.println("Author : " + record.getValue("author")); System.out.println("Books : " + record.getValue("books"));
In most SQL databases, aliasing of column expressions in top level selects is optional. The database will generate a column name that is roughly based on the expression for documentation purposes (e.g. when running the query in a tool like SQL Developer), but applications cannot rely on the name explicitly. This is not a problem as columns can still be referenced by index.
In a similar fashion, jOOQ will assume an unspecified, generated column name for column expressions, based on their content.
-- Arithmetic expression 1 + 2 -- Correlated subquery (SELECT 1 AS a)
// Arithmetic expression inline(1).plus(inline(2)); // Correlated subquery field(select(inline(1).as("a")));
These unnamed expressions can be used both in SQL as well as with jOOQ. However, do note that jOOQ will use Field.getName() to extract this column name from the field, when referencing the field or when nesting it in derived tables. In order to stay in full control of any such column names, it is always a good idea to provide explicit aliasing for column expressions, both in SQL as well as in jOOQ.