Onion Constraints

Constraints in the QuerySearch classes translate to the WHERE clause of a JPQL SELECT query. These can be defined declaratively using what we've named onion constraints. This allows you to represent the constraints as a logical statement formed out of static method calls.

The onion constraints have two responsibilities:

Defining activation conditions, which determines what parts of the constraint-tree are added to the query.
Defining how a constraint is translated to JPQL-query syntax, given that it is activated.

How the activation condition is generated, depends on the type of constraint. Some onion constraints exist purely to define activation conditions, whereas others exist purely to define JPQL query constraints. There are of course also some constraints that have more hybrid functionality and often have nested constraints that influence its activation condition or the JPQL query it produces.

Example

As an example, the following code shows how the QueryFilter object is processed to handle a typical resource identifier lookup, where the resource identifier represents a path parameter in the URL of a REST GET call to get a specific database record. If this is given in the filter, this will match it against a uuid field of the element, but also case-insensitive against its name so the name can also be used in the URL to request that record. Additionally, a second name filter field is checked against the name to do a search for records where the filter value is part of the name with case-insensitive matching.

and(
    when(filter.getResourceId() != null, () ->
        or(
            equal("o.uuid", filter.getResourceId()),
            equal("o.name", filter.getResourceId(), CASE_INSENSITIVE)
        )
    ),
    when(filter.getName() != null, () ->
        equal("o.name", filter.getName(), CONTAINS, CASE_INSENSITIVE)
    )
)

Given the values below, the QueryBuilder translates this to JPQL as:

filter.resourceId: e71dac0b-a8d4-45dd-9b6a-19cc1cbd8950
filter.name: myFavoriteResource

JPQL
((o.uuid = 'e71dac0b-a8d4-45dd-9b6a-19cc1cbd8950') OR
  (o.name LIKE 'e71dac0b-a8d4-45dd-9b6a-19cc1cbd8950')) AND
  (o.name LIKE '%myFavoriteResource%')

Note that the values are not inserted directly into the JPQL query string, but rather injected by JPA after properly escaping the values to avoid SQL injection attacks.

And constraint

and(constraint1, constraint2, ..., constraintN)

Combines multiple constraints by applying the logical AND operator on all given constraints.

If only a single nested constraint is activated, the AND constraint is omitted and only that constraint is added.
If none of the nested constraints are activated, the AND constraint and its child constraints are not added to the query.

Logic table

A	B	A and B
False	False	False
False	True	False
True	False	False
True	True	True

Between constraint

between(fieldName, leftValue, rightValue, [queryOptions])
between(fieldName, valueType, leftValue, rightValue, [queryOptions])

The between constraint is used to verify that the value in a field is between two given values. This means equal to either boundaries or between them. The type of the value should be inferred automatically, but can also be passed directly into the constraint. Multiple options can be passed to affect the behavior of the constraint.

Options

QUERY_OPERAND: Treats the value parameters as a literal query part.

Examples

Example (Basic)

Constraint
between("o.counter", 5, 7)

Constraint
between("o.counter", INTEGER, "5", "7")

JPQL
o.counter BETWEEN 5 AND 7

In this statement, we are matching the field counter to make sure it is between (or equal to) the given values of 5 and 7. We supply the type of the value explicitly, though it should also be automatically inferred. In this example, o is the object that is being selected in the query.

Empty constraint

empty()

The empty constraint is a constraint that does nothing. It exists purely for technical reasons. This constraint's activation condition is always false and it generated no JPQL statements. The most prominent use-case for this constraint would be in the creation of simple custom constraints that relate mostly to constraint activation conditions.

Examples

Example

In the OnionQueryConstraintFactory, the when()-constraint is defined as follows:

public static QueryConstraint when(boolean condition, Supplier<QueryConstraint> constraint) {
  return whenOrElse(condition, constraint, OnionQueryConstraintFactory::empty);
}

The given constraint is returned if condition is true. If condition is false, empty() is returned so there's no additional JPQL statements added to the query if the condition is not met.

Equal constraint

equal(fieldName, value, [queryOptions])
equal(fieldName, valueType, value, [queryOptions])

The equal constraint is used to verify that a field equals a given value. The type of the value should be inferred automatically, but can also be passed directly into the constraint. Multiple options can be passed to affect the behavior of the constraint.

Options

