The Query and its various subtypes

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 : jOOQ classes and their usage : The Query and its various subtypes

# SELECT statements

There are essentially two ways of creating SELECT statements in jOOQ. For historical reasons, you can create org.jooq.SimpleSelectQuery or org.jooq.SelectQuery objects and add additional query clauses, such as Conditions or SortFields to it. Since jOOQ 1.3, there is also the possibility to create SELECT statements using jOOQ's DSL API in a much more intuitive and SQL-like way.

Use the DSL API when:

You want your code to look like SQL
You want your IDE to help you with auto-completion (you will not be able to write select .. order by .. where .. join or any of that stuff)

Use the regular API when:

You want to create your query step-by-step, creating query parts one-by-one
You need to assemble your query from various places, passing the query around, adding new conditions and joins on the way

In any case, all API's will construct the same underlying implementation object, and in many cases, you can combine the two approaches. Let's check out the various SELECT statement types:

org.jooq.Select: This Query subtype stands for a general type of SELECT statement. It is also the main Select type for the DSL API. When executed, this object will hold a Result containing the resulting Records. This type is further subtyped for the various uses of a SELECT statement as such:
org.jooq.SimpleSelectQuery: This Query will allow for selecting from single physical Tables only. It therefore has access to the Table's generic type parameter <R extends Record> and will provide a matching Result<R>. This is especially useful if <R> is a subtype of UpdatableRecord. Then you will be able to perform updates on your result set immediately.
org.jooq.SelectQuery: This Query will allow for selecting a subset of Fields from several Tables. Because the results of such a query are considered of an anonymous or ad-hoc type, this Query will bind <R> to the general type Record itself. The purpose of this Query type is to allow for full SQL support, including SELECT, JOIN and GROUP BY clauses.

# Example: SQL query and DSL query

-- Select all books by authors born after 1920, named "Paulo"
-- from a catalogue consisting of authors and books:


SELECT *
  FROM t_author
  JOIN t_book
    ON t_author.id = t_book.author_id
 WHERE t_author.year_of_birth > 1920
   AND t_author.first_name = 'Paulo'
 ORDER BY t_book.title

// Instanciate your factory using a JDBC connection.
Factory create = new Factory(connection, SQLDialect.ORACLE);

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

In the above example, some generated artefacts are used for querying. In this case, T_AUTHOR and T_BOOK are instances of types TAuthor and TBook respectively. Their full qualification would read TAuthor.T_AUTHOR and TBook.T_BOOK, but in many cases, it's useful to static import elements involved with queries, in order to decrease verbosity:

import static com.example.jooq.Tables.*;

Apart from the singleton Table instances TAuthor.T_AUTHOR and TBook.T_BOOK, these generated classes also contain one member for every physical field, such as TAuthor.ID or TBook.TAUTHOR_ID, etc. Depending on your configuration, those members can be static members (better for static imports) or instance members (better for aliasing)

For more information about code generation, check out the manual's section about Meta model source code generation.
For more DSL examples, please consider the manual's section about the DSL API.

# Example: Non-DSL query

