This post is about Kotlin SQLite Database Android Tutorial With Example.
You will learn how to use SQLite database to save data in android device using kotlin.
Sometimes you want to give user an offline features, hence you need to store information in the SQLite Database.
We will cover simple operations like adding and deleting records in sqlite database using Kotlin.
If you wish to do operations like insert, update, retrieve and delete records then read Kotlin SQLite CRUD Android Example Tutorial.
You will learn how to insert and retrieve data from sqlite database in android using Kotlin in this tutorial.
First of all, check the below output video.
Step 1. New Project For SQLite
Your first task is to make a fresh new android studio project.
In this process, you need to be sure that you have selected empty activity as a default activity. Another thing that you need to select Kotlin as the primary language for the project.
Step 2. SQLite Database Creation
Make a new kotlin class and give it a name like DatabaseHelper.kt
Write down the following source lines in DatabaseHelper.kt
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 allStudentsList: ArrayList<String>
get() {
val studentsArrayList = ArrayList<String>()
var name = ""
val selectQuery = "SELECT * FROM $TABLE_STUDENTS"
val db = this.readableDatabase
val c = db.rawQuery(selectQuery, null)
if (c.moveToFirst()) {
do {
name = c.getString(c.getColumnIndex(KEY_FIRSTNAME))
studentsArrayList.add(name)
} while (c.moveToNext())
Log.d("array", studentsArrayList.toString())
}
return studentsArrayList
}
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_STUDENTS'")
onCreate(db)
}
fun addStudentDetail(student: String): Long {
val db = this.writableDatabase
// Creating content values
val values = ContentValues()
values.put(KEY_FIRSTNAME, student)
// insert row in students table
return db.insert(TABLE_STUDENTS, null, values)
}
companion object {
var DATABASE_NAME = "student_database"
private val DATABASE_VERSION = 1
private val TABLE_STUDENTS = "students"
private val KEY_ID = "id"
private val KEY_FIRSTNAME = "name"
/*CREATE TABLE students ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, phone_number TEXT......);*/
private val CREATE_TABLE_STUDENTS = ("CREATE TABLE "
+ TABLE_STUDENTS + "(" + KEY_ID
+ " INTEGER PRIMARY KEY AUTOINCREMENT," + KEY_FIRSTNAME + " TEXT );")
}
}
Now let us see what above lines is saying to the compiler.
First of all, checkout the following
companion object {
var DATABASE_NAME = "student_database"
private val DATABASE_VERSION = 1
private val TABLE_STUDENTS = "students"
private val KEY_ID = "id"
private val KEY_FIRSTNAME = "name"
/*CREATE TABLE students ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, phone_number TEXT......);*/
private val CREATE_TABLE_STUDENTS = ("CREATE TABLE "
+ TABLE_STUDENTS + "(" + KEY_ID
+ " INTEGER PRIMARY KEY AUTOINCREMENT," + KEY_FIRSTNAME + " TEXT );")
}
First line is defining the variable DATABASE_NAME. This variable contains the name of the sqlite table.
Second line is for DATABASE_VERSION variable. This variable defines the current version of the database.
Then three variables TABLE_STUDENTS, KEY_ID, KEY_FIRSTNAME are defining the names of the columns of the sqlite table.
A variable CREATE_TABLE_STUDENTS is making an sqlite query to make a new table with three columns.
Inside onCreate() method, compiler will execute the query to construct a table.
Below is the code block for onCreate() method.
override fun onCreate(db: SQLiteDatabase) {
db.execSQL(CREATE_TABLE_STUDENTS)
}
In this example, we are dealing with just one table, that’s why there is only line in the onCreate() method.
If you want to create more than one table then you need to add multiple lines.
Now see the following snippet of onUpgrade() method.
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS '" + TABLE_STUDENTS + "'");
onCreate(db);
}
When compiler execute the above code, it will drop all the tables from database first and then it will recreate all the tables.
onCreate() method is called within onUpgrade() method to create tables.
In following scenarios, you need to upgrade DATABASE_VERSION number in increment order.
- When you add, update or delete any column of any table in the whole database.
- If you update any column name of any table.
- After you add, update or delete any table.
- Update the table name and increase DATABASE_VERSION.
- onUpgrade() method is called every time When you update the DATABASE_VERSION in the increment order.
Now read the below code lines
val allStudentsList: ArrayList<String>
get() {
val studentsArrayList = ArrayList<String>()
var name = ""
val selectQuery = "SELECT * FROM $TABLE_STUDENTS"
val db = this.readableDatabase
val c = db.rawQuery(selectQuery, null)
if (c.moveToFirst()) {
do {
name = c.getString(c.getColumnIndex(KEY_FIRSTNAME))
studentsArrayList.add(name)
} while (c.moveToNext())
Log.d("array", studentsArrayList.toString())
}
return studentsArrayList
}
Above code will fetch all the rows from the table.
It will save all these rows into an arraylist whose name is allStudentsList.
Now focus on the below code
fun addStudentDetail(student: String): Long {
val db = this.writableDatabase
// Creating content values
val values = ContentValues()
values.put(KEY_FIRSTNAME, student)
// insert row in students table
return db.insert(TABLE_STUDENTS, null, values)
}
This function is getting the string variable into the parameter. This parameter is the name of the student.
Compiler will use this string parameter and it will insert it into the database table.
Step 3. Making Main Activity
Open up your activity_main.xml and add the following in it
<?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 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"/>
<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 Names from SQLite"/>
<TextView
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:id="@+id/tvnames"
android:layout_marginLeft="10dp"
android:textColor="#fff"
android:textSize="20sp"
android:layout_marginTop="10dp"
android:text="Enter Name to store in SQLite" />
</LinearLayout>
There are two text views, two buttons and one edit text is there.
In the edit text, user will enter the name of the student.
There are two buttons : Store and Get All Names from SQLite. When the user clicks store button, compiler will add the name of the students into the SQLite database.
On the click of Get All Names from SQLite button, system will fetch all the names from the database and it will print them in one text view.
Now in your MainActivity.kt file, add the following lines
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.TextView
import android.widget.Toast
import java.util.ArrayList
class MainActivity : AppCompatActivity() {
private var btnStore: Button? = null
private var btnGetall: Button? = null
private var etname: EditText? = null
private var databaseHelper: DatabaseHelper? = null
private var tvnames: TextView? = null
private var arrayList: ArrayList<String>? = null
override fun onCreate(savedInstanceState: Bundle?) {
super.onCreate(savedInstanceState)
setContentView(R.layout.activity_main)
databaseHelper = DatabaseHelper(this)
tvnames = findViewById(R.id.tvnames) as TextView
btnStore = findViewById(R.id.btnstore) as Button
btnGetall = findViewById(R.id.btnget) as Button
etname = findViewById(R.id.etname) as EditText
btnStore!!.setOnClickListener {
databaseHelper!!.addStudentDetail(etname!!.text.toString())
etname!!.setText("")
Toast.makeText(this@MainActivity, "Stored Successfully!", Toast.LENGTH_SHORT).show()
}
btnGetall!!.setOnClickListener {
arrayList = databaseHelper!!.allStudentsList
tvnames!!.text = ""
for (i in arrayList!!.indices) {
tvnames!!.text = tvnames!!.text.toString() + ", " + arrayList!![i]
}
}
}
}
Deep Main Activity
Let us see the work flow of main activity.
First of all, focus on the below
private var btnStore: Button? = null
private var btnGetall: Button? = null
private var etname: EditText? = null
private var databaseHelper: DatabaseHelper? = null
private var tvnames: TextView? = null
private var arrayList: ArrayList<String>? = null
First two lines are making the objects of a button class.
Third line is for edit text object. Fourth line is giving us the object of the database helper class.
Fifth line is for text view object and last line is the arraylist with the string variables.
Compiler will execute the below lines when the user clicks the Store button.
btnStore!!.setOnClickListener {
databaseHelper!!.addStudentDetail(etname!!.text.toString())
etname!!.setText("")
Toast.makeText(this@MainActivity, "Stored Successfully!", Toast.LENGTH_SHORT).show()
}
Compiler call the addStudentDetail() method using the database helper object. Here, it has the string parameter.
For this string parameter, compiler will get the text from the edit text. User have entered name in this edit text thus, compiler will fetch the name and will send it in the parameter.
addStudentDetail() method will add this name in to the SQLite database.
Now, follow the following code snippet
btnGetall!!.setOnClickListener {
arrayList = databaseHelper!!.allStudentsList
tvnames!!.text = ""
for (i in arrayList!!.indices) {
tvnames!!.text = tvnames!!.text.toString() + ", " + arrayList!![i]
}
}
Compiler will go through the above code when the user clicks Get All Names from SQLite button.
It will simply call allStudentsList() method from the Database Helper class. This method will return the string arraylist which will be stored in the variable named arraylist.
Then compiler will execute one for loop. During the every iteration of for loop, it will fetch the one name from the arraylist and it will print it on the text view.
Thus, after the execution of the for loop, all the names stored in the database will be printed on the text view.