The name of the database in Android is SQLite. SQLite is the name of the open-source sql database that stores the data in the text file in the device. Here, we will be review and explaining how to execute them with the Kotlin language.
Before diving into the implementation we are going to review a little in details regarding the SQLite class. There are many methods available in the SQLiteDatabase class but the ones we will use are below
| Method | Description |
| execSQL(): Unit | Executes the SQL query, this is not to be mistaken for a select query |
| insert(): Long | Insert a record into the database |
| update(): Int | Fires the update request on a select row |
| delete(): | |
| query(): Cursor | Returns a cursor over the resultset |
HandlerDB
First we are going to create a class that extends to the SQLiteOpenHelper and override its onCreate(), onUpgrade() functions.
- CreatePerson – will insert the data by passing the ContentvValues to object to the insert() method
- UpdatePerson – will update the record by passining the ContentValues to the update() method
- DeletePerson – will delete the record by passing the Content
- ViewPerson – Will create a list adding the records to return to the view
package com.programmingninja.coeus.helper
import android.content.ContentValues
import android.content.Context
import android.database.Cursor
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteException
import android.database.sqlite.SQLiteOpenHelper
import com.programmingninja.coeus.model.PersonModel
import java.time.LocalDateTime
import kotlin.collections.ArrayList
class HandlerDB (context: Context): SQLiteOpenHelper(context, DATABASE_NAME,null,DATABASE_VERSION) {
companion object {
private val DATABASE_VERSION = 1
private val DATABASE_NAME = "ProjectTitan"
private val TABLE_NAME = "Person"
private val KEY_ID = "userid"
private val KEY_USERNAME = "username"
private val KEY_FIRST_NAME = "firstname"
private val KEY_LAST_NAME = "lastname"
}
override fun onCreate(db: SQLiteDatabase?) {
//creating the table and columns
val CREATE_PERSON_TABLE = ("CREATE TABLE $TABLE_NAME (" +
"$KEY_ID TEXT PRIMARY KEY," +
"$KEY_USERNAME TEXT," +
"$KEY_FIRST_NAME TEXT," +
"$KEY_LAST_NAME TEXT," +
"timestamp DEFAULT CURRENT_TIMESTAMP)")
db?.execSQL(CREATE_PERSON_TABLE)
}
override fun onUpgrade(db: SQLiteDatabase?, oldVersion: Int, newVersion: Int) {
db!!.execSQL("DROP TABLE IF EXISTS $TABLE_NAME")
}
//method to read data
fun ViewPerson(): List<PersonModel> {
val personList:ArrayList<PersonModel> = ArrayList()
val personCandidates = "SELECT * FROM $TABLE_NAME"
val db = this.readableDatabase
val cursor: Cursor?
try {
cursor = db.rawQuery(personCandidates, null)
} catch (e: SQLiteException) {
db.execSQL(personCandidates)
return ArrayList()
}
var userId: String
var userName: String
var fName: String
var lName: String
if (cursor.moveToFirst()) {
do {
userId = cursor.getString(cursor.getColumnIndex("userid"))
userName = cursor.getString(cursor.getColumnIndex("username"))
fName = cursor.getString(cursor.getColumnIndex("firstname"))
lName = cursor.getString(cursor.getColumnIndex("lastname"))
val person = PersonModel(userId = userId, userName = userName, fName = fName, lName = lName)
personList.add(person)
} while (cursor.moveToNext())
}
return personList
}
//method to create data
fun CreatePerson(person: PersonModel): Long {
val db = this.writableDatabase
val cv = ContentValues()
cv.put(KEY_ID, person.userId)
cv.put(KEY_USERNAME, person.userName)
cv.put(KEY_FIRST_NAME, person.fName)
cv.put(KEY_LAST_NAME, person.lName)
//now insert into the db
val successfulInsert = db.insert(TABLE_NAME, null, cv)
db.close()
return successfulInsert
}
//method to update data
fun UpdatePerson(person: PersonModel): Int {
val db = this.writableDatabase
val cv = ContentValues()
cv.put(KEY_USERNAME, person.userName)
cv.put(KEY_FIRST_NAME, person.fName)
cv.put(KEY_LAST_NAME, person.lName)
val whereClause = "${KEY_ID} = ?"
val whereArgs = arrayOf(person.userId.toString())
//attempt to update the record
val successfulUpdate = db.update(TABLE_NAME, cv, whereClause, whereArgs)
db.close()
return successfulUpdate
}
//method to delete data
fun DeletePerson(person: PersonModel): Int {
val db = this.writableDatabase
val cv = ContentValues()
cv.put(KEY_ID, person.userId)
val whereClause = "${KEY_ID} = ?"
val whereArg = arrayOf(person.userId.toString())
//delete candidate
val successfulDelete = db.delete(TABLE_NAME, whereClause, whereArg)
db.close()
return successfulDelete
}
}
activity_main.xml
Now we are going to work on the main xml layout. This is going to be the main display the user interacts with
<?xml version="1.0" encoding="utf-8"?>
<androidx.constraintlayout.widget.ConstraintLayout
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">
<androidx.appcompat.widget.LinearLayoutCompat
android:layout_width="match_parent"
android:layout_height="match_parent"
android:layout_marginBottom="8dp"
android:layout_marginEnd="8dp"
android:layout_marginStart="8dp"
android:layout_marginTop="8dp"
android:orientation="vertical">
<TableLayout
android:layout_width="match_parent"
android:layout_height="wrap_content">
<TableRow>
<androidx.appcompat.widget.AppCompatTextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:hint="User ID"
android:layout_column="1"/>
<androidx.appcompat.widget.AppCompatTextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:id="@+id/tv_userId"
android:layout_marginLeft="20dp"
android:layout_marginStart="20dp"
android:width="150dp"/>
</TableRow>
<TableRow>
<androidx.appcompat.widget.AppCompatTextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:hint="Username"
android:layout_column="1"/>
<androidx.appcompat.widget.AppCompatEditText
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:id="@+id/et_userName"
android:layout_marginLeft="20dp"
android:layout_marginStart="20dp"
android:width="150dp"/>
</TableRow>
<TableRow>
<androidx.appcompat.widget.AppCompatTextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:hint="First Name"
android:layout_column="1"/>
<androidx.appcompat.widget.AppCompatEditText
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:id="@+id/et_fName"
android:layout_marginLeft="20dp"
android:layout_marginStart="20dp"
android:width="150dp"/>
</TableRow>
<TableRow>
<androidx.appcompat.widget.AppCompatTextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:hint="Last Name"
android:layout_column="1"/>
<androidx.appcompat.widget.AppCompatEditText
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:id="@+id/et_lName"
android:layout_marginLeft="20dp"
android:layout_marginStart="20dp"
android:width="150dp"/>
</TableRow>
</TableLayout>
<androidx.appcompat.widget.LinearLayoutCompat
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginTop="20dp">
<ListView
android:id="@+id/listView"
android:layout_width="wrap_content"
android:layout_height="350sp"
android:longClickable="false" />
</androidx.appcompat.widget.LinearLayoutCompat>
<androidx.appcompat.widget.LinearLayoutCompat
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_marginTop="40dp"
android:orientation="horizontal"
android:layout_gravity="center">
<androidx.appcompat.widget.AppCompatButton
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="View"
android:id="@+id/pressMe"
android:onClick="ViewRecord" />
<androidx.appcompat.widget.AppCompatButton
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="Save"
android:onClick="SaveRecord" />
<androidx.appcompat.widget.AppCompatButton
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="Update"
android:onClick="UpdateRecord" />
<androidx.appcompat.widget.AppCompatButton
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="Delete"
android:onClick="DeleteRecord" />
</androidx.appcompat.widget.LinearLayoutCompat>
</androidx.appcompat.widget.LinearLayoutCompat>
</androidx.constraintlayout.widget.ConstraintLayout>
Update Person Alert
//creat the layout to display an alertdialog to update request item
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:padding="10dp"
android:orientation="vertical">
<androidx.appcompat.widget.AppCompatTextView
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:id="@+id/tv_userId_update"
android:ems="10"
android:text="User Id"/>
<androidx.appcompat.widget.AppCompatEditText
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:id="@+id/et_nameUser_update"
android:ems="10"
android:hint="username"/>
<androidx.appcompat.widget.AppCompatEditText
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:id="@+id/et_firstname_update"
android:ems="10"
android:hint="first name"/>
<androidx.appcompat.widget.AppCompatEditText
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:id="@+id/et_lastname_update"
android:ems="10"
android:hint="Last Name"/>
</LinearLayout>
Delete Person Alert
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical"
android:padding="10dp">
<androidx.appcompat.widget.AppCompatTextView
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:id="@+id/tv_userId_delete"
android:textSize="20dp"
android:text="userid" />
<androidx.appcompat.widget.AppCompatTextView
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:id="@+id/tv_Name_delete"
android:textSize="20dp"
android:text="Name"/>
</LinearLayout>
MainActivity.kt
Now we are focusing on the MainActivity.kt class and we are going to add the following code. In this class, the following functions are going to be added:
- saveRecord() function which creates the records.
- viewRecord() function will read all the records and displays them into ListView,
- updateRecord() function updates the record on the basis on id, and
- deleteRecord() function deletes the record.
The val db = DatabaseHandler(this) creates the instance of HandlerDB class calls the SQLite database logic.
package com.programmingninja.coeus
import android.content.DialogInterface
import androidx.appcompat.app.AppCompatActivity
import android.os.Bundle
import android.view.View
import android.widget.AdapterView
import android.widget.EditText
import android.widget.TextView
import android.widget.Toast
import androidx.appcompat.app.AlertDialog
import androidx.appcompat.widget.AppCompatButton
import androidx.appcompat.widget.AppCompatEditText
import androidx.appcompat.widget.AppCompatTextView
import com.programmingninja.coeus.helper.HandlerDB
import com.programmingninja.coeus.helper.MyListadapter
import com.programmingninja.coeus.model.PersonModel
import kotlinx.android.synthetic.main.activity_main.*
import kotlinx.android.synthetic.main.activity_main.view.*
import kotlinx.android.synthetic.main.update_person.*
import java.util.*
class MainActivity : AppCompatActivity() {
override fun onCreate(savedInstanceState: Bundle?) {
super.onCreate(savedInstanceState)
setContentView(R.layout.activity_main)
}
//method for reviewing records
fun ViewRecord (view:View) {
//creating the instance of the db handler
val dbHandler = HandlerDB(this)
//calling the person view
val person: List<PersonModel> = dbHandler.ViewPerson()
val personArrayId = Array<String>(person.size){"0"}
val personArrayUsername = Array<String>(person.size){""}
val personArrayFirst = Array<String>(person.size){""}
val personArrayLast = Array<String>(person.size){""}
for ((index, p) in person.withIndex()) {
personArrayId[index] = p.userId
personArrayUsername[index] = p.userName
personArrayFirst[index] = p.fName
personArrayLast[index] = p.lName
}
//creating custom adapter
val listAdapter = MyListadapter(this, personArrayId, personArrayUsername, personArrayFirst, personArrayLast)
listView.adapter = listAdapter
listView.onItemClickListener = AdapterView.OnItemClickListener {
adapterView, view, position, id ->
//Toast.makeText(applicationContext, "${person[position].userId}",Toast.LENGTH_LONG).show()
UpdateRecord(person[position].userId)
}
listView.onItemLongClickListener = AdapterView.OnItemLongClickListener {
adapterView, view, position, l ->
DeleteRecord(person[position].userId, person[position].fName + " " + person[position].lName)
}
}
//method for deleting a record
fun DeleteRecord(deleteUserId: String, deleteUserName: String): Boolean {
//create AlertDisplay
val alertDialog = AlertDialog.Builder(this)
val inflater = this.layoutInflater
val dv = inflater.inflate(R.layout.delete_person, null)
alertDialog.setView(dv)
val delete_id = dv.findViewById<AppCompatTextView>(R.id.tv_userId_delete)
val delete_name = dv.findViewById<AppCompatTextView>(R.id.tv_Name_delete)
delete_id.text = deleteUserId
delete_name.text = deleteUserName
val deletePersonId = delete_id.text
alertDialog.setTitle("Delete User")
alertDialog.setMessage("Are you sure you want to delete the user")
alertDialog.setPositiveButton("Yes, Delete", DialogInterface.OnClickListener { _,_->
//creating the instance of the handler
val db = HandlerDB(this)
//calling the delete method within the handler
val status = db.DeletePerson(PersonModel(deletePersonId.toString(),"","",""))
if (status > -1) {
Toast.makeText(applicationContext, "User has been deleted", Toast.LENGTH_LONG).show()
val buttonPress = findViewById<AppCompatButton>(R.id.pressMe)
buttonPress.performClick()
}
})
alertDialog.setNegativeButton("Cancel", DialogInterface.OnClickListener { _, _->
})
val b = alertDialog.create()
b.show()
return false
}
//method for updating a record
fun UpdateRecord(updatingUser: String) {
val alertBuilder = AlertDialog.Builder(this)
val inflater = this.layoutInflater
val dv = inflater.inflate(R.layout.update_person, null)
val uPersonUserId = dv.findViewById(R.id.tv_userId_update) as AppCompatTextView
val uPersonUsername = dv.findViewById(R.id.et_nameUser_update) as AppCompatEditText
val uPersonFirstName = dv.findViewById<AppCompatEditText>(R.id.et_firstname_update)
val uPersonLastName = dv.findViewById(R.id.et_lastname_update) as AppCompatEditText
uPersonUserId.text = updatingUser
val updatePersonId = uPersonUserId.text
val updatePersonUsername = uPersonUsername.text
val updatePersonFirst = uPersonFirstName.text
val updatePersonLast = uPersonLastName.text
alertBuilder.setTitle("Update Person")
alertBuilder.setMessage("Update person data")
alertBuilder.setPositiveButton("Update", DialogInterface.OnClickListener { _, _->
//creating an instance of the handler class
val db = HandlerDB(this)
if (!updatePersonUsername.isNullOrEmpty() && !updatePersonFirst.isNullOrEmpty() && !updatePersonLast.isNullOrEmpty()) {
val status = db.UpdatePerson(PersonModel(updatePersonId.toString(), updatePersonUsername.toString(), updatePersonFirst.toString(), updatePersonLast.toString()))
if (status>-1) {
Toast.makeText(applicationContext,"Record Update", Toast.LENGTH_LONG).show()
val buttonPress = findViewById<AppCompatButton>(R.id.pressMe)
buttonPress.performClick()
}
} else {
Toast.makeText(applicationContext, "username, first and last name required", Toast.LENGTH_LONG).show()
}
})
alertBuilder.setNegativeButton("Cancel", DialogInterface.OnClickListener { di, which -> })
alertBuilder.setView(dv)
val b = alertBuilder.create()
b.show()
}
//method for saving the records
fun SaveRecord (view: View) {
val id = UUID.randomUUID()
val uName = et_userName.text.toString()
val fName = et_fName.text.toString()
val lName = et_lName.text.toString()
val db_handler: HandlerDB = HandlerDB(this)
if (uName.trim().isNotEmpty() || fName.trim().isNotEmpty() || lName.trim().isNotEmpty()) {
val status = db_handler.CreatePerson(PersonModel(id.toString(), uName, fName, lName))
if (status > -1) {
Toast.makeText(applicationContext, "Person saved", Toast.LENGTH_LONG).show()
et_userName.setText("")
et_fName.setText("")
et_lName.setText("")
}
} else {
Toast.makeText(applicationContext, "Username, First Name and Last Name is required", Toast.LENGTH_LONG).show()
}
}
}
PersonModel.kt
package com.programmingninja.coeus.model
class PersonModel (var userId: String, val userName: String, val fName: String, val lName: String) {
}
custom_list.xml
//create a custom row layout for displauong the list items in the ListView
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical"
android:id="@+id/lLayout">
<androidx.appcompat.widget.AppCompatTextView
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:id="@+id/tv_userId"
android:text="User Id"
android:textAppearance="@style/TextAppearance.AppCompat.Medium" />
<androidx.appcompat.widget.AppCompatTextView
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:id="@+id/tv_nameU"
android:text="Username"
android:textAppearance="@style/TextAppearance.AppCompat.Medium" />
<androidx.appcompat.widget.AppCompatTextView
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:id="@+id/tv_nameF"
android:text="First Name"
android:textAppearance="@style/TextAppearance.AppCompat.Medium" />
<androidx.appcompat.widget.AppCompatTextView
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:id="@+id/tv_nameL"
android:text="Last Name"
android:textAppearance="@style/TextAppearance.AppCompat.Medium" />
</LinearLayout>
MyListadapter
//Now, we create the adapter that will populate the data model within the listview
package com.programmingninja.coeus.helper
import android.app.Activity
import android.content.Context
import android.view.View
import android.view.ViewGroup
import android.widget.ArrayAdapter
import androidx.appcompat.widget.AppCompatTextView
import com.programmingninja.coeus.R
class MyListadapter (private val context: Activity, private val idUser: Array<String>, private val nameUser: Array<String>, private val userFirst: Array<String>, private val userLast: Array<String>)
: ArrayAdapter<String>(context, R.layout.custom_list, nameUser)
{
override fun getView(position: Int, view: View?, parent: ViewGroup): View {
val inflater = context.layoutInflater
val rowView = inflater.inflate(R.layout.custom_list, null, true)
val idUserTxt = rowView.findViewById(R.id.tv_userId) as AppCompatTextView
val nameUserTxt = rowView.findViewById(R.id.tv_nameU) as AppCompatTextView
val nameFTxt = rowView.findViewById(R.id.tv_nameF) as AppCompatTextView
val nameLTxt = rowView.findViewById(R.id.tv_nameL) as AppCompatTextView
idUserTxt.text = "User Id: ${idUser[position]}"
nameUserTxt.text = "Username: ${nameUser[position]}"
nameFTxt.text = "First Name: ${userFirst[position]}"
nameLTxt.text = "Last Name: ${userLast[position]}"
return rowView
}
}
