Suppose you want to create an application like Android Note App or ToDo Task app that need to save data into your device, so that later you can access them without internet connection. You may insert new data, search data, update or delete them. In short, an offline database functionality is needed for your application. Different database can be implemented for those purposes, SQLite is one of them. SQLite database supports all the relational database features. In order to access this database, you don’t need to establish any kind of connections for it like JDBC, ODBC etc. In today’s tutorial, you’ll learn implementing SQLite database in your android app using Kotlin.
Android SQLite
Android SQLite is a very lightweight database which comes with Android OS. Android SQLite combines a clean SQL interface with a very small memory footprint and decent speed. For Android, SQLite is “baked into” the Android runtime, so every Android application can create its own SQLite databases.
Android SQLite native API is not JDBC, as JDBC might be too much overhead for a memory-limited smartphone. Once a database is created successfully its located in data/data//databases/ accessible from Android Device Monitor.
SQLite is a typical relational database, containing tables (which consists of rows and columns), indexes etc. We can create our own tables to hold the data accordingly. This structure is referred to as a schema.
The above video demonstrate the app we’ll be building today while learning SQLite using Kotlin. You can directly download the full Android Studio Project below, but I would suggest to read the article first for your better understanding.
Download SQLite Tutorial Source Code
Download the Android Studio source code of Save Data into SQLite Database using Kotlin in AndroidSize: 147.25 Kb
Create a database using SQLiteOpenHelper
First of all, For this Android SQLite tutorial, we’ll create a class that will contain all database related functionality like creating the database for first time, inserting new data, updating existing data or deleting specific data. But before that let me show you how are database table will look like.
id | name | age | |
1 | Someone’s Name | 20 | user1@email.com |
2 | Someone’s Name | 25 | user2@email.com |
So I guess, you understood how our data will be saved in a Table. We’ll create our database, then create our table “users” with these 4 attributes or columns:
- id – It will contain integer values and it will increment automatically.
- name – It will contain text values.
- age – It will contain text values. You may also make it’s data type integer.
- email – It will also contain text values.
So create a new Kotlin file named “DBHelper.kt” and place the below codes. I’ll break them later.
package com.frsarker.sqlitetutorial
import android.content.ContentValues
import android.content.Context
import android.database.Cursor
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper
class DBHelper(context: Context, factory: SQLiteDatabase.CursorFactory?) :
SQLiteOpenHelper(context, DATABASE_NAME, factory, DATABASE_VERSION) {
override fun onCreate(db: SQLiteDatabase) {
db.execSQL(
"CREATE TABLE $TABLE_NAME " +
"($COLUMN_ID INTEGER PRIMARY KEY, $COLUMN_NAME TEXT, $COLUMN_AGE TEXT, $COLUMN_EMAIL TEXT)"
)
}
override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
db.execSQL("DROP TABLE IF EXISTS $TABLE_NAME")
onCreate(db)
}
fun insertRow(name: String, age:String, email: String) {
val values = ContentValues()
values.put(COLUMN_NAME, name)
values.put(COLUMN_AGE, age)
values.put(COLUMN_EMAIL, email)
val db = this.writableDatabase
db.insert(TABLE_NAME, null, values)
db.close()
}
fun updateRow(row_id: String, name: String, age:String, email: String) {
val values = ContentValues()
values.put(COLUMN_NAME, name)
values.put(COLUMN_AGE, age)
values.put(COLUMN_EMAIL, email)
val db = this.writableDatabase
db.update(TABLE_NAME, values, "$COLUMN_ID = ?", arrayOf(row_id))
db.close()
}
fun deleteRow(row_id: String) {
val db = this.writableDatabase
db.delete(TABLE_NAME, "$COLUMN_ID = ?", arrayOf(row_id))
db.close()
}
fun getAllRow(): Cursor? {
val db = this.readableDatabase
return db.rawQuery("SELECT * FROM $TABLE_NAME", null)
}
companion object {
const val DATABASE_VERSION = 1
const val DATABASE_NAME = "myDBfile.db"
const val TABLE_NAME = "users"
const val COLUMN_ID = "id"
const val COLUMN_NAME = "name"
const val COLUMN_AGE = "age"
const val COLUMN_EMAIL = "email"
}
}
So now lets break the code one by one. First we declared all our necessary values like database name, database version, table name, column’s name as
companion object {
const val DATABASE_VERSION = 1
const val DATABASE_NAME = "myDBfile.db"
const val TABLE_NAME = "users"
const val COLUMN_ID = "id"
const val COLUMN_NAME = "name"
const val COLUMN_AGE = "age"
const val COLUMN_EMAIL = "email"
}
The onCreate() method is called only the first time of execution and it creates the database table using SQL query
override fun onCreate(db: SQLiteDatabase) {
db.execSQL(
"CREATE TABLE $TABLE_NAME " +
"($COLUMN_ID INTEGER PRIMARY KEY, $COLUMN_NAME TEXT, $COLUMN_AGE TEXT, $COLUMN_EMAIL TEXT)"
)
}
The onUpgrade() method will be called if you change the database version. Cause in the next version of database you may add one or more column in the table, So only then, the table need to be deleted and create again.
override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
db.execSQL("DROP TABLE IF EXISTS $TABLE_NAME")
onCreate(db)
}
The insertRow() method will be used for inserting new user’s information into the table using db.insert()
fun insertRow(name: String, age:String, email: String) {
val values = ContentValues()
values.put(COLUMN_NAME, name)
values.put(COLUMN_AGE, age)
values.put(COLUMN_EMAIL, email)
val db = this.writableDatabase
db.insert(TABLE_NAME, null, values)
db.close()
}
The updateRow() will be used for modifying any existing information. And deleteRow() will be used for up modifying and deleting existing information respectively.
Displaying all data inside ListView
As I mentioned before that MainActivity.kt will be used for displaying all saved user’s information in a list. That meas we’ll be pupulating ListView using a custom adapter. This is our MainActivity.kt
package com.frsarker.sqlitetutorial
import android.content.Intent
import android.os.Bundle
import android.view.View
import android.widget.ListView
import androidx.appcompat.app.AppCompatActivity
class MainActivity : AppCompatActivity() {
val dbHandler = DBHelper(this, null)
var dataList = ArrayList<HashMap<String, String>>()
override fun onCreate(savedInstanceState: Bundle?) {
super.onCreate(savedInstanceState)
setContentView(R.layout.activity_main)
}
fun loadIntoList(){
dataList.clear()
val cursor = dbHandler.getAllRow()
cursor!!.moveToFirst()
while (!cursor.isAfterLast) {
val map = HashMap<String, String>()
map["id"] = cursor.getString(cursor.getColumnIndex(DBHelper.COLUMN_ID))
map["name"] = cursor.getString(cursor.getColumnIndex(DBHelper.COLUMN_NAME))
map["age"] = cursor.getString(cursor.getColumnIndex(DBHelper.COLUMN_AGE))
map["email"] = cursor.getString(cursor.getColumnIndex(DBHelper.COLUMN_EMAIL))
dataList.add(map)
cursor.moveToNext()
}
findViewById<ListView>(R.id.listView).adapter = CustomAdapter(this@MainActivity, dataList)
findViewById<ListView>(R.id.listView).setOnItemClickListener { _, _, i, _ ->
val intent = Intent(this, DetailsActivity::class.java)
intent.putExtra("id", dataList[+i]["id"])
intent.putExtra("name", dataList[+i]["name"])
intent.putExtra("age", dataList[+i]["age"])
intent.putExtra("email", dataList[+i]["email"])
startActivity(intent)
}
}
fun fabClicked(v:View){
val intent = Intent(this, DetailsActivity::class.java)
startActivity(intent)
}
public override fun onResume() {
super.onResume()
loadIntoList()
}
}
Create a new kotlin file named “CustomAdapter.kt“
package com.frsarker.sqlitetutorial
import android.content.Context
import android.view.LayoutInflater
import android.view.View
import android.view.ViewGroup
import android.widget.BaseAdapter
import android.widget.TextView
class CustomAdapter(private val context: Context,
private val dataList: ArrayList<HashMap<String, String>>) : BaseAdapter() {
private val inflater: LayoutInflater = this.context.getSystemService(Context.LAYOUT_INFLATER_SERVICE) as LayoutInflater
override fun getCount(): Int { return dataList.size }
override fun getItem(position: Int): Int { return position }
override fun getItemId(position: Int): Long { return position.toLong() }
override fun getView(position: Int, convertView: View?, parent: ViewGroup): View {
var dataitem = dataList[position]
val rowView = inflater.inflate(R.layout.list_row, parent, false)
rowView.findViewById<TextView>(R.id.row_name).text = dataitem["name"]
rowView.findViewById<TextView>(R.id.row_age).text = "Age: " + dataitem["age"]
rowView.findViewById<TextView>(R.id.row_email).text = "Email: " + dataitem["email"]
rowView.tag = position
return rowView
}
}
Layout resource file for CustomAdapter “list_row.xml“
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:orientation="vertical"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:padding="20dp"
android:gravity="center_vertical">
<TextView
android:id="@+id/row_name"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="Name"
android:textStyle="bold"
android:textSize="22dp"/>
<LinearLayout android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal">
<TextView
android:id="@+id/row_age"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="Age"/>
<TextView
android:id="@+id/row_email"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_marginLeft="15dp"
android:text="City"/>
</LinearLayout>
</LinearLayout>
And now our activity_main.xml
<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout
xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="match_parent" android:layout_height="match_parent"
xmlns:app="http://schemas.android.com/apk/res-auto"
android:orientation="vertical">
<ListView
android:id="@+id/listView"
android:layout_width="match_parent"
android:layout_height="match_parent"/>
<com.google.android.material.floatingactionbutton.FloatingActionButton
style="@style/Widget.MaterialComponents.FloatingActionButton"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignParentRight="true"
android:layout_alignParentBottom="true"
android:layout_marginRight="30dp"
android:layout_marginBottom="50dp"
android:onClick="fabClicked"
app:srcCompat="@android:drawable/ic_input_add"
app:tint="#FFFFFF"
app:backgroundTint="#E91E63"/>
</RelativeLayout>
You can see that I’ve used a FloatingActionButton, for using a FloatingActionButton you’ll need to add this dependency
implementation 'com.google.android.material:material:1.0.0'
The time I’m writing this article, version 1.0.0 is the stable version of this library. You should use the latest library always. You can find the latest library version from MVN Repository.
Insert, Update or Delete Data
In the flowchart I’ve given before, you know that we’ll be using DetailsActivity.kt for both inserting a new user’s information and updating or deleting them.
Create a new Activity called DetailsActivity.kt
package com.frsarker.sqlitetutorial
import androidx.appcompat.app.AppCompatActivity
import android.os.Bundle
import android.view.View
import android.widget.Button
import android.widget.EditText
import android.widget.Toast
class DetailsActivity : AppCompatActivity() {
private val dbHandler = DBHelper(this, null)
lateinit var nameEditText:EditText
lateinit var ageEditText:EditText
lateinit var emailEditText:EditText
lateinit var modifyId:String
override fun onCreate(savedInstanceState: Bundle?) {
super.onCreate(savedInstanceState)
setContentView(R.layout.activity_details)
nameEditText = findViewById(R.id.name)
ageEditText = findViewById(R.id.age)
emailEditText = findViewById(R.id.email)
/* Check if activity opened from List Item Click */
if(intent.hasExtra("id")){
modifyId = intent.getStringExtra("id")
nameEditText.setText(intent.getStringExtra("name"))
ageEditText.setText(intent.getStringExtra("age"))
emailEditText.setText(intent.getStringExtra("email"))
findViewById<Button>(R.id.btnAdd).visibility = View.GONE
}else{
findViewById<Button>(R.id.btnUpdate).visibility = View.GONE
findViewById<Button>(R.id.btnDelete).visibility = View.GONE
}
}
fun add(v:View){
val name = nameEditText.text.toString()
val age = ageEditText.text.toString()
val email = emailEditText.text.toString()
dbHandler.insertRow(name, age, email)
Toast.makeText(this, "Data Addeded", Toast.LENGTH_SHORT).show()
finish()
}
fun update(v:View){
val name = nameEditText.text.toString()
val age = ageEditText.text.toString()
val email = emailEditText.text.toString()
dbHandler.updateRow(modifyId, name, age, email)
Toast.makeText(this, "Data Updated", Toast.LENGTH_SHORT).show()
finish()
}
fun delete(v:View){
dbHandler.deleteRow(modifyId)
Toast.makeText(this, "Data Deleted", Toast.LENGTH_SHORT).show()
finish()
}
}
You can see that if the activity opens by intent with extras then we make the insert button invisible otherwise the update and delete button is invisible.
Other things I’m not explaining because they are just calling methods of DBHelper class.
and the layout resource file for this activity activity_details.xml
<?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="15dp">
<EditText
android:id="@+id/name"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="Name"/>
<EditText
android:id="@+id/age"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="Age"
android:inputType="number"/>
<EditText
android:id="@+id/email"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="Email"/>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal">
<Button
android:id="@+id/btnAdd"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="Add"
android:onClick="add"/>
<Button
android:id="@+id/btnUpdate"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="Update"
android:onClick="update"/>
<Button
android:id="@+id/btnDelete"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="Delete"
android:onClick="delete"/>
</LinearLayout>
</LinearLayout>
This is how my AndroidManifest.xml looks now.
<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
package="com.frsarker.sqlitetutorial">
<application
android:allowBackup="true"
android:icon="@mipmap/ic_launcher"
android:label="@string/app_name"
android:roundIcon="@mipmap/ic_launcher_round"
android:supportsRtl="true"
android:theme="@style/AppTheme">
<activity android:name=".MainActivity">
<intent-filter>
<action android:name="android.intent.action.MAIN" />
<category android:name="android.intent.category.LAUNCHER" />
</intent-filter>
</activity>
<activity android:name=".DetailsActivity"/>
</application>
</manifest>
Now Run the App and start with adding new user’s information into SQLite database by clicking the FAB button.