Android SQLite Tutorial - Kotlin

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 code
class 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.

Share this

Related Posts

Previous
Next Post »