If you choose not to use the DSL API (for instance, because you don't want to add Query parts in the order SQL expects them), you can use this syntax:

// Re-use the factory to create a SelectQuery. This example will not make use of static imports...
SelectQuery q = create.selectQuery();
q.addFrom(T_AUTHOR);

// This example shows some "mixed" API usage, where the JOIN is added with the standard API, and the
// Condition is created using the DSL API
q.addJoin(T_BOOK, T_AUTHOR.ID.equal(T_BOOK.AUTHOR_ID));

// The AND operator between Conditions is implicit here
q.addConditions(T_AUTHOR.YEAR_OF_BIRTH.greaterThan(1920));
q.addConditions(T_AUTHOR.FIRST_NAME.equal("Paulo"));
q.addOrderBy(T_BOOK.TITLE);

# Fetching data

The org.jooq.Select interface extends org.jooq.ResultQuery, which provides a range of methods to fetch data from the database. Once you have constructed your SELECT query (see examples above), you may choose to either simply execute() it, or use a variety of convenience fetchXXX() methods.

See the manual's section on the ResultQuery for more details.

# INSERT Statements

jOOQ supports two modes for INSERT statements. The INSERT VALUES and the INSERT SELECT syntax

# Example: SQL query and DSL query

INSERT INTO T_AUTHOR
    (ID, FIRST_NAME, LAST_NAME)
VALUES
    (100, 'Hermann', 'Hesse'),
    (101, 'Alfred', 'Döblin');

create.insertInto(T_AUTHOR,
        T_AUTHOR.ID, T_AUTHOR.FIRST_NAME, T_AUTHOR.LAST_NAME)
      .values(100, "Hermann", "Hesse")
      .values(101, "Alfred", "Döblin")
      .execute();

The DSL syntax tries to stay close to actual SQL. In detail, however, Java is limited in its possibilities. That's why the .values() clause is repeated for every record in multi-record inserts. Some RDBMS support inserting several records at the same time. This is also supported in jOOQ, and simulated using UNION clauses for those RDBMS that don't support this syntax.

INSERT INTO .. SELECT .. UNION ALL SELECT ..

Note: Just like in SQL itself, you can have syntax errors when you don't have matching numbers of fields/values. Also, you can run into runtime problems, if your field/value types don't match.

# Example: DSL Query, alternative syntax

MySQL (and some other RDBMS) allow for using an UPDATE-like syntax for INSERT statements. This is also supported in jOOQ, should you prefer that syntax. The above INSERT statement can also be expressed as follows:

create.insertInto(T_AUTHOR)
      .set(T_AUTHOR.ID, 100)
      .set(T_AUTHOR.FIRST_NAME, "Hermann")
      .set(T_AUTHOR.LAST_NAME, "Hesse")
      .newRecord()
      .set(T_AUTHOR.ID, 101)
      .set(T_AUTHOR.FIRST_NAME, "Alfred")
      .set(T_AUTHOR.LAST_NAME, "Döblin")
      .execute();

As you can see, this syntax is a bit more verbose, but also more type-safe, as every field can be matched with its value.

# Example: ON DUPLICATE KEY UPDATE clause

The MySQL database supports a very convenient way to INSERT or UPDATE a record. This is a non-standard extension to the SQL syntax, which is supported by jOOQ and simulated in other RDBMS, where this is possible. Here is an example how to use the ON DUPLICATE KEY UPDATE clause:

// Add a new author called "Koontz" with ID 3.
// If that ID is already present, update the author's name
create.insertInto(T_AUTHOR, T_AUTHOR.ID, T_AUTHOR.LAST_NAME)
      .values(3, "Koontz")
      .onDuplicateKeyUpdate()
      .set(T_AUTHOR.LAST_NAME, "Koontz")
      .execute();

# Example: ON DUPLICATE KEY IGNORE clause

The MySQL database also supports an INSERT IGNORE INTO clause. This is supported by jOOQ using the more convenient SQL syntax variant of ON DUPLICATE KEY IGNORE, which can be equally simulated in other databases using a MERGE statement:

// Add a new author called "Koontz" with ID 3.
// If that ID is already present, ignore the INSERT statement
create.insertInto(T_AUTHOR, T_AUTHOR.ID, T_AUTHOR.LAST_NAME)
      .values(3, "Koontz")
      .onDuplicateKeyIgnore()
      .execute();

# Example: INSERT .. RETURNING clause

The Postgres database has native support for an INSERT .. RETURNING clause. This is a very powerful concept that is simulated for all other dialects using JDBC's getGeneratedKeys() method. Take this example:

// Add another author, with a generated ID
Record<?> record =
create.insertInto(T_AUTHOR, T_AUTHOR.FIRST_NAME, T_AUTHOR.LAST_NAME)
      .values("Charlotte", "Roche")
      .returning(T_AUTHOR.ID)
      .fetchOne();

System.out.println(record.getValue(T_AUTHOR.ID));

// For some RDBMS, this also works when inserting several values
// The following should return a 2x2 table
Result<?> result =
create.insertInto(T_AUTHOR, T_AUTHOR.FIRST_NAME, T_AUTHOR.LAST_NAME)
      .values("Johann Wolfgang", "von Goethe")
      .values("Friedrich", "Schiller")
      // You can request any field. Also trigger-generated values
      .returning(T_AUTHOR.ID, T_AUTHOR.CREATION_DATE)
      .fetch();

Be aware though, that this can lead to race-conditions in those databases that cannot properly return generated ID values.

# Example: Non-DSL Query

You can always use the more verbose regular syntax of the InsertQuery, if you need more control:

// Insert a new author into the T_AUTHOR table
InsertQuery<TAuthorRecord> i = create.insertQuery(T_AUTHOR);
i.addValue(T_AUTHOR.ID, 100);
i.addValue(T_AUTHOR.FIRST_NAME, "Hermann");
i.addValue(T_AUTHOR.LAST_NAME, "Hesse");

i.newRecord();
i.addValue(T_AUTHOR.ID, 101);
i.addValue(T_AUTHOR.FIRST_NAME, "Alfred");
i.addValue(T_AUTHOR.LAST_NAME, "Döblin");
i.execute();

# Example: INSERT Query combined with SELECT statements

The InsertQuery.addValue() method is overloaded, such that you can also provide a Field, potentially containing an expression:

// Insert a new author into the T_AUTHOR table
InsertQuery<TAuthorRecord> i = create.insertQuery(T_AUTHOR);
i.addValue(T_AUTHOR.ID, create.select(max(T_AUTHOR.ID).add(1)).from(T_AUTHOR).asField())
i.addValue(T_AUTHOR.FIRST_NAME, "Hermann");
i.addValue(T_AUTHOR.LAST_NAME, "Hesse");
i.execute();

Note that especially MySQL (and some other RDBMS) has some limitations regarding that syntax. You may not be able to select from the same table you're inserting into

# Example: INSERT SELECT syntax support

In some occasions, you may prefer the INSERT SELECT syntax, for instance, when you copy records from one table to another:

Insert i = create.insertInto(T_AUTHOR_ARCHIVE)
                 .select(create.selectFrom(T_AUTHOR).where(T_AUTHOR.DECEASED.isTrue()));
i.execute();

# UPDATE Statements

UPDATE statements are only possible on single tables. Support for multi-table updates will be implemented in the near future.

# Example: SQL query and DSL query

UPDATE T_AUTHOR
   SET FIRST_NAME = 'Hermann',
       LAST_NAME = 'Hesse'
 WHERE ID = 3;

create.update(T_AUTHOR)
      .set(T_AUTHOR.FIRST_NAME, "Hermann")
      .set(T_AUTHOR.LAST_NAME, "Hesse")
      .where(T_AUTHOR.ID.equal(3))
      .execute();

# Example: Non-DSL Query

Using the org.jooq.UpdateQuery class, this is how you could express an UPDATE statement:

UpdateQuery<TAuthorRecord> u = create.updateQuery(T_AUTHOR);
u.addValue(T_AUTHOR.FIRST_NAME, "Hermann");
u.addValue(T_AUTHOR.FIRST_NAME, "Hesse");
u.addConditions(T_AUTHOR.ID.equal(3));
u.execute();

# DELETE Statements

DELETE statements are only possible on single tables. Support for multi-table deletes will be implemented in the near future.

# Example: SQL query and DSL query

DELETE T_AUTHOR
 WHERE ID = 100;

create.delete(T_AUTHOR)
      .where(T_AUTHOR.ID.equal(100))
      .execute();

# Example: Non-DSL Query

Using the org.jooq.DeleteQuery class, this is how you could express a DELETE statement:

DeleteQuery<TAuthorRecord> d = create.deleteQuery(T_AUTHOR);
d.addConditions(T_AUTHOR.ID.equal(100));
d.execute();

# MERGE Statement

The MERGE statement is one of the most advanced standardised SQL constructs, which is supported by DB2, HSQLDB, Oracle, SQL Server and Sybase (MySQL has the similar INSERT .. ON DUPLICATE KEY UPDATE construct)

The point of the standard MERGE statement is to take a TARGET table, and merge (INSERT, UPDATE) data from a SOURCE table into it. DB2, Oracle, SQL Server and Sybase also allow for DELETING some data and for adding many additional clauses. With jOOQ 2.0.1, only Oracle's MERGE extensions are supported. Here is an example:

-- Check if there is already an author called 'Hitchcock'
-- If there is, rename him to John. If there isn't add him.

MERGE INTO T_AUTHOR
USING (SELECT 1 FROM DUAL)
ON (LAST_NAME = 'Hitchcock')
WHEN MATCHED THEN UPDATE SET FIRST_NAME = 'John'
WHEN NOT MATCHED THEN INSERT (LAST_NAME)
                      VALUES ('Hitchcock')

create.mergeInto(T_AUTHOR)
      .using(create().selectOne())
      .on(T_AUTHOR.LAST_NAME.equal("Hitchcock"))
      .whenMatchedThenUpdate()
      .set(T_AUTHOR.FIRST_NAME, "John")
      .whenNotMatchedThenInsert(T_AUTHOR.LAST_NAME)
      .values("Hitchcock")
      .execute();

# MERGE Statement (H2-specific syntax)

The H2 database ships with a somewhat less powerful but a little more intuitive syntax for its own version of the MERGE statement. An example more or less equivalent to the previous one can be seen here:

-- Check if there is already an author called 'Hitchcock'
-- If there is, rename him to John. If there isn't add him.

MERGE INTO T_AUTHOR (FIRST_NAME, LAST_NAME)
KEY (LAST_NAME)
VALUES ('John', 'Hitchcock')

create.mergeInto(T_AUTHOR,
                 T_AUTHOR.FIRST_NAME,
                 T_AUTHOR.LAST_NAME)
      .key(T_AUTHOR.LAST_NAME)
      .values("John", "Hitchcock")
      .execute();

This syntax can be fully simulated by jOOQ for all other databases that support the SQL standard. For more information about the H2 MERGE syntax, see the documentation here:

http://www.h2database.com/html/grammar.html#merge

# TRUNCATE Statement

The syntax is trivial:

TRUNCATE TABLE T_AUTHOR;

create.truncate(T_AUTHOR).execute();

This is not supported by Ingres and SQLite. jOOQ will execute a DELETE FROM T_AUTHOR statement instead.

The jOOQ User Manual. Multiple Pages : jOOQ classes and their usage : The Query and its various subtypes

previous : next