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

This tutorial also uses Coroutines with Room. To learn more, read our Coroutines With Room Persistence Library tutorial.

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.