Search with the Where Clause¶

A where clause is a condition which references columns of a data table where the query is sent. For any given query Backendless returns the data records which match the condition in the where clause. A where clause is a query string which must conform to a subset of the SQL-92 standard. The subset of the syntax supported by Backendless is the part that goes into the WHERE clause of an SQL query. This is the reason why we refer to it as the where clause. For example, consider the following data objects, specifically notice the columns name and age:

Values for columns of types STRING and TEXT must be wrapped in single quotes. Suppose you need to retrieve an object where the value of the name property is "Joe". This can be accomplished with the following where clause:

name = 'Joe'

If the value itself has a single quote, it can be "escaped" by adding an additional single quote. For example:

name = 'Charlie''s Angels'

If you need all objects where the age column contains a value less than 30:

age < 30

Just like in SQL, columns can be combined in a single where clause. The following condition would retrieve all objects containing letter J in the name and the age column's value is greater than 20:

name LIKE 'J%' and age > 20

Condition	Sample where clause	Column type
Value in a column equals a string literal.	`name = 'foo'`	STRING, TEXT or EXTENDED STRING
Value in a column does not equal a string literal.	`name != 'foo'`	STRING, TEXT or EXTENDED STRING
Value in a column is not assigned	`name is null`	STRING, TEXT or EXTENDED STRING
Value in a column is assigned	`name is not null`	STRING, TEXT or EXTENDED STRING
Value in a column contains a substring literal	`name LIKE '%foo%'`	STRING, TEXT or EXTENDED STRING
Value in a column ends with a substring literal	`name LIKE '%foo'`	STRING, TEXT or EXTENDED STRING
Value in a column starts with a substring literal	`name LIKE 'foo%'`	STRING, TEXT or EXTENDED STRING
Value in a column is one of enumerated string literals.	`name IN ('value1', 'value2', 'value3)` or `name = 'value1' OR name = 'value2' or name = 'value3'`	STRING, TEXT or EXTENDED STRING
Value in a column equals a numeric literal	`age = 21`	INT or DOUBLE
Value in a column is greater than a numeric literal	`age > 21`	INT or DOUBLE
Value in a column is greater than or equal a numeric literal	`age >= 21`	INT or DOUBLE
Value in a column is less than a numeric literal	`age < 21`	INT or DOUBLE
Value in a column is less than or equal a numeric literal	`age <= 21`	INT or DOUBLE

Examples

Using Java Map

You do not need to declare/write any custom classes when using the Map approach. Data records are represented as java.util.Map objects. Column names become map property names and the object values are corresponding property values. === "Custom Class"

Consider the following class:

package com.sample;

public class Contact
{
  private String objectId;
  private String name;
  private int age;
  private String phone;
  private String title;

  public String getObjectId() {
    return objectId;
  }

  public void setObjectId( String objectId ) {
    this.objectId = objectId;
  }

  public String getName() {
    return name;
  }

  public void setName( String name ) {
    this.name = name;
  }

  public int getAge() {
    return age;
  }

  public void setAge( int age ) {
    this.age = age;
  }

  public String getPhone() {
    return phone;
  }

  public void setPhone( String phone ) {
    this.phone = phone;
  }

  public String getTitle() {
    return title;
  }

  public void setTitle( String title ) {
    this.title = title;
  }
}

Find all contacts where the value of the "age" property equals 47:

Using Java MapCustom Class

String whereClause = "age = 47";
DataQueryBuilder queryBuilder = DataQueryBuilder.create();
queryBuilder.setWhereClause( whereClause );

// ***********************************************************
// Synchronous API:
// ***********************************************************
List<Map> result = Backendless.Data.of( "Contact" ).find( queryBuilder );

// ***********************************************************
// Asynchronous API:
// ***********************************************************
Backendless.Data.of( "Contact" ).find( queryBuilder, 
                                       new AsyncCallback<List<Map>>(){
  @Override
  public void handleResponse( List<Map> foundContacts )
  {
    // every loaded object from the "Contact" table is now an individual java.util.Map
  }
  @Override
  public void handleFault( BackendlessFault fault )
  {
    // an error has occurred, the error code can be retrieved with fault.getCode()
  }
});

String whereClause = "age = 47";
DataQueryBuilder queryBuilder = DataQueryBuilder.create();
queryBuilder.setWhereClause( whereClause );

// ***********************************************************
// Synchronous API:
// ***********************************************************
List<Contact> result = Backendless.Data.of( Contact.class ).find( queryBuilder );

// ***********************************************************
// Asynchronous API:
// ***********************************************************
Backendless.Data.of( Contact.class ).find( queryBuilder, 
                                           new AsyncCallback<List<Contact>>(){
  @Override
  public void handleResponse( List<Contact> foundContacts )
  {
    // the "foundContact" collection now contains instances of the Contact class.
    // each instance represents an object stored on the server.
  }
  @Override
  public void handleFault( BackendlessFault fault )
  {
    // an error has occurred, the error code can be retrieved with fault.getCode()
  }
});

Find all contacts where the value of the "age" property is greater than 21:

Using Java MapCustom Class

String whereClause = "age > 21";
DataQueryBuilder queryBuilder = DataQueryBuilder.create();
queryBuilder.setWhereClause( whereClause );

// ***********************************************************
// Synchronous API:
// ***********************************************************
List<Map> result = Backendless.Data.of( "Contact" ).find( queryBuilder );

