Android Room Database with a View

Maintaining a Relationship is one of the toughest thing.

Trust me I know this better than anyone.

Well I am talking about maintaining an relationship between Tables in an RDBMS.

As an Android developer, The best thing we can deliver to the user is to make an product that is capable of working Offline.

To achieve such greatness, We are in need to maintain an impeccable relationship between our local tables to avoid entry of unnecessary data. Such that CRUD operation of data is faster.

Lets start the tutorial with an Use case. Say you have one table called Students

This Students table store the value of a student information like name ,department id , department name. In an Single Department we could have multiple students. So any table of this structure would result in so many redundant data.

So what can we do?

The simple answer is create two table.

Yes create two table one for storing student related information and one for storing department related information. This would help us to save lots of data and we can just fetching by joining both the tables.

So I hope We all would have did the same in establishing a relationship between one or more tables in our local storage. But establishing a relationship between one or more tables is not everyone’s piece of cake.

Say bye bye to the old days. Now to solve this problem we have Room with a View

So what is this View means - They are just an virtual table based on the result set of an SQL statement.

Say we have this query, This can be used to fetching information from both Student and Department table.

SELECT student_table.id, 
student_table.name, 
student_table.departmentId,
department.name AS departmentName 
FROM student_table 
INNER JOIN department 
ON student_table.departmentId = department.id

Yes we are going to use the same thing in Android using DatabaseView

DatabaseView is available in Android after Room Version 2.1.0.

What it does?

We can use the same above mentioned query inside DatabaseView annotation with an data class defining the column properties. This would create our View.

@DatabaseView(
    "SELECT student_table.id, student_table.name, student_table.departmentId," +
            "department.name AS departmentName FROM student_table " +
            "INNER JOIN department ON student_table.departmentId = department.id"
)
data class UserDetail(
    val id: Long,
    val name: String?,
    val departmentId: Long,
    val departmentName: String?
)

Now we have created an view, Next we need to include this View as a part of our App’s Database class.

@Database(entities = [User::class, Department::class], views = [UserDetail::class], version = 1)
abstract class UserRoomDatabase : RoomDatabase() {
    abstract fun userDao(): UserDao
}

So to fetch the value, we need to query our view in Dao.

@Query("SELECT * FROM UserDetail") // Name of View data class
fun getUserDetails(): LiveData<List<UserDetail>>

That’s is it we are good to go.

We can use this concept to maintain relationship between as many table as possible without making an effort.

You can checkout the full project here

Let me know if you have any doubts in the comments.