Perform Crud Operation In Sqlite Database Android/Kotlin

HOW TO PERFORM CRUD OPERATION IN SQLITE DATABASE ANDROID/Kotlin

by Posted on
Share Post
   1       
1
Share

Please Subscribe Youtube| Like Facebook | Follow Twitter

For Java follow this article

Download Source Code

Introduction

In this article we will learn how to perform CRUD operation in Sqlite Database Android (Example Code).                                                             

Our Example

In our example we have Student Database table containing StudentID and StudentName as columns. In our main screen we will perform CRUD operation i.e create, read, update and delete student record.

Steps

Follow below steps

1) Code Database Operations in your app

2) Run and test your app

1) Code Database Operations in your app

Below is xml code of our Layout file

<?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:layout_width="match_parent"
    android:layout_height="match_parent"
    tools:context=".MainActivity"
    android:orientation="vertical"
    >

    <EditText
        android:id="@+id/studentId"
        android:layout_width="match_parent"
        android:layout_height="0dp"
        android:layout_weight="1"
        android:ems="10"
        android:inputType="number"
        android:hint="Student ID"
        />

    <EditText
        android:id="@+id/studentName"
        android:layout_width="match_parent"
        android:layout_height="0dp"
        android:layout_weight="1"
        android:ems="10"
        android:inputType="textPersonName"
        android:hint="Student Name"
        />

    <Button
        android:layout_width="match_parent"
        android:layout_height="0dp"
        android:layout_weight="1"

        android:text="Load All Students"
        android:onClick="loadStudents"
        />

    <TextView
        android:id="@+id/resultText"
        android:layout_width="match_parent"
        android:layout_height="0dp"
        android:layout_weight="1"
        android:hint="Result"
        android:textSize="30dp"
        />

    <Button
        android:layout_width="match_parent"
        android:layout_height="0dp"
        android:layout_weight="1"

        android:onClick="addStudent"
        android:text="ADD" />

    <Button
        android:layout_width="match_parent"
        android:layout_height="0dp"
        android:layout_weight="1"

        android:onClick="updateStudent"
        android:text="UPDATE"
        />

    <Button
        android:layout_width="match_parent"
        android:layout_height="0dp"
        android:layout_weight="1"

        android:onClick="deleteStudent"
        android:text="DELETE By Id"
        />


</LinearLayout>

MyDBHandler class is our database handler class which extends SQLiteOpenHelper class to manage database operations. First time on constructor (MyDBHandler) call we will create our database. In onCreate() method we will create student table.

class MyDBHandler internal constructor(context: Context?) : SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {
    override fun onCreate(db: SQLiteDatabase) {
        val CREATE_STUDENT_TABLE = ("CREATE TABLE " +
                TABLE_STUDENTS + "(" + COLUMN_ID + " INTEGER PRIMARY KEY," + COLUMN_NAME
                + " TEXT " + ")")
        db.execSQL(CREATE_STUDENT_TABLE)
    }
	
	companion object {
		private const val DATABASE_VERSION = 1
		private const val DATABASE_NAME = "studentDB.db"
		private const val TABLE_STUDENTS = "students"
		private const val COLUMN_ID = "StudentID"
		private const val COLUMN_NAME = "StudentName"
	}
}

loadHandler() method will read all records from student table. addHandler() method will insert a new record in student table. updateHandler() method will update an existing record in student table. deleteHandler() method will delete selected record from student table.

fun loadHandler(): String {
	var result = ""
	val query = "Select*FROM $TABLE_STUDENTS"
	val db = this.writableDatabase
	val cursor = db.rawQuery(query, null)
	while (cursor.moveToNext()) {
		val result_0 = cursor.getInt(0)
		val result_1 = cursor.getString(1)
		result += result_0.toString() + " " + result_1 +
				System.getProperty("line.separator")
	}
	cursor.close()
	db.close()
	if (result == "") result = "No Record Found"
	return result
}

fun addHandler(student: Student): Long {
	val id: Long
	val values = ContentValues()
	values.put(COLUMN_ID, student.studentID)
	values.put(COLUMN_NAME, student.studentName)
	val db = this.writableDatabase
	id = db.insert(TABLE_STUDENTS, null, values)
	db.close()
	return id
}

fun updateHandler(student: Student): Boolean {
	val db = this.writableDatabase
	val args = ContentValues()
	args.put(COLUMN_ID, student.studentID)
	args.put(COLUMN_NAME, student.studentName)
	return db.update(TABLE_STUDENTS, args, "$COLUMN_ID=${student.studentID}", null) > 0
}

