Room DataBase Operations — Kotlin

3 min readNov 29, 2020

This story explains about Room DataBase with an use case. It covers most important RoomDB operations like insert,Query(3 variations),update,delete queries.

Repository Link:

https://github.com/chandragithub2014/MyBillsInfo

Use Case:

The user using this app can save information about his expenses in local DB. The app retrieves the added expense list and provides user an option to add new expense, update and delete individual items.

The below are the fragments that are used :

(1) LoginRegistrationFragment : This Fragment will have an edit text where user can enter userId. In subsequent logins user should enter the same userid to view list of expense lists he saved otherwise new list is created with entered userid if the userid does not exist.

(2)BillListFragment : This Fragment displays expense list by retrieving from local RoomDB if there are some items. Otherwise it provides floating action button to add new item.

(3)AddBillFragment: This Fragment allows user to add new expense item in to localDB for the logged in user.

(4)EditBillFragment: This Fragment allows to edit an expense item or delete the item.

(5)UserViewModel: This view model contains all the methods to interact with RoomDB repository to fetch required info to the corresponding fragment.

(6)UserDBRepository : This class contains methods to interact with RoomDB and return the results to corresponding viewmodel.

(7)UserInfoDataBase(Abstract class) & UserDao(Interface) : These 2 classes contains methods and to create RoomDB and UserDao contains all the methods that contains queries for RoomDB operations.

@Dao
interface UserDao {

    @Query("SELECT * FROM user_bills WHERE user_name = :userId")
    fun fetchByUserId(userId: String): Flow<List<User>>

    @Query("SELECT * FROM user_bills WHERE uid = :uId")
    fun fetchByUId(uId: Long): Flow<User>

    @Insert
    fun insertAll(vararg users: User)

    @Query("DELETE from user_bills where uid = :uid")
    suspend fun delete(uid: Long) : Int


    @Insert(onConflict = OnConflictStrategy.IGNORE)
    suspend fun insert(user: User) : Long

    @Update
    suspend fun updateUser(user: User) : Int

    @Query("SELECT SUM(visit_expense) FROM user_bills WHERE user_name = :userId")
    fun fetchSumOfExpenses(userId: String):Flow<Long>
}

(8)Model: A dataclass with name User represent the RoomDB table

@Entity(tableName = "user_bills")
data class User(
    @PrimaryKey(autoGenerate = true) val uid: Long,
    @ColumnInfo(name = "user_name") val userName: String?,
    @ColumnInfo(name = "place_visited") val placeVisited: String?,
    @ColumnInfo(name = "visit_purpose") val visitPurpose: String?,
    @ColumnInfo(name = "visit_expense") val visitExpense: Long?,
    @ColumnInfo(name = "visited_date") val visitedDate: String?

)

APIs Used:

Coroutines, LiveData, ViewModel , RoomDB APIs

Design Pattern: MVVM design pattern

Features used:

Navigation Component:

The Navigation Architecture component is a part of the new AndroidX package that’s introduced since Android SDK 28.

This component consists of new guidelines to structure your application, especially navigation between Fragments.

RecyclerView

Extension Functions

Screen shots: