Kotlin Android SQLite Simple CRUD - INSERT SELECT UPDATE DELETE

| Page Views: 301

Kotlin Android SQLite database simple CRUD example tutorial

This is an SQLite tutorial for android using Kotlin programming language. We see how to perforom operations like inserting, updating, reading and deleting data.

Why This Tutorial

This tutorial is really suitable for newbies who just want to learn how to perform operations against SQLite database. We are seeing how to add data to sqlite from edittexts on button click.

The user also utilizes the same edittexts for updating data. However for this to happen you have to supply the ud. The id isn't necessary while inserting data. This is because it gets auto generated and autoincremented by SQLite datbase itself.

To delete you also have to supply the id. This identifies the row to be deleted.

Demo

Here's the gif demo of the project:

KotlinAndroid SQLite CRUD Example

Video Tutorial(Recommended)

Here's the video tutorial. I recommend you watch the video tutorial as it contains explantions on step by step process of how to create this. We start from scratch by creating a project and I take you through all the steps and code.

Layouts

We have only a single layout:

(a). activitt_main.xml

Our mainactivity's layout. It will contained several edittexts and buttons which will allow user to perform CRUD operations.

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    xmlns:tools="http://schemas.android.com/tools"
    android:orientation="vertical"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    tools:context=".MainActivity">

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal">

        <TextView
            android:id="@+id/nameLabel"
            android:layout_width="0dp"
            android:layout_weight="1"
            android:layout_height="wrap_content"
            android:maxLines="1"
            android:padding="5dp"
            android:text="ID"
            android:textColor="@color/colorAccent"
            android:textAppearance="?android:attr/textAppearanceSmall"
            android:textStyle="bold" />

        <EditText
            android:id="@+id/idTxt"
            android:layout_width="0dp"
            android:layout_weight="2"
            android:layout_height="wrap_content"
            android:ems="10"
            android:inputType="number"
            android:hint="ID" />
    </LinearLayout>

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal">

        <TextView
            android:layout_width="0dp"
            android:layout_weight="1"
            android:layout_height="wrap_content"
            android:maxLines="1"
            android:padding="5dp"
            android:text="NAME: "
            android:textColor="@color/colorAccent"
            android:textAppearance="?android:attr/textAppearanceSmall"
            android:textStyle="bold" />

        <EditText
            android:id="@+id/nameTxt"
            android:layout_width="0dp"
            android:layout_weight="2"
            android:layout_height="wrap_content"
            android:ems="10"
            android:inputType="textPersonName"
            android:hint="NAME" />
    </LinearLayout>

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal">

        <TextView
            android:layout_width="0dp"
            android:layout_weight="1"
            android:layout_height="wrap_content"
            android:maxLines="1"
            android:padding="5dp"
            android:text="GALAXY: "
            android:textColor="@color/colorAccent"
            android:textAppearance="?android:attr/textAppearanceSmall"
            android:textStyle="bold" />

        <EditText
            android:id="@+id/galaxyTxt"
            android:layout_width="0dp"
            android:layout_weight="2"
            android:layout_height="wrap_content"
            android:ems="10"
            android:inputType="textPersonName"
            android:hint="GALAXY" />
    </LinearLayout>

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal">

        <TextView
            android:layout_width="0dp"
            android:layout_weight="1"
            android:layout_height="wrap_content"
            android:maxLines="1"
            android:padding="5dp"
            android:text="TYPE: "
            android:textColor="@color/colorAccent"
            android:textAppearance="?android:attr/textAppearanceSmall"
            android:textStyle="bold" />

        <EditText
            android:id="@+id/typeTxt"
            android:layout_width="0dp"
            android:layout_weight="2"
            android:layout_height="wrap_content"
            android:ems="10"
            android:inputType="textCapWords"
            android:hint="STAR TYPE" />
    </LinearLayout>

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal">

        <Button
            android:id="@+id/insertBtn"
            android:layout_width="0dp"
            android:layout_weight="1"
            android:layout_height="wrap_content"
            android:text="INSERT" />

        <Button
            android:id="@+id/updateBtn"
            android:layout_width="0dp"
            android:layout_weight="1"
            android:layout_height="wrap_content"
            android:text="UPDATE" />
    </LinearLayout>

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal">

        <Button
            android:id="@+id/deleteBtn"
            android:layout_width="0dp"
            android:layout_weight="1"
            android:layout_height="wrap_content"
            android:text="DELETE" />

        <Button
            android:id="@+id/viewBtn"
            android:layout_width="0dp"
            android:layout_weight="1"
            android:layout_height="wrap_content"
            android:text="VIEW ALL" />
    </LinearLayout>