Option	Description
`SPLIT`	Splits the given value on a `,` (comma) character and then converts the constraint to an or constraint where each piece of the string is individually matched as an equal constraint.
`CONTAINS`	Checks whether the given value is contained within the field. Only applies for a string value.
`CASE_INSENSITIVE`	Matches a string value case-insensitive to the column.
`QUERY_OPERAND`	Treats the value parameter as a literal query part.
`HAS_PREFIX`	Checks whether the field starts with the given value. Only applies for a string value.
`HAS_SUFFIX`	Checks whether the field ends with the given value. Only applies for a string value.

Examples

Example (Basic)

Constraint
equal("o.name", "NameValue")

JPQL
o.name = 'NameValue'

In this statement, we are matching the field name with a given value. In this example, o is the object that is being selected in the query.

Example (Integer)

Constraint
equal("o.counter", INTEGER, 5)

Constraint
equal("o.counter", INTEGER, "5")

JPQL
o.counter = 5

In this statement, we are matching the field counter with a given value. We supply the type of the value explicitly, though it should also be automatically inferred. In this example, o is the object that is being selected in the query.

Example (Split string)

Constraint
equal("o.name", "stringA,stringB,finalString", SPLIT, CASE_INSENSITIVE)

The constraint above is translated to:

Constraint
or(
  equal("o.name", "stringA", CASE_INSENSITIVE),
  equal("o.name", "stringB", CASE_INSENSITIVE),
  equal("o.name", "finalString", CASE_INSENSITIVE)
)

JPQL
(o.name LIKE 'stringA') OR (o.name LIKE 'stringB') OR (o.name LIKE 'finalString')

Exists constraint

exists(queryBuilder, [queryOptions])

The exists constraint is a constraint that checks if a nested query returns a result. The nested query is given as a separate QueryBuilder object. As it is a nested query, it can (and typically should) access the variable that is being queried in the parent query to relate it correctly.

Options

There are currently no options that can be applied to this constraint.

Examples

Example (Basic)

Constraint
exists(select("e")
    .from("com.example.Entity", "e")
    .where(and(
        equal("o.entity.id", "e.id", QUERY_OPERAND),
        equal("e.name", "Entity name")
    )))

JPQL
EXISTS (SELECT e FROM com.example.Entity e WHERE (o.entity.id = e.id) AND (e.name = 'Entity name'))

Greater than or equal constraint

gte(fieldName, value, [queryOptions])
gte(fieldName, valueType, value, [queryOptions])

The greater than or equal constraint is used to verify that a field is greater than or equal to a given value. The type of the value should be inferred automatically, but can also be passed directly into the constraint. Multiple options can be passed to affect the behavior of the constraint.

Options

Option	Description
`SPLIT`	Splits the given value on a `,` (comma) character and then converts the constraint to an or constraint where each piece of the string is individually matched as an equal constraint.
`QUERY_OPERAND`	Treats the value parameter as a literal query part.

Examples

Example (Basic)

Constraint
gte("o.counter", 5)

Constraint
gte("o.counter", "5")

JPQL
o.counter >= 5

In this statement, we are matching the field counter to make sure it is greater than or equal to a given value. We supply the type of the value explicitly, though it should also be automatically inferred. In this example, o is the object that is being selected in the query.

Example (Split values)

Constraint
gte("o.counter", INTEGER, "1,2,6", SPLIT)

The constraint above is translated to:

Constraint
or(
  gte("o.counter", INTEGER, "1"),
  gte("o.counter", INTEGER, "2"),
  gte("o.counter", INTEGER, "6")
)

JPQL
(o.counter >= 1) OR (o.counter >= 2) OR (o.counter >= 6)

Greater than constraint

gt(fieldName, value, [queryOptions])
gt(fieldName, valueType, value, [queryOptions])

The greater than constraint is used to verify that a field is greater than a given value. The type of the value should be inferred automatically, but can also be passed directly into the constraint. Multiple options can be passed to affect the behavior of the constraint.

Options

Option	Description
`SPLIT`	Splits the given value on a `,` (comma) character and then converts the constraint to an or constraint where each piece of the string is individually matched as an equal constraint.
`QUERY_OPERAND`	Treats the value parameter as a literal query part.

Examples

Example (Basic)

Constraint
gt("o.counter", 5)

Constraint
gt("o.counter", "5")

JPQL
o.counter > 5

