ObjectBox Queries
ObjectBox queries return persisted objects that match user defined criteria. Learn here how to use the QueryBuilder class and specify criteria to create Queries with ObjectBox DB in Java.
With ObjectBox DB you use the QueryBuilder class to specify criteria and create Query objects. The Query class will actually run the query and return matching objects.
New code should use the new query API, see below!
The
QueryBuilder<T>
class lets you build custom queries for your entities. Create an instance via Box.query()
. QueryBuilder
offers several methods to define query conditions for properties of an entity. To specify a property ObjectBox does not use their names as text but meta-information "underscore" classes (like User_
) that are generated during build time. The meta-information classes have a static field for each property (like User_.firstName
). This allows referencing properties safely with compile-time checks to prevent runtime errors, for example, because of typos.Here are two examples using a simple and a more complicated query:
Simple condition example: Query for all users with the first name “Joe”:
Java
Kotlin
Dart
Query<User> query = userBox.query().equal(User_.firstName, "Joe").build();
List<User> joes = query.find();
query.close();
val query = userBox.query().equal(User_.firstName, "Joe").build()
val joes = query.find()
query.close()
// Or shorten with use:
val joes = userBox.query().equal(User_.firstName, "Joe").build()
.use { it.find() }
Query<User> query = userBox.query(User_.firstName.equals('Joe')).build();
List<User> joes = query.find();
query.close();
Multiple conditions example: Get users with the first name “Joe” that are born later than 1970 and whose last name starts with “O”.
Java
Kotlin
Dart
Query<User> query = userBox.query()
.equal(User_.firstName, "Joe")
.greater(User_.yearOfBirth, 1970)
.startsWith(User_.lastName, "O")
.build();
List<User> youngJoes = query.find();
query.close(); // Or keep the query instance to re-use it
val query = userBox.query()
.equal(User_.firstName, "Joe")
.greater(User_.yearOfBirth, 1970)
.startsWith(User_.lastName, "O")
.build()
val youngJoes = query.find()
query.close() // Or keep the query instance to re-use it
Query<User> query = userBox.query(
User_.firstName.equals('Joe') &
User_.yearOfBirth.greaterThan(1970) &
User_.lastName.startsWith('O')).build();
List<User> youngJoes = query.find();
ObjectBox for Java/Kotlin
3.0.0
introduced a new query API that accepts complex nested conditions, e.g. the equivalent of (A or B) and (C or D)
. It now works similar to the existing Dart Query API.To build a query with the new API use
Box.query(condition)
and supply a condition
built using entity Property
methods, like property.equal(value)
. All properties of an entity can be accessed using its underscore class. For example, for an entity User
a property
could be User_.firstName
, a condition
using it could be User_.firstName.equal("Joe")
.Simple condition example: Query for all users with the first name “Joe”.
Java
Kotlin
Dart
Query<User> query = userBox.query(User_.firstName.equal("Joe")).build();
List<User> joes = query.find();
query.close();
val query = userBox.query(User_.firstName.equal("Joe")).build()
val joes = query.find()
query.close()
Query<User> query = userBox.query(User_.firstName.equals('Joe')).build();
List<User> joes = query.find();
query.close();
Multiple conditions example: Get users with the first name “Joe” that are born later than 1970 and whose last name starts with “O”.
Java
Kotlin
Dart
Query<User> query = userBox.query(
User_.firstName.equal("Joe")
.and(User_.yearOfBirth.greater(1970))
.and(User_.lastName.startsWith("O")))
.build();
List<User> youngJoes = query.find();
query.close();
val query = userBox.query(
User_.firstName equal "Joe"
and (User_.yearOfBirth greater 1970)
and (User_.lastName startsWith "O")
.build()
val youngJoes = query.find()
query.close()
Query<User> query = userBox.query(
User_.firstName.equal('Joe')
.and(User_.yearOfBirth.greaterThan(1970))
.and(User_.lastName.startsWith('O')))
.build();
// or use operator overloads:
Query<User> query = userBox.query(
User_.firstName.equal('Joe') &
User_.yearOfBirth.greaterThan(1970) &
User_.lastName.startsWith('O'))
.build();
Conditions are combined with
QueryCondition.and(condition)
or QueryCondition.or(condition)
. This implicitly adds parentheses around the combined conditions, e.g. cond1.and(cond2)
is logically equivalent to (cond1 AND cond2)
.To nest conditions pass a single or combined
condition
.Java
Kotlin
Dart
// equal AND (less OR oneOf)
Query<User> query = box.query(
User_.firstName.equal("Joe")
.and(User_.age.less(12)
.or(User_.stamp.oneOf(new long[]{1012}))))
.order(User_.age)
.build();
// equal AND (less OR oneOf)
val query = box.query(
User_.firstName equal "Joe"
and (User_.age less 12
or (User_.stamp oneOf longArrayOf(1012))))
.order(User_.age)
.build()
Query<User> query = box.query(
User_.firstName.equal('Joe')
.and(User_.age.lessThan(12)
.or(User_.stamp.oneOf([1012]))))
.order(User_.age)
.build();
property.oneOf(array)
replaces thein(property, array)
(inValues(property, array)
for Kotlin) condition.- Use Kotlin infix functions to write
condition and condition
andcondition or condition
. - Use Dart operator overloads to write
condition & condition
andcondition | conditon
- Use
condition.alias(aliasName)
to set an alias for acondition
that can later be used with theQuery
setParameter methods.
In addition to expected conditions like
equal()
, notEqual()
, greater()
and less()
there are also conditions like:isNull()
andnotNull()
,between()
to filter for values that are between the given two,in()
andnotIn()
to filter for values that match any in the given array,startsWith()
,endsWith()
andcontains()
for extended String filtering.
In addition, there is
and()
and or()
to build more complex combinations of conditions.Dart only
To create query conditions for
DateTime
properties, values should be converted to int
. This is because DateTime
is stored as time in milliseconds internally in the database (or nanoseconds for @Property(type: PropertyType.dateNano).
For example, to query an
Order
entity with a date
field to find all orders in 2020:final query = box.query(
Order_.date.between(DateTime(2020).millisecondsSinceEpoch,
DateTime(2021).millisecondsSinceEpoch - 1)
).build();
For an overview of all available criteria, please refer to the QueryBuilder class and its method documentations.
In addition to specifying conditions, you can order the returned results using the
order()
method:Java
Kotlin
Dart
Query<User> query = userBox
.query(User_.firstName.equal("Joe"))
.order(User_.lastName) // in ascending order, ignoring case
.build();
val query = userBox
.query(User_.firstName.equal("Joe"))
.order(User_.lastName) // in ascending order, ignoring case
.build()
// in ascending order, ignoring case
final qBuilder = box.query(User_.firstName.equals('Joe')..order(User_.lastName);
final query = qBuilder.build();
You can also pass flags to
order()
to sort in descending order, to sort case sensitive or to specially treat null values. For example to sort the above results in descending order and case sensitive instead:Java
Kotlin
Dart
.order(User_.lastName, QueryBuilder.DESCENDING | QueryBuilder.CASE_SENSITIVE)
.order(User_.lastName, QueryBuilder.DESCENDING or QueryBuilder.CASE_SENSITIVE)
.order(User_.lastName, flags: Order.descending | Order.caseSensitive)
To see what query is actually executed by ObjectBox:
Java
Kotlin
Dart
// Set the LOG_QUERY_PARAMETERS debug flag
BoxStore store = MyObjectBox.builder()
.debugFlags(DebugFlags.LOG_QUERY_PARAMETERS)
.build();
// Execute a query
query.find();
// Set the LOG_QUERY_PARAMETERS debug flag
val store = MyObjectBox.builder()
.debugFlags(DebugFlags.LOG_QUERY_PARAMETERS)
.build()
// Execute a query
query.find()
print(query.describeParameters());
Then in your console (or logcat on Android) you will see log output like:
Parameters for query #2:
(firstName ==(i) "Joe"
AND age < 12)
Query<User> query = builder.build();
There are a couple of find methods to retrieve objects matching the query:
// return all entities matching the query
List<User> joes = query.find();
// return only the first result or null if none
User joe = query.findFirst();
// return the only result or null if none, throw if more than one result
User joe = query.findUnique();
To return all entities matching the query simply call
find()
.To only return the first result, use
findFirst()
.If you expect a unique result, call
findUnique()
instead. It will give you a single result or null, if no matching entity was found and throw an exception if there was more than one result.If you frequently run the same query you should cache the
Query
object and re-use it. To make a Query
more reusable you can change the values, or query parameters, of each condition you added even after the Query
is built. Let's see how.Query is not thread safe. To use a query in a different thread, either build a new query or synchronize access to it. Alternatively, use
query.copy()
or a QueryThreadLocal
to obtain an instance for each thread.Assume we want to find a list of
User
with specific firstName
values. First, we build a regular Query
with an equal()
condition for firstName
. Because we have to pass an initial parameter value to equal()
but plan to override it before running the Query
later, we just pass an empty string:Java
Kotlin
Dart
// build a query
Query<User> query = userBox.query(User_.firstName.equal("")).build();
// build a query
val query = userBox.query(User_.firstName.equal("")).build()
// build a query
final query = userBox.query(User_.firstName.equals('')).build();
Now at some later point, we want to run the
Query
with an actual value for the equals
condition onfirstName
:Java
Kotlin
Dart
// change firstName parameter to "Joe", get results
List<User> joes = query.setParameter(User_.firstName, "Joe").find();
...
// change firstName parameter to "Jake", get results
List<User> jakes = query.setParameter(User_.firstName, "Jake").find();
// change firstName parameter to "Joe", get results
val joes = query.setParameter(User_.firstName, "Joe").find()
...
// change firstName parameter to "Jake", get results
val jakes = query.setParameter(User_.firstName, "Jake").find()
// change firstName parameter to "Joe", get results
query.param(User_.firstName).value = 'Joe';
final joes = query.find();
...
// change firstName parameter to "Jake", get results
final jakes = (query..param(User_.firstName).value = 'Jake').find();
You might already be wondering what happens if you have more than one condition using
firstName
? For this purpose you can assign each parameter an alias while specifying the condition:Java
Kotlin
Dart
// assign alias "name" to the equal query parameter
Query<User> query = userBox
.query(User_.firstName.equal("").alias("name"));
// assign alias "name" to the equal query parameter
val query = userBox
.query(User_.firstName.equal("").alias("name"))
// assign alias "name" to the equals query parameter
final query = userBox.query(User_.firstName.equals('', alias: 'name')).build();
Then, when setting a new parameter value pass the alias instead of the property:
Java
Kotlin
Dart
// change parameter with alias "name" to "Joe", get results
List<User> joes = query.setParameter("name", "Joe").find();
// change parameter with alias "name" to "Joe", get results
List<User> joes = query.setParameter("name", "Joe").find();
// change parameter with alias "name" to "Joe", get results
final joes = (query..param(User_.firstName, alias: 'name').value = 'Joe').find();
Sometimes you only need a subset of a query, for example, the first 10 elements to display in your user interface. This is especially helpful (and resource-efficient) when you have a high number of entities and you cannot limit the result using query conditions only.
Java
Kotlin
Dart
// offset by 10, limit to at most 5 results
List<User> joes = query.find(10, 5);
// offset by 10, limit to at most 5 results
val joes = query.find(10, 5)
// offset by 10, limit to at most 5 results
query
..offset = 10
..limit = 5;
List<User> joes = query.find();
offset:
The first offset
results are skipped.limit:
At most limit
results are returned.Only Java/Kotlin
To avoid loading query results right away, Query offers
findLazy()
and findLazyCached()
which return a LazyList
of the query results.LazyList is a thread-safe, unmodifiable list that reads entities lazily only once they are accessed. Depending on the find method called, the lazy list will be cached or not. Cached lazy lists store the previously accessed objects to avoid loading entities more than once. Some features of the list are limited to cached lists (e.g. features that require the entire list). See the LazyList class documentation for more details.
Only Dart
Instead of reading the whole result (list of objects) using
find()
you can stream it using stream()
:Dart
Query<User> query = userBox.query().build();
Stream<User stream = query.stream();
await stream.forEach((User user) => print(user));
query.close();
To remove all objects matching a query, call
query.remove()
.If you only want to return the values of a particular property and not a list of full objects you can use a PropertyQuery. After building a query, simply call
property(Property)
to define the property followed by the appropriate find method.For example, instead of getting all
User
s, to just get their email addresses:Java
Kotlin
Dart
String[] emails = userBox.query().build()
.property(User_.email)
.findStrings();
// or use .findString() to return just the first result
val emails = userBox.query().build()
.property(User_.email)
.findStrings()
// or use .findString() to return just the first result
final query = userBox.query().build();
List<String> emails = query.property(User_.email).find();
query.close();
Note: the returned array of property values is not in any particular order, even if you did specify an order when building the query.
By default, null values are not returned. However, you can specify a replacement value to return if a property is null:
Java
Kotlin
Dart
// includes 'unknown' for each null email
String[] emails = userBox.query().build()
.property(User_.email)
.nullValue("unknown")
.findStrings();
// includes 'unknown' for each null email
val emails = userBox.query().build()
.property(User_.email)
.nullValue("unknown")
.findStrings()
final query = userBox.query().build();
// includes 'unknown' for each null email
List<String> emails = query.property(User_.email).find(replaceNullWith: 'unknown');
query.close();
The property query can also only return distinct values:
Java
Kotlin
Dart
PropertyQuery pq = userBox.query().build().property(User_.firstName);
// returns ['joe'] because by default, the case of strings is ignored.
String[] names = pq.distinct().findStrings();
// returns ['Joe', 'joe', 'JOE']
String[] names = pq.distinct(StringOrder.CASE_SENSITIVE).findStrings();
// the query can be configured to throw there is more than one value
String[] names = pq.unique().findStrings();
val pq = userBox.query().build().property(User_.firstName)
// returns ['joe'] because by default, the case of strings is ignored.
val names = pq.distinct().findStrings()
// returns ['Joe', 'joe', 'JOE']
val names = pq.distinct(StringOrder.CASE_SENSITIVE).findStrings()
// the query can be configured to throw there is more than one value
val names = pq.unique().findStrings()
final query = userBox.query().build();
PropertyQuery<String> pq = query.property(User_.firstName);
pq.distinct = true;
// returns ['Joe', 'joe', 'JOE']
List<String> names = pq.find();
// returns ['joe']
pq.caseSensitive = false;
List<String> names = pq.find();
query.close();
Property queries (JavaDoc and Dart API docs) also offer aggregate functions to directly calculate the minimum, maximum, average, sum and count of all found values:
min()
/minDouble()
: Finds the minimum value for the given property over all objects matching the query.max()
/maxDouble()
: Finds the maximum value.sum()
/sumDouble()
: Calculates the sum of all values. Note: the non-double version detects overflows and throws an exception in that case.avg()
: Calculates the average (always a double) of all values.count()
: returns the number of results. This is faster than finding and getting the length of the result array. Can be combined withdistinct()
to count only the number of distinct values.
After creating a relation between entities, you might want to add a query condition for a property that only exists in the related entity. In SQL this is solved using JOINs. But as ObjectBox is not a SQL database we built something very similar: links. Links are based on Relations - see the doc page for the introduction.
Assume there is a
Person
that can be associated with multiple Address
entities:Java
Kotlin
Dart
@Entity
public class Person {
@Id long id;
String name;
ToMany<Address> addresses;
}
@Entity
public class Address {
@Id long id;
String street;
String zip;
}
@Entity
class Person {
@Id var id: Long = 0
var name: String? = null
lateinit var addresses: ToMany<Address>
}
@Entity
class Address {
@Id var id: Long = 0
var street: String? = null
var zip: String? = null
{
@Entity()
class Person {
int id;
String name;
final addresses = ToMany<Address>();
}
@Entity()
class Address {
int id;
String street;
String zip;
}
To get a
Person
with a certain name that also lives on a specific street, we need to query the associated Address
entities of a Person
. To do this, use the link()
method of the query builder to tell that the addresses
relation should be queried. Then add a condition for Address
:Java
Kotlin
Dart
// get all Person objects named "Elmo"...
QueryBuilder<Person> builder = personBox
.query(Person_.name.equal("Elmo"));
// ...which have an address on "Sesame Street"
builder.link(Person_.addresses)
.apply(Address_.street.equal("Sesame Street"));
List<Person> elmosOnSesameStreet = builder.build().find();
// get all Person objects named "Elmo"...
val builder = personBox
.query(Person_.name.equal("Elmo"))
// ...which have an address on "Sesame Street"
builder.link(Person_.addresses)
.apply(Address_.street.equal("Sesame Street"))
val elmosOnSesameStreet = builder.build().find()
// get all Person objects named "Elmo"...
QueryBuilder<Person> builder = personBox
.query(Person_.name.equals('Elmo'));
// ...which have an address on "Sesame Street"
builder.linkMany(Person_.addresses, Address_.street.equals('Sesame Street'));
Query<Person> query = builder.build();
List<Person> elmosOnSesameStreet = query.find();
query.close();
What if we want to get a list of
Address
instead of Person
? If you know ObjectBox relations well, you would probably add a @Backlink
relation to Address
and build your query using it with link()
as shown above:Java
Kotlin
Dart
@Entity
public class Address {
// ...
@Backlink(to = "addresses")
ToMany<Person> persons;
}
// get all Address objects with street "Sesame Street"...
QueryBuilder<Address> builder = addressBox
.query(Address_.street.equal("Sesame Street"));
// ...which are linked from a Person named "Elmo"
builder.link(Address_.persons)
.apply(Person_.name.equal("Elmo"));
List<Address> sesameStreetsWithElmo = builder.build().find();
@Entity
class Address {
// ...
@Backlink(to = "addresses")
lateinit var persons: ToMany<Person>
}
// get all Address objects with street "Sesame Street"...
val builder = addressBox
.query(Address_.street.equal("Sesame Street"))
// ...which are linked from a Person named "Elmo"
builder.link(Address_.persons)
.apply(Person_.name.equal("Elmo")
val sesameStreetsWithElmo = builder.build().find()
@Entity()
class Address {
...
@Backlink()
final persons = ToMany<Person>();
}
// get all Address objects with street "Sesame Street"...
QueryBuilder<Address> builder =
addressBox.query(Address_.street.equals('Sesame Street'));
// ...which are linked from a Person named "Elmo"
builder.linkMany(Address_.persons, Person_.name.equals('Elmo'));
Query<Address> query = builder.build();
List<Address> sesameStreetsWithElmo = query.find();
query.close();
But actually, you do not have to modify the
Address
entity (you still can if you need the @Backlink
elsewhere). Instead, we can use the backlink()
method to create a backlink to the addresses
relation from Person
just for that query:Java
Kotlin
Dart
// get all Address objects with street "Sesame Street"...
QueryBuilder<Address> builder = addressBox
.query(Address_.street.equal("Sesame Street"));
// ...which are linked from a Person named "Elmo"
builder.backlink(Person_.addresses)
.apply(Person_.name.equal("Elmo"));
List<Address> sesameStreetsWithElmo = builder.build().find();
// get all Address objects with street "Sesame Street"...
val builder = addressBox
.query(Address_.street.equal("Sesame Street"))
// ...which are linked from a Person named "Elmo"
builder.backlink(Person_.addresses)
.apply(Person_.name.equal("Elmo"))
val sesameStreetsWithElmo = builder.build().find()
// get all Address objects with street "Sesame Street"...
QueryBuilder<Address> builder =
addressBox.query(Address_.street.equals('Sesame Street'));
// ...which are linked from a Person named "Elmo"
builder.backlinkMany(Person_.addresses, Person_.name.equals('Elmo'));
Query<Address> query = builder.build();
List<Address> sesameStreetsWithElmo = query.find();
query.close();
Only Java/Kotlin
By default relations are loaded lazily: when you first access a
ToOne
or ToMany
property it will perform a database lookup to get its data. On each subsequent access it will use a cached version of that data.Java
Kotlin
List<Customer> customers = customerBox.query().build().find();
// Customer has a ToMany called orders.
// First access: this will cause a database lookup.
Order order = customers.get(0).orders.get(0);
val customers = customerBox.query().build().find()
// Customer has a ToMany called orders
val order = customers[0].orders[0] // first access: causes a database lookup
While this initial lookup is fast, you might want to prefetch
ToOne
or ToMany
values before the query results are returned. To do this call the QueryBuilder.eager
method when building your query and pass the