</LinearLayout>

Kotlin Code

We have two classes:

(a). DatabaseHelper.java

This class will allow us work with SQLite easily, allowing us to perform CRUD operations like adding, deleting, updating and deleting data.

package info.camposha.kotlinsqlite

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

/**
 * Let's start by creating our database CRUD helper class
 * based on the SQLiteHelper.
 */
class DatabaseHelper(context: Context) :
        SQLiteOpenHelper(context, DATABASE_NAME, null, 1) {

    /**
     * Our onCreate() method.
     * Called when the database is created for the first time. This is
     * where the creation of tables and the initial population of the tables
     * should happen.
     */
    override fun onCreate(db: SQLiteDatabase) {
        db.execSQL("CREATE TABLE $TABLE_NAME (ID INTEGER PRIMARY KEY " +
                "AUTOINCREMENT,NAME TEXT,GALAXY TEXT,TYPE TEXT)")
    }

    /**
     * Let's create Our onUpgrade method
     * Called when the database needs to be upgraded. The implementation should
     * use this method to drop tables, add tables, or do anything else it needs
     * to upgrade to the new schema version.
     */
    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME)
        onCreate(db)
    }

    /**
     * Let's create our insertData() method.
     * It Will insert data to SQLIte database.
     */
    fun insertData(name: String, surname: String, marks: String) {
        val db = this.writableDatabase
        val contentValues = ContentValues()
        contentValues.put(COL_2, name)
        contentValues.put(COL_3, surname)
        contentValues.put(COL_4, marks)
        db.insert(TABLE_NAME, null, contentValues)
    }

    /**
     * Let's create  a method to update a row with new field values.
     */
    fun updateData(id: String, name: String, surname: String, marks: String):
            Boolean {
        val db = this.writableDatabase
        val contentValues = ContentValues()
        contentValues.put(COL_1, id)
        contentValues.put(COL_2, name)
        contentValues.put(COL_3, surname)
        contentValues.put(COL_4, marks)
        db.update(TABLE_NAME, contentValues, "ID = ?", arrayOf(id))
        return true
    }

    /**
     * Let's create a function to delete a given row based on the id.
     */
    fun deleteData(id : String) : Int {
        val db = this.writableDatabase
        return db.delete(TABLE_NAME,"ID = ?", arrayOf(id))
    }

    /**
     * The below getter property will return a Cursor containing our dataset.
     */
    val allData : Cursor
        get() {
            val db = this.writableDatabase
            val res = db.rawQuery("SELECT * FROM " + TABLE_NAME, null)
            return res
        }

    /**
     * Let's create a companion object to hold our static fields.
     * A Companion object is an object that is common to all instances of a given
     * class.
     */
    companion object {
        val DATABASE_NAME = "stars.db"
        val TABLE_NAME = "star_table"
        val COL_1 = "ID"
        val COL_2 = "NAME"
        val COL_3 = "GALAXY"
        val COL_4 = "TYPE"
    }
}
//end
(b). MainActivity.java

The main activity is the entry point to our Kotlin app. We have cleanly commented the code for inline explanations.

package info.camposha.kotlinsqlite

import android.app.AlertDialog
import android.os.Bundle
import android.support.v7.app.AppCompatActivity
import android.view.View
import android.widget.Toast
import kotlinx.android.synthetic.main.activity_main.*

