Aliased tables and fields

This version of the manual is outdated. For the latest version, follow this link: http://www.jooq.org/doc/3.0/manual.

The jOOQ User Manual. Multiple Pages : DSL or fluent API. Where SQL meets Java : Aliased tables and fields

# Aliasing Tables

A typical example of what you might want to do in SQL is this:

SELECT a.ID, b.ID
  FROM T_AUTHOR a
  JOIN T_BOOK b on a.ID = b.AUTHOR_ID

In this example, we are aliasing Tables, calling them a and b. The way aliasing works depends on how you generate your meta model using jooq-codegen (see the manual's section about generating tables). Things become simpler when you choose the instance/dynamic model, instead of the static one. Here is how you can create Table aliases in jOOQ:

Table<TBookRecord> book = T_BOOK.as("b");
Table<TAuthorRecord> author = T_AUTHOR.as("a");

// If you choose not to generate a static meta model, this becomes even better
TBook book = T_BOOK.as("b");
TAuthor author = T_AUTHOR.as("a");

Now, if you want to reference any fields from those Tables, you may not use the original T_BOOK or T_AUTHOR meta-model objects anymore. Instead, you have to get the fields from the new book and author Table aliases:

Field<Integer> bookID = book.getField(TBook.ID);
Field<Integer> authorID = author.getField(TAuthor.ID);

// Or with the instance field model:
Field<Integer> bookID = book.ID;
Field<Integer> authorID = author.ID;

So this is how the above SQL statement would read in jOOQ:

create.select(authorID, bookID)
      .from(author)
      .join(book).on(authorID.equal(book.getField(T_BOOK.AUTHOR_ID)));

// Or with the instance field model:
create.select(author.ID, book.ID)
      .from(author)
      .join(book).on(author.ID.equal(book.AUTHOR_ID))

# Aliasing nested selects as tables

There is an interesting, more advanced example of how you can select from an aliased nested select in the manual's section about nested selects

# Aliasing fields

Fields can also be aliased independently from Tables. Most often, this is done when using functions or aggregate operators. Here is an example:

  SELECT FIRST_NAME || ' ' || LAST_NAME author, COUNT(*) books
    FROM T_AUTHOR
    JOIN T_BOOK ON T_AUTHOR.ID = AUTHOR_ID
GROUP BY FIRST_NAME, LAST_NAME;

Here is how it's done with jOOQ:

Record record = create.select(
         concat(T_AUTHOR.FIRST_NAME, " ", T_AUTHOR.LAST_NAME).as("author"),
         count().as("books"))
      .from(T_AUTHOR)
      .join(T_BOOK).on(T_AUTHOR.ID.equal(T_BOOK.AUTHOR_ID))
      .groupBy(T_AUTHOR.FIRST_NAME, T_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"));

The jOOQ User Manual. Multiple Pages : DSL or fluent API. Where SQL meets Java : Aliased tables and fields

previous : next