Querying a View

And, just as we can have a @Dao class that works with AppEntity and its table, we can have a @Dao class that works with AppView and its database view:

package com.commonsware.room.misc

import androidx.room.Dao
import androidx.room.DatabaseView
import androidx.room.Query

@DatabaseView(
  viewName = "appListView",
  value = "SELECT applicationId, displayName, shortDescription, iconUrl FROM apps"
)
data class AppView(
  val applicationId: String,
  val displayName: String,
  val shortDescription: String,
  val iconUrl: String
) {
  @Dao
  interface Store {
    @Query("SELECT * FROM appListView")
    fun loadListView(): List<AppView>

    @Query("SELECT * FROM appListView WHERE applicationId = :applicationId")
    fun findById(applicationId: String): AppView?
  }
}

loadListView() on AppView.Store will return the exact same list as does listListModels() on AppEntity.Store, as they both refer to the same query. We reference appListView in @Query annotations just like we do tables like apps. The available columns are determined by our value query in the @DatabaseView and our properties on the class that we apply @DatabaseView to. Under the covers, when we query appListView, SQLite will really query apps based on the value query and then sub-select off of that based on the @Query against appListView.

We can also have queries against the view that apply constraints, as seen in findById(). In the end, we treat the database view much like a table… for read operations. We cannot @Insert, @Update, or @Delete data in a database view — for those operations, we have to manipulate the table that is used by the view.