Accessing data using Room DAOs

To access your app's data using the Room persistence library, you work with data access objects, or DAOs. This set of Dao objects forms the main component of Room, as each DAO includes methods that offer abstract access to your app's database.

By accessing a database using a DAO class instead of query builders or direct queries, you can separate different components of your database architecture. Furthermore, DAOs allow you to easily mock database access as you test your app.

A DAO can be either an interface or an abstract class. If it's an abstract class, it can optionally have a constructor that takes a RoomDatabase as its only parameter. Room creates each DAO implementation at compile time.

Note: Room doesn't support database access on the main thread unless you've called allowMainThreadQueries() on the builder because it might lock the UI for a long period of time. Asynchronous queries—queries that return instances of LiveData or Flowable—are exempt from this rule because they asynchronously run the query on a background thread when needed.

Define methods for convenience

There are multiple convenience queries that you can represent using a DAO class. This document includes several common examples.

Insert

When you create a DAO method and annotate it with @Insert, Room generates an implementation that inserts all parameters into the database in a single transaction.

The following code snippet shows several example queries:

@Dao
interface MyDao {
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    fun insertUsers(vararg users: User)

    @Insert
    fun insertBothUsers(user1: User, user2: User)

    @Insert
    fun insertUsersAndFriends(user: User, friends: List<User>)
}

@Dao
public interface MyDao {
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    public void insertUsers(User... users);

    @Insert
    public void insertBothUsers(User user1, User user2);

    @Insert
    public void insertUsersAndFriends(User user, List<User> friends);
}

If the @Insert method receives only 1 parameter, it can return a long, which is the new rowId for the inserted item. If the parameter is an array or a collection, it should return long[] or List<Long> instead.

For more details, see the reference documentation for the @Insert annotation, as well as the SQLite documentation for rowid tables.

Update

The Update convenience method modifies a set of entities, given as parameters, in the database. It uses a query that matches against the primary key of each entity.

The following code snippet demonstrates how to define this method:

@Dao
interface MyDao {
    @Update
    fun updateUsers(vararg users: User)
}

@Dao
public interface MyDao {
    @Update
    public void updateUsers(User... users);
}

Although usually not necessary, you can have this method return an int value instead, indicating the number of rows updated in the database.

Delete

The Delete convenience method removes a set of entities, given as parameters, from the database. It uses the primary keys to find the entities to delete.

The following code snippet demonstrates how to define this method:

@Dao
interface MyDao {
    @Delete
    fun deleteUsers(vararg users: User)
}

@Dao
public interface MyDao {
    @Delete
    public void deleteUsers(User... users);
}

Although usually not necessary, you can have this method return an int value instead, indicating the number of rows removed from the database.

Query for information

@Query is the main annotation used in DAO classes. It allows you to perform read/write operations on a database. Each @Query method is verified at compile time, so if there is a problem with the query, a compilation error occurs instead of a runtime failure.

Room also verifies the return value of the query such that if the name of the field in the returned object doesn't match the corresponding column names in the query response, Room alerts you in one of the following two ways:

• It gives a warning if only some field names match.
• It gives an error if no field names match.

Simple queries

@Dao
interface MyDao {
    @Query("SELECT * FROM user")
    fun loadAllUsers(): Array<User>
}

@Dao
public interface MyDao {
    @Query("SELECT * FROM user")
    public User[] loadAllUsers();
}

This is a very simple query that loads all users. At compile time, Room knows that it is querying all columns in the user table. If the query contains a syntax error, or if the user table doesn't exist in the database, Room displays an error with the appropriate message as your app compiles.

Passing parameters into the query

Most of the time, you need to pass parameters into queries to perform filtering operations, such as displaying only users who are older than a certain age. To accomplish this task, use method parameters in your Room annotation, as shown in the following code snippet:

@Dao
interface MyDao {
    @Query("SELECT * FROM user WHERE age > :minAge")
    fun loadAllUsersOlderThan(minAge: Int): Array<User>
}

