raw()

The Raw expression lets us use any database specific function or expression in the where clause of a query.

// e.g. use a database function
.raw("add_days(orderDate, 10) < ?", someDate)


// e.g. subquery
.raw("customer.id in (select o.customer_id from orders o where o.id in (?1))", orderIds);

 


Convenience expressions

These are expressions that combine other simple expressions together. We have them because they occur often enough in applications.

inRange()

property >= value1 and property < value2

.orderDate.inRange(today.minusDays(7), today)

InRange expression is similar to BETWEEN except is "half open interval". The property is strictly less than the top value rather than being less than or equal to.

This makes inRange more practically useful to define intervals on things like timestamps and dates etc.

inRangeWith()

property <= ? and ( highProperty > ? or highProperty is null)

.startDate.inRangeWith(endDate, asAt)

This is most commonly used for "effective dating" or an "effective range" where you have 2 properties like startDate and endDate that form a range.

inOrEmpty()

This is a "conditional IN" where the IN expression is only added if the collection is not empty.

List<Long> customerIds = ...

// only add the expression if the customerIds is not empty
.customer.id.inOrEmpty(customerIds)

The above will only add the IN expression if the customerIds collection is not null and not empty.

rawOrEmpty()

This is a "conditional raw expression" where the raw expression uses a collection (like a raw subquery expression) and we only add the expression if the collection is not empty.

List<String> names = ...

// only add the expression if the names is not empty
.rawOrEmpty("customer.id in (select c.id from customer c where c.name in (?1))", names)

 


Simple expressions

The following expressions are simple expressions.

isNull()

IsNull on a associated many property translates to isEmpty()

isEmpty()

IsEmpty expression should be used on a ToMany property. A sql exists subquery is used to implement the isEmpty expression.

in()

inPairs()

like

startsWith

endsWith

contains

eq - equal to

ne - not equal

gt - greater than

ge - greater than or equal

lt - less than

le - less than or equal

between

betweenProperties

example

bitwiseAny

bitwiseAnd

bitwiseAll

bitwiseNot

arrayContains

arrayNotContains

arrayIsEmpty

arrayIsNotEmpty