Kotlin Android SQLite 

– SQLite is an open source database based on SQL

language. Android has SQLite database implementation by default.
Kotlin Android SQLite Example Application
Koltin Android SQLite Example Application : In this Android Tutorial, we shall learn
how to use SQLite database in your Android Application with an example
using Kotlin Programming language.

We shall look into typical classes that we use for using SQLite database.

DB Contract Contains schema (table name and column names) for program
Class understandability.

DB Helper This class contains methods that do database operations like insert,
Class select, update, delete, etc.

Model Data
Class Used to carry objects (rows of DB table)

This is class file of your Activity from which you call DB Helper’s
Activity Class methods for database activities

Following are the details of the Android Application we created for this
example.

Application Name SQLiteTutorial

Company name tutorialkart.com

Minimum SDK API 21: Android 5.0 (Lollipop)

Activity Empty Activity

You may keep rest of the values as default and create Android Application with
Kotlin Support.
activity_main.xml

1 <?xml version="1.0" encoding="utf-8"?>

2 <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
3     xmlns:app="http://schemas.android.com/apk/res-auto"
4     xmlns:tools="http://schemas.android.com/tools"
5     android:layout_width="match_parent"
6     android:layout_height="wrap_content"
7     android:orientation="vertical"
8     android:gravity="center"
9     tools:context="com.tutorialkart.sqlitetutorial.MainActivity">
10  
11     <TextView
12         android:layout_width="wrap_content"
13         android:layout_height="wrap_content"
14         android:text="SQLite Tutorial - User Management"
15         android:textSize="20dp"
16         android:padding="10dp" />
17  
18     <LinearLayout
19         android:layout_width="match_parent"
20         android:layout_height="wrap_content"
21         android:orientation="vertical">
22         <EditText
23             android:id="@+id/edittext_userid"
24             android:hint="User ID"
25             android:gravity="center"
26             android:layout_width="match_parent"
27             android:layout_height="wrap_content" />
28         <EditText
29             android:id="@+id/edittext_name"
30             android:hint="User Name"
31             android:gravity="center"
32             android:layout_width="match_parent"
33             android:layout_height="wrap_content" />
34         <EditText
35             android:id="@+id/edittext_age"
36             android:hint="User Age"
37             android:gravity="center"
38             android:layout_width="match_parent"
39             android:layout_height="wrap_content" />
40     </LinearLayout>
41  
42     <LinearLayout
43         android:layout_width="match_parent"
44         android:layout_height="wrap_content"
45         android:orientation="horizontal">
46         <Button
47             android:id="@+id/button_add_user"
48             android:layout_width="wrap_content"
49             android:layout_height="wrap_content"
50             android:layout_weight="1"
51             android:onClick="addUser"
52             android:text="Add" />
53  
54         <Button
55             android:id="@+id/button_delete_user"
56             android:layout_width="wrap_content"
57             android:layout_height="wrap_content"
58             android:layout_weight="1"
59             android:onClick="deleteUser"
60             android:text="Delete" />
61  
62         <Button
63             android:id="@+id/button_show_all"
64             android:layout_width="wrap_content"
65             android:layout_height="wrap_content"
66             android:layout_weight="1"
67             android:onClick="showAllUsers"
68             android:text="Show All" />
69     </LinearLayout>
70     <TextView
71         android:id="@+id/textview_result"
72         android:layout_width="match_parent"
73         android:layout_height="wrap_content" />
74     <LinearLayout
75         android:id="@+id/ll_entries"
76         android:padding="15dp"
77         android:orientation="vertical"
78         android:layout_width="match_parent"
79         android:layout_height="wrap_content"></LinearLayout>
80 </LinearLayout>
UserModel.kt

1 package com.tutorialkart.sqlitetutorial
2 
3 class UserModel(val userid: String, val name: String, val age: String)
DBContract.kt

