|The jOOQ User Manual. Multiple Pages : SQL building : Table expressions : Aliased Tables||previous : next|
Available in ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The strength of jOOQ's code generator becomes more obvious when you perform table aliasing and dereference fields from generated aliased tables. This can best be shown by example:
-- Select all books by authors born after 1920, -- named "Paulo" from a catalogue: SELECT * FROM author a JOIN book b ON a.id = b.author_id WHERE a.year_of_birth > 1920 AND a.first_name = 'Paulo' ORDER BY b.title
// Declare your aliases before using them in SQL: Author a = AUTHOR.as("a"); Book b = BOOK.as("b"); // Use aliased tables in your statement create.select() .from(a) .join(b).on(a.ID.eq(b.AUTHOR_ID)) .where(a.YEAR_OF_BIRTH.gt(1920) .and(a.FIRST_NAME.eq("Paulo"))) .orderBy(b.TITLE);
As you can see in the above example, calling as() on generated tables returns an object of the same type as the table. This means that the resulting object can be used to dereference fields from the aliased table. This is quite powerful in terms of having your Java compiler check the syntax of your SQL statements. If you remove a column from a table, dereferencing that column from that table alias will cause compilation errors.
TODO document this