Database Views With Room for Android
In this Android Room tutorial, you’ll learn how to use the Database Views feature of Room to create pre-packaged SELECT statements. By Harun Wangereka.
Sign up/Sign in
With a free Kodeco account you can download source code, track your progress, bookmark, personalise your learner profile and more!
Create accountAlready a member of Kodeco? Sign in
Sign up/Sign in
With a free Kodeco account you can download source code, track your progress, bookmark, personalise your learner profile and more!
Create accountAlready a member of Kodeco? Sign in
Sign up/Sign in
With a free Kodeco account you can download source code, track your progress, bookmark, personalise your learner profile and more!
Create accountAlready a member of Kodeco? Sign in
Contents
Database Views With Room for Android
15 mins
- Getting Started
- Using Database Views
- Comparing a Database View and an Entity
- Submitting the Survey
- Creating a Database View
- Using Room Database Views in DAO Queries
- Fetching Data Using a DatabaseView
- Displaying the Data to the UI
- Fetching Data for the Different Views
- Navigating to All Surveys
- Where to Go From Here?
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 View
s andEntity
s 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 DatabaseView
s and Entity
s:
- You can use INSERT, UPDATE and DELETE with an
Entity
, but not with aDatabaseView
. - You define all your views in your apps using
views
, but you define entities usingentities
.
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:
- You get the email from
editEmail
and assign it to a variable:email
. - This condition check calls
validateEmail(email)
, which checks if the email isnull
or not. It returnsfalse
if it’snull
. It also checks if the email entered is valid and returnsfalse
if it’s not. - The code inside the
if
statement executes whenvalidateEmail(email)
returnstrue
.meal
holds the type of meal the user selected from the radio groups. - Once you have
meal
‘s value, you createSurveyListItem.CustomerSurvey
, which has all the information about the survey. It pulls the values forquestionOneAnswer
,questionTwoAnswer
andquestionThreeAnswer
fromtoggleButtonListeners()
, which has listeners for that purpose. - Here, you save
customerSurvey
by callinginsertCustomerSurvey(customerSurvey)
inCustomerSurveyViewModel
, which handles the logic to save to Room. - 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 fromCustomerSurvey
.
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 listLiveData
of typeSurveyListItem.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 isnull
, you setTextView
‘s No surveys found! message to visible and hideRecyclerView
. If it’s notnull
, you set the visibility ofTextView
to GONE and showRecyclerView
. You also callinitView(customerSurveyList)
with thecustomerSurveyList
value fromCustomerSurveyViewModel
. -
initView(customerSurveySurveyList: List
initializes) CustomerSurveysAdapter
withcustomerSurveyList
and sets the adapter forRecyclerView
toCustomerSurveysAdapter
, 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.
All videos. All books.
One low price.
A Kodeco subscription is the best way to learn and master mobile development — plans start at just $19.99/month! Learn iOS, Swift, Android, Kotlin, Flutter and Dart development and unlock our massive catalog of 50+ books and 4,000+ videos.
Learn more