DSL or fluent API. Where SQL meets Java

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

# Overview

jOOQ ships with its own DSL (or Domain Specific Language) that simulates SQL as good as possible in Java. This means, that you can write SQL statements almost as if Java natively supported that syntax just like .NET's C# does with LINQ to SQL.

Here is an example to show you what that means. When you want to write a query like this in SQL:

-- Select all books by authors born after 1920,
-- named "Paulo" from a catalogue:
SELECT *
  FROM t_author a
  JOIN t_book b ON a.id = b.author_id
 WHERE a.year_of_birth > 1920
   AND a.first_name = 'Paulo'
 ORDER BY b.title

Result<Record> result =
create.select()
      .from(T_AUTHOR.as("a"))
      .join(T_BOOK.as("b")).on(a.ID.equal(b.AUTHOR_ID))
      .where(a.YEAR_OF_BIRTH.greaterThan(1920)
      .and(a.FIRST_NAME.equal("Paulo")))
      .orderBy(b.TITLE)
      .fetch();

You couldn't come much closer to SQL itself in Java, without re-writing the compiler. We'll see how the aliasing works later in the section about aliasing

# Table of contents

1. Complete SELECT syntax
2. Table sources
3. Conditions
4. Aliased tables and fields
5. Nested SELECT using the IN operator
6. Nested SELECT using the EXISTS operator
7. Other types of nested SELECT
8. UNION and other set operations
9. Functions and aggregate operators
10. Stored procedures and functions
11. Arithmetic operations and concatenation
12. The CASE clause
13. Type casting
14. When it's just easier: Plain SQL