Database Views With Room for Android

Room is an abstraction layer over SQLite that Google packaged as an AndroidX library and also recommends. Since version 2.1, Room offers the ability to add Database Views, also known as stored queries.

Some of the good reasons to use Database Views would be:

• They make it easy for you to write complex queries and use them in Data Access Object (DAO) queries.
• You can query only the fields you need, rather than having to go through all the fields in a table.

In this tutorial, you’ll be building a Customer Surveys app which lets a restaurant’s customers leave feedback, then saves that feedback in Room managed database. During the process you’ll learn the following:

• What is a Database View?
• How to create Database Views?
• How to use them to simplify writing SELECT queries

Getting Started

Download the starter project by clicking the Download Materials button at the top or bottom of this tutorial.

Extract the ZIP file and open the starter project in Android Studio 4.0 or later by selecting Open an existing Android Studio project from the welcome screen.

Once the Gradle sync is complete, explore the project structure. The project follows MVVM architecture, so similar functionalities are under one package. Familiarize yourself with the packages present — you’ll use them in this tutorial.

Build and run. You’ll see a simple screen with a welcome message, an image and a START SURVEY button.

Tap the START SURVEY button. For the purpose of this tutorial, you can ignore the fact that you haven’t eaten a meal at the restaurant. :]

The next screen is the survey screen. It has an email input field, radio buttons to choose the meal that you’re rating and three questions. Each question has Good, Average and Bad buttons below them, so the user can rate their satisfaction.

You’ll also see the SUBMIT SURVEY button. Tap it and you’ll see a toast which says it’s not time to take the survey yet. Don’t worry, you’ll fix that over the course of this tutorial.

You’ve now been welcomed to The View Restaurant, where you’ll get to see amazing nature views, taste their delicious meals and rate your satisfaction. While at it, you’ll also learn about Room Database Views.

Using Database Views

Consider a table that has extra functionality of pre-packaged SELECT queries for convenience. Room version 2.1 and higher calls these as a Database View and provides an annotation with the same name i.e @DatabaseView.

Using this annotation you can mark a class to behave like a Database View. This will enable you to attach a query to the class, like below:

@DatabaseView("SELECT user.id, user.name " +
  "AS departmentName FROM user " +
  "WHERE user.departmentId = department.id")
class User {
  var id: Long = 0
  var name: String? = null
}

You can then use this class in your DAO to query data the same way you would do with a class marked as an Entity i.e Table in a database.

A DAO helps you access data from your app’s database. It typically contain the CUD (Create, Update and Delete) methods and can also contain other methods that may be necessary for read and write access to the database.

The relationship between Database Views and the database is similar to the relationship between entities and the database. You’ll take a deeper look at those relationships next.

Comparing a Database View and an Entity

Classes annotated with @DatabaseView are similar to Entity classes. Here’s how:

• Both can use SELECT FROM in DAO queries.
• Database Views and Entitys can both use @ColumnInfo, which allows you to customize the column information associated with a field.
• They can both use @Embedded, which allows a field to have nested fields that queries can reference directly.

While there are many similarities between the two, there are also differences between DatabaseViews and Entitys:

• You can use INSERT, UPDATE and DELETE with an Entity, but not with a DatabaseView.
• You define all your views in your apps using views, but you define entities using entities.

Now that you know what a DatabaseView is and how it compares and contrasts with an Entity class, it’s time to use it and start submitting the survey for The View Restaurant.

Submitting the Survey

Your first step is to add the logic to submit the survey and save it to the Room database after you tap the SUBMIT SURVEY button.

Navigate to customersurveys/CustomerSurveyFragment.kt, where you’ll add the logic for collecting the responses and saving them to Room. Do this by replacing the code in submitSurvey() with this:

// 1
val email = editEmail.text.toString()
// 2
if (validateEmail(email)) {
  // 3
  val meal = when (radioGroupMeals.checkedRadioButtonId) {
      R.id.radioBreakfast -> "Breakfast"
      R.id.radioLunch -> "Lunch"
      R.id.radioDinner -> "Dinner"
      else -> "No Meal"
  }
  // 4
  val customerSurvey = SurveyListItem
                        .CustomerSurvey(0, email, meal, questionOneAnswer, 
                                    questionTwoAnswer, questionThreeAnswer)
  customerSurveyViewModel.insertCustomerSurvey(customerSurvey)
  
  // 5
  findNavController()
    .navigate(R.id.action_surveyFragment_to_surveyCompletedFragment)
}

Here’s what you’re doing with this code:

1. You get the email from editEmail and assign it to a variable: email.
2. This condition check calls validateEmail(email), which checks if the email is null or not. It returns false if it’s null. It also checks if the email entered is valid and returns false if it’s not.
3. The code inside the if statement executes when validateEmail(email) returns true. meal holds the type of meal the user selected from the radio groups.
4. Once you have meal‘s value, you create SurveyListItem.CustomerSurvey, which has all the information about the survey. It pulls the values for questionOneAnswer, questionTwoAnswer and questionThreeAnswer from toggleButtonListeners(), which has listeners for that purpose.
5. Here, you save customerSurvey by calling insertCustomerSurvey(customerSurvey) in CustomerSurveyViewModel, which handles the logic to save to Room.
6. You navigate to SurveyCompletedFragment.

After adding this, you’ll notice that customerSurveyViewModel and findNavController() have red underlines. To fix this, first add the CustomerSurveyViewModel initialization at the top of the class, just below the questionThreeAnswer initialization.

private val customerSurveyViewModel: CustomerSurveyViewModel by viewModels()

Make sure to add respective import statements when the IDE prompts you.

Build and run. Start the survey, enter the required email input and select your answers to the questions.

Great, you’ve completed the survey.

Tap the VIEW SURVEYS button… oops, it doesn’t do anything yet. Don’t worry, you’ll fix that soon.

In the next section, you’ll learn how to create your first DatabaseView.

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.

Navigating to All Surveys

This is the last step you need to see the views in action.

Navigate to completedsurvey/SurveyCompletedFragment.kt and uncomment the code inside btnViewSurveys. Your final result will look like this:

override fun onViewCreated(view: View, savedInstanceState: Bundle?) {
  super.onViewCreated(view, savedInstanceState)
  btnViewSurveys.setOnClickListener {
    findNavController()
         .navigate(R.id.action_surveyCompletedFragment_to_allSurveysFragment)
  }
}

Here, you’re simply setting the click listener for the VIEW SURVEYS button and navigating to AllSurveysFragment.

Once you uncomment the code, the IDE will prompt you to import findNavController(). Simply import the required.

Build, run and start a survey, then answer the questions and submit them. Finally, view All Surveys, where you’ll be able to fetch all the data depending on the option you selected on the spinner.

Congratulations! You’ve finished your experience at The View Restaurant. Hopefully you have had a great meal, seen amazing views and had a chance to learn what DatabaseViews are.

Where to Go From Here?

Download the final project by using the Download Materials button at the top or bottom of the tutorial.

For more information about Room’s features, check out the official documentation from Android.

We hope you enjoyed this Room Database Views tutorial. If you have any questions, comments or awesome modifications to this project app, please join the forum discussion below.