This is about Kotlin SQLite CRUD Operation Android Example Tutorial.
In this tutorial, you will learn how to insert and retrieve data from sqlite database in android using Kotlin.
CRUD Operations stands for create (store), read, update and delete data in the SQLite database with kotlin in android studio.
After retrieving the data from SQLite database, we will show or display them into the listview.
If you are beginner and do not know the basics of SQLite then read Kotlin SQLite tutorial With Example Android which is the simple tutorial.
First of all, check the following video to see the result of this example tutorial.
Step 1. Making a new Project
As always, our first task is to make a new project in the android studio with empty activity as the default one.
Also, set the Kotlin as the main or primary language for the android project.
Step 2. Database Configurations
We are creating a special class that will take care about sqlite configurations.
So go on and make a new kotlin class and give it a name like DatabaseHelper.kt
You need to write the below code in DatabaseHelper.kt class.
import android.content.ContentValues
import android.content.Context
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper
import android.util.Log
import java.util.ArrayList
/**
* Created by Parsania Hardik on 11/01/2016.
*/
class DatabaseHelper(context: Context) : SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {
// looping through all rows and adding to list
// adding to Students list
val getAllUsers: ArrayList<UserModel>
get() {
val userModelArrayList = ArrayList<UserModel>()
val selectQuery = "SELECT * FROM $TABLE_USER"
val db = this.readableDatabase
val c = db.rawQuery(selectQuery, null)
if (c.moveToFirst()) {
do {
val userModel = UserModel()
userModel.setIds(c.getInt(c.getColumnIndex(KEY_ID)))
userModel.setNames(c.getString(c.getColumnIndex(KEY_FIRSTNAME)))
userModel.setHobbys(c.getString(c.getColumnIndex(KEY_HOBBY)))
userModelArrayList.add(userModel)
} while (c.moveToNext())
}
return userModelArrayList
}
init {
Log.d("table", CREATE_TABLE_STUDENTS)
}
override fun onCreate(db: SQLiteDatabase) {
db.execSQL(CREATE_TABLE_STUDENTS)
}
override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
db.execSQL("DROP TABLE IF EXISTS '$TABLE_USER'")
onCreate(db)
}
fun addUserDetail(name: String, hobby: String): Long {
val db = this.writableDatabase
// Creating content values
val values = ContentValues()
values.put(KEY_FIRSTNAME, name)
values.put(KEY_HOBBY, hobby)
// insert row in students table
return db.insert(TABLE_USER, null, values)
}
fun updateUser(id: Int, name: String, hobby: String): Int {
val db = this.writableDatabase
// Creating content values
val values = ContentValues()
values.put(KEY_FIRSTNAME, name)
values.put(KEY_HOBBY, hobby)
// update row in students table base on students.is value
return db.update(
TABLE_USER, values, "$KEY_ID = ?",
arrayOf(id.toString())
)
}
fun deleteUSer(id: Int) {
// delete row in students table based on id
val db = this.writableDatabase
db.delete(
TABLE_USER, "$KEY_ID = ?",
arrayOf(id.toString())
)
}
companion object {
var DATABASE_NAME = "user_database"
private val DATABASE_VERSION = 1
private val TABLE_USER = "users"
private val KEY_ID = "id"
private val KEY_FIRSTNAME = "name"
private val KEY_HOBBY = "hobby"
/*CREATE TABLE students ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, phone_number TEXT......);*/
private val CREATE_TABLE_STUDENTS = ("CREATE TABLE "
+ TABLE_USER + "(" + KEY_ID
+ " INTEGER PRIMARY KEY AUTOINCREMENT," + KEY_FIRSTNAME + " TEXT, " + KEY_HOBBY + " TEXT );")
}
}
More details
See the below block first
companion object {
var DATABASE_NAME = "user_database"
private val DATABASE_VERSION = 1
private val TABLE_USER = "users"
private val KEY_ID = "id"
private val KEY_FIRSTNAME = "name"
private val KEY_HOBBY = "hobby"
/*CREATE TABLE students ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, phone_number TEXT......);*/
private val CREATE_TABLE_STUDENTS = ("CREATE TABLE "
+ TABLE_USER + "(" + KEY_ID
+ " INTEGER PRIMARY KEY AUTOINCREMENT," + KEY_FIRSTNAME + " TEXT, " + KEY_HOBBY + " TEXT );")
}
String variables like DATABASE_NAME, DATABASE_VERSION, TABLE_USER etc. are holding the name of the database, version of the database, name of the table, column names etc.
A variable CREATE_TABLE_STUDENTS is making a SQL query to create a new table into SQLite database.
Now see the below code
fun addUserDetail(name: String, hobby: String): Long {
val db = this.writableDatabase
// Creating content values
val values = ContentValues()
values.put(KEY_FIRSTNAME, name)
values.put(KEY_HOBBY, hobby)
// insert row in students table
return db.insert(TABLE_USER, null, values)
}
Above method will insert the record into the database. This method is getting two string variables in the parameter.
One is name and another is hobby. Our table have two columns and their names also name and hobby.
So, this method will add the records as name in the name column and hobby in the hobby column.
Now focus on the following
val getAllUsers: ArrayList<UserModel>
get() {
val userModelArrayList = ArrayList<UserModel>()
val selectQuery = "SELECT * FROM $TABLE_USER"
val db = this.readableDatabase
val c = db.rawQuery(selectQuery, null)
if (c.moveToFirst()) {
do {
val userModel = UserModel()
userModel.setIds(c.getInt(c.getColumnIndex(KEY_ID)))
userModel.setNames(c.getString(c.getColumnIndex(KEY_FIRSTNAME)))
userModel.setHobbys(c.getString(c.getColumnIndex(KEY_HOBBY)))
userModelArrayList.add(userModel)
} while (c.moveToNext())
}
return userModelArrayList
}
init {
Log.d("table", CREATE_TABLE_STUDENTS)
}
This method will simply fetch all the records from the database. This method will create one arraylist which have the objects of the UserModel class. (We will create this UserModel class later)
It will store each row details in one separate UserModel object and it will combine all these objects into the arraylist.
Below is the code for updateUser() method.
fun updateUser(id: Int, name: String, hobby: String): Int {
val db = this.writableDatabase
// Creating content values
val values = ContentValues()
values.put(KEY_FIRSTNAME, name)
values.put(KEY_HOBBY, hobby)
// update row in students table base on students.is value
return db.update(
TABLE_USER, values, "$KEY_ID = ?",
arrayOf(id.toString())
)
}
This method is for updating the existing row into the database table. This method is getting id , name and hobby into the parameter.
Id will help the compiler to find which row it needs to update. It will simply replace the existing value of name and hobby with the new ones.
Follow the below function
fun deleteUSer(id: Int) {
// delete row in students table based on id
val db = this.writableDatabase
db.delete(
TABLE_USER, "$KEY_ID = ?",
arrayOf(id.toString())
)
}
This function is for deleting the particular row from the database.
An id is there into the parameter. Compiler will find the row with the help of the id and will simply delete it.
See the following methods
override fun onCreate(db: SQLiteDatabase) {
db.execSQL(CREATE_TABLE_STUDENTS)
}
override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
db.execSQL("DROP TABLE IF EXISTS '$TABLE_USER'")
onCreate(db)
}
Both these methods are important. If you wish to have some more information about these methods then read Kotlin Android SQLite Database tutorial With Example
Step 3. Model and XML file for Listview
Make a new Kotlin file and give it a name like UserModel.kt
Following is the source lines for UserModel.kt
import java.io.Serializable
class UserModel : Serializable {
var name: String? = null
var hobby: String? = null
var id: Int = 0
fun getIds(): Int {
return id
}
fun setIds(id: Int) {
this.id = id
}
fun getNames(): String {
return name.toString()
}
fun setNames(name: String) {
this.name = name
}
fun getHobbys(): String {
return hobby.toString()
}
fun setHobbys(hobby: String) {
this.hobby = hobby
}
}
Our database table have three columns like id, name and hobby. So we are making three variables in the above class.
I have also created getter and setter methods for all these three variables. These methods will help us to maintain data in the listview.
Now, in the app->res->layout directory, create a new XML file named lv_item.xml and add the below in it
<?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">
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:background="#000"
android:orientation="vertical">
<TextView
android:id="@+id/name"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:textColor="#fff"
android:layout_marginTop="10dp"
android:gravity="center_vertical"
android:textAppearance="?android:attr/textAppearanceMedium"
android:paddingLeft="10dp"
android:text="Name" />
<TextView
android:id="@+id/hobby"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:textColor="#fff"
android:gravity="center_vertical"
android:textAppearance="?android:attr/textAppearanceMedium"
android:paddingLeft="10dp"
android:text="Hobby" />
</LinearLayout>
<View
android:layout_width="match_parent"
android:layout_height="1dp"
android:layout_marginTop="10dp"
android:layout_marginLeft="10dp"
android:layout_marginRight="10dp"
android:background="@color/colorAccent"/>
</LinearLayout>
This xml file is for the look and feel of rows of the listview.
Every listview row will be created using this file’s layout.
Step 4. Writing Adapter
Make another Kotlin class with the name like CustomAdapter.kt
Below is the source snippet for CustomAdapter.kt file.
import android.content.Context
import android.view.LayoutInflater
import android.view.View
import android.view.ViewGroup
import android.widget.BaseAdapter
import android.widget.TextView
import java.util.ArrayList
/**
* Created by Parsania Hardik on 26-Apr-17.
*/
class CustomAdapter(private val context: Context, private val userModelArrayList: ArrayList<UserModel>) :
BaseAdapter() {
override fun getCount(): Int {
return userModelArrayList.size
}
override fun getItem(position: Int): Any {
return userModelArrayList[position]
}
override fun getItemId(position: Int): Long {
return 0
}
override fun getView(position: Int, convertView: View?, parent: ViewGroup): View {
var convertView = convertView
val holder: ViewHolder
if (convertView == null) {
holder = ViewHolder()
val inflater = context
.getSystemService(Context.LAYOUT_INFLATER_SERVICE) as LayoutInflater
convertView = inflater.inflate(R.layout.lv_item, null, true)
holder.tvname = convertView!!.findViewById(R.id.name) as TextView
holder.tvcountry = convertView.findViewById(R.id.hobby) as TextView
convertView.tag = holder
} else {
// the getTag returns the viewHolder object set as a tag to the view
holder = convertView.tag as ViewHolder
}
holder.tvname!!.text = "Name: " + userModelArrayList[position].getNames()
holder.tvcountry!!.text = "Hobby: " + userModelArrayList[position].getHobbys()
return convertView
}
private inner class ViewHolder {
var tvname: TextView? = null
var tvcountry: TextView? = null
}
}
This file is the adapter for the listview. After fetching all the records from the sqlite database, we will show them into the listview.
Adapter will set the data into the every row of the listview.
Look at the getView() method in the above adapter class. This method is setting the lv_item.xml as the view for the row layout.
After this, it is finding the two text views and it will set their values using usersModelArralist.
Step 5. Displaying All the Records
Let us create a new activity and set it’s name as GetAllUsersActivity
One file will be there : activity_get_all_users.xml and set it’s code as the below
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:background="@color/colorAccent"
android:orientation="vertical">
<TextView
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:gravity="center"
android:layout_marginTop="10dp"
android:textSize="20sp"
android:textColor="#000"
android:text="Data from SQLite"/>
<ListView
android:layout_width="match_parent"
android:layout_height="match_parent"
android:id="@+id/lv">
</ListView>
</LinearLayout>
It has one text view and one listview.
Now write down the below code lines in GetAllUsersActivity.kt
import android.content.Intent
import android.support.v7.app.AppCompatActivity
import android.os.Bundle
import android.view.View
import android.widget.AdapterView
import android.widget.ListView
import java.util.ArrayList
class GetAllUsersActivity : AppCompatActivity() {
private var listView: ListView? = null
private var userModelArrayList: ArrayList<UserModel>? = null
private var customAdapter: CustomAdapter? = null
private var databaseHelper: DatabaseHelper? = null
override fun onCreate(savedInstanceState: Bundle?) {
super.onCreate(savedInstanceState)
setContentView(R.layout.activity_get_all_users)
listView = findViewById(R.id.lv) as ListView
databaseHelper = DatabaseHelper(this)
userModelArrayList = databaseHelper!!.getAllUsers
customAdapter = CustomAdapter(this, this!!.userModelArrayList!!)
listView!!.adapter = customAdapter
listView!!.onItemClickListener = AdapterView.OnItemClickListener { parent, view, position, id ->
val intent = Intent(this@GetAllUsersActivity, UpdateDeleteActivity::class.java)
intent.putExtra("user", userModelArrayList!![position])
startActivity(intent)
}
}
}
Compiler will first create the objects of listview, CustomAdapter and Database helper classes. It will also create an arraylist with the objects of the UserModel class.
Then it will execute the getAllUsers() function with the help of the Database helper object. This function will fetch all the records and it will store them into the userModelArraylist.
Using this userModelArraylist ,compiler will create the object of the adapter and it will bind it to the listview.
When the user clicks on the listview, it will open a new activity called UpdateDeleteActivity
Step 6. Updating or Deleting SQLite
Let us make a new activity and give it a name as UpdateDeleteActivity
Below is the block for activity_update_delete.xml
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:background="@color/colorAccent"
android:orientation="vertical">
<TextView
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="Name"
android:layout_marginLeft="10dp"
android:layout_marginTop="10dp"
android:textColor="#000"
android:textSize="20sp"/>
<EditText
android:layout_width="match_parent"
android:layout_height="40dp"
android:id="@+id/etname"
android:background="#fff"
android:layout_marginLeft="10dp"
android:layout_marginTop="10dp"
android:layout_marginRight="10dp"
android:hint="Enter Name"/>
<TextView
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="Hobby"
android:layout_marginLeft="10dp"
android:layout_marginTop="10dp"
android:textColor="#000"
android:textSize="20sp"/>
<EditText
android:layout_width="match_parent"
android:layout_height="40dp"
android:id="@+id/ethobby"
android:background="#fff"
android:layout_marginLeft="10dp"
android:layout_marginTop="10dp"
android:layout_marginRight="10dp"
android:hint="Enter Hobby"/>
<Button
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_marginTop="10dp"
android:layout_marginLeft="10dp"
android:id="@+id/btnupdate"
android:text="update"/>
<Button
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_marginTop="10dp"
android:layout_marginLeft="10dp"
android:id="@+id/btndelete"
android:text="delete"/>
</LinearLayout>
This file has two text views , two edit texts and two buttons.
One button is for updating the record and another one is for deleting the row.
Now add the following source code in UpdateDeleteActivity.kt
import android.content.Intent
import android.support.v7.app.AppCompatActivity
import android.os.Bundle
import android.view.View
import android.widget.Button
import android.widget.EditText
import android.widget.Toast
class UpdateDeleteActivity : AppCompatActivity() {
private var userModel: UserModel? = null
private var etname: EditText? = null
private var ethobby: EditText? = null
private var btnupdate: Button? = null
private var btndelete: Button? = null
private var databaseHelper: DatabaseHelper? = null
override fun onCreate(savedInstanceState: Bundle?) {
super.onCreate(savedInstanceState)
setContentView(R.layout.activity_update_delete)
val intent = intent
userModel = intent.getSerializableExtra("user") as UserModel
databaseHelper = DatabaseHelper(this)
etname = findViewById(R.id.etname) as EditText
ethobby = findViewById(R.id.ethobby) as EditText
btndelete = findViewById(R.id.btndelete) as Button
btnupdate = findViewById(R.id.btnupdate) as Button
etname!!.setText(userModel!!.getNames())
ethobby!!.setText(userModel!!.getHobbys())
btnupdate!!.setOnClickListener {
databaseHelper!!.updateUser(userModel!!.getIds(), etname!!.text.toString(), ethobby!!.text.toString())
Toast.makeText(this@UpdateDeleteActivity, "Updated Successfully!", Toast.LENGTH_SHORT).show()
val intent = Intent(this@UpdateDeleteActivity, MainActivity::class.java)
intent.addFlags(Intent.FLAG_ACTIVITY_CLEAR_TASK or Intent.FLAG_ACTIVITY_NEW_TASK)
startActivity(intent)
}
btndelete!!.setOnClickListener {
databaseHelper!!.deleteUSer(userModel!!.getIds())
Toast.makeText(this@UpdateDeleteActivity, "Deleted Successfully!", Toast.LENGTH_SHORT).show()
val intent = Intent(this@UpdateDeleteActivity, MainActivity::class.java)
intent.addFlags(Intent.FLAG_ACTIVITY_CLEAR_TASK or Intent.FLAG_ACTIVITY_NEW_TASK)
startActivity(intent)
}
}
}
When the user comes to this activity, compiler will set the name and hobby into the edit text.
If user wants to update the record, he can change the value of the edit texts and then click on the Update button.
And if user wants to delete the record then he needs to simply click the Delete button.
For updating the record, compiler will call the updateUser() function with the help of Database Helper object.
Similarly for deleting the user, it will call deleteUser() method.
Step 7. Last and Main Step
Let us write last but main activity. There should be two files when you created a new project : activity_main.xml and MainActivity.kt
Here is the coding for activity_main.xml
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical"
android:background="@color/colorAccent">
<TextView
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:gravity="center"
android:textColor="#fff"
android:textSize="20sp"
android:layout_marginTop="10dp"
android:text="Enter Name, Hobby to store in SQLite" />
<EditText
android:layout_width="match_parent"
android:layout_height="40dp"
android:id="@+id/etname"
android:background="#fff"
android:layout_marginTop="20dp"
android:layout_marginLeft="20dp"
android:hint="Enter Name"/>
<EditText
android:layout_width="match_parent"
android:layout_height="40dp"
android:id="@+id/ethobby"
android:background="#fff"
android:layout_marginTop="20dp"
android:layout_marginLeft="20dp"
android:hint="Enter Hobby"/>
<Button
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:id="@+id/btnstore"
android:layout_marginLeft="20dp"
android:layout_marginTop="10dp"
android:text="Store"/>
<Button
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:id="@+id/btnget"
android:layout_marginLeft="20dp"
android:layout_marginTop="10dp"
android:text="Get All Users from SQLite"/>
</LinearLayout>
Some UI widgets line two edit texts, two buttons and one text view is there.
Now add the following source lines for MainActivity.kt
import android.content.Intent
import android.support.v7.app.AppCompatActivity
import android.os.Bundle
import android.view.View
import android.widget.Button
import android.widget.EditText
import android.widget.Toast
class MainActivity : AppCompatActivity() {
private var btnStore: Button? = null
private var btnGetall: Button? = null
private var etname: EditText? = null
private var ethobby: EditText? = null
private var databaseHelper: DatabaseHelper? = null
override fun onCreate(savedInstanceState: Bundle?) {
super.onCreate(savedInstanceState)
setContentView(R.layout.activity_main)
databaseHelper = DatabaseHelper(this)
btnStore = findViewById(R.id.btnstore) as Button
btnGetall = findViewById(R.id.btnget) as Button
etname = findViewById(R.id.etname) as EditText
ethobby = findViewById(R.id.ethobby) as EditText
btnStore!!.setOnClickListener {
databaseHelper!!.addUserDetail(etname!!.text.toString(), ethobby!!.text.toString())
etname!!.setText("")
ethobby!!.setText("")
Toast.makeText(this@MainActivity, "Stored Successfully!", Toast.LENGTH_SHORT).show()
}
btnGetall!!.setOnClickListener {
val intent = Intent(this@MainActivity, GetAllUsersActivity::class.java)
startActivity(intent)
}
}
}
Compiler will first create the objects of edit text, button and database helper class.
User can enter name and hobby into two different edit texts.
When the user clicks Store button, compiler will call addUserDetail() function from DatabaseHelper class. This function will insert the record into the database.
Then compiler will clear both the edit texts.
When the user clicks the Get All Users button, compiler will simply open the GetAllUsersActivity.
Download Code For Kotlin SQLite CRUD Example
https://github.com/demonuts/Kotlin-SQLite-CRUD-Operation-Android-Example-Tutorial