In this article, you will learn about the Android SQLite Database in-depth with a good example. Using android SQLite database I will show you how to perform all the crud operations like Create, Insert, Update, and Delete.

Android SQLite Database

SQLite database is an opensource, lightweight, no network access, and standalone SQL database that stores data to a text file on a device. SQLite database supports all the relational database features. SQLite is a build-in database implementation that comes with Android. You don’t need to establish any connections like JDBC, ODBC, or any other external connection for it, like what you will need to do in java applications.

In Android, whenever an application needs to store a large amount of data the SQLite is the most preferred.  It is better than any other repository systems like SharedPreferences or saving data in files. For many applications in Android, SQLite is the backbone of the app, whether it’s used directly or via some third-party wrapper.

SQLite Database Package

To use the SQLite database in the Android application, the main package will be android.database.sqlite.

Three main components are used to handle SQLite operations in Android:

SQLiteOpenHelper Class

The SQLiteOpenHelper class is used to determine the name and version of the database used in this class.  It is responsible for opening the database if it exists, creating if it does not exists and upgrading if required.

There are the onCreate() and onUpgrade() methods. 

onCreate() invoked if the database doesn’t exist, meaning the very first time dealing with the SQLite database. Moreover, the onUpgrade() method is used to update the database schema to the most recent or, let’s say, the existing without losing the data:

SQLiteOpenHelper class have getReadableDatabase() and getWritableDatabase() method in order to access to the object SQLite Database, either in read or write mode.

SQLiteDatabase

The base class of the SQLite database class is SQLiteDatabase.

Android has its implementation to perform CRUD (Create, Read, Update, Delete) operations. 

The SQLiteDatabase have methods such as insert(), update(), delete() and also execSQL() which are used for the execution of the database.

For using the methods insert() and update(), you need to define the object ContentValues in terms of KEY and VALUE to the table. The key represents the identity column of the table. And the value represents the record of the table of the column.

The query can be built in the SQLite database with the methods such as rawQuery() and query() using the SQLiteQueryBuilder class. The rawQuery() method accepts an SQL statement as input.

The query() method has a structured interface in terms of parameters to specify the SQL query. The SQLiteQueryBuilder class allows you to create SQL queries.

Example of Query()

Example of rawQuery()

Cursor

The query function will return the cursor object, which results from a query. One record is equal to the row of the query results.

In Android, the cursor can perform buffer query results efficiently as it does not need to load the data into the memory.

The most commonly used methods of the cursor are getCount(), moveToFirst(), moveToNext(). The getCount() used to get the number of records from the query results. The moveToFirst() used to move to the first record of the line. And the moveToNext() used to move to the next line.

For more details, you can have a look at the official documentation of Android SQLiteDatabase.

Step by step guide to build a simple Android application using Android SQLite Database

Create a New Project

Create a new Android Studio project using Kotlin:

Select the option as Start a new Android Studio project:

Select Basic Activity and click on Next:

Name the project as you want and update the package name. I have named the project asSQLiteDemo and also updated the package name. Also, select the project location where you want to save the project.

Create a project using Kotlin.

In this demo project, I will show you how to add the records to the database. I will show how to insert the employee name and email ID. Update, delete or display them on the list.

Now, before moving further in this project, I will use the Android RecyclerView to display the inserted data in the list view format. I assume that you are familiar with the Android RecyclerView concept. If you are not, then do not worry. You can check out my other article Android RecyclerView in Kotlin, where you learn about recycler view in Android using Kotlin. 

Let’s start with the designing of the application.

  1. Go to the activity_main.xml file located in the layout folder in resources. Remove the FloatingActionButton component.
  2. Go to MainActivity.kt and remove the click event of the fab button.
  3. Go to content_main.xml and remove the fragment tag. I am asking you to do this because we are not going to use these components to avoid the confusion of an unnecessary piece of code.
Add the design code of the main activity in the content_main.xml:

In the content_main.xml file, I used the list of components as LinearLayout, TextView, EditText, Button, and RecyclerView to design the UI.

Create a DatabaseHandler.kt class

Add the below code of the file:

In the above class, the SQLiteOpenHelper is extended, and based on that the methods are auto-implemented. You need to name the database and also need to define the database version to it.


After naming the database and defining the version, I have declared the table name as “EmployeeTable” and added the columns. I have created a constant private variable, so to speak while using them to create the table.