fun deleteHandler(ID: Int): Boolean {
	var result = false
	val query = "Select*FROM $TABLE_STUDENTS WHERE $COLUMN_ID = '$ID'"
	val db = this.writableDatabase
	val cursor = db.rawQuery(query, null)
	if (cursor.moveToFirst()) {
		val id= cursor.getString(0).toInt()
		db.delete(TABLE_STUDENTS, "$COLUMN_ID=?", arrayOf(
				java.lang.String.valueOf(id)
		))
		cursor.close()
		result = true
	}
	db.close()
	return result
}

In Mainactivity on respective buttons clicks loadStudents(), addStudent(), updateStudent() and deleteStudent() methods will be called which will then internally invokes corresponding MyDBHandler method for crud operation.

fun loadStudents(view: View?) {
	resultText.text = dbHandler!!.loadHandler()
	studentId.setText("")
	studentName.setText("")
}

fun addStudent(view: View?) {
	if (!studentId.text.toString().isEmpty() && !studentName.text.toString().isEmpty()) {
		val id = studentId.text.toString().toInt()
		val name = studentName.text.toString()
		val student = Student(id, name)
		val insertId = dbHandler!!.addHandler(student)
		if (insertId == -1L) {
			resultText.text = "Record already exists"
		} else {
			studentId.setText("")
			studentName.setText("")
			resultText.text = "Record added"
		}
	} else {
		resultText.text = "Please fill correct id and name"
	}
}

fun updateStudent(view: View?) {
	if (!studentId.text.toString().isEmpty() && !studentName.text.toString().isEmpty()) {
		val id = studentId.text.toString().toInt()
		val name = studentName.text.toString()
		val student = Student(id, name)
		val result = dbHandler!!.updateHandler(student)
		if (result) {
			studentId.setText("")
			studentName.setText("")
			resultText.text = "Record Updated"
		} else {
			resultText.text = "No Record Found"
		}
	} else {
		resultText.text = "Please fill correct id and name"
	}
}

fun deleteStudent(view: View?) {
	if (!studentId.text.toString().isEmpty()) {
		val result = dbHandler!!.deleteHandler(
				studentId.text.toString().toInt())
		if (result) {
			studentId.setText("")
			studentName.setText("")
			resultText.text = "Record Deleted"
		} else {
			resultText.text = "No Record Found"
		}
	} else {
		resultText.text = "Please fill correct id"
	}
}

Whole Code

activity_main xml file

<?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:layout_width="match_parent"
    android:layout_height="match_parent"
    tools:context=".MainActivity"
    android:orientation="vertical"
    >

    <EditText
        android:id="@+id/studentId"
        android:layout_width="match_parent"
        android:layout_height="0dp"
        android:layout_weight="1"
        android:ems="10"
        android:inputType="number"
        android:hint="Student ID"
        />

    <EditText
        android:id="@+id/studentName"
        android:layout_width="match_parent"
        android:layout_height="0dp"
        android:layout_weight="1"
        android:ems="10"
        android:inputType="textPersonName"
        android:hint="Student Name"
        />

    <Button
        android:layout_width="match_parent"
        android:layout_height="0dp"
        android:layout_weight="1"

        android:text="Load All Students"
        android:onClick="loadStudents"
        />

    <TextView
        android:id="@+id/resultText"
        android:layout_width="match_parent"
        android:layout_height="0dp"
        android:layout_weight="1"
        android:hint="Result"
        android:textSize="30dp"
        />

    <Button
        android:layout_width="match_parent"
        android:layout_height="0dp"
        android:layout_weight="1"

        android:onClick="addStudent"
        android:text="ADD" />

    <Button
        android:layout_width="match_parent"
        android:layout_height="0dp"
        android:layout_weight="1"

        android:onClick="updateStudent"
        android:text="UPDATE"
        />

    <Button
        android:layout_width="match_parent"
        android:layout_height="0dp"
        android:layout_weight="1"

        android:onClick="deleteStudent"
        android:text="DELETE By Id"
        />


</LinearLayout>

MainActivity.kt

package com.programtown.example

import android.os.Bundle
import android.text.method.ScrollingMovementMethod
import android.view.View
import androidx.appcompat.app.AppCompatActivity
import kotlinx.android.synthetic.main.activity_main.*

class MainActivity : AppCompatActivity() {