@Dao
public interface MyDao {
    @Query("SELECT * FROM user WHERE age > :minAge")
    public User[] loadAllUsersOlderThan(int minAge);
}

When this query is processed at compile time, Room matches the :minAge bind parameter with the minAge method parameter. Room performs the match using the parameter names. If there is a mismatch, an error occurs as your app compiles.

You can also pass multiple parameters or reference them multiple times in a query, as shown in the following code snippet:

@Dao
interface MyDao {
    @Query("SELECT * FROM user WHERE age BETWEEN :minAge AND :maxAge")
    fun loadAllUsersBetweenAges(minAge: Int, maxAge: Int): Array<User>

    @Query("SELECT * FROM user WHERE first_name LIKE :search " +
           "OR last_name LIKE :search")
    fun findUserWithName(search: String): List<User>
}

@Dao
public interface MyDao {
    @Query("SELECT * FROM user WHERE age BETWEEN :minAge AND :maxAge")
    public User[] loadAllUsersBetweenAges(int minAge, int maxAge);

    @Query("SELECT * FROM user WHERE first_name LIKE :search " +
           "OR last_name LIKE :search")
    public List<User> findUserWithName(String search);
}

Returning subsets of columns

Most of the time, you need to get only a few fields of an entity. For example, your UI might display just a user's first name and last name, rather than every detail about the user. By fetching only the columns that appear in your app's UI, you save valuable resources, and your query completes more quickly.

Room allows you to return any Java-based object from your queries as long as the set of result columns can be mapped into the returned object. For example, you can create the following plain old Java-based object (POJO) to fetch the user's first name and last name:

data class NameTuple(
    @ColumnInfo(name = "first_name") var firstName: String?,
    @ColumnInfo(name = "last_name") var lastName: String?
)

public class NameTuple {
    @ColumnInfo(name = "first_name")
    public String firstName;

    @ColumnInfo(name = "last_name")
    public String lastName;
}

Now, you can use this POJO in your query method:

@Dao
interface MyDao {
    @Query("SELECT first_name, last_name FROM user")
    fun loadFullName(): List<NameTuple>
}

@Dao
public interface MyDao {
    @Query("SELECT first_name, last_name FROM user")
    public List<NameTuple> loadFullName();
}

Room understands that the query returns values for the first_name and last_name columns and that these values can be mapped into the fields of the NameTuple class. Therefore, Room can generate the proper code. If the query returns too many columns, or a column that doesn't exist in the NameTuple class, Room displays a warning.

Passing a collection of arguments

Some of your queries might require you to pass in a variable number of parameters, with the exact number of parameters not known until runtime. For example, you might want to retrieve information about all users from a subset of regions. Room understands when a parameter represents a collection and automatically expands it at runtime based on the number of parameters provided.

@Dao
interface MyDao {
    @Query("SELECT first_name, last_name FROM user WHERE region IN (:regions)")
    fun loadUsersFromRegions(regions: List<String>): List<NameTuple>
}

@Dao
public interface MyDao {
    @Query("SELECT first_name, last_name FROM user WHERE region IN (:regions)")
    public List<NameTuple> loadUsersFromRegions(List<String> regions);
}

Observable queries

When performing queries, you'll often want your app's UI to update automatically when the data changes. To achieve this, use a return value of type LiveData in your query method description. Room generates all necessary code to update the LiveData when the database is updated.

@Dao
interface MyDao {
    @Query("SELECT first_name, last_name FROM user WHERE region IN (:regions)")
    fun loadUsersFromRegionsSync(regions: List<String>): LiveData<List<User>>
}

@Dao
public interface MyDao {
    @Query("SELECT first_name, last_name FROM user WHERE region IN (:regions)")
    public LiveData<List<User>> loadUsersFromRegionsSync(List<String> regions);
}

Reactive queries with RxJava

Room provides the following support for return values of RxJava2 types:

• @Query methods: Room supports return values of type Publisher, Flowable, and Observable.
• @Insert, @Update, and @Delete methods: Room 2.1.0 and higher supports return values of type Completable, Single<T>, and Maybe<T>.