I have written a code to create the table in the onCreate method. Here the table is created for the very first time while dealing with SQLite in the application. The onUpgrade method is used to update the database schema the most recent. If we want to add the fourth column without deleting the database, we can use this method.

Create a Data Model class

Create a data model class as EmpModelClass.kt. Add the below code in it:

The data model class is used to deal with database and RecyclerView in our example. Instead of passing all the values back and forth we can assign them to the data model class and pass the data model class to operate.

Insert record in the database

Add the method to insert the employee details in the database DatabaseHandler.kt class:

Create a method to Add the record in the MainActivity.kt class:

In the above code, the employee name and email id is captured and the details inserted into the database. Also, we have validated the input as it should not be empty.

Assign the click event to the add record button

Assign the click event to add a record and call the add record method when you click on it:

Output after inserting the employee record

You can see in the screenshot above that the toast message appeared as Record Saved. That means our entry is inserted successfully in the database.

Create a Custom Layout file for RecyclerView

Add the custom layout file for RecyclerView as item_row.xml in the resource layout:

The images used in the ic_action_edit and ic_action_delete can be replaced by whatever you want.

Create an Item Adapter Class

Create an Item Adapter class named ItemAdapter.kt.

Create the view holder for view items, connect the data source of the RecyclerView and handle the view logic by creating a RecyclerView Adapter:

We used the custom layout file to bind the views to the RecyclerView. In the onCreateViewHolder function, we have inflated it.


In the onBindViewHolder function we have set the text to the TextView from the data list. Also, we have added the different background colors to the CardView based on the odd and even positions.

Add the colors such as colorLightGray and white in the colors.xml.

Get the inserted records from the database in form of Array List

Create a private method in DatabaseHandler.kt class as below:

Create a method in MainActivity.kt to get the list of records using the above code:

Now, as we have everything that is required, I mean the list of records and the custom layout for RecyclerView, let’s bind the view to the UI.

Attach the adapter to the RecyclerView

Create a method in the MainActivity.kt to attach the recylerview:

The LayoutManager used here is LinearLayoutManager for the RecyclerView.

Adapter class is initialized and the data list is passed to it.

Finally, the adapter class is attached to the RecyclerView.

Now call the method on the required places in the MainActivity.kt class. First, in the onCreate method and in the add record method when the record is inserted successfully.

Run the app and check the output.

Output to display the list of inserted record in the database:

In the above screenshot, you can see that the record inserted above is displayed in the list format.

Now we will perform the update operation.

Update the record

Create a private method in DatabaseHandler.kt class as below:

Above method is used to update the record in the database using update query based on the Id.

Custom layout design for update dialog

Now, I will show you how to use the custom dialog to update the records.

If you are worried about how we have created custom dialog you can check out my custom dialog article Android Custom Progress Bar and Alert Dialogs.

Create a custom layout design for update dialog as dialog_update.xml:

Here, I am going to use the custom dialog to update the record. The above file will be used to inflate the view in the custom dialog.

Before creating a method let’s add the design theme for the dialog.

Add the below line of code to your styles.xml in the values folder:

Now, create a private method in MainActivity.kt class as below to update the record.

In the above piece of code, I have used the updated record code of the database handler class and layout file to inflate the view in the dialog.

Now call the update dialog method by clicking the edit button from the list.

Assign, the click event to the edit icon in the ItemAdapter.kt class. Add the below in piece of code in the onBindViewHolder method of ItemAdapter.kt class:

Now, run the application and try to update any record and see how it works.

Output of the update record:

Now, you can see that the records are updating.

Delete the record

Create a private method in DatabaseHandler.kt class as below:

The above code is used to delete the record from the database using the delete query with the help of Id.

 

Now, create a private method in MainActivity.kt class as below to delete the record:

 

Call the delete alert dialog method by clicking on the delete button from the list.

Assign the click event to the delete icon in the ItemAdapter.kt class. Add the below piece of code in the onBindViewHolder method of ItemAdapter.kt class.

Now, run the application and try to delete any record and see it works.

Output of the delete record:

Final code of MainActivity.kt class

Above is the final code of MainActivity.kt class.

Final code of ItemAdapter.kt class: 

Above is the final code of ItemAdapter.kt class.

Summary

In this article, you learned about the Android SQLite database with different CRUD operations like Insert, Select, Update and Delete.