CRUD Operations In Android SQLite - Kotlin

Kotlin

In this article, we will learn how to perform CRUD (Create Read Update Delete) operations SQLite using Kotlin, the official first-class programming language for Android development. It is very easy and similar to implement like Java. We will learn SQLite implementation by building a simple TODO Application.

SQLite

 SQLite is an open source database based SQL Language. It is widely used and Android has it by default to store data locally.

Steps

I have split this article into four steps as follows.

Step 1 - Creating a new Android Project with Kotlin in Android Studio.

Step 2 - Adding User Interface in your layout file.

Step 3 - Creating Database and tables using Kotlin.

Step 4: Implementation of the CRUD Operations in Android applications.

Step 1 - Creating a New Android Project with Kotlin in Android Studio

By default, Android Studio 3.0 has the checkbox for Kotlin Support for your Android Application. Create a new project in Android Studio, check the Kotlin support and start as usual with Android Studio 3.0. 

Kotlin

 

For migrating Java Android Project to Kotlin Android Project, you can do the following processes.

Configuring Kotlin

Kotlin support can be configured by selecting Tools - Kotlin - Configure Kotlin. Then, Click “Sync Now”. This step is applicable to the Android Studio pre-versions of 3.0. The best way is you must update your Android Studio.

In Android Studio 3.0, by default, you have Kotlin Activity. For those who have Android Studio with Version less than 3.0, they can convert their Java activity into Kotlin Activity.

Open Quick Search or Click Ctrl + Shift + A for Windows Users and Search and Select “Convert Java to Kotlin” or simply Select Ctrl + Shift + Alt + K. 

Step 2 - Adding User Interface in your Layout file

In this step, we will add the user interfaces for the TODO application. The application has the following screens.

  1. Tasks List Screen
    • List Item Layout for Custom Adapter
  2. Add Task Screen
  3. Update/Delete Screen
Tasks List Screen

I have used RecyclerView to lists the tasks saved in SQLite DB. To know how to use RecyclerView implementation with Kotlin click here. Create a layout file named as per your wish and paste the following.

  1. <?xml version="1.0" encoding="utf-8"?>  
  2. <android.support.constraint.ConstraintLayout xmlns:android="http://schemas.android.com/apk/res/android"  
  3.     xmlns:app="http://schemas.android.com/apk/res-auto"  
  4.     xmlns:tools="http://schemas.android.com/tools"  
  5.     android:layout_width="match_parent"  
  6.     android:layout_height="match_parent"  
  7.     app:layout_behavior="@string/appbar_scrolling_view_behavior"  
  8.     tools:context="com.androidmads.kotlinsqlite.MainActivity"  
  9.     tools:showIn="@layout/activity_main">  
  10.   
  11.     <android.support.v7.widget.RecyclerView  
  12.         android:layout_width="match_parent"  
  13.         android:layout_height="match_parent"  
  14.         android:id="@+id/recycler_view"  
  15.         app:layout_constraintBottom_toBottomOf="parent"  
  16.         app:layout_constraintLeft_toLeftOf="parent"  
  17.         app:layout_constraintRight_toRightOf="parent"  
  18.         app:layout_constraintTop_toTopOf="parent"/>  
  19.   
  20. </android.support.constraint.ConstraintLayout>  

