ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

How to Create And Manage Local Database on Android Effortlessly

Updated on March 20, 2020
Dav Vendator profile image

Davinder Singh is a Polymath. He loves reading, learning and writing technical cookbooks about new technologies.

Room Architecture
Room Architecture | Source

What You Will Learn?

  • You will learn how to do basic database management with sqlite.
  • You will learn how to add Room persistence API in your project
  • You will learn how to implement Room API and perform database operations with it.

Prerequisites

  • A firm understanding of SQL statements.
  • Know-how of working with Android at any level of expertise with Kotlin programming language.
  • A computer with strong Internet connection.

Crash Course in Android Sqlite Database API

Database is crucial part of any application regardless of its size. In android there are two options for storing and retrieving data viz. SharedPreferences and Sqlite Database Management System (DBMS). While trivial data involving key–value pairs only, upto a limited amount of size can be handled with ease through shared-preferences; Data containing complex structures having reasonably large size is only suitably can be handled with sqlite.

Android has native support for sqlite. You can either ship your application with pre-created database or you can ship it with schema(skeleton) of database. This schema can then be used to form tables during runtime.To add sqlite database capabilities into your application all that you will have to do is extend SQLiteOpenHelper class(resides inside android.database.sqlite package) and overriding following methods: onCreate, onUpgrade.

“In computer programming, boilerplate code or just boilerplate refers to sections of code that have to be included in many places with little or no alteration.”

We will create a simple application to manage database of employees of an organisation by following normal approach i.e. extending SqliteOpenHelper class and then we will have a look at how this same application can be implemented with Room. However, I will not go into details of implementing the GUI of this application instead, I will merely show you how you can implement database capabilities in this app.

Column name
Data type
emp_id
integer(Primary key and Auto-increment)
name
string/text
email
string/text
phone_number
string/text

Create A Model Data Class for Employee

  • Create a new Android project with empty activity.
  • Add a class file to the project. Name it Employee.kt. This will represent the model of an employee. Model class basically describes the entity–Employee in this case.

//Employee.kt
data class Employee(
    val emp_id: Int,
    val name: String,
    val email: String,
    val number: String
)

Create SqlHelper Class

Add another class file to the project, this class will extend SQLiteOpenHelper and provide us access to create, manage, manipulate and query database.

import android.content.ContentValues
import android.content.Context
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper


class SqlHelper(context: Context) : SQLiteOpenHelper(
    context, DB_NAME,
    null, 1
) {

    companion object {
        private const val EMPLOYEE_TABLE = "employee_table"
        private const val DB_NAME = "employee_db"
        //--TABLE COLUMN NAMES
        private const val EMP_ID = "emp_id"
        private const val EMP_NAME = "name"
        private const val EMP_EMAIL = "email"
        private const val EMP_NUMBER = "phone_number"
    }

    override fun onCreate(db: SQLiteDatabase?) {
        db!!.execSQL(
            "CREATE TABLE $EMPLOYEE_TABLE " +
                    "($EMP_ID integer primary key AUTOINCREMENT," +
                    "$EMP_NAME text," +
                    "$EMP_EMAIL text," +
                    "$EMP_NUMBER text)"
        )
    }

    override fun onUpgrade(db: SQLiteDatabase?, oldVersion: Int, newVersion: Int) {
        db!!.execSQL("DROP TABLE IF EXISTS $EMPLOYEE_TABLE")
        onCreate(db)
    }

    fun insertEmployee(employee: Employee): Long {
        val contentValue = ContentValues()
        contentValue.put(EMP_NAME, employee.name)
        contentValue.put(EMP_NUMBER, employee.number)
        contentValue.put(EMP_EMAIL, employee.email)
        return writableDatabase.insert(EMPLOYEE_TABLE, null, contentValue)
    }

    fun deleteRow(employee: Employee): Int {
        return writableDatabase.delete(EMPLOYEE_TABLE, "$EMP_EMAIL = ?", arrayOf(employee.email))
    }

    fun getAllEmployess(): List<Employee> {
        val cursor = readableDatabase.rawQuery("SELECT * FROM $EMPLOYEE_TABLE", null)
        val list = ArrayList<Employee>()
        cursor.use{ cur ->
            cur.moveToFirst()
            with(cur) {
            while (!isAfterLast) {
                list.add(
                    Employee(
                        getString(getColumnIndex(EMP_ID)),
                        getString(getColumnIndex(EMP_NAME)),
                        getString(getColumnIndex(EMP_EMAIL)),
                        getString(getColumnIndex(EMP_NUMBER))
                    )
                )
             }
           }
        }
        return list
    }
}

Performing Database Operations

Now in the final step, We can perform operations on database with the help of an instance of SqlHelper class.

