Conditions

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 : Conditions

previous : next

# Conditions are the SELECT's core business

In your average application, you will typically have 3-4 SQL queries that have quite a long list of predicates (and possibly JOINs), such that you start to lose track over the overall boolean expression that you're trying to apply.

In jOOQ, most Conditions can be created and combined almost as easily as in SQL itself. The two main participants for creating Conditions are the Field, which is typically a participant of a condition, and the Condition itself:

public interface Condition {
    Condition and(Condition other);
    Condition and(String sql);
    Condition and(String sql, Object... bindings);
    Condition andNot(Condition other);
    Condition andExists(Select<?> select);
    Condition andNotExists(Select<?> select);
    Condition or(Condition other);
    Condition or(String sql);
    Condition or(String sql, Object... bindings);
    Condition orNot(Condition other);
    Condition orExists(Select<?> select);
    Condition orNotExists(Select<?> select);
    Condition not();
}

The above example describes the essence of boolean logic in jOOQ. As soon as you have a Condition object, you can connect that to other Conditions, which will then give you a combined condition with exactly the same properties. There are also convenience methods to create an EXISTS clause and connect it to an existing condition. In order to create a new Condition you are going to depart from a Field in most cases. Here are some important API elements in the Field interface:

public interface Field<T> {
    Condition isNull();
    Condition isNotNull();
    Condition like(T value);
    Condition likeIgnoreCase(T value);
    Condition notLike(T value);
    Condition notLikeIgnoreCase(T value);
    Condition in(T... values);
    Condition in(Select<?> query);
    Condition notIn(Collection<T> values);
    Condition notIn(T... values);
    Condition notIn(Select<?> query);
    Condition in(Collection<T> values);
    Condition between(T minValue, T maxValue);
    Condition contains(T value);
    Condition contains(Field<T> value);
    Condition equal(T value);
    Condition equal(Field<T> field);
    Condition equal(Select<?> query);
    Condition equalAny(Select<?> query);
    Condition equalAny(T... array);
    Condition equalAny(Field<T[]> array);
    Condition equalAll(Select<?> query);
    Condition equalAll(T... array);
    Condition equalAll(Field<T[]> array);
    Condition equalIgnoreCase(String value);
    Condition equalIgnoreCase(Field<String> value);
    Condition notEqual(T value);
    Condition notEqual(Field<T> field);
    Condition notEqual(Select<?> query);
    Condition notEqualAny(Select<?> query);
    Condition notEqualAny(T... array);
    Condition notEqualAny(Field<T[]> array);
    Condition notEqualAll(Select<?> query);
    Condition notEqualAll(T... array);
    Condition notEqualAll(Field<T[]> array);

    // Subselects, ANY and ALL quantifiers are also supported for these:
    Condition lessThan(T value);
    Condition lessOrEqual(T value);
    Condition greaterThan(T value);
    Condition greaterOrEqual(T value);
}

As you see in the partially displayed API above, you can compare a Field either with other Fields, with constant values (which is a shortcut for calling Factory.val(T value)), or with a nested SELECT statement. See some more Examples of nested SELECTs.

Combining the API of Field and Condition you can express complex predicates like this:

(T_BOOK.TYPE_CODE IN (1, 2, 5, 8, 13, 21)       AND T_BOOK.LANGUAGE = 'DE') OR
(T_BOOK.TYPE_CODE IN (2, 3, 5, 7, 11, 13)       AND T_BOOK.LANGUAGE = 'FR') OR
(T_BOOK.TYPE_CODE IN (SELECT CODE FROM T_TYPES) AND T_BOOK.LANGUAGE = 'EN')

Just write:

T_BOOK.TYPE_CODE.in(1, 2, 5, 8, 13, 21)                       .and(T_BOOK.LANGUAGE.equal("DE")).or(
T_BOOK.TYPE_CODE.in(2, 3, 5, 7, 11, 13)                       .and(T_BOOK.LANGUAGE.equal("FR")).or(
T_BOOK.TYPE_CODE.in(create.select(T_TYPES.CODE).from(T_TYPES)).and(T_BOOK.LANGUAGE.equal("EN"))));

The jOOQ User Manual. Multiple Pages : DSL or fluent API. Where SQL meets Java : Conditions

previous : next