class MainActivity : AppCompatActivity() {

    //In Kotlin `var` is used to declare a mutable variable. On the other hand
    //`internal` means a variable is visible within a given module.
    internal var dbHelper = DatabaseHelper(this)

    /**
     * Let's create a function to show Toast message
     */
    fun showToast(text: String){
        Toast.makeText([email protected], text, Toast.LENGTH_LONG).show()
    }

    /**
     * Let's create a function to show an alert dialog with data dialog.
     */
    fun showDialog(title : String,Message : String){
        val builder = AlertDialog.Builder(this)
        builder.setCancelable(true)
        builder.setTitle(title)
        builder.setMessage(Message)
        builder.show()
    }

    /**
     * Let's create a method to clear our edittexts
     */
    fun clearEditTexts(){
        nameTxt.setText("")
        galaxyTxt.setText("")
        typeTxt.setText("")
        idTxt.setText("")
    }

    /**
     * Let's override our onCreate method.
     */
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)

        handleInserts()
        handleUpdates()
        handleDeletes()
        handleViewing()
    }

    /**
     * When our handleInserts button is clicked.
     */
    fun handleInserts() {
        insertBtn.setOnClickListener {
            try {
                dbHelper.insertData(nameTxt.text.toString(),galaxyTxt.text.toString(),
                        typeTxt.text.toString())
                clearEditTexts()
            }catch (e: Exception){
                e.printStackTrace()
                showToast(e.message.toString())
            }
        }
    }

    /**
     * When our handleUpdates data button is clicked
     */
    fun handleUpdates() {
        updateBtn.setOnClickListener {
            try {
                val isUpdate = dbHelper.updateData(idTxt.text.toString(),
                        nameTxt.text.toString(),
                        galaxyTxt.text.toString(), typeTxt.text.toString())
                if (isUpdate == true)
                    showToast("Data Updated Successfully")
                else
                    showToast("Data Not Updated")
            }catch (e: Exception){
                e.printStackTrace()
                showToast(e.message.toString())
            }
        }
    }

    /**
     * When our handleDeletes button is clicked
     */
    fun handleDeletes(){
        deleteBtn.setOnClickListener {
            try {
                dbHelper.deleteData(idTxt.text.toString())
                clearEditTexts()
            }catch (e: Exception){
                e.printStackTrace()
                showToast(e.message.toString())
            }
        }
    }

    /**
     * When our View All is clicked
     */
    fun handleViewing() {
        viewBtn.setOnClickListener(
                View.OnClickListener {
                    val res = dbHelper.allData
                    if (res.count == 0) {
                        showDialog("Error", "No Data Found")
                        [email protected]
                    }

                    val buffer = StringBuffer()
                    while (res.moveToNext()) {
                        buffer.append("ID :" + res.getString(0) + "\n")
                        buffer.append("NAME :" + res.getString(1) + "\n")
                        buffer.append("GALAXY :" + res.getString(2) + "\n")
                        buffer.append("TYPE :" + res.getString(3) + "\n\n")
                    }
                    showDialog("Data Listing", buffer.toString())
                }
        )
    }
}
//end
Download

You can download the full source code below or watch the video from the link provided.

No. Location Link
1. GitHub Direct Download
2. GitHub Browse
3. YouTube Video Tutorial
4. YouTube ProgrammingWizards TV Channel

How do You Feel after reading this?

According to scientists, we humans have 8 primary innate emotions: joy, acceptance, fear, surprise, sadness, disgust, anger, and anticipation. Feel free to tell us how you feel about this article using these emotes or via the comment section.

About Me.

After completing his Software Engineering bachelors program, Oclemy(Clement Ochieng) these days is a man of two lives. At day he works for a startup in Nairobi, Kenya. At night he works tirelessly on building ProgrammingWizards TV, a tv channel for student coders and this website to help share the source code. In between he practices Meditation and Self actualization to help him keep balance. He also likes going for long solo walks to connect more with nature.




Recommendations


What do You Think


Previous Post Next Post