class MainActivity : AppCompatActivity() {
    private lateinit var dbHelper: SqlHelper

    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)
        dbHelper = SqlHelper(this)
        insert()
        show()
    }

    private fun insert() {
        dbHelper.insertEmployee(Employee("DV", "erudtio@gmail.com", "1234455"))
        dbHelper.insertEmployee(Employee("DV2", "erudti@gmail.com", "1234455"))
        dbHelper.insertEmployee(Employee("DV3", "erudi@gmail.com", "1234455"))
    }

    private fun show() {
        dbHelper.getAllEmployess().forEachIndexed { index, it ->
            Log.e("Employe: $index", "Name: ${it.name}, Email: ${it.email}, Number: ${it.number}")
        }
    }
}

Problems With Native API

There are many problems with this approach because of low-level nature of the API. While they are not quite apparent in our little application; they often turn up in applications of normal size. Following are some of them:

  • Readability of code is very less.
  • Because, there's no compile time checking of SQL queries, finding errors as well as debugging can become headache and In worst cases application might crash during runtime.
  • As there's no requirement forced on programmer for using background thread for executing slow database queries (I/O bound tasks); Performing them on main thread can waste resources and in worst case can hang whole application itself.

Room Persistence API

The point of above discussion was that, Native APIs for working with Sqlite in Android are very low level and hence we require an abstraction layer upon these APIs which provide us with concise, efficient and easy to debug methods for working with sqlite.

Room persistence API is part of Android JetPack components. It is an easy to understand, readable and easy to debug library for working with sqlite. It is a high level layer upon native sqlite APIs.

Basics of Room API

Before we move on to implementing Room API; We first have to understand few basic terminologies:

  • DAO short for Data Access Object. It contains all the methods for accessing database.
  • Entity is class which represents a table (schema). In other sense it's model of database.
  • Database contains glue to hold together DAOs and Entities. Any abstract class which is annotated with “@Database” and extends RoomDatabase class is valid database class. Every database operation is carryout through instance of this class. Which can be created during runtime by calling Room.databaseBuilder() or Room.inMemoryDatabaseBuilder() methods.

Implementing Room API

We will implement same example again (Employee Application) but this time with the help of Room persistence API. Again for GUI you can add two fragments, One for adding content another one for displaying the content.

Application doesn't required to have a sophisticated interface, Add two fragment: AddEmployee fragment and DisplayEmployee fragment.

In AddEmployee fragment:

  • Add three edit texts with labels: Employee Name, Employee Email and Employee Phone number.
  • A single button in the middle, for performing validation of data as well as insertion of it into the table.

In DisplayEmployee fragment:

  • Add a recycler view for displaying employee list.
  • In list item for the recycler view add four textviews horizontally spaced apart. Displaying Employee Id, Name, Email and Phone number.
  • Add a button in the middle for removing all of the data in a single go.

Step 0: Add Room Dependencies

Before using Room, We must first have to add gradle dependency for Room into the project. Add following lines in your module level gradle file

dependencies {
    def room_version = "2.1.0-alpha04"
    //Room
    implementation "androidx.room:room-runtime:$room_version"
    kapt "androidx.room:room-compiler:$room_version"
}

Also at the very beginning of the this file, add following lines which will indicate gradle to apply plugins to the project during the build.

//Kotlin annotation processor
apply plugin: 'kotlin-kapt'

Step 1: Add An Entity Class

Entity class is where we define to Room the data model or skeleton or schema of database. At this point I am assuming that you have Room API included in the project and it (project) is compiling as well as running fine. For sake of showing flexibility of Room, Let add one more field to the employee table: hiring_date.

Column Name
Data type
emp_id
integer(Primary key, Autoincrement)
name
string/text
email
string/text
phone_number
string/text
hire_date
long

Room has annotations to help you describe schema of entity. Following is list of some of them:

  • @Entity: This is class level annotation i.e. it only can be placed above a class. Entity annotation takes following arguments: tableName, array of primary keys and list of indices on table etcetera.
  • @TypeConverters: This is also class level annotation. Argument to this annotations is an array of classes which have methods for mapping between complex koltin/Java types such as Date into simple SQL types. This is doesn't required if your entity class doesn't have any types other than: Number and String types.
  • @PrimaryKey: This a field level annotation meaning that it can only be used to annotate variables. It indicates that the underlying field is a Primary key in the table. You can pass autoGenerate = true to let Room know that this value-type is auto increment-able.
  • @ColumnInfo: Column info annotation is also field type annotation. It is used to annotate fields so as to make them fields in the table. It takes name as argument to specify name of column being represented by this field.

//Employee.kt
@Entity(tableName = “employee_table”)
@TypeConverters(DateConverter::class)
data class Employee(
    @PrimaryKey(autoGenerate = true) var emp_id: Int,
    @ColumnInfo(name = “name”) var name: String,
    @ColumnInfo(name = “email”) var email: String,
    @ColumnInfo(name = “phone_number”) var phoneNumber: String,
    @ColumnInfo(name = “hiring_date”) var hiringDate: Date
)

class DateConverter {
    @TypeConverter
    fun fromTimestamp(value: Long?): Date? {
        return value?.let { Date(it) }
    }

    @TypeConverter
    fun dateToTimestamp(date: Date?): Long? {
        return date?.time?.toLong()
    }
}