    var dbHandler: MyDBHandler? = null

    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)

        resultText.movementMethod = ScrollingMovementMethod()
        dbHandler = MyDBHandler(this)
    }

    fun loadStudents(view: View?) {
        resultText.text = dbHandler!!.loadHandler()
        studentId.setText("")
        studentName.setText("")
    }

    fun addStudent(view: View?) {
        if (!studentId.text.toString().isEmpty() && !studentName.text.toString().isEmpty()) {
            val id = studentId.text.toString().toInt()
            val name = studentName.text.toString()
            val student = Student(id, name)
            val insertId = dbHandler!!.addHandler(student)
            if (insertId == -1L) {
                resultText.text = "Record already exists"
            } else {
                studentId.setText("")
                studentName.setText("")
                resultText.text = "Record added"
            }
        } else {
            resultText.text = "Please fill correct id and name"
        }
    }

    fun updateStudent(view: View?) {
        if (!studentId.text.toString().isEmpty() && !studentName.text.toString().isEmpty()) {
            val id = studentId.text.toString().toInt()
            val name = studentName.text.toString()
            val student = Student(id, name)
            val result = dbHandler!!.updateHandler(student)
            if (result) {
                studentId.setText("")
                studentName.setText("")
                resultText.text = "Record Updated"
            } else {
                resultText.text = "No Record Found"
            }
        } else {
            resultText.text = "Please fill correct id and name"
        }
    }

    fun deleteStudent(view: View?) {
        if (!studentId.text.toString().isEmpty()) {
            val result = dbHandler!!.deleteHandler(
                    studentId.text.toString().toInt())
            if (result) {
                studentId.setText("")
                studentName.setText("")
                resultText.text = "Record Deleted"
            } else {
                resultText.text = "No Record Found"
            }
        } else {
            resultText.text = "Please fill correct id"
        }
    }

    override fun onDestroy() {
        super.onDestroy()
        dbHandler?.close()
    }
}

MyDBHandler.kt

package com.programtown.example

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

class MyDBHandler internal constructor(context: Context?) : SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {
    override fun onCreate(db: SQLiteDatabase) {
        val CREATE_STUDENT_TABLE = ("CREATE TABLE " +
                TABLE_STUDENTS + "(" + COLUMN_ID + " INTEGER PRIMARY KEY," + COLUMN_NAME
                + " TEXT " + ")")
        db.execSQL(CREATE_STUDENT_TABLE)
    }

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

    fun loadHandler(): String {
        var result = ""
        val query = "Select*FROM $TABLE_STUDENTS"
        val db = this.writableDatabase
        val cursor = db.rawQuery(query, null)
        while (cursor.moveToNext()) {
            val result_0 = cursor.getInt(0)
            val result_1 = cursor.getString(1)
            result += result_0.toString() + " " + result_1 +
                    System.getProperty("line.separator")
        }
        cursor.close()
        db.close()
        if (result == "") result = "No Record Found"
        return result
    }

    fun addHandler(student: Student): Long {
        val id: Long
        val values = ContentValues()
        values.put(COLUMN_ID, student.studentID)
        values.put(COLUMN_NAME, student.studentName)
        val db = this.writableDatabase
        id = db.insert(TABLE_STUDENTS, null, values)
        db.close()
        return id
    }

    fun updateHandler(student: Student): Boolean {
        val db = this.writableDatabase
        val args = ContentValues()
        args.put(COLUMN_ID, student.studentID)
        args.put(COLUMN_NAME, student.studentName)
        return db.update(TABLE_STUDENTS, args, "$COLUMN_ID=${student.studentID}", null) > 0
    }

    fun deleteHandler(ID: Int): Boolean {
        var result = false
        val query = "Select*FROM $TABLE_STUDENTS WHERE $COLUMN_ID = '$ID'"
        val db = this.writableDatabase
        val cursor = db.rawQuery(query, null)
        if (cursor.moveToFirst()) {
            val id= cursor.getString(0).toInt()
            db.delete(TABLE_STUDENTS, "$COLUMN_ID=?", arrayOf(
                    java.lang.String.valueOf(id)
            ))
            cursor.close()
            result = true
        }
        db.close()
        return result
    }

    companion object {
        private const val DATABASE_VERSION = 1
        private const val DATABASE_NAME = "studentDB.db"
        private const val TABLE_STUDENTS = "students"
        private const val COLUMN_ID = "StudentID"
        private const val COLUMN_NAME = "StudentName"
    }
}

Student.kt

package com.programtown.example

data class Student(val studentID:Int,val studentName: String) {

}

2) Run and test your app

Conclusion

So in this post we have learned how to perform CRUD operation in SQlite Database Android with example code.

Please Subscribe Youtube| Like Facebook | Follow Twitter


Share Post
   1       
1
Share

Published by ProgramTown

Leave a Reply

Your email address will not be published. Required fields are marked *