In this statement, we are matching the field counter to make sure it is greater than a given value. We supply the type of the value explicitly, though it should also be automatically inferred. In this example, o is the object that is being selected in the query.

Example (Split values)

Constraint
gt("o.counter", INTEGER, "1,2,6", SPLIT)

The constraint above is translated to:

Constraint
or(
  gt("o.counter", INTEGER, "1"),
  gt("o.counter", INTEGER, "2"),
  gt("o.counter", INTEGER, "6")
)

JPQL
(o.counter > 1) OR (o.counter > 2) OR (o.counter > 6)

In constraint

in(fieldName, queryBuilder, [queryOptions])
in(fieldName, values, [queryOptions])

The in constraint can be used to verify that the value of a field is found in the set of values returned by a subquery. To create the object for the subquery, the select() method has to be used and the field to search in has to be passed in that method.

Options

Option	Description
`QUERY_OPERAND`	When values are passed, the values are treated as a literal query part.

Examples

Example (Subquery)

Constraint
in("o.foreign_id",
  select("e.id").from("org.example.MyElement", "e").where(
    equal("e.name", "TheBest", HAS_PREFIX)
  )
)

JPQL
o.foreign_id IN (SELECT e.id FROM org.example.MyElement e WHERE e.name = 'TheBest%')

Example (Values)

Constraint
in("o.counter", Arrays.asList(42, 1337, 9001))

JPQL
o.counter IN (42, 1337, 9001)

Is null constraint

isNull(fieldName)

The is null constraint can be used to verify that the a field has no value (is set to NULL in the database).

Examples

Example

Constraint
isNull("o.name")

JPQL
o.name IS NULL

In this statement, we check that the name field has no value.

Less than constraint

lt(fieldName, value, [queryOptions])
lt(fieldName, valueType, value, [queryOptions])

The less than constraint is used to verify that a field is less than a given value. The type of the value should be inferred automatically, but can also be passed directly into the constraint. Multiple options can be passed to affect the behavior of the constraint.

Options

Option	Description
`SPLIT`	Splits the given value on a `,` (comma) character and then converts the constraint to an or constraint where each piece of the string is individually matched as an equal constraint.
`QUERY_OPERAND`	Treats the value parameter as a literal query part.

Examples

Example (Basic)

Constraint
lt("o.counter", 5)

Constraint
lt("o.counter", INTEGER, "5")

JPQL
o.counter < 5

In this statement, we are matching the field counter to make sure it is less than a given value. We supply the type of the value explicitly, though it should also be automatically inferred. In this example, o is the object that is being selected in the query.

Example (Split values)

Constraint
lt("o.counter", INTEGER, "1,2,6", SPLIT)

The constraint above is translated to:

Constraint
or(
  lt("o.counter", INTEGER, "1"),
  lt("o.counter", INTEGER, "2"),
  lt("o.counter", INTEGER, "6")
)

JPQL
(o.counter < 1) OR (o.counter < 2) OR (o.counter <> 6)

Less than or equal constraint

lte(fieldName, value, [queryOptions])
lte(fieldName, valueType, value, [queryOptions])

The less than or equal constraint is used to verify that a field is less than or equal to a given value. The type of the value should be inferred automatically, but can also be passed directly into the constraint. Multiple options can be passed to affect the behavior of the constraint.

Options

Option	Description
`SPLIT`	Splits the given value on a `,` (comma) character and then converts the constraint to an or constraint where each piece of the string is individually matched as an equal constraint.
`QUERY_OPERAND`	Treats the value parameter as a literal query part.

Examples

Example (Basic)

Constraint
lte("o.counter", 5)

Constraint
lte("o.counter", INTEGER, "5")

JPQL
o.counter <= 5

In this statement, we are matching the field counter to make sure it is less than or equal to a given value. We supply the type of the value explicitly, though it should also be automatically inferred. In this example, o is the object that is being selected in the query.

Example (Split values)

Constraint
lte("o.counter", INTEGER, "1,2,6", SPLIT)

The constraint above is translated to:

Constraint
or(
  lte("o.counter", INTEGER, "1"),
  lte("o.counter", INTEGER, "2"),
  lte("o.counter", INTEGER, "6")
)

JPQL
(o.counter <= 1) OR (o.counter <= 2) OR (o.counter <= 6)

Not constraint

not(constraint)

