Kotlin Android SQLite Tutorial
Kotlin Android SQLite – SQLite is an open source database based on SQL language. Android has SQLite database implementation by default.
In this tutorial, we will learn how to do basic SQLite operations like inserting a row into table, reading rows from table, updating rows in table and deleting rows.
Example – Kotlin Android SQLite
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 Class | Contains schema (table name and column names) for program understandability. |
| DB Helper Class | This class contains methods that do database operations like insert, select, update, delete, etc. |
| Model Data Class | Used to carry objects (rows of DB table) |
| Activity Class | This is class file of your Activity from which you call DB Helper’s 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
</>
Copy
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout 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="wrap_content"
android:orientation="vertical"
android:gravity="center"
tools:context="com.tutorialkart.sqlitetutorial.MainActivity">
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="SQLite Tutorial - User Management"
android:textSize="20dp"
android:padding="10dp" />
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="vertical">
<EditText
android:id="@+id/edittext_userid"
android:hint="User ID"
android:gravity="center"
android:layout_width="match_parent"
android:layout_height="wrap_content" />
<EditText
android:id="@+id/edittext_name"
android:hint="User Name"
android:gravity="center"
android:layout_width="match_parent"
android:layout_height="wrap_content" />
<EditText
android:id="@+id/edittext_age"
android:hint="User Age"
android:gravity="center"
android:layout_width="match_parent"
android:layout_height="wrap_content" />
</LinearLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal">
<Button
android:id="@+id/button_add_user"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_weight="1"
android:onClick="addUser"
android:text="Add" />
<Button
android:id="@+id/button_delete_user"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_weight="1"
android:onClick="deleteUser"
android:text="Delete" />
<Button
android:id="@+id/button_show_all"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_weight="1"
android:onClick="showAllUsers"
android:text="Show All" />
</LinearLayout>
<TextView
android:id="@+id/textview_result"
android:layout_width="match_parent"
android:layout_height="wrap_content" />
<LinearLayout
android:id="@+id/ll_entries"
android:padding="15dp"
android:orientation="vertical"
android:layout_width="match_parent"
android:layout_height="wrap_content"></LinearLayout>
</LinearLayout>UserModel.kt
</>
Copy
package com.tutorialkart.sqlitetutorial
class UserModel(val userid: String, val name: String, val age: String)DBContract.kt
</>
Copy
package com.tutorialkart.sqlitetutorial
import android.provider.BaseColumns
object DBContract {
/* Inner class that defines the table contents */
class UserEntry : BaseColumns {
companion object {
val TABLE_NAME = "users"
val COLUMN_USER_ID = "userid"
val COLUMN_NAME = "name"
val COLUMN_AGE = "age"
}
}
}
UsersDBHelper.kt
</>
Copy
package com.tutorialkart.sqlitetutorial
import android.content.ContentValues
import android.content.Context
import android.database.Cursor
import android.database.sqlite.SQLiteConstraintException
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteException
import android.database.sqlite.SQLiteOpenHelper
import java.util.ArrayList
class UsersDBHelper(context: Context) : SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {
override fun onCreate(db: SQLiteDatabase) {
db.execSQL(SQL_CREATE_ENTRIES)
}
override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
// This database is only a cache for online data, so its upgrade policy is
// to simply to discard the data and start over
db.execSQL(SQL_DELETE_ENTRIES)
onCreate(db)
}
override fun onDowngrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
onUpgrade(db, oldVersion, newVersion)
}
@Throws(SQLiteConstraintException::class)
fun insertUser(user: UserModel): Boolean {
// Gets the data repository in write mode
val db = writableDatabase
// Create a new map of values, where column names are the keys
val values = ContentValues()
values.put(DBContract.UserEntry.COLUMN_USER_ID, user.userid)
values.put(DBContract.UserEntry.COLUMN_NAME, user.name)
values.put(DBContract.UserEntry.COLUMN_AGE, user.age)
// Insert the new row, returning the primary key value of the new row
val newRowId = db.insert(DBContract.UserEntry.TABLE_NAME, null, values)
return true
}
@Throws(SQLiteConstraintException::class)
fun deleteUser(userid: String): Boolean {
// Gets the data repository in write mode
val db = writableDatabase
// Define 'where' part of query.
val selection = DBContract.UserEntry.COLUMN_USER_ID + " LIKE ?"
// Specify arguments in placeholder order.
val selectionArgs = arrayOf(userid)
// Issue SQL statement.
db.delete(DBContract.UserEntry.TABLE_NAME, selection, selectionArgs)
return true
}
fun readUser(userid: String): ArrayList<UserModel> {
val users = ArrayList<UserModel>()
val db = writableDatabase
var cursor: Cursor? = null
try {
cursor = db.rawQuery("select * from " + DBContract.UserEntry.TABLE_NAME + " WHERE " + DBContract.UserEntry.COLUMN_USER_ID + "='" + userid + "'", null)
} catch (e: SQLiteException) {
// if table not yet present, create it
db.execSQL(SQL_CREATE_ENTRIES)
return ArrayList()
}
var name: String
var age: String
if (cursor!!.moveToFirst()) {
while (cursor.isAfterLast == false) {
name = cursor.getString(cursor.getColumnIndex(DBContract.UserEntry.COLUMN_NAME))
age = cursor.getString(cursor.getColumnIndex(DBContract.UserEntry.COLUMN_AGE))
users.add(UserModel(userid, name, age))
cursor.moveToNext()
}
}
return users
}
fun readAllUsers(): ArrayList<UserModel> {
val users = ArrayList<UserModel>()
val db = writableDatabase
var cursor: Cursor? = null
try {
cursor = db.rawQuery("select * from " + DBContract.UserEntry.TABLE_NAME, null)
} catch (e: SQLiteException) {
db.execSQL(SQL_CREATE_ENTRIES)
return ArrayList()
}
var userid: String
var name: String
var age: String
if (cursor!!.moveToFirst()) {
while (cursor.isAfterLast == false) {
userid = cursor.getString(cursor.getColumnIndex(DBContract.UserEntry.COLUMN_USER_ID))
name = cursor.getString(cursor.getColumnIndex(DBContract.UserEntry.COLUMN_NAME))
age = cursor.getString(cursor.getColumnIndex(DBContract.UserEntry.COLUMN_AGE))
users.add(UserModel(userid, name, age))
cursor.moveToNext()
}
}
return users
}
companion object {
// If you change the database schema, you must increment the database version.
val DATABASE_VERSION = 1
val DATABASE_NAME = "FeedReader.db"
private val SQL_CREATE_ENTRIES =
"CREATE TABLE " + DBContract.UserEntry.TABLE_NAME + " (" +
DBContract.UserEntry.COLUMN_USER_ID + " TEXT PRIMARY KEY," +
DBContract.UserEntry.COLUMN_NAME + " TEXT," +
DBContract.UserEntry.COLUMN_AGE + " TEXT)"
private val SQL_DELETE_ENTRIES = "DROP TABLE IF EXISTS " + DBContract.UserEntry.TABLE_NAME
}
}MainActivity.kt
</>
Copy
package com.tutorialkart.sqlitetutorial
import android.support.v7.app.AppCompatActivity
import android.os.Bundle
import android.view.View
import android.widget.TextView
import kotlinx.android.synthetic.main.activity_main.*
class MainActivity : AppCompatActivity() {
lateinit var usersDBHelper : UsersDBHelper
override fun onCreate(savedInstanceState: Bundle?) {
super.onCreate(savedInstanceState)
setContentView(R.layout.activity_main)
usersDBHelper = UsersDBHelper(this)
}
fun addUser(v:View){
var userid = this.edittext_userid.text.toString()
var name = this.edittext_name.text.toString()
var age = this.edittext_age.text.toString()
var result = usersDBHelper.insertUser(UserModel(userid = userid,name = name,age = age))
//clear all edittext s
this.edittext_age.setText("")
this.edittext_name.setText("")
this.edittext_userid.setText("")
this.textview_result.text = "Added user : "+result
this.ll_entries.removeAllViews()
}
fun deleteUser(v:View){
var userid = this.edittext_userid.text.toString()
val result = usersDBHelper.deleteUser(userid)
this.textview_result.text = "Deleted user : "+result
this.ll_entries.removeAllViews()
}
fun showAllUsers(v:View){
var users = usersDBHelper.readAllUsers()
this.ll_entries.removeAllViews()
users.forEach {
var tv_user = TextView(this)
tv_user.textSize = 30F
tv_user.text = it.name.toString() + " - " + it.age.toString()
this.ll_entries.addView(tv_user)
}
this.textview_result.text = "Fetched " + users.size + " users"
}
}Conclusion
In this Kotlin Android 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