Database Views With Room for Android

Creating a Database View

To create a view, you’ll add a @DatabaseView annotation to a class or data class. Start by navigating to customersurveys/SurveyListItem.kt. This is a sealed class with a couple of data classes for you to use in this tutorial.

At the bottom of SurveyListItem, just below QuestionOneSadView, add the following:

data class HappyBreakFastView(
    override val email: String
) : SurveyListItem()

This data class overrides the email variable from SurveyListItem and inherits from the class — meaning it’s a subtype of SurveyListItem.

After creating this data class, add @DatabaseView with a SELECT query to fetch all email ids from CustomerSurvey table where meal is set to “Breakfast”, just above the HappyBreakFastView data class. Your annotation should be look like this:

@DatabaseView("SELECT CustomerSurvey.email FROM CustomerSurvey WHERE CustomerSurvey.meal = 'Breakfast'")

A few things to note about the query inside the annotation:

• The query works like any other query you’ve written in Room.
• You need to request all the fields that you have in your data class as you write the query. In this case, you only need the email. You use SELECT CustomerSurvey.email From... to get the email from CustomerSurvey.

Congratulations, you’ve created your first view! Next, you’re going to see how you can use the view in your DAO queries.

Using Room Database Views in DAO Queries

First, you’ll include HappyBreakFastView in views in the app’s @Database.

Navigate to database/AppDatabase.kt and, inside views, add SurveyListItem.HappyBreakFastView::class. Your updated @Database annotation should look like below:

@Database(entities = [SurveyListItem.CustomerSurvey::class], version = 2, exportSchema = false,
    views = [
      SurveyListItem.AverageLunchView::class, 
      SurveyListItem.SadDinnerView::class,
      SurveyListItem.QuestionOneSadView::class,
      // Added
      SurveyListItem.HappyBreakFastView::class
])

Notice that the version = 2. You need to update the database version every time you add a view in the AppDatabase — otherwise, your app will crash. In this case, you have updated the version to 2. Sync gradle to apply all these changes.

Next, navigate to customers/CustomerSurveysDao.kt and, just below getQuestionOneSadView(), add the following code:

@Query("SELECT * FROM HappyBreakFastView")
fun getHappyBreakFastCustomers():LiveData<List<SurveyListItem.HappyBreakFastView>>

This method gets all customers that were happy with any aspect of the survey from the restaurant. To explain it in more detail:

• First, you use HappyBreakFastView as you would in a normal query.
• You call this method in CustomerSurveyRepo to get a list of all customers that responded to any of the questions with Good. Note that the return type of the method is a list LiveData of type SurveyListItem.HappyBreakFastView, which is an observable variable holder.

Now, you’ve created a view and the method to query the list of customers who responded with a positive answer in CustomerSurveysDao. In the next section, you’ll learn how to call this method from the repository class.

Fetching Data Using a DatabaseView

Navigate to customersurveys/CustomerSurveyRepo.kt and add the following method just below getQuestionOneSadView():

fun getHappyBreakFastCustomers()
            : LiveData<List<SurveyListItem.HappyBreakFastView>> {
  return customerSurveysDao.getHappyBreakFastCustomers()
}

This method calls getHappyBreakFastCustomers() from CustomerSurveysDao to get the data from Room. Its return type is a LiveData, which allows the caller of this method to observe any changes in the data.

Next, you’ll add a call to getHappyBreakFastCustomers() in CustomerSurveyViewModel. It’s responsible for displaying the data to the view — which, in this case, is not DatabaseView but AllSurveysFragment.

Navigate to customersurveys/CustomerSurveyViewModel.kt and add the following code:

val happyBreakfastCustomers 
            : LiveData<List<SurveyListItem.HappyBreakFastView>> by lazy {
  customerSurveyRepo.getHappyBreakFastCustomers()
}

This variable gets its value by calling getHappyBreakFastCustomers() from CustomerSurveyRepo. There’s a by lazy{} so that you don’t load the data immediately, but rather when the variable is first accessed.

Next, you’ll update the UI so it can display the data.

Displaying the Data to the UI

Navigate to allsurveys/AllSurveysFragment.kt and add the following code at the bottom of the class:

private fun getHappyBreakfastCustomers() {
  customerSurveyViewModel.happyBreakfastCustomers.observe(viewLifecycleOwner, Observer { customerSurveyList ->
    if (customerSurveyList.isEmpty()) {
      layoutEmptyView.visibility = View.VISIBLE
      rvReviews.visibility = View.GONE
    } else {
      layoutEmptyView.visibility = View.GONE
      rvReviews.visibility = View.VISIBLE
      initView(customerSurveyList)
    }
  })
}
private fun initView(customerSurveySurveyList: List<SurveyListItem.HappyBreakFastView>) {
  val customerSurveysAdapter = CustomerSurveysAdapter(customerSurveySurveyList)
  rvReviews.adapter = customerSurveysAdapter
}

To explain what the code does:

• First, it calls happyBreakfastCustomers and observes its value.
• Inside the observe lambda, there’s a check to see if customerSurveyList is null or not. If the list is null, you set TextView‘s No surveys found! message to visible and hide RecyclerView. If it’s not null, you set the visibility of TextView to GONE and show RecyclerView. You also call initView(customerSurveyList) with the customerSurveyList value from CustomerSurveyViewModel.
• initView(customerSurveySurveyList: List) initializes CustomerSurveysAdapter with customerSurveyList and sets the adapter for RecyclerView to CustomerSurveysAdapter, which now displays the list of surveys to the UI.

The IDE will prompt you to add the SurveyListItem import. If it doesn’t, add this import:

import com.raywenderlich.android.customersurveys.customersurveys.SurveyListItem

Now that you’ve displayed the data to the UI, you have only a few more steps before everything works perfectly.

Next, you’ll add the code that handles fetching data from Room depending on the option selected on the dropdown in the user interface i.e Spinner widget.

Fetching Data for the Different Views

Add the following piece of code just below onCreate in AllSurveysFragment.kt::

private fun spinnerListener() {
  filterSpinner.onItemSelectedListener = object : AdapterView.OnItemSelectedListener {
    override fun onNothingSelected(parent: AdapterView<*>?) {}
    override fun onItemSelected(parent: AdapterView<*>?, view: View?, position: Int, id: Long) {
      when (position) {
        0 -> resetState()
        1 -> getAllCustomerSurveys()
        2 -> getHappyBreakfastCustomers()
        3 -> getSadDinnerCustomers()
        4 -> getAverageLunchCustomers()
        5 -> getQuestionOneSadCustomers()
      }
    }
  }
}

The piece of code above sets onItemSelectedListener to filterSpinner and overrides two methods: onNothingSelected and onItemSelected. You don’t want to do anything when nothing is selected, so onNothingSelected is left empty. You do want to react to when an Item is selected, so you need to implement onItemSelected.

onItemSelected has a when expression that calls different methods depending on the option selected in filterSpinner. These methods are similar to getHappyBreakfastCustomers(), but they fetch data using a different DatabaseView.

Make sure you add the imports when the IDE prompts you.

Finally, add a call to spinnerListener() inside onViewCreated, right after setupSpinner(), as shown below:

override fun onViewCreated(view: View, savedInstanceState: Bundle?) {
  super.onViewCreated(view, savedInstanceState)
  setupSpinner()

  // Added
  spinnerListener()
}

Now that you have everything ready to fetch the surveys, your next step is to add the code to navigate to AllSurveysFragment.