Logically inverts the implication of a given constraint.

Logic table

A	not A
False	True
True	False

Member constraint

member(value, fieldName, [queryOptions])
member(valueType, value, fieldName, [queryOptions])

The member constraint is used to check if a given value is part of a collection field. The type of the value should be inferred automatically, but can also be passed directly into the constraint.

The constraint is translated to the JPA (jpql) collection MEMBER OF expression, which in turn is translated by JPA into a subquery.

Options

Option	Description
`QUERY_OPERAND`	Treats the value parameter as a literal query part.

Examples

Example (Value)

Constraint
member("NameValue", "o.names")

JPQL
'NameValue' MEMBER OF o.names

In this statement, we are checking whether the string NameValue is part of the names field, assuming that field is a collection of strings. In this example, o is the object that is being selected in the query.

Example (Literal)

Constraint
member("c.field", "o.fieldInstances", QUERY_OPERAND)

JPQL
c.field MEMBER OF o.fieldInstances

In this statement, we are explicitly checking if the field field of some object c is a member of the collection field fieldInstances of object o.

Or constraint

or(constraint1, constraint2, ..., constraintN)

Combines multiple constraints by applying the logical OR operator on all given constraints.

If only a single nested constraint is activated, the OR constraint is omitted and only that constraint is added.
If none of the nested constraints are activated, the OR constraint and its child constraints are not added to the query.

Logic table

A	B	A or B
False	False	False
False	True	True
True	False	True
True	True	True

When constraint

when(condition, constraint)
whenOrElse(condition, constraint, elseConstraint)

The "when(OrElse) constraint" is in itself not actually a query constraint. Though it is part of the process of constructing the query, it does not add anything to the query related to the given condition. It allows you to activate specific constraints based on a condition that checks fields in the QueryFilter object, which allows queries to be more lean.

The constraint is itself defined as a QueryConstraint, so it can be easily used as a constraint filtering mechanism that can be part of the declarative onion constraints. The constraints are defined as Supplier-objects (given as lambda expressions), so they are only evaluated when the given condition requires them to be added to the query.

Examples

Example (when)

Given that the QueryFilter object, has the field name with value NameValue:

Constraint
when(filter.getName() != null, () ->
    equal("o.name", filter.getName()))

JPQL
o.name = 'NameValue'

The equals constraint is added, because the condition filter.getName() != null is true. As a result, we check whether the name of an object equals the string value given in the name field on the query filter, which is NameValue in this example as previously stated.

Example (when with failed condition)

Given that the QueryFilter object, has the field name set to null:

Constraint
when(filter.getName() != null, () ->
    equal("o.name", filter.getName()))

JPQL

# No JPQL is generated

Since the name field is set to null, the condition fails. Given the condition, we don't want to enforce the constraint on name if no name is given. As a result, the equals check defined by the equal() condition is not added to the JPQL query.

Example (whenOrElse with failed condition)

Given that the QueryFilter object, has the field name set to null:

Constraint
whenOrElse(filter.getName() != null,
    () -> equal("o.name", filter.getName()),
    () -> equal("o.name", "DefaultName"))

JPQL
o.name = 'DefaultName'

Because the condition filter.getName() != null is false, we add the constraints defined in the else expression, which in this case enforce that if no name is given, the name should always be DefaultName.

And constraint​

Logic table​

Between constraint​

Options​

Examples​

Empty constraint​

Examples​

Equal constraint​

Options​

Examples​

Exists constraint​

Options​

Examples​

Greater than or equal constraint​

Options​

Examples​

Greater than constraint​

Options​

Examples​

In constraint​

Options​

Examples​

Is null constraint​

Examples​

Less than constraint​

Options​

Examples​

Less than or equal constraint​

Options​

Examples​

Not constraint​

Logic table​

Member constraint​

Options​

Examples​

Or constraint​

Logic table​

When constraint​

Examples​

And constraint

Logic table

Between constraint

Options

Examples

Empty constraint

Examples

Equal constraint

Options

Examples

Exists constraint

Options

Examples

Greater than or equal constraint

Options

Examples

Greater than constraint

Options

Examples

In constraint

Options

Examples

Is null constraint

Examples

Less than constraint

Options

Examples

Less than or equal constraint

Options

Examples

Not constraint

Logic table

Member constraint

Options

Examples

Or constraint

Logic table

When constraint

Examples