1 package com.tutorialkart.sqlitetutorial
2  
3 import android.provider.BaseColumns
4  
5 object DBContract {
6  
7     /* Inner class that defines the table contents */
8     class UserEntry : BaseColumns {
9         companion object {
10             val TABLE_NAME = "users"
11             val COLUMN_USER_ID = "userid"
12             val COLUMN_NAME = "name"
13             val COLUMN_AGE = "age"
14         }
15     }
16 }
UsersDBHelper.kt

1 package com.tutorialkart.sqlitetutorial
2  
3 import android.content.ContentValues
4 import android.content.Context
5 import android.database.Cursor
6 import android.database.sqlite.SQLiteConstraintException
7 import android.database.sqlite.SQLiteDatabase
8 import android.database.sqlite.SQLiteException
9 import android.database.sqlite.SQLiteOpenHelper
10  
11 import java.util.ArrayList
12  
13 class UsersDBHelper(context: Context) : SQLiteOpenHelper(context, DATABASE_NAME, null,
14 DATABASE_VERSION) {
15     override fun onCreate(db: SQLiteDatabase) {
16         db.execSQL(SQL_CREATE_ENTRIES)
17     }
18  
19     override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
20         // This database is only a cache for online data, so its upgrade policy is
21         // to simply to discard the data and start over
22         db.execSQL(SQL_DELETE_ENTRIES)
23         onCreate(db)
24     }
25  
26     override fun onDowngrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
27         onUpgrade(db, oldVersion, newVersion)
28     }
29  
30     @Throws(SQLiteConstraintException::class)
31     fun insertUser(user: UserModel): Boolean {
32         // Gets the data repository in write mode
33         val db = writableDatabase
34  
35         // Create a new map of values, where column names are the keys
36         val values = ContentValues()
37         values.put(DBContract.UserEntry.COLUMN_USER_ID, user.userid)
38         values.put(DBContract.UserEntry.COLUMN_NAME, user.name)
39         values.put(DBContract.UserEntry.COLUMN_AGE, user.age)
40  
41         // Insert the new row, returning the primary key value of the new row
42         val newRowId = db.insert(DBContract.UserEntry.TABLE_NAME, null, values)
43  
44         return true
45     }
46  
47     @Throws(SQLiteConstraintException::class)
48     fun deleteUser(userid: String): Boolean {
49         // Gets the data repository in write mode
50         val db = writableDatabase
51         // Define 'where' part of query.
52         val selection = DBContract.UserEntry.COLUMN_USER_ID + " LIKE ?"
53         // Specify arguments in placeholder order.
54         val selectionArgs = arrayOf(userid)
55         // Issue SQL statement.
56         db.delete(DBContract.UserEntry.TABLE_NAME, selection, selectionArgs)
57  
58         return true
59     }
60  
61     fun readUser(userid: String): ArrayList<UserModel> {
62         val users = ArrayList<UserModel>()
63         val db = writableDatabase
64         var cursor: Cursor? = null
65         try {
66             cursor = db.rawQuery("select * from " + DBContract.UserEntry.TABLE_NAME + " WHERE " +
67 DBContract.UserEntry.COLUMN_USER_ID + "='" + userid + "'", null)
68         } catch (e: SQLiteException) {
69             // if table not yet present, create it
70             db.execSQL(SQL_CREATE_ENTRIES)
71             return ArrayList()
72         }
73  
74         var name: String
75         var age: String
76         if (cursor!!.moveToFirst()) {
77             while (cursor.isAfterLast == false) {
78                 name = cursor.getString(cursor.getColumnIndex(DBContract.UserEntry.COLUMN_NAME))
79                 age = cursor.getString(cursor.getColumnIndex(DBContract.UserEntry.COLUMN_AGE))
80  
81                 users.add(UserModel(userid, name, age))
82                 cursor.moveToNext()
83             }
84         }
85         return users
86     }
87  
88     fun readAllUsers(): ArrayList<UserModel> {
89         val users = ArrayList<UserModel>()
90         val db = writableDatabase
91         var cursor: Cursor? = null
92         try {
93             cursor = db.rawQuery("select * from " + DBContract.UserEntry.TABLE_NAME, null)
94         } catch (e: SQLiteException) {
95             db.execSQL(SQL_CREATE_ENTRIES)
96             return ArrayList()
97         }
98  
99         var userid: String
100         var name: String
101         var age: String
102         if (cursor!!.moveToFirst()) {
103             while (cursor.isAfterLast == false) {
104                 userid = cursor.getString(cursor.getColumnIndex(DBContract.UserEntry.COLUMN_USER_ID))
105                 name = cursor.getString(cursor.getColumnIndex(DBContract.UserEntry.COLUMN_NAME))
106                 age = cursor.getString(cursor.getColumnIndex(DBContract.UserEntry.COLUMN_AGE))
107  
                users.add(UserModel(userid, name, age))
108                 cursor.moveToNext()
109             }
110         }
111         return users
112     }
113  
114     companion object {
115         // If you change the database schema, you must increment the database version.
116         val DATABASE_VERSION = 1
117         val DATABASE_NAME = "FeedReader.db"
118  
119         private val SQL_CREATE_ENTRIES =
120                 "CREATE TABLE " + DBContract.UserEntry.TABLE_NAME + " (" +
121                         DBContract.UserEntry.COLUMN_USER_ID + " TEXT PRIMARY KEY," +
122                         DBContract.UserEntry.COLUMN_NAME + " TEXT," +
123                         DBContract.UserEntry.COLUMN_AGE + " TEXT)"
124  
125         private val SQL_DELETE_ENTRIES = "DROP TABLE IF EXISTS " + DBContract.UserEntry.TABLE_NAME
126     }
127  
}
MainAcitivity.kt