Then, create a list item for your RecyclerView. Create a Layout file and add the following lines.

  1. <?xml version="1.0" encoding="utf-8"?>  
  2. <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"  
  3.     xmlns:tools="http://schemas.android.com/tools"  
  4.     android:layout_width="match_parent"  
  5.     android:layout_height="wrap_content"  
  6.     android:background="@color/colorAccent"  
  7.     android:orientation="vertical">  
  8.   
  9.     <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"  
  10.         android:layout_width="match_parent"  
  11.         android:layout_height="wrap_content"  
  12.         android:layout_marginLeft="5dp"  
  13.         android:background="@android:color/white"  
  14.         android:orientation="vertical">  
  15.   
  16.         <TextView  
  17.             android:id="@+id/tvName"  
  18.             android:fontFamily="sans-serif-smallcaps"  
  19.             android:layout_width="match_parent"  
  20.             android:layout_height="wrap_content"  
  21.             android:paddingLeft="5dp"  
  22.             android:paddingRight="5dp"  
  23.             android:paddingTop="5dp"  
  24.             android:text="Name"  
  25.             android:textSize="16sp"  
  26.             android:textStyle="bold"  
  27.             tools:targetApi="jelly_bean" />  
  28.   
  29.         <TextView  
  30.             android:id="@+id/tvDesc"  
  31.             android:fontFamily="sans-serif-smallcaps"  
  32.             android:layout_width="match_parent"  
  33.             android:layout_height="wrap_content"  
  34.             android:padding="5dp"  
  35.             android:text="Desc"  
  36.             tools:targetApi="jelly_bean" />  
  37.   
  38.     </LinearLayout>  
  39. </LinearLayout>  
Add or Update Screen

Here, we will design the screens for adding/updating the tasks in your TODO Application. Here, I am used the same screen for both Adding and Updating the tasks. Create a layout file and name it as you wish and add the following files.

  1. <?xml version="1.0" encoding="utf-8"?>  
  2. <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"  
  3.     xmlns:tools="http://schemas.android.com/tools"  
  4.     android:layout_width="match_parent"  
  5.     android:layout_height="match_parent"  
  6.     android:orientation="vertical"  
  7.     android:padding="10dp">  
  8.   
  9.     <android.support.design.widget.TextInputLayout  
  10.         android:layout_width="match_parent"  
  11.         android:layout_height="wrap_content"  
  12.         android:fontFamily="sans-serif-smallcaps"  
  13.         tools:targetApi="jelly_bean">  
  14.   
  15.         <EditText  
  16.             android:id="@+id/input_name"  
  17.             android:layout_width="match_parent"  
  18.             android:layout_height="wrap_content"  
  19.             android:hint="Task Name"  
  20.             android:fontFamily="sans-serif-smallcaps"  
  21.             android:inputType="text"  
  22.             tools:ignore="TextFields"  
  23.             tools:targetApi="jelly_bean" />  
  24.     </android.support.design.widget.TextInputLayout>  
  25.   
  26.     <android.support.design.widget.TextInputLayout  
  27.         android:layout_width="match_parent"  
  28.         android:layout_height="wrap_content"  
  29.         android:fontFamily="sans-serif-smallcaps"  
  30.         tools:targetApi="jelly_bean">  
  31.   
  32.         <EditText  
  33.             android:id="@+id/input_desc"  
  34.             android:layout_width="match_parent"  
  35.             android:layout_height="wrap_content"  
  36.             android:hint="Task Description"  
  37.             android:fontFamily="sans-serif-smallcaps"  
  38.             android:inputType="text"  
  39.             tools:targetApi="jelly_bean"  
  40.             tools:ignore="TextFields" />  
  41.     </android.support.design.widget.TextInputLayout>  
  42.   
  43.     <android.support.v7.widget.SwitchCompat  
  44.         android:id="@+id/swt_completed"  
  45.         android:layout_width="wrap_content"  
  46.         android:layout_height="wrap_content"  
  47.         android:text="Completed  "  
  48.         android:textSize="16sp"  
  49.         android:textColor="@android:color/darker_gray"  
  50.         android:fontFamily="sans-serif-smallcaps"  
  51.         tools:targetApi="jelly_bean" />  
  52.   
  53.     <android.support.v7.widget.AppCompatButton  
  54.         android:id="@+id/btn_save"  
  55.         android:layout_width="fill_parent"  
  56.         android:layout_height="wrap_content"  
  57.         android:padding="12dp"  
  58.         android:text="Save"  
  59.         android:fontFamily="sans-serif-smallcaps"  
  60.         tools:targetApi="jelly_bean" />  
  61.   
  62.     <android.support.v7.widget.AppCompatButton  
  63.         android:id="@+id/btn_delete"  
  64.         android:layout_width="fill_parent"  
  65.         android:layout_height="wrap_content"  
  66.         android:padding="12dp"  
  67.         android:text="Delete"  
  68.         android:fontFamily="sans-serif-smallcaps"  
  69.         tools:targetApi="jelly_bean" />  
  70.   
  71. </LinearLayout>  