To use this functionality, include the latest version of the rxjava2 artifact in your app's build.gradle file:

app/build.gradle

dependencies {
    implementation 'androidx.room:room-rxjava2:2.1.0-alpha02'
}

The following code snippet shows several examples of how you might use these return types:

@Dao
interface MyDao {
    @Query("SELECT * from user where id = :id LIMIT 1")
    fun loadUserById(id: Int): Flowable<User>

    // Emits the number of users added to the database.
    @Insert
    fun insertLargeNumberOfUsers(users: List<User>): Maybe<Int>

    // Makes sure that the operation finishes successfully.
    @Insert
    fun insertLargeNumberOfUsers(varargs users: User): Completable

    /* Emits the number of users removed from the database. Always emits at
       least one user. */
    @Delete
    fun deleteAllUsers(users: List<User>): Single<Int>
}

@Dao
public interface MyDao {
    @Query("SELECT * from user where id = :id LIMIT 1")
    public Flowable<User> loadUserById(int id);

    // Emits the number of users added to the database.
    @Insert
    public Maybe<Integer> insertLargeNumberOfUsers(List<User> users);

    // Makes sure that the operation finishes successfully.
    @Insert
    public Completable insertLargeNumberOfUsers(User... users);

    /* Emits the number of users removed from the database. Always emits at
       least one user. */
    @Delete
    public Single<Integer> deleteUsers(List<User> users);
}

For more details, see the Google Developers Room and RxJava article.

Direct cursor access

If your app's logic requires direct access to the return rows, you can return a Cursor object from your queries, as shown in the following code snippet:

@Dao
interface MyDao {
    @Query("SELECT * FROM user WHERE age > :minAge LIMIT 5")
    fun loadRawUsersOlderThan(minAge: Int): Cursor
}

@Dao
public interface MyDao {
    @Query("SELECT * FROM user WHERE age > :minAge LIMIT 5")
    public Cursor loadRawUsersOlderThan(int minAge);
}

Caution: It's highly discouraged to work with the Cursor API because it doesn't guarantee whether the rows exist or what values the rows contain. Use this functionality only if you already have code that expects a cursor and that you can't refactor easily.

Querying multiple tables

Some of your queries might require access to multiple tables to calculate the result. Room allows you to write any query, so you can also join tables. Furthermore, if the response is an observable data type, such as Flowable or LiveData, Room watches all tables referenced in the query for invalidation.

The following code snippet shows how to perform a table join to consolidate information between a table containing users who are borrowing books and a table containing data about books currently on loan:

@Dao
interface MyDao {
    @Query(
        "SELECT * FROM book " +
        "INNER JOIN loan ON loan.book_id = book.id " +
        "INNER JOIN user ON user.id = loan.user_id " +
        "WHERE user.name LIKE :userName"
    )
    fun findBooksBorrowedByNameSync(userName: String): List<Book>
}

@Dao
public interface MyDao {
    @Query("SELECT * FROM book " +
           "INNER JOIN loan ON loan.book_id = book.id " +
           "INNER JOIN user ON user.id = loan.user_id " +
           "WHERE user.name LIKE :userName")
   public List<Book> findBooksBorrowedByNameSync(String userName);
}

You can also return POJOs from these queries. For example, you can write a query that loads a user and their pet's name as follows:

@Dao
interface MyDao {
    @Query(
        "SELECT user.name AS userName, pet.name AS petName " +
        "FROM user, pet " +
        "WHERE user.id = pet.user_id"
    )
    fun loadUserAndPetNames(): LiveData<List<UserPet>>

    // You can also define this class in a separate file.
    data class UserPet(var userName: String?, var petName: String?)
}

@Dao
public interface MyDao {
   @Query("SELECT user.name AS userName, pet.name AS petName " +
          "FROM user, pet " +
          "WHERE user.id = pet.user_id")
   public LiveData<List<UserPet>> loadUserAndPetNames();

   // You can also define this class in a separate file, as long as you add the
   // "public" access modifier.
   static class UserPet {
       public String userName;
       public String petName;
   }
}