SQLite is a popular relational database management system (RDBMS) that, unlike many RDBMS systems, is not a client-server database engine. Instead, a SQLite database is embedded directly into an application.
Android provides full support for SQLite. SQLite databases are accessible throughout an Android project by classes. Note that in Android, a database is only accessible to the application that created it.
The use of the Room persistence library is the recommended method of working with SQLite in Android. The first step to work with room in Android is the inclusion of its necessary dependencies in a project's build.gradle script:
implementation "android.arch.persistence.room:runtime:1.0.0-alpha9-1"
implementation "android.arch.persistence.room:rxjava2:1.0.0-alpha9-1"
implementation "io.reactivex.rxjava2:rxandroid:2.0.1"
kapt "android.arch.persistence.room:compiler:1.0.0-alpha9-1"
Entities can easily be created with the help of Room. All entities must be annotated with @Entity. The following is a simple User entity:
package com.example.roomexample.data
import android.arch.persistence.room.ColumnInfo
import android.arch.persistence.room.Entity
import android.arch.persistence.room.PrimaryKey
@Entity
data class User(
@ColumnInfo(name = "first_name")
var firstName: String = "",
@ColumnInfo(name = "surname")
var surname: String = "",
@ColumnInfo(name = "phone_number")
var phoneNumber: String = "",
@PrimaryKey(autoGenerate = true)
var id: Long = 0
)
Room will create the necessary SQLite table for the User entity defined. The table will have a name, user, and will have four attributes: id, first_name, surname, and phone_number. The id attribute is the primary of the user table created. We specified that this should be the case by using the @PrimaryKey annotation. We specified that the primary keys of each record in the user table should be generated by Room, by setting autoGenerate = true in the @PrimaryKey annotation. @ColumnInfo is an annotation used to specify additional information pertaining to a column in a table. Take the following code snippet, for example:
@ColumnInfo(name = "first_name")
var firstName: String = ""
The preceding code specifies that there is a firstName attribute possessed by a User. @ColumnInfo(name ="first_name") sets the name of the column in the user table for the firstName attribute to first_name.
In order to read and write records to and from a database, you need a Data Access Object (DAO). A DAO allows the performance of database operations with the use of annotated methods. The following is a DAO for the User entity:
package com.example.roomexample.data
import android.arch.persistence.room.Dao
import android.arch.persistence.room.Insert
import android.arch.persistence.room.OnConflictStrategy
import android.arch.persistence.room.Query
import io.reactivex.Flowable
@Dao
interface UserDao {
@Query("SELECT * FROM user")
fun all(): Flowable<List<User>>
@Query("SELECT * FROM user WHERE id = :id")
fun findById(id: Long): Flowable<User>
@Insert(onConflict = OnConflictStrategy.REPLACE)
fun insert(user: User)
}
The @Query annotation marks a method in a DAO class as a query method. The query that will be run when the method is invoked is passed as a value to the annotation. Naturally, the queries passed to @Query are SQL queries. The writing of SQL queries is far too vast a topic to cover here, but it is a good idea to take some time to understand how to write them properly.
The @Insert annotation is used to insert data into a table. Other important annotations that exist are @Update and @Delete. They are used to update and delete data within a database table.
Lastly, after creating the necessary entities and DAOs, you must define your application's database. In order to do this, you must create a subclass of RoomDatabase and annotate it with @Database. At the minimum, the annotation should include a collection of entity class references and a database version number. The following is a sample AppDatabase abstract class:
@Database(entities = [User::class], version = 1)
public abstract class AppDatabase : RoomDatabase() {
abstract fun userDao(): UserDao
}Now that we have our DAO and entity created, we must create an AppDatabase class. Add
Once your app database class is created, you can get an instance of the database by calling databaseBuilder():
val db = Room.databaseBuilder(<context>, AppDatabase::class.java,
"app-database").build()
Once you have an instance of your RoomDatabase, you can use it to retrieve data access objects that in turn can be used to read, write, update, query, and delete data from a database.
Keeping with the practice we have adopted so far, let's create a simple application that shows how to utilize SQLite with the help of Room in Android. The application we are about to build will let an app user manually input information pertaining to people (users), and view all user information input at a later time.
Create a new Android project with an empty MainActivity set as its launcher activity. Add the following dependencies to your application's build.gradle script:
implementation 'com.android.support:design:26.1.0'
implementation "android.arch.persistence.room:runtime:1.0.0"
implementation "android.arch.persistence.room:rxjava2:1.0.0"
implementation "io.reactivex.rxjava2:rxandroid:2.0.1"
kapt "android.arch.persistence.room:compiler:1.0.0"
In addition, apply the kotlin-kapt standalone plugin to your build.gradle script:
apply plugin: 'kotlin-kapt'
Having added the preceding project dependencies, create a data and a ui package within the project source package. In the ui package, add the MainView, which is as follows:
package com.example.roomexample.ui
interface MainView {
fun bindViews()
fun setupInstances()
}
After adding MainView to the ui package, relocate MainActivity to the ui package as well. Now let's work on the database of our application. As the application is going to be storing user information, we need to create a User entity. Add the following User entity to the data package:
package com.example.roomexample.data
import android.arch.persistence.room.ColumnInfo
import android.arch.persistence.room.Entity
import android.arch.persistence.room.PrimaryKey
@Entity
data class User(
@ColumnInfo(name = "first_name")
var firstName: String = "",
@ColumnInfo(name = "surname")
var surname: String = "",
@ColumnInfo(name = "phone_number")
var phoneNumber: String = "",
@PrimaryKey(autoGenerate = true)
var id: Long = 0
)
Now create a UserDao within the data package:
package com.example.roomexample.data
import android.arch.persistence.room.Dao
import android.arch.persistence.room.Insert
import android.arch.persistence.room.OnConflictStrategy
import android.arch.persistence.room.Query
import io.reactivex.Flowable
@Dao
interface UserDao {
@Query("SELECT * FROM user")
fun all(): Flowable<List<User>>
@Query("SELECT * FROM user WHERE id = :id")
fun findById(id: Long): Flowable<User>
@Insert(onConflict = OnConflictStrategy.REPLACE)
fun insert(user: User)
}
The UserDao interface has three methods: all(), findById(), and Insert(). The all() method returns a Flowable containing a list of all users. The findById() method finds a User who has an id matching that which is passed to the method, if any, and returns the User in a Flowable. The insert() method is used to insert a user as a record into the user table.
Now that we have our DAO and entity created, we must create an AppDatabase class. Add the following to the data package:
package com.example.roomexample.data
import android.arch.persistence.room.Database
import android.arch.persistence.room.Room
import android.arch.persistence.room.RoomDatabase
import android.content.Context
@Database(entities = arrayOf(User::class), version = 1, exportSchema = false)
internal abstract class AppDatabase : RoomDatabase() {
abstract fun userDao(): UserDao
companion object Factory {
private var appDatabase: AppDatabase? = null
fun create(ctx: Context): AppDatabase {
if (appDatabase == null) {
appDatabase = Room.databaseBuilder(ctx.applicationContext,
AppDatabase::class.java,
"app-database").build()
}
return appDatabase as AppDatabase
}
}
}
We created a Factory companion object possessing a single create() function that has the sole job of creating an AppDatabase instance—if not previously created—and returning that instance for use.
Creating an AppDatabase is the last thing we need to do pertaining to data. Now we must create suitable layouts for our application views. We will make use of two fragments in our MainActivity. The first will be used to collect input for a new user to be created and the second will display the information of all created users in a RecyclerView. Firstly, modify the activity_main.xml layout to contain the following:
<?xml version="1.0" encoding="utf-8"?>
<android.support.constraint.ConstraintLayout
xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
tools:context="com.example.roomexample.ui.MainActivity">
<LinearLayout
android:id="@+id/ll_container"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical"/>
</android.support.constraint.ConstraintLayout>
The LinearLayout in activity_main.xml will contain the fragments of MainActivity. Add a fragment_create_user.xml file to the resource layout directory with the following content:
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:orientation="vertical"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:gravity="center_horizontal"
android:padding="@dimen/padding_default">
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:textSize="32sp"
android:text="@string/create_user"/>
<EditText
android:id="@+id/et_first_name"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginTop="@dimen/margin_default"
android:hint="@string/first_name"
android:inputType="text"/>
<EditText
android:id="@+id/et_surname"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginTop="@dimen/margin_default"
android:hint="@string/surname"
android:inputType="text"/>
<EditText
android:id="@+id/et_phone_number"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginTop="@dimen/margin_default"
android:hint="@string/phone_number"
android:inputType="phone"/>
<Button
android:id="@+id/btn_submit"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginTop="@dimen/margin_default"
android:text="@string/submit"/>
<Button
android:id="@+id/btn_view_users"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginTop="@dimen/margin_default"
android:text="@string/view_users"/>
</LinearLayout>
Now add a fragment_list_users.xml layout resource with the following content:
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:orientation="vertical"
android:layout_width="match_parent"
android:layout_height="match_parent">
<android.support.v7.widget.RecyclerView
android:id="@+id/rv_users"
android:layout_width="match_parent"
android:layout_height="match_parent"/>
</LinearLayout>
The fragment_list_users.xml file has a RecyclerView that will display the information of each user saved to the database. We must create a view holder layout resource item for this RecyclerView. We'll call this layout file vh_user.xml. Create a new vh_user.xml resource file and add the following content to it:
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:orientation="vertical"
android:layout_width="match_parent"
android:padding="@dimen/padding_default"
android:layout_height="wrap_content">
<TextView
android:id="@+id/tv_first_name"
android:layout_width="wrap_content"
android:layout_height="wrap_content"/>
<TextView
android:id="@+id/tv_surname"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_marginTop="@dimen/margin_default"/>
<TextView
android:id="@+id/tv_phone_number"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_marginTop="@dimen/margin_default"/>
<View
android:layout_width="match_parent"
android:layout_height="1dp"
android:layout_marginTop="@dimen/margin_default"
android:background="#e8e8e8"/>
</LinearLayout>
As you might have expected, we must add some string and dimension resources to our project. Open your application's strings.xml layout file and add the following string resources to it:
<resources>
...
<string name="first_name">First name</string>
<string name="surname">Surname</string>
<string name="phone_number">Phone number</string>
<string name="submit">Submit</string>
<string name="create_user">Create User</string>
<string name="view_users">View users</string>
</resources>
Now create the following dimension resources in your project:
<?xml version="1.0" encoding="utf-8"?>
<resources>
<dimen name="padding_default">16dp</dimen>
<dimen name="margin_default">16dp</dimen>
</resources>
It is now time to work on MainActivity. As we previously established, we will be using two distinct fragments in our MainActivity class. The first fragment enables a person to save the data of an individual to a SQL database, and the next will allow a user to view the information of people that has been saved in the database.
We will create a CreateUserFragment first. Add the following fragment class to MainActivity (located in the MainActivity.kt) .
class CreateUserFragment : Fragment(), MainView, View.OnClickListener {
private lateinit var btnSubmit: Button
private lateinit var etSurname: EditText
private lateinit var btnViewUsers: Button
private lateinit var layout: LinearLayout
private lateinit var etFirstName: EditText
private lateinit var etPhoneNumber: EditText
private lateinit var userDao: UserDao
private lateinit var appDatabase: AppDatabase
override fun onCreateView(inflater: LayoutInflater,
container: ViewGroup?, savedInstanceState: Bundle?): View {
layout = inflater.inflate(R.layout.fragment_create_user,
container, false) as LinearLayout
bindViews()
setupInstances()
return layout
}
override fun bindViews() {
btnSubmit = layout.findViewById(R.id.btn_submit)
btnViewUsers = layout.findViewById(R.id.btn_view_users)
etSurname = layout.findViewById(R.id.et_surname)
etFirstName = layout.findViewById(R.id.et_first_name)
etPhoneNumber = layout.findViewById(R.id.et_phone_number)
btnSubmit.setOnClickListener(this)
btnViewUsers.setOnClickListener(this)
}
override fun setupInstances() {
appDatabase = AppDatabase.create(activity)
// getting an instance of AppDatabase
userDao = appDatabase.userDao() // getting an instance of UserDao
}
The following method validates the inputs submitted in the create a user form:
private fun inputsValid(): Boolean {
var inputValid = true
val firstName = etFirstName.text
val surname = etSurname.text
val phoneNumber = etPhoneNumber.text
if (TextUtils.isEmpty(firstName)) {
etFirstName.error = "First name cannot be empty"
etFirstName.requestFocus()
inputValid = false
} else if (TextUtils.isEmpty(surname)) {
etSurname.error = "Surname cannot be empty"
etSurname.requestFocus()
inputValid = false
} else if (TextUtils.isEmpty(phoneNumber)) {
etPhoneNumber.error = "Phone number cannot be empty"
etPhoneNumber.requestFocus()
inputValid = false
} else if (!android.util.Patterns.PHONE
.matcher(phoneNumber).matches()) {
etPhoneNumber.error = "Valid phone number required"
etPhoneNumber.requestFocus()
inputValid = false
}
return inputValid
}
The following function shows toast message indicating the user successfully created:
private fun showCreationSuccess() {
Toast.makeText(activity, "User successfully created.",
Toast.LENGTH_LONG).show()
}
override fun onClick(view: View?) {
val id = view?.id
if (id == R.id.btn_submit) {
if (inputsValid()) {
val user = User(
etFirstName.text.toString(),
etSurname.text.toString(),
etPhoneNumber.text.toString())
Observable.just(userDao)
.subscribeOn(Schedulers.io())
.subscribe( { dao ->
dao.insert(user) // using UserDao to save user to database.
activity?.runOnUiThread { showCreationSuccess() }
}, Throwable::printStackTrace)
}
} else if (id == R.id.btn_view_users) {
val mainActivity = activity as MainActivity
mainActivity.navigateToList()
mainActivity.showHomeButton()
}
}
}
We have worked with fragments numerous times already. As such, our focus of explanation will be on the parts of this fragment that work with the AppDatabase. In setupInstances(), we set up references to the AppDatabase and UserDao. We retrieved an instance of the AppDatabase by invoking the create() function of the Factory companion object of the AppDatabase. An instance of UserDao was easily retrieved by calling the appDatabase.userDao().
Let's move on to the onClick() method of the fragment class. When the submit button is clicked, the submitted user information will be checked for validity. An appropriate error message is shown if any of the input is invalid. Once it is asserted that all input are valid, a new User object containing the submitted user information is created and saved to the database. This is done in the following lines:
if (inputsValid()) {
val user = User(
etFirstName.text.toString(),
etSurname.text.toString(),
etPhoneNumber.text.toString())
Observable.just(userDao)
.subscribeOn(Schedulers.io())
.subscribe( { dao ->
dao.insert(user) // using UserDao to save user to database.
activity?.runOnUiThread { showCreationSuccess() }
}, Throwable::printStackTrace)
}
Creating the ListUsersFragment is similarly easy to achieve. Add the following ListUsersFragment to MainActivity:
class ListUsersFragment : Fragment(), MainView {
private lateinit var layout: LinearLayout
private lateinit var rvUsers: RecyclerView
private lateinit var appDatabase: AppDatabase
override fun onCreateView(inflater: LayoutInflater,
container: ViewGroup?, savedInstanceState: Bundle?): View {
layout = inflater.inflate(R.layout.fragment_list_users,
container, false) as LinearLayout
bindViews()
setupInstances()
return layout
}
Bind the user recycler view instance to its layout element:
override fun bindViews() {
rvUsers = layout.findViewById(R.id.rv_users)
}
override fun setupInstances() {
appDatabase = AppDatabase.create(activity)
rvUsers.layoutManager = LinearLayoutManager(activity)
rvUsers.adapter = UsersAdapter(appDatabase)
}
private class UsersAdapter(appDatabase: AppDatabase) :
RecyclerView.Adapter<UsersAdapter.ViewHolder>() {
private val users: ArrayList<User> = ArrayList()
private val userDao: UserDao = appDatabase.userDao()
init {
populateUsers()
}
override fun onCreateViewHolder(parent: ViewGroup?, viewType: Int):
ViewHolder {
val layout = LayoutInflater.from(parent?.context)
.inflate(R.layout.vh_user, parent, false)
return ViewHolder(layout)
}
override fun onBindViewHolder(holder: ViewHolder?, position: Int) {
val layout = holder?.itemView
val user = users[position]
val tvFirstName = layout?.findViewById<TextView>(R.id.tv_first_name)
val tvSurname = layout?.findViewById<TextView>(R.id.tv_surname)
val tvPhoneNumber = layout?.findViewById<TextView>
(R.id.tv_phone_number)
tvFirstName?.text = "First name: ${user.firstName}"
tvSurname?.text = "Surname: ${user.surname}"
tvPhoneNumber?.text = "Phone number: ${user.phoneNumber}"
}
//Populates users ArrayList with User objects
private fun populateUsers() {
users.clear()
Let's get all users in the user table of the database. And upon successful retrieval of the list, add all user objects in the list to users ArrayList:
userDao.all()
.subscribeOn(Schedulers.io())
.observeOn(AndroidSchedulers.mainThread())
.subscribe({ res ->
users.addAll(res)
notifyDataSetChanged()
}, Throwable::printStackTrace)
}
override fun getItemCount(): Int {
return users.size
}
class ViewHolder(itemView: View) : RecyclerView.ViewHolder(itemView)
}
}
UsersAdapter in the ListUsersFragment uses an instance of UserDao to populate its user list. This population is done within populateUsers(). When populateUsers() is invoked, a list of all users that have been saved by the application is retrieved by invoking userDao.all(). Upon successful retrieval of all users, all User objects are added to the users ArrayList of UserAdapter. The adapter is then notified of the change in data in its dataset by the call to notifyDataSetChanged().
MainActivity itself requires some minor additions. Your completed MainActivity should look like this:
package com.example.roomexample.ui
import android.app.Fragment
import android.support.v7.app.AppCompatActivity
import android.os.Bundle
import android.support.v7.widget.LinearLayoutManager
import android.support.v7.widget.RecyclerView
import android.text.TextUtils
import android.view.LayoutInflater
import android.view.MenuItem
import android.view.View
import android.view.ViewGroup
import android.widget.*
import com.example.roomexample.R
import com.example.roomexample.data.AppDatabase
import com.example.roomexample.data.User
import com.example.roomexample.data.UserDao
import io.reactivex.Observable
import io.reactivex.android.schedulers.AndroidSchedulers
import io.reactivex.schedulers.Schedulers
class MainActivity : AppCompatActivity() {
override fun onCreate(savedInstanceState: Bundle?) {
super.onCreate(savedInstanceState)
setContentView(R.layout.activity_main)
navigateToForm()
}
private fun showHomeButton() {
supportActionBar?.setDisplayHomeAsUpEnabled(true)
}
private fun hideHomeButton() {
supportActionBar?.setDisplayHomeAsUpEnabled(false)
}
private fun navigateToForm() {
val transaction = fragmentManager.beginTransaction()
transaction.add(R.id.ll_container, CreateUserFragment())
transaction.commit()
}
The following function is called when the user click the back button, if the fragments back stack has one or more fragments, the fragments manager pops the fragment and displays it to the user:
override fun onBackPressed() {
if (fragmentManager.backStackEntryCount > 0) {
fragmentManager.popBackStack()
hideHomeButton()
} else {
super.onBackPressed()
}
}
private fun navigateToList() {
val transaction = fragmentManager.beginTransaction()
transaction.replace(R.id.ll_container, ListUsersFragment())
transaction.addToBackStack(null)
transaction.commit()
}
override fun onOptionsItemSelected(item: MenuItem?): Boolean {
val id = item?.itemId
if (id == android.R.id.home) {
onBackPressed()
hideHomeButton()
}
return super.onOptionsItemSelected(item)
}
class CreateUserFragment : Fragment(), MainView, View.OnClickListener {
...
}
class ListUsersFragment : Fragment(), MainView {
...
}
}
We must now run the application to see if it works as we would like. Build and run the project on a device of your choice. Once the project launches, you will come face to face with the user creation form. Go ahead and input some user information in the form:
Once you have input valid information into the create user form, click the Submit button to save the user to the application's SQLite database. You will be notified once the user has been saved successfully. Having been notified, click VIEW USERS to see the information of the user you just saved:
You can create and view information for as many users as you like. There's no upper limit to the amount of information the database can contain!