In the next step, we will see how to create Database and tables in SQLite.

Step 3 - Creating Database and Tables Using Kotlin

Create a Model class and name it as per your wish and in my case, I have created Tasks.kt class file. Add the following lines.

  1. public class Tasks {  
  2.   
  3.     var id: Int = 0  
  4.     var name: String = ""  
  5.     var desc: String = ""  
  6.     var completed: String = "N"  
  7.   
  8. }  

Database Handler

Create a Kotlin class and name it as DatabaseHandler.kt and inherit with SQLiteOpenHelper. SQLiteOpenHelper is used to create and upgrade the database and tables of your application. The DB is created as in the following and add the following lines to DatabaseHandler.kt.

  1. class DatabaseHandler(context: Context) : SQLiteOpenHelper(context, DatabaseHandler.DB_NAME, null, DatabaseHandler.DB_VERSION) {  
  2.   
  3.     override fun onCreate(db: SQLiteDatabase) {  
  4.         val CREATE_TABLE = "CREATE TABLE $TABLE_NAME (" +  
  5.                 ID + " INTEGER PRIMARY KEY," +  
  6.                 NAME + " TEXT," + DESC + " TEXT," +  
  7.                 COMPLETED + " TEXT);"  
  8.         db.execSQL(CREATE_TABLE)  
  9.     }  
  10.   
  11.     override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {  
  12.         val DROP_TABLE = "DROP TABLE IF EXISTS " + TABLE_NAME  
  13.         db.execSQL(DROP_TABLE)  
  14.         onCreate(db)  
  15.     }  
  16.   
  17.     companion object {  
  18.   
  19.         private val DB_VERSION = 1  
  20.         private val DB_NAME = "MyTasks"  
  21.         private val TABLE_NAME = "Tasks"  
  22.         private val ID = "Id"  
  23.         private val NAME = "Name"  
  24.         private val DESC = "Desc"  
  25.         private val COMPLETED = "Completed"  
  26.     }  
  27. }  
Here, I have created a table Tasks as shown in the code above and this will create and upgrade the Database of your application.

In the next step we will see how to perform CRUD operations on Android using Kotlin.

Step 4 - Implementation of CRUD operations in Android

We will see the implementation of CRUD one by one. Just open your DatabaseHandler.kt and the followings.

  • CREATE, UPDATE, DELETE – should have writing permission.
  • READ – should have readable permission and cursor is used to read data from SQLite.
