Query Expressions

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()

new QOrder()
  .lines.isNull()
  .findList();

select ...
from orders t0
where not exists (select 1 from order_lines x where x.order_id = t0.id)

isEmpty()

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

new QOrder()
  .lines.isEmpty()
  .findList();

select ...
from orders t0
where not exists (select 1 from order_lines x where x.order_id = t0.id)

in()

new QOrder()
  .status.in(Order.Status.NEW, Order.Status.PENDING)
  .findList();

inPairs()

inPairs() is used when we have a compound natual key with 3 or more properties and we have queries that combine eq and in expressions that together provide the natural key (together cover all the properties that make up the natural key).

@Cache(naturalKey = {"store","code","sku"})
@Entity
public class ProductRange {
  ...
}

With the .store.eq("myStoreCode") expression combined with the .inPairs(pairs) expression we have defined complete natural keys covering all of the store, code and sku properties.

Pairs pairs = new Pairs("sku", "code")
  .add("S2", 1000)
  .add("S2", 1001)
  .add("S3", 1000);

new QProductRange()
    .store.eq("myStoreCode")
    .inPairs(pairs)
    .setBeanCacheMode(CacheMode.ON)
    .order("sku desc")
    .findList();

We are using inPairs when our in clause has 2 properties. If instead we have the eq expression with 2 properties then our in clause just has the usual single property.

For example:

new QProductRange()
    .store.eq("myStoreCode")
    .code.eq(1004)
    .in("S2", "S3", "S4")
    .setBeanCacheMode(CacheMode.ON)
    .order("sku desc")
    .findList();

With the above query we have eq and in expressions that combine to produce unique natural keys. Ebean can hit L2 natural key cache for this query.

like

With like we use the SQL % and _ place holder characters to match varchar properties.

List<Contact> contacts =
  new QContact()
    .firstName.like("Rob%")
    .findList();

ilike - case-insensitive like

With ilike we use the SQL % and _ place holder characters to match varchar properties.

List<Contact> contacts =
  new QContact()
    .firstName.ilike("Rob%")
    .findList();

startsWith

List<Contact> contacts =
  new QContact()
    .firstName.startsWith("Rob")
    .findList();

istartsWith - case-insensitive startsWith

List<Contact> contacts =
  new QContact()
    .firstName.istartsWith("Rob")
    .findList();

endsWith

List<Contact> contacts =
  new QContact()
    .email.endsWith("@foo.com")
    .findList();

iendsWith - case-insensitive endsWith

List<Contact> contacts =
  new QContact()
    .email.iendsWith("@foo.com")
    .findList();

contains

List<Contact> contacts =
  new QContact()
    .email.contains("ob")
    .findList();

eq - equal to

List<Order> orders =
  new QOrder()
    .status.eq(Order.Status.NEW)
    .findList();

ieq - case-insensitive equal to

List<Customer> orders =
  new QCustomer()
    .name.ieq("rob")
    .findList();

ne - not equal

List<Order> orders =
  new QOrder()
    .status.ne(Order.Status.NEW)
    .findList();

gt - greater than

List<Order> orders =
  new QOrder()
    .whenCreated.gt(LocalDate.now().minusDays(7))
    .findList();

ge - greater than or equal

List<Order> orders =
  new QOrder()
    .whenCreated.ge(LocalDate.now().minusDays(7))
    .findList();

lt - less than

List<Order> orders =
  new QOrder()
    .lines.orderQuantity.lt(10)
    .findList();

le - less than or equal

List<Order> orders =
  new QOrder()
    .lines.orderQuantity.le(10)
    .findList();

between

List<Order> orders =
  new QOrder()
    .orderDate.between(firstDay, lastDay)
    .findList();

betweenProperties

When a value is between 2 properties.

Timestamp timestamp = new Timestamp(System.currentTimeMillis());
 ...
 .where().betweenProperties("effectiveStart", "effectiveEnd", timestamp)

bitwiseAny

flags.bitwiseAny(BwFlags.HAS_BULK + BwFlags.HAS_SIZE)

bitwiseAnd

int selectedFlags = BwFlags.HAS_BULK + BwFlags.HAS_SIZE;
int mask = BwFlags.HAS_SIZE; // Only Size flag set

bitwiseAnd(selectedFlags, mask)

bitwiseAll

flags.bitwiseAll(BwFlags.HAS_BULK + BwFlags.HAS_COLOUR)

bitwiseNot

flags.bitwiseNot(BwFlags.HAS_COLOUR)

The following expressions are for @DbArray. In these examples contact phoneNumbers is a @DbArray.

arrayContains

new QContact()
 .phoneNumbers.contains("4321")
 .findList();

arrayNotContains

new QContact()
 .phoneNumbers.notContains("4321")
 .findList();

arrayIsEmpty

new QContact()
 .phoneNumbers.isEmpty()
 .findList();

arrayIsNotEmpty

new QContact()
 .phoneNumbers.isNotEmpty()
 .findList();