In this tutorial, we will learn how to implement SQLite Operations in Android with Kotlin. It is similar to the way of implementing SQLite using Java. To learn the basics of Kotlin click here.
Project Setup:
Create new Project with Kotlin Support in Android Studio 3.0 or If you are using Android Studio version below 3.0 then setup Kotlin Plugin.
Coding Part
Create a kotlin class named as Tasks.kt and paste the following codeclass Tasks {
var id: Int = 0
var name: String = ""
var desc: String = ""
var completed: String = "N"
}
DatabaseHandler
Create a Kotlin class named as DatabaseHandler.kt. It is used to Handle the database operations of SQLite with Kotlin.
The Parent of this class is SQLiteOpenHelper.
Paste the following code in DatabaseHandler.kt
class DatabaseHandler(context: Context) : SQLiteOpenHelper(context, DatabaseHandler.DB_NAME, null, DatabaseHandler.DB_VERSION) {
override fun onCreate(db: SQLiteDatabase) {
val CREATE_TABLE = "CREATE TABLE $TABLE_NAME ($ID INTEGER PRIMARY KEY, $NAME TEXT,$DESC TEXT,$COMPLETED TEXT);"
db.execSQL(CREATE_TABLE)
}
override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
val DROP_TABLE = "DROP TABLE IF EXISTS $TABLE_NAME"
db.execSQL(DROP_TABLE)
onCreate(db)
}
}
CRUD Operations of SQLite
Now we need to write methods for handling all database read and write operations. Here we are implementing following methods for our tasks table.
Insert data to Table
The following code is used to add tasks to SQLite.
fun addTask(tasks: Tasks): Boolean {
val db = this.writableDatabase
val values = ContentValues()
values.put(NAME, tasks.name)
values.put(DESC, tasks.desc)
values.put(COMPLETED, tasks.completed)
val _success = db.insert(TABLE_NAME, null, values)
db.close()
Log.v("InsertedId", "$_success")
return (Integer.parseInt("$_success") != -1)
}
Get all data from Table
The following code is used to get all tasks from SQLite.
fun task(): List {
val taskList = ArrayList()
val db = writableDatabase
val selectQuery = "SELECT * FROM $TABLE_NAME"
val cursor = db.rawQuery(selectQuery, null)
if (cursor != null) {
if (cursor.moveToFirst()) {
do {
val tasks = Tasks()
tasks.id = Integer.parseInt(cursor.getString(cursor.getColumnIndex(ID)))
tasks.name = cursor.getString(cursor.getColumnIndex(NAME))
tasks.desc = cursor.getString(cursor.getColumnIndex(DESC))
tasks.completed = cursor.getString(cursor.getColumnIndex(COMPLETED))
taskList.add(tasks)
} while (cursor.moveToNext())
}
}
cursor.close()
return taskList
}
Get particular data from Table
The following code is used to get particular task from SQLite.
fun getTask(_id: Int): Tasks {
val tasks = Tasks()
val db = writableDatabase
val selectQuery = "SELECT * FROM $TABLE_NAME WHERE $ID = $_id"
val cursor = db.rawQuery(selectQuery, null)
cursor?.moveToFirst()
tasks.id = Integer.parseInt(cursor.getString(cursor.getColumnIndex(ID)))
tasks.name = cursor.getString(cursor.getColumnIndex(NAME))
tasks.desc = cursor.getString(cursor.getColumnIndex(DESC))
tasks.completed = cursor.getString(cursor.getColumnIndex(COMPLETED))
cursor.close()
return tasks
}
Update data to Table
The following code is used to update particular task to SQLite.
fun updateTask(tasks: Tasks): Boolean {
val db = this.writableDatabase
val values = ContentValues()
values.put(NAME, tasks.name)
values.put(DESC, tasks.desc)
values.put(COMPLETED, tasks.completed)
val _success = db.update(TABLE_NAME, values, ID + "=?", arrayOf(tasks.id.toString())).toLong()
db.close()
return Integer.parseInt("$_success") != -1
}
Delete data from Table
The following code is used to delete particular or all task(s) from SQLite.
// delete particular data
fun deleteTask(_id: Int): Boolean {
val db = this.writableDatabase
val _success = db.delete(TABLE_NAME, ID + "=?", arrayOf(_id.toString())).toLong()
db.close()
return Integer.parseInt("$_success") != -1
}
// delete all data
fun deleteAllTasks(): Boolean {
val db = this.writableDatabase
val _success = db.delete(TABLE_NAME, null, null).toLong()
db.close()
return Integer.parseInt("$_success") != -1
}
Full Code of DatabaseHandler
class DatabaseHandler(context: Context) : SQLiteOpenHelper(context, DatabaseHandler.DB_NAME, null, DatabaseHandler.DB_VERSION) {
override fun onCreate(db: SQLiteDatabase) {
val CREATE_TABLE = "CREATE TABLE $TABLE_NAME ($ID INTEGER PRIMARY KEY, $NAME TEXT,$DESC TEXT,$COMPLETED TEXT);"
db.execSQL(CREATE_TABLE)
}
override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
val DROP_TABLE = "DROP TABLE IF EXISTS $TABLE_NAME"
db.execSQL(DROP_TABLE)
onCreate(db)
}
fun addTask(tasks: Tasks): Boolean {
val db = this.writableDatabase
val values = ContentValues()
values.put(NAME, tasks.name)
values.put(DESC, tasks.desc)
values.put(COMPLETED, tasks.completed)
val _success = db.insert(TABLE_NAME, null, values)
db.close()
Log.v("InsertedId", "$_success")
return (Integer.parseInt("$_success") != -1)
}
fun getTask(_id: Int): Tasks {
val tasks = Tasks()
val db = writableDatabase
val selectQuery = "SELECT * FROM $TABLE_NAME WHERE $ID = $_id"
val cursor = db.rawQuery(selectQuery, null)
cursor?.moveToFirst()
tasks.id = Integer.parseInt(cursor.getString(cursor.getColumnIndex(ID)))
tasks.name = cursor.getString(cursor.getColumnIndex(NAME))
tasks.desc = cursor.getString(cursor.getColumnIndex(DESC))
tasks.completed = cursor.getString(cursor.getColumnIndex(COMPLETED))
cursor.close()
return tasks
}
fun task(): List {
val taskList = ArrayList()
val db = writableDatabase
val selectQuery = "SELECT * FROM $TABLE_NAME"
val cursor = db.rawQuery(selectQuery, null)
if (cursor != null) {
if (cursor.moveToFirst()) {
do {
val tasks = Tasks()
tasks.id = Integer.parseInt(cursor.getString(cursor.getColumnIndex(ID)))
tasks.name = cursor.getString(cursor.getColumnIndex(NAME))
tasks.desc = cursor.getString(cursor.getColumnIndex(DESC))
tasks.completed = cursor.getString(cursor.getColumnIndex(COMPLETED))
taskList.add(tasks)
} while (cursor.moveToNext())
}
}
cursor.close()
return taskList
}
fun updateTask(tasks: Tasks): Boolean {
val db = this.writableDatabase
val values = ContentValues()
values.put(NAME, tasks.name)
values.put(DESC, tasks.desc)
values.put(COMPLETED, tasks.completed)
val _success = db.update(TABLE_NAME, values, ID + "=?", arrayOf(tasks.id.toString())).toLong()
db.close()
return Integer.parseInt("$_success") != -1
}
fun deleteTask(_id: Int): Boolean {
val db = this.writableDatabase
val _success = db.delete(TABLE_NAME, ID + "=?", arrayOf(_id.toString())).toLong()
db.close()
return Integer.parseInt("$_success") != -1
}
fun deleteAllTasks(): Boolean {
val db = this.writableDatabase
val _success = db.delete(TABLE_NAME, null, null).toLong()
db.close()
return Integer.parseInt("$_success") != -1
}
companion object {
private val DB_VERSION = 1
private val DB_NAME = "MyTasks"
private val TABLE_NAME = "Tasks"
private val ID = "Id"
private val NAME = "Name"
private val DESC = "Desc"
private val COMPLETED = "Completed"
}
}
Add Tasks
The following code is used to call Insert Function in DatabaseHandler
val tasks: Tasks = Tasks()
tasks.name = input_name.text.toString()
tasks.desc = input_desc.text.toString()
if (swt_completed.isChecked)
tasks.completed = "Y"
else
tasks.completed = "N"
success = dbHandler?.addTask(tasks) as Boolean
Update Tasks
The following code is used to call update Function in DatabaseHandler
val tasks: Tasks = Tasks()
tasks.id = intent.getIntExtra("Id", 0)
tasks.name = input_name.text.toString()
tasks.desc = input_desc.text.toString()
if (swt_completed.isChecked)
tasks.completed = "Y"
else
tasks.completed = "N"
success = dbHandler?.updateTask(tasks) as Boolean
Delete Tasks
The following code is used to call delete Functions in DatabaseHandler
// Delete Task
val success = dbHandler?.deleteTask(intent.getIntExtra("Id", 0)) as Boolean
// Delete All Tasks
dbHandler!!.deleteAllTasks()
Read Tasks
The following code is used to call read Functions in DatabaseHandler
// Read All Tasks
val tasks: Tasks = dbHandler!!.getTask(intent.getIntExtra("Id",0))
// Read Particular Task
dbHandler = DatabaseHandler(this)
listTasks = (dbHandler as DatabaseHandler).task()
In this tutorial, I have used Recyclerview. To implement Recyclerview click here.
Full Code of this tutorial
Create MainActivity.kt and Paste the following code
class MainActivity : AppCompatActivity() {
var taskRecyclerAdapter: TaskRecyclerAdapter? = null;
var fab: FloatingActionButton? = null
var recyclerView: RecyclerView? = null
var dbHandler: DatabaseHandler? = null
var listTasks: List = ArrayList()
var linearLayoutManager: LinearLayoutManager? = null
override fun onCreate(savedInstanceState: Bundle?) {
super.onCreate(savedInstanceState)
setContentView(R.layout.activity_main)
initViews()
initOperations()
//initDB()
}
fun initDB() {
dbHandler = DatabaseHandler(this)
listTasks = (dbHandler as DatabaseHandler).task()
taskRecyclerAdapter = TaskRecyclerAdapter(tasksList = listTasks, context = applicationContext)
(recyclerView as RecyclerView).adapter = taskRecyclerAdapter
}
fun initViews() {
val toolbar = findViewById(R.id.toolbar) as Toolbar
setSupportActionBar(toolbar)
fab = findViewById(R.id.fab) as FloatingActionButton
recyclerView = findViewById(R.id.recycler_view) as RecyclerView
taskRecyclerAdapter = TaskRecyclerAdapter(tasksList = listTasks, context = applicationContext)
linearLayoutManager = LinearLayoutManager(applicationContext)
(recyclerView as RecyclerView).layoutManager = linearLayoutManager
}
fun initOperations() {
fab?.setOnClickListener { view ->
val i = Intent(applicationContext, AddOrEditActivity::class.java)
i.putExtra("Mode", "A")
startActivity(i)
}
}
override fun onCreateOptionsMenu(menu: Menu): Boolean {
menuInflater.inflate(R.menu.menu_main, menu)
return true
}
override fun onOptionsItemSelected(item: MenuItem): Boolean {
val id = item.itemId
if (id == R.id.action_delete) {
val dialog = AlertDialog.Builder(this).setTitle("Info").setMessage("Click 'YES' Delete All Tasks")
.setPositiveButton("YES", { dialog, i ->
dbHandler!!.deleteAllTasks()
initDB()
dialog.dismiss()
})
.setNegativeButton("NO", { dialog, i ->
dialog.dismiss()
})
dialog.show()
return true
}
return super.onOptionsItemSelected(item)
}
override fun onResume() {
super.onResume()
initDB()
}
}
Create AddOrEditActivity.kt and Paste the following code
class AddOrEditActivity : AppCompatActivity() {
var dbHandler: DatabaseHandler? = null
var isEditMode = false
public override fun onCreate(savedInstanceState: Bundle?) {
super.onCreate(savedInstanceState)
setContentView(R.layout.activity_add_edit)
supportActionBar?.setDisplayHomeAsUpEnabled(true)
initDB()
initOperations()
}
private fun initDB() {
dbHandler = DatabaseHandler(this)
btn_delete.visibility = View.INVISIBLE
if (intent != null && intent.getStringExtra("Mode") == "E") {
isEditMode = true
val tasks: Tasks = dbHandler!!.getTask(intent.getIntExtra("Id",0))
input_name.setText(tasks.name)
input_desc.setText(tasks.desc)
swt_completed.isChecked = tasks.completed == "Y"
btn_delete.visibility = View.VISIBLE
}
}
private fun initOperations() {
btn_save.setOnClickListener({
var success: Boolean = false
if (!isEditMode) {
val tasks: Tasks = Tasks()
tasks.name = input_name.text.toString()
tasks.desc = input_desc.text.toString()
if (swt_completed.isChecked)
tasks.completed = "Y"
else
tasks.completed = "N"
success = dbHandler?.addTask(tasks) as Boolean
} else {
val tasks: Tasks = Tasks()
tasks.id = intent.getIntExtra("Id", 0)
tasks.name = input_name.text.toString()
tasks.desc = input_desc.text.toString()
if (swt_completed.isChecked)
tasks.completed = "Y"
else
tasks.completed = "N"
success = dbHandler?.updateTask(tasks) as Boolean
}
if (success)
finish()
})
btn_delete.setOnClickListener({
val dialog = AlertDialog.Builder(this).setTitle("Info").setMessage("Click 'YES' Delete the Task.")
.setPositiveButton("YES", { dialog, i ->
val success = dbHandler?.deleteTask(intent.getIntExtra("Id", 0)) as Boolean
if (success)
finish()
dialog.dismiss()
})
.setNegativeButton("NO", { dialog, i ->
dialog.dismiss()
})
dialog.show()
})
}
override fun onOptionsItemSelected(item: MenuItem): Boolean {
val id = item.itemId
if (id == android.R.id.home) {
finish()
return true
}
return super.onOptionsItemSelected(item)
}
}
Download Code:
You can the download code for this post from Github. If you like this, tutorial star it on Github.