Step 2: Add Database Access Object (DAO)

Now that we have our database model ready, We need corresponding DAO interface for performing operations over the model or entity. Database Access Object is basically a middleman between your application's business logic and database.

Inside the DAO interface, all you have to do is add methods for each corresponding query that you want to perform. And best part is they all are auto-generated for you from annotations by annotation processor. Common annotations:

  • @Query is annotation which is used to annotate methods with corresponding SQL queries or raw sql queries.
  • @Insert is used to annotate method which will inturn perform insertion operations.
  • @Delete annotation indicates the methods is to be for deleting entries from the table.

//EmployeeDao.kt
@Dao
interface EmployeeDao {
    @Query("SELECT * FROM employee_table ORDER BY hiring_date ASC")
    fun getAllEmployees(): List<Employee>

    @Insert
    fun insertAll(vararg employees: Employee)

    @Delete
    fun delete(employee: Employee): Int

    @Query("DELETE FROM employee_table WHERE `email`=:email")
    fun deleteAllWith(email: String): Int
}

Step 3: Add Room Database Class

Room database class is an abstract class. Again Room's annotation processor takes care of providing underlying definitions and implementation part. So, All you have to do is annotate the class. Following are the annotations that you can use with database class.

  • @Database annotation is class level annotation. It specifies that the annotated class is a Room database class. Argument to this annotation includes: Array of entities class, version of database and boolean indicating whether to export this schema to disk or not.

@Database(entities = [Employee::class], version = 1, exportSchema = false)
abstract class EmployeeDatabase : RoomDatabase() {
    abstract fun employeeDao(): EmployeeDao
}

Exporting schema means that Room should export the schema into a folder. Defaults to true but only works if you have had the room.schemaLocation variable set.

Version is used for upgrading purposes. In cases when you have updated or changed the schema of underlying database after you already have released the application.

Step 4: Add Database Provider

Database provider is last piece of this whole Room puzzle. Database provider class supplies the database object when required. Make sure that it's a singleton object. We call Room API's databaseBuilder inside this class.

class DatabaseProvider(var context: Context) {
    val db by lazy {
        Room.databaseBuilder(
            context,
            EmployeeDatabase::class.java, "employee-db"
        ).build()
    }
}

Step 5: Inserting Data

Now that we have completed implementing the Room API we are ready to use it to perform database operations. We'll take a look at inserting, deleting and querying.

Note: Room restrict performing database operations on Main thread and hence all database operations must be performed on background thread using either AsyncTask or Bare thread.


class InsertDataFragment : Fragment(){
        //....
        
   fun addCallbacks(){
      btAddEntry.setOnClickListener{
         InsertData(
           WeakReference(
	                   EmployeeDatabaseProvider(context!!)))
                    .execute(Employee(/*EmployeeData*/))
            }
        }

   class InsertData(private val provider: WeakReference<DatabaseProvider>) : AsyncTask<Employee, Void?, Void?>(){
	        override fun doInBackground(vararg params: Employee): Void? {
	            val dao = provider.get()!!.db.employeeDao()
	            dao.insertAll(*params)
	            return  null
	        }
	    }
}

Step 7: Removing Data

Removing data is also as easy as inserting the data. Again we will use AsyncTask for database removal.

class DeleteListAsync(
    val dao: EmployeeDao,
    var adapter: WeakReference<EmployeeListAdapter>
) : AsyncTask<Int, Void?, Void?>() {
    override fun doInBackground(vararg params: Int?): Void? {
        print(adapter.get()?.array?.size)
        adapter.get()?.array
            ?.forEach { employee ->
                println(dao.delete(employee))
            }
        return null
    }

    override fun onPostExecute(result: Void?) {
        super.onPostExecute(result)
        val mAdapter = adapter.get()
        mAdapter!!.array = listOf()
        mAdapter.notifyDataSetChanged()
    }
}

Step 8: Querying Data

And now for the last part we will have a look at how to get list of employees.

class GetListAsync(
        val dao: EmployeeDao,
        var adapter: WeakReference<EmployeeListAdapter>
    ) : AsyncTask<Void, Void, List<Employee>>() {
        override fun doInBackground(): List<Employee> {
            return dao.getAllEmployee()
        }

        override fun onPostExecute(result: List<Employee>?) {
            super.onPostExecute(result)
            val mAdapter = adapter.get()
            mAdapter!!.array = result!!
            mAdapter.notifyDataSetChanged()
        }
    }

Conclusion

Well that's all for this one folk. Room is powerful yet easy to use API. It provides a feature rich and MVVM compatible access to sqlite. You can read more about Room from here. If you ran into any problems or have questions feel free to ask them below in the comment section. As always Thank you for reading.

This content is accurate and true to the best of the author’s knowledge and is not meant to substitute for formal and individualized advice from a qualified professional.

© 2019 Dav Vendator

Comments

    0 of 8192 characters used
    Post Comment

    No comments yet.

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://maven.io/company/pages/privacy

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)
    ClickscoThis is a data management platform studying reader behavior (Privacy Policy)