1 package com.tutorialkart.sqlitetutorial
2  
3 import android.support.v7.app.AppCompatActivity
4 import android.os.Bundle
5 import android.view.View
6 import android.widget.TextView
7 import kotlinx.android.synthetic.main.activity_main.*
8  
9 class MainActivity : AppCompatActivity() {
10  
11     lateinit var usersDBHelper : UsersDBHelper
12  
13     override fun onCreate(savedInstanceState: Bundle?) {
14         super.onCreate(savedInstanceState)
15         setContentView(R.layout.activity_main)
16  
17         usersDBHelper = UsersDBHelper(this)
18     }
19  
20     fun addUser(v:View){
21         var userid = this.edittext_userid.text.toString()
22         var name = this.edittext_name.text.toString()
23         var age = this.edittext_age.text.toString()
24         var result = usersDBHelper.insertUser(UserModel(userid = userid,name = name,age = age))
25         //clear all edittext s
26         this.edittext_age.setText("")
27         this.edittext_name.setText("")
28         this.edittext_userid.setText("")
29         this.textview_result.text = "Added user : "+result
30         this.ll_entries.removeAllViews()
31     }
32  
33     fun deleteUser(v:View){
34         var userid = this.edittext_userid.text.toString()
35         val result = usersDBHelper.deleteUser(userid)
36         this.textview_result.text = "Deleted user : "+result
37         this.ll_entries.removeAllViews()
38     }
39  
40     fun showAllUsers(v:View){
41         var users = usersDBHelper.readAllUsers()
42         this.ll_entries.removeAllViews()
43         users.forEach {
44             var tv_user = TextView(this)
45             tv_user.textSize = 30F
46             tv_user.text = it.name.toString() + " - " + it.age.toString()
47             this.ll_entries.addView(tv_user)
48         }
49         this.textview_result.text = "Fetched " + users.size + " users"
50     }
51 }

Conclusion :
In this tutorial – Kotlin Android SQLite, we have learnt how to use SQLite
database (CRUD Operations) in Android Application, and necessary classes
for maintenance, with an Example Android Application