CREATE/INSERT Data
  1. fun addTask(tasks: Tasks): Boolean {  
  2.     val db = this.writableDatabase  
  3.     val values = ContentValues()  
  4.     values.put(NAME, tasks.name)  
  5.     values.put(DESC, tasks.desc)  
  6.     values.put(COMPLETED, tasks.completed)  
  7.     val _success = db.insert(TABLE_NAME, null, values)  
  8.     db.close()  
  9.     return (Integer.parseInt("$_success") != -1)  

Here, the DB returns the ID of the last inserted data as the output of inserting data.

READ ALL/ONE Data

Cursor is used to read the SQLite database in Android.

  1. fun getTask(_id: Int): Tasks {  
  2.     val tasks = Tasks()  
  3.     val db = writableDatabase  
  4.     val selectQuery = "SELECT  * FROM $TABLE_NAME WHERE $ID = $_id"  
  5.     val cursor = db.rawQuery(selectQuery, null)  
  6.     if (cursor != null) {  
  7.         cursor.moveToFirst()  
  8.         while (cursor.moveToNext()) {  
  9.             tasks.id = Integer.parseInt(cursor.getString(cursor.getColumnIndex(ID)))  
  10.             tasks.name = cursor.getString(cursor.getColumnIndex(NAME))  
  11.             tasks.desc = cursor.getString(cursor.getColumnIndex(DESC))  
  12.             tasks.completed = cursor.getString(cursor.getColumnIndex(COMPLETED))  
  13.         }  
  14.     }  
  15.     cursor.close()  
  16.     return tasks  
  17. }  

Here, the DB returns the data respective to the ID.

  1. val task: List<Tasks>  
  2.     get() {  
  3.         val taskList = ArrayList<Tasks>()  
  4.         val db = writableDatabase  
  5.         val selectQuery = "SELECT  * FROM $TABLE_NAME"  
  6.         val cursor = db.rawQuery(selectQuery, null)  
  7.         if (cursor != null) {  
  8.             cursor.moveToFirst()  
  9.             while (cursor.moveToNext()) {  
  10.                 val tasks = Tasks()  
  11.                 tasks.id = Integer.parseInt(cursor.getString(cursor.getColumnIndex(ID)))  
  12.                 tasks.name = cursor.getString(cursor.getColumnIndex(NAME))  
  13.                 tasks.desc = cursor.getString(cursor.getColumnIndex(DESC))  
  14.                 tasks.completed = cursor.getString(cursor.getColumnIndex(COMPLETED))  
  15.                 taskList.add(tasks)  
  16.             }  
  17.         }  
  18.         cursor.close()  
  19.         return taskList  
  20.     }  

Here, the DB returns all the data. Cursor is used to retrieve the data from SQLite.

UPDATE Data
  1. fun updateTask(tasks: Tasks): Boolean {  
  2.     val db = this.writableDatabase  
  3.     val values = ContentValues()  
  4.     values.put(NAME, tasks.name)  
  5.     values.put(DESC, tasks.desc)  
  6.     values.put(COMPLETED, tasks.completed)  
  7.     val _success = db.update(TABLE_NAME, values, ID + "=?", arrayOf(tasks.id.toString())).toLong()  
  8.     db.close()  
  9.     return Integer.parseInt("$_success") != -1  
  10. }  

Here, the DB returns the ID of the last updated data as the output of update method.

DELETE Data
  1. fun deleteTask(_id: Int): Boolean {  
  2.     val db = this.writableDatabase  
  3.     val _success = db.delete(TABLE_NAME, ID + "=?", arrayOf(_id.toString())).toLong()  
  4.     db.close()  
  5.     return Integer.parseInt("$_success") != -1  
  6. }  

Here, the DB returns the ID of the last deleted data as the output of delete method.

Full Code of DatabaseHandler

  1. class DatabaseHandler(context: Context) : SQLiteOpenHelper(context, DatabaseHandler.DB_NAME, null, DatabaseHandler.DB_VERSION) {  
  2.   
  3.     override fun onCreate(db: SQLiteDatabase) {  
  4.         val CREATE_TABLE = "CREATE TABLE $TABLE_NAME (" +  
  5.                 ID + " INTEGER PRIMARY KEY," +  
  6.                 NAME + " TEXT," + DESC + " TEXT," +  
  7.                 COMPLETED + " TEXT);"  
  8.         db.execSQL(CREATE_TABLE)  
  9.     }  
  10.   
  11.     override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {  
  12.         val DROP_TABLE = "DROP TABLE IF EXISTS " + TABLE_NAME  
  13.         db.execSQL(DROP_TABLE)  
  14.         onCreate(db)  
  15.     }  
  16.   
  17.     fun addTask(tasks: Tasks): Boolean {  
  18.         val db = this.writableDatabase  
  19.         val values = ContentValues()  
  20.         values.put(NAME, tasks.name)  
  21.         values.put(DESC, tasks.desc)  
  22.         values.put(COMPLETED, tasks.completed)  
  23.         val _success = db.insert(TABLE_NAME, null, values)  
  24.         db.close()  
  25.         return (Integer.parseInt("$_success") != -1)  
  26.     }  
  27.   
  28.     fun getTask(_id: Int): Tasks {  
  29.         val tasks = Tasks()  
  30.         val db = writableDatabase  
  31.         val selectQuery = "SELECT  * FROM $TABLE_NAME WHERE $ID = $_id"  
  32.         val cursor = db.rawQuery(selectQuery, null)  
  33.         if (cursor != null) {  
  34.             cursor.moveToFirst()  
  35.             while (cursor.moveToNext()) {  
  36.                 tasks.id = Integer.parseInt(cursor.getString(cursor.getColumnIndex(ID)))  
  37.                 tasks.name = cursor.getString(cursor.getColumnIndex(NAME))  
  38.                 tasks.desc = cursor.getString(cursor.getColumnIndex(DESC))  
  39.                 tasks.completed = cursor.getString(cursor.getColumnIndex(COMPLETED))  
  40.             }  
  41.         }  
  42.         cursor.close()  
  43.         return tasks  
  44.     }  
  45.   
  46.     val task: List<Tasks>  
  47.         get() {  
  48.             val taskList = ArrayList<Tasks>()  
  49.             val db = writableDatabase  
  50.             val selectQuery = "SELECT  * FROM $TABLE_NAME"  
  51.             val cursor = db.rawQuery(selectQuery, null)  
  52.             if (cursor != null) {  
  53.                 cursor.moveToFirst()  
  54.                 while (cursor.moveToNext()) {  
  55.                     val tasks = Tasks()  
  56.                     tasks.id = Integer.parseInt(cursor.getString(cursor.getColumnIndex(ID)))  
  57.                     tasks.name = cursor.getString(cursor.getColumnIndex(NAME))  
  58.                     tasks.desc = cursor.getString(cursor.getColumnIndex(DESC))  
  59.                     tasks.completed = cursor.getString(cursor.getColumnIndex(COMPLETED))  
  60.                     taskList.add(tasks)  
  61.                 }  
  62.             }  
  63.             cursor.close()  
  64.             return taskList  
  65.         }  
  66.   
  67.     fun updateTask(tasks: Tasks): Boolean {  
  68.         val db = this.writableDatabase  
  69.         val values = ContentValues()  
  70.         values.put(NAME, tasks.name)  
  71.         values.put(DESC, tasks.desc)  
  72.         values.put(COMPLETED, tasks.completed)  
  73.         val _success = db.update(TABLE_NAME, values, ID + "=?", arrayOf(tasks.id.toString())).toLong()  
  74.         db.close()  
  75.         return Integer.parseInt("$_success") != -1  
  76.     }  
  77.   
  78.     fun deleteTask(_id: Int): Boolean {  
  79.         val db = this.writableDatabase  
  80.         val _success = db.delete(TABLE_NAME, ID + "=?", arrayOf(_id.toString())).toLong()  
  81.         db.close()  
  82.         return Integer.parseInt("$_success") != -1  
  83.     }  
  84.   
  85.     companion object {  
  86.   
  87.         private val DB_VERSION = 1  
  88.         private val DB_NAME = "MyTasks"  
  89.         private val TABLE_NAME = "Tasks"  
  90.         private val ID = "Id"  
  91.         private val NAME = "Name"  
  92.         private val DESC = "Desc"  
  93.         private val COMPLETED = "Completed"  
  94.     }  
  95. }  

Executing the Operations

The DB and table is automatically created, when we are calling/initializing the DatabaseHandler class, like below.

DatabaseHandler dbHandler = DatabaseHandler(this)

Full Code

You can find the full code of the activities here.

Download Code

You can download the example code from GitHub for SQLite using Kotlin. If this article is useful to you, do like & star the repo on GitHub. Keep on commenting, if you have any doubts.