// ***********************************************************
// Asynchronous API:
// ***********************************************************
Backendless.Data.of( "Contact" ).find( queryBuilder, 
                                       new AsyncCallback<List<Map>>(){
  @Override
  public void handleResponse( List<Map> foundContacts )
  {
    // every loaded object from the "Contact" table is now an individual java.util.Map
  }
  @Override
  public void handleFault( BackendlessFault fault )
  {
    // an error has occurred, the error code can be retrieved with fault.getCode()
  }
});

String whereClause = "age > 21";
DataQueryBuilder queryBuilder = DataQueryBuilder.create();
queryBuilder.setWhereClause( whereClause );

// ***********************************************************
// Synchronous API:
// ***********************************************************
List<Contact> result = Backendless.Data.of( Contact.class ).find( queryBuilder );

// ***********************************************************
// Asynchronous API:
// ***********************************************************
Backendless.Data.of( Contact.class ).find( queryBuilder, 
                                           new AsyncCallback<List<Contact>>(){
  @Override
  public void handleResponse( List<Contact> foundContacts )
  {
    // the "foundContact" collection now contains instances of the Contact class.
    // each instance represents an object stored on the server.
  }
  @Override
  public void handleFault( BackendlessFault fault )
  {
    // an error has occurred, the error code can be retrieved with fault.getCode()
  }
});

Find all contacts by name:

Using Java MapCustom Class

String whereClause = "name = 'Jack Daniels'";
DataQueryBuilder queryBuilder = DataQueryBuilder.create();
queryBuilder.setWhereClause( whereClause );

// ***********************************************************
// Synchronous API:
// ***********************************************************
List<Map> result = Backendless.Data.of( "Contact" ).find( queryBuilder );

// ***********************************************************
// Asynchronous API:
// ***********************************************************
Backendless.Data.of( "Contact" ).find( queryBuilder, 
                                       new AsyncCallback<List<Map>>(){
  @Override
  public void handleResponse( List<Map> foundContacts )
  {
    // every loaded object from the "Contact" table is now an individual java.util.Map
  }
  @Override
  public void handleFault( BackendlessFault fault )
  {
    // an error has occurred, the error code can be retrieved with fault.getCode()
  }
});

String whereClause = "name = 'Jack Daniels'";
DataQueryBuilder queryBuilder = DataQueryBuilder.create();
queryBuilder.setWhereClause( whereClause );

// ***********************************************************
// Synchronous API:
// ***********************************************************
List<Contact> result = Backendless.Data.of( Contact.class ).find( queryBuilder );

// ***********************************************************
// Asynchronous API:
// ***********************************************************
Backendless.Data.of( Contact.class ).find( queryBuilder, 
                                           new AsyncCallback<List<Contact>>(){
  @Override
  public void handleResponse( List<Contact> foundContacts )
  {
    // the "foundContact" collection now contains instances of the Contact class.
    // each instance represents an object stored on the server.
  }
  @Override
  public void handleFault( BackendlessFault fault )
  {
    // an error has occurred, the error code can be retrieved with fault.getCode()
  }
});

Find all contacts by partial name match:

Using Java MapCustom Class

String whereClause = "name LIKE 'Jack%'";
DataQueryBuilder queryBuilder = DataQueryBuilder.create();
queryBuilder.setWhereClause( whereClause );

// ***********************************************************
// Synchronous API:
// ***********************************************************
List<Map> result = Backendless.Data.of( "Contact" ).find( queryBuilder );

// ***********************************************************
// Asynchronous API:
// ***********************************************************
Backendless.Data.of( "Contact" ).find( queryBuilder, 
                                       new AsyncCallback<List<Map>>(){
  @Override
  public void handleResponse( List<Map> foundContacts )
  {
    // every loaded object from the "Contact" table is now an individual java.util.Map
  }
  @Override
  public void handleFault( BackendlessFault fault )
  {
    // an error has occurred, the error code can be retrieved with fault.getCode()
  }
});

String whereClause = "name LIKE 'Jack%'";
DataQueryBuilder queryBuilder = DataQueryBuilder.create();
queryBuilder.setWhereClause( whereClause );

// ***********************************************************
// Synchronous API:
// ***********************************************************
List<Contact> result = Backendless.Data.of( Contact.class ).find( queryBuilder );

// ***********************************************************
// Asynchronous API:
// ***********************************************************
Backendless.Data.of( Contact.class ).find( queryBuilder, 
                                           new AsyncCallback<List<Contact>>(){
  @Override
  public void handleResponse( List<Contact> foundContacts )
  {
    // the "foundContact" collection now contains instances of the Contact class.
    // each instance represents an object stored on the server.
  }
  @Override
  public void handleFault( BackendlessFault fault )
  {
    // an error has occurred, the error code can be retrieved with fault.getCode()
  }
});

Codeless Reference¶

Consider the following data table:

For demonstration purposes, this Codeless Reference section has only one example which describes all use-cases of the where clause presented in the Examples section of this topic: Refer to the Examples section to find description for every Codeless use-case presented below:

The numbering in the list presented below is also referenced in the screenshot of the Codeless logic example:

1. Find all contacts where the value of the "age" property equals 20.

2. Find all contacts where the value of the "age" property is greater than 21.

3. Find all contacts where the value of the "age" property is between 21 and 40.

4. Find all contacts whose "age" property is 30 or 10.

5. Find all contacts by name 'Joe'.

6. Find all contacts whose name is 'Joe' or 'Kevin':

7. Find all contacts by partial name match.

data_service_search_with_the_where_clause

Important

For a detailed description of all input parameters see the Basic Object Retrieval topic of this guide.