HOW TO PERFORM CRUD OPERATION IN SQLITE DATABASE ANDROID/Kotlin
Please Subscribe Youtube| Like Facebook | Follow Twitter
For Java follow this article
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