DAOs and Queries

One popular thing to do with a database is to get data out of it. For that, we add @Query methods on our DAO.

Those do not have to be especially complicated, as we saw with the TripStore:

package com.commonsware.android.room;

import android.arch.persistence.room.Dao;
import android.arch.persistence.room.Delete;
import android.arch.persistence.room.Insert;
import android.arch.persistence.room.OnConflictStrategy;
import android.arch.persistence.room.Query;
import android.arch.persistence.room.Update;
import java.util.List;

@Dao
interface TripStore {
  @Query("SELECT * FROM trips ORDER BY title")
  List<Trip> selectAll();

  @Query("SELECT * FROM trips WHERE id=:id")
  Trip findById(String id);

  @Insert
  void insert(Trip... trips);

  @Update
  void update(Trip... trips);

  @Delete
  void delete(Trip... trips);
}

However, SQL queries with SQLite can get remarkably complicated. Room tries to support a lot of the standard SQL syntax, but Room adds its own complexity, in terms of trying to decipher how to interpret your @Query method’s arguments and return type.

Adding Parameters

As we saw with findById() on TripStore, you can map method arguments to query parameters by using : syntax. Put : before the argument name and its value will be injected into the query:

  @Query("SELECT * FROM thingy WHERE id=:id AND version_code=:versionCode")
  VersionedThingy findById(String id, int versionCode);

Bear in mind that the rest of the SQL statement is based on the table, not the entity. Table names and column names will either be the code-generated names or your overridden names (via tableName and @ColumnInfo).

WHERE Clause

Principally, your method arguments will be injected into your WHERE clause, such as in the above examples.

Note that Room has special support for IN in a WHERE clause. So, while this works for a single postalCode:

@Query("SELECT * FROM Customer WHERE postalCode IN (:postalCodes)")
List<Customer> findByPostalCodes(String postalCodes);

…you can also do:

@Query("SELECT * FROM Customer WHERE postalCode IN (:postalCodes)")
List<Customer> findByPostalCodes(List<String> postalCodes);

…or even:

@Query("SELECT * FROM Customer WHERE postalCode IN (:postalCodes)")
List<Customer> findByPostalCodes(String... postalCodes);

Room will convert the collection argument into a comma-delimited list for use with the SQL query.

Other Clauses

If SQLite allows ? placeholders, Room should allow method arguments to be used instead.

So, for example, you can parameterize a LIMIT clause:

  @Query("SELECT * FROM Customer WHERE postalCode IN (:postalCodes) LIMIT :max")
  List<Customer> findByPostalCodes(int max, String... postalCodes);

Here, because Java needs the varargs to be the last parameter, we need to have max first.

What You Can Return

We have seen that a @Query can return a single entity (e.g., findById() returning a single Trip) or a collection of entity (e.g., selectAll() returning a List of Trip entities).

While those are simple, Room offers a fair bit more flexibility than that. In particular, not only does Room support reactive return values, but we can return objects that are not actually entities.

Specific Return Types

In addition to returning single objects or collections of objects, a Room @Query can return a good old-fashioned Cursor. This is particularly useful if you are migrating legacy code that uses CursorAdapter or other Cursor-specific classes. Similarly, if you are looking to expose part of a Room-defined database via a ContentProvider, it may be more convenient for you to get your results in the form of a Cursor, so that you can just return that from the provider’s query() method.

Beyond that, a @Query method can return:

• A Flowable or Publisher from RxJava2, a popular framework for reactive programming
• A LiveData object

NOTE: The upcoming Room 2.1.0 release will support other RxJava types, such as Single.

We will explore what a LiveData object is later in this book.

Breadth of Results

For small entities, like Trip, usually we will retrieve all columns in the query. However, the real rule is: the core return object of the @Query method must be something that Room knows how to fill in from the columns that you request.

For wider tables with many columns, this is important. For example, perhaps for a RecyclerView, you only need a couple of columns, but for all entities in the table. In that case, it might be nice to only retrieve those specific columns. You have two ways to do that:

1. Have your @Entity support only a subset of columns, allowing the rest to be null or otherwise tracking the fact that we only retrieved a subset of columns from the table
2. Return something other than the entity that you have associated with this table

If you look at your @Dao-annotated interface, you will notice that while methods might refer to entities, its annotations do not. That is because the DAO is somewhat independent of the entities. The entities describe the table, but the DAO is not limited to using those entities. So long as the DAO can fulfill the contract stipulated by the SQL, the method arguments, and the method return type, Room is perfectly happy.

So, for example, suppose that Customer not only tracks an id and a postalCode, but also has many other fields, including a displayName:

package com.commonsware.android.room.dao;

import android.arch.persistence.room.Entity;
import android.arch.persistence.room.Ignore;
import android.arch.persistence.room.Index;
import android.arch.persistence.room.PrimaryKey;
import android.support.annotation.NonNull;
import java.util.UUID;

@Entity(indices={@Index(value="postalCode", unique=true)})
class Customer {
  @PrimaryKey
  @NonNull
  public final String id;

  public final String postalCode;
  public final String displayName;

  @Ignore
  Customer(String postalCode, String displayName) {
    this(UUID.randomUUID().toString(), postalCode, displayName);
  }

  Customer(String id, String postalCode, String displayName) {
    this.id=id;
    this.postalCode=postalCode;
    this.displayName=displayName;
  }
}

Perhaps to show a list of customers, we need the displayName (to show in the list) and the id (to know which specific customer this is). But we do not need the postalCode or the rest of the fields in the Customer class.

We can still return a Customer:

@Query("SELECT id, displayName FROM Customer WHERE postalCode IN (:postalCodes) LIMIT :max")
List<Customer> findByPostalCodes(List<String> postalCodes, int max);

The code that Room generates will simply fill in null for the postalCode, since that was not one of the returned columns. However, then it is not obvious whether a given instance of Customer is completely filled in from data in the table (and it is genuinely missing its postalCode) or whether this is a partially-populated Customer object.

However, we could also define a dedicated CustomerDisplayTuple class:

package com.commonsware.android.room.dao;

public class CustomerDisplayTuple {
  public final String id;
  public final String displayName;

  public CustomerDisplayTuple(String id, String displayName) {
    this.id=id;
    this.displayName=displayName;
  }
}

Then, we can return a List of CustomerDisplayTuple from our DAO:

  @Query("SELECT id, displayName FROM Customer WHERE postalCode IN (:postalCodes) LIMIT :max")
  List<CustomerDisplayTuple> loadDisplayTuplesByPostalCodes(int max, String... postalCodes);

This way, we get our subset of data, and we know by class whether we have the full Customer or just the subset for display purposes.

Note that @ColumnInfo annotations can be used on any class, not just entities. In particular, if you use @ColumnInfo on a field in an entity, you will need the same @ColumnInfo on any “tuple”-style classes that represent subsets of data that include that same field.

Aggregate Functions

A @Query can also return an int, for simple aggregate functions:

  @Query("SELECT COUNT(*) FROM Customer")
  int getCustomerCount();

If you wish to compute several aggregate functions, create a “tuple”-style class to hold the values:

package com.commonsware.android.room.dao;

public class CustomerStats {
  public final int count;
  public final String max;

  public CustomerStats(int count, String max) {
    this.count=count;
    this.max=max;
  }
}

…and use AS to name the aggregate function “columns” to match the tuple:

  @Query("SELECT COUNT(*) AS count, MAX(postalCode) AS max FROM Customer")
  CustomerStats getCustomerStats();