Kotlin SQLite example – CRUD operations with ListView | Android

SQLite is an Open Source database that supports standard relational database features like SQL syntax, transactions and prepared statements. Each column in an SQLite database is assigned one of data types: NULL, INTEGER, REAL, TEXT, BLOB. In this tutorial, we’re gonna look at how to make SQLite CRUD operations when interacting with ListView.

Related Posts:
Kotlin ListView example | Android
Kotlin GridView example: Show List of Items on Grid | Android

More Practice: Kotlin SQLite example – Search with Listview | Android

I. Technologies

– Android Studio 3
– Kotlin 1.1.51

II. Overview

1. Goal

We will build an Android App that supports showing, inserting, editing, deleting Notes from/to SQLite Database with ListView:

kotlin-sqlite-goal

2. SQLite

2.1 SQLiteDatabase

SQLiteDatabase is the base class for working with a SQLite Database in Android. It provides:
insert(), update(), delete() method
execSQL() to execute an SQL statement directly

To work with SQLite Database, we create a subclass of SQLiteOpenHelper class:
– constructor: call super() method to specify database_name, version and context
– override onCreate() to execute CREATE TABLE command
– override onUpgrade() to handle database version increment
– indicate read/write mode by using getReadableDatabase() or getWritableDatabase() method

For example:


class DatabaseHelper : SQLiteOpenHelper {

    var context: Context? = null

    constructor(context: Context) : super(context, dbName, null, dbVersion) {
        this.context = context
    }

    override fun onCreate(db: SQLiteDatabase?) {
        db!!.execSQL(CREATE_TABLE_SQL)
    }

    override fun onUpgrade(db: SQLiteDatabase?, oldVersion: Int, newVersion: Int) {
        db!!.execSQL("Drop table IF EXISTS " + dbTable)
    }
}

// ---------------
var dbHelper = DatabaseHelper(context)
db = dbHelper.writableDatabase  // getWritableDatabase()

db!!.insert("JSANotesTable", "", contentValues)
db!!.update("JSANotesTable", contentValues, "Id=?", arrayOf(id.toString()))

db!!.execSQL("CREATE TABLE IF NOT EXISTS JSANotesTable (Id INTEGER PRIMARY KEY, Title TEXT, Content TEXT);")

2.2 Query

A query can be created via:
rawQuery() or query()
SQLiteQueryBuilder

For example:


var db: SQLiteDatabase = ...

db!!.rawQuery("select * from JSANotesTable", null)

val qb = SQLiteQueryBuilder()
qb.tables = "JSANotesTable"
val projections = arrayOf("Id", "Title", "Content")
val selectionArgs = arrayOf(title)

val cursor = qb.query(db, projection, selection, selectionArgs, null, null, "Title")

2.3 ContentValues

ContentValues object can be used for insert and update database entries.

For example:


var values = ContentValues()
values.put("Title", "JavaSampleApproach")
values.put("Content", "Java Technology - Spring Framework")

db!!.insert(dbTable, "", values)
3. Project Structure

kotlin-sqlite-structure

III. Practice

1. Set up Project

– Create New Project with package name com.javasampleapproach.kotlin.sqlite and Kotlin support.
– Add images (found in source code) to drawable.

2. Layout

2.1 Main Activity

Open res/layout/activity_main.xml file:

<?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">

    <ListView
        android:id="@+id/lvNotes"
        android:layout_width="match_parent"
        android:layout_height="match_parent" />
</LinearLayout>

2.2 Item Layout

Add note.xml layout file to res/layout:

<?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"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="horizontal"
    android:padding="10dp"
    android:weightSum="10">

    <LinearLayout
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_weight="9"
        android:orientation="vertical">

        <TextView
            android:id="@+id/tvTitle"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:padding="5dp"
            android:text="Title"
            android:textColor="@color/colorPrimary"
            android:textSize="18sp" />

        <TextView
            android:id="@+id/tvContent"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:padding="5dp"
            android:text="Text for Content" />
    </LinearLayout>

    <LinearLayout
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_weight="1"
        android:orientation="vertical"
        android:gravity="right">

        <ImageView
            android:id="@+id/ivEdit"
            android:layout_width="30dp"
            android:layout_height="30dp"
            app:srcCompat="@drawable/ic_edit" />

        <ImageView
            android:id="@+id/ivDelete"
            android:layout_width="30dp"
            android:layout_height="30dp"
            app:srcCompat="@drawable/ic_delete" />
    </LinearLayout>

</LinearLayout>

2.3 Activity Layout for Adding/Updating Note

Add activity_note.xml layout file to res/layout:

<?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/edtTitle"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_marginTop="30dp"
        android:hint="Java Sample Approach"
        android:inputType="textPersonName" />

    <EditText
        android:id="@+id/edtContent"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_marginTop="20dp"
        android:hint="Java technology, Spring Framework - approach to Java by Sample."
        android:inputType="textMultiLine" />

    <Button
        android:id="@+id/btAdd"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginTop="20dp"
        android:text="Add" />
</LinearLayout>

2.4 Menu

Under res folder, create menu folder and add menu_main.xml:

<?xml version="1.0" encoding="utf-8"?>
<menu xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:app="http://schemas.android.com/apk/res-auto">

    <item
        android:id="@+id/addNote"
        android:icon="@drawable/ic_add"
        android:title="Add"
        app:showAsAction="ifRoom" />
</menu>

3. Data Model

Add Kotlin class:


package com.javasampleapproach.kotlin.sqlite

class Note {

    var id: Int? = null
    var title: String? = null
    var content: String? = null

    constructor(id: Int, title: String, content: String) {
        this.id = id
        this.title = title
        this.content = content
    }

}

4. Database Manager


package com.javasampleapproach.kotlin.sqlite

import android.content.ContentValues
import android.content.Context
import android.database.Cursor
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper
import android.widget.Toast

class NoteDbManager {

    private val dbName = "JSANotes"
    private val dbTable = "Notes"
    private val colId = "Id"
    private val colTitle = "Title"
    private val colContent = "Content"
    private val dbVersion = 1

    private val CREATE_TABLE_SQL = "CREATE TABLE IF NOT EXISTS " + dbTable + " (" + colId + " INTEGER PRIMARY KEY," + colTitle + " TEXT, " + colContent + " TEXT);"
    private var db: SQLiteDatabase? = null

    constructor(context: Context) {
        var dbHelper = DatabaseHelper(context)
        db = dbHelper.writableDatabase
    }

    fun insert(values: ContentValues): Long {

        val ID = db!!.insert(dbTable, "", values)
        return ID
    }

    fun queryAll(): Cursor {

        return db!!.rawQuery("select * from " + dbTable, null)
    }

    fun delete(selection: String, selectionArgs: Array): Int {

        val count = db!!.delete(dbTable, selection, selectionArgs)
        return count
    }

    fun update(values: ContentValues, selection: String, selectionargs: Array): Int {

        val count = db!!.update(dbTable, values, selection, selectionargs)
        return count
    }

    inner class DatabaseHelper : SQLiteOpenHelper {

        var context: Context? = null

        constructor(context: Context) : super(context, dbName, null, dbVersion) {
            this.context = context
        }

        override fun onCreate(db: SQLiteDatabase?) {
            db!!.execSQL(CREATE_TABLE_SQL)
            Toast.makeText(this.context, " database is created", Toast.LENGTH_LONG).show()
        }

        override fun onUpgrade(db: SQLiteDatabase?, oldVersion: Int, newVersion: Int) {
            db!!.execSQL("Drop table IF EXISTS " + dbTable)
        }
    }
}

5. Activity

5.1 Main Activity

To work with ListView, we will:
– create ViewHolder class
– create BaseAdapter subclass
– set adapter for ListView
– add Listener to ListView

To work with Add Note button on Menu, we will:
– override onCreateOptionsMenu() function to specify menu layout resource
– override onOptionsItemSelected function to launch NoteActivity

To display List of Items, we will create loadQueryAll() and call it inside onCreate() and onResume().

To support edit Note and delete Note, we register a callback for ivEdit and ivDelete ImageView.
With edit Note, to transfer data between 2 Activity, we use Intent.


package com.javasampleapproach.kotlin.sqlite

import android.content.Context
import android.content.Intent
import android.support.v7.app.AppCompatActivity
import android.os.Bundle
import android.util.Log
import android.view.Menu
import android.view.MenuItem
import android.view.View
import android.view.ViewGroup
import android.widget.*
import kotlinx.android.synthetic.main.activity_main.*

class MainActivity : AppCompatActivity() {

    private var listNotes = ArrayList()

    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)

//        listNotes.add(Note(1, "JavaSampleApproach", "Java technology, Spring Framework - approach to Java by Sample."))
//        listNotes.add(Note(2, "Kotlin Android Tutorial", "Create tutorial for people to learn Kotlin Android. Kotlin is now an official language on Android. It's expressive, concise, and powerful. Best of all, it's interoperable with our existing Android languages and runtime."))
//        listNotes.add(Note(3, "Android Studio", "Android Studio 3.0 provides helpful tools to help you start using Kotlin. Convert entire Java files or convert code snippets on the fly when you paste Java code into a Kotlin file."))
//        listNotes.add(Note(4, "Java Android Tutorial", "Create tutorial for people to learn Java Android. Learn Java in a greatly improved learning environment with more lessons, real practice opportunity, and community support."))
//        listNotes.add(Note(5, "Spring Boot Tutorial", "Spring Boot help build stand-alone, production Spring Applications easily, less configuration then rapidly start new projects."))

        loadQueryAll()

        lvNotes.onItemClickListener = AdapterView.OnItemClickListener { adapterView, view, position, id ->
            Toast.makeText(this, "Click on " + listNotes[position].title, Toast.LENGTH_SHORT).show()
        }
    }

    override fun onCreateOptionsMenu(menu: Menu?): Boolean {
        menuInflater.inflate(R.menu.menu_main, menu)

        return super.onCreateOptionsMenu(menu)
    }

    override fun onOptionsItemSelected(item: MenuItem?): Boolean {
        if (item != null) {
            when (item.itemId) {
                R.id.addNote -> {
                    var intent = Intent(this, NoteActivity::class.java)
                    startActivity(intent)
                }
            }
        }

        return super.onOptionsItemSelected(item)
    }

    override fun onResume() {
        super.onResume()
        loadQueryAll()
    }

    fun loadQueryAll() {

        var dbManager = NoteDbManager(this)
        val cursor = dbManager.queryAll()

        listNotes.clear()
        if (cursor.moveToFirst()) {

            do {
                val id = cursor.getInt(cursor.getColumnIndex("Id"))
                val title = cursor.getString(cursor.getColumnIndex("Title"))
                val content = cursor.getString(cursor.getColumnIndex("Content"))

                listNotes.add(Note(id, title, content))

            } while (cursor.moveToNext())
        }

        var notesAdapter = NotesAdapter(this, listNotes)
        lvNotes.adapter = notesAdapter
    }

    inner class NotesAdapter : BaseAdapter {

        private var notesList = ArrayList()
        private var context: Context? = null

        constructor(context: Context, notesList: ArrayList) : super() {
            this.notesList = notesList
            this.context = context
        }

        override fun getView(position: Int, convertView: View?, parent: ViewGroup?): View? {

            val view: View?
            val vh: ViewHolder

            if (convertView == null) {
                view = layoutInflater.inflate(R.layout.note, parent, false)
                vh = ViewHolder(view)
                view.tag = vh
                Log.i("JSA", "set Tag for ViewHolder, position: " + position)
            } else {
                view = convertView
                vh = view.tag as ViewHolder
            }

            var mNote = notesList[position]

            vh.tvTitle.text = mNote.title
            vh.tvContent.text = mNote.content

            vh.ivEdit.setOnClickListener {
                updateNote(mNote)
            }

            vh.ivDelete.setOnClickListener {
                var dbManager = NoteDbManager(this.context!!)
                val selectionArgs = arrayOf(mNote.id.toString())
                dbManager.delete("Id=?", selectionArgs)
                loadQueryAll()
            }

            return view
        }

        override fun getItem(position: Int): Any {
            return notesList[position]
        }

        override fun getItemId(position: Int): Long {
            return position.toLong()
        }

        override fun getCount(): Int {
            return notesList.size
        }
    }

    private fun updateNote(note: Note) {
        var intent = Intent(this, NoteActivity::class.java)
        intent.putExtra("MainActId", note.id)
        intent.putExtra("MainActTitle", note.title)
        intent.putExtra("MainActContent", note.content)
        startActivity(intent)
    }

    private class ViewHolder(view: View?) {
        val tvTitle: TextView
        val tvContent: TextView
        val ivEdit: ImageView
        val ivDelete: ImageView

        init {
            this.tvTitle = view?.findViewById(R.id.tvTitle) as TextView
            this.tvContent = view?.findViewById(R.id.tvContent) as TextView
            this.ivEdit = view?.findViewById(R.id.ivEdit) as ImageView
            this.ivDelete = view?.findViewById(R.id.ivDelete) as ImageView
        }

        // with API 26
//        init {
//            this.tvTitle = view?.findViewById(R.id.tvTitle) as TextView
//            this.tvContent = view?.findViewById(R.id.tvContent) as TextView
//            this.ivEdit = view?.findViewById(R.id.ivEdit) as ImageView
//            this.ivDelete = view?.findViewById(R.id.ivDelete) as ImageView
        }
    }

}

5.2 Note Activity


package com.javasampleapproach.kotlin.sqlite

import android.content.ContentValues
import android.support.v7.app.AppCompatActivity
import android.os.Bundle
import android.widget.Toast
import kotlinx.android.synthetic.main.activity_note.*

class NoteActivity : AppCompatActivity() {

    var id = 0

    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_note)

        try {
            var bundle: Bundle = intent.extras
            id = bundle.getInt("MainActId", 0)
            if (id != 0) {
                edtTitle.setText(bundle.getString("MainActTitle"))
                edtContent.setText(bundle.getString("MainActContent"))
            }
        } catch (ex: Exception) {
        }

        btAdd.setOnClickListener {
            var dbManager = NoteDbManager(this)

            var values = ContentValues()
            values.put("Title", edtTitle.text.toString())
            values.put("Content", edtContent.text.toString())

            if (id == 0) {
                val mID = dbManager.insert(values)

                if (mID > 0) {
                    Toast.makeText(this, "Add note successfully!", Toast.LENGTH_LONG).show()
                    finish()
                } else {
                    Toast.makeText(this, "Fail to add note!", Toast.LENGTH_LONG).show()
                }
            } else {
                var selectionArs = arrayOf(id.toString())
                val mID = dbManager.update(values, "Id=?", selectionArs)

                if (mID > 0) {
                    Toast.makeText(this, "Add note successfully!", Toast.LENGTH_LONG).show()
                    finish()
                } else {
                    Toast.makeText(this, "Fail to add note!", Toast.LENGTH_LONG).show()
                }
            }
        }
    }
}

IV. Source Code

Kotlin-SQLite



By grokonez | December 9, 2017.

Last updated on April 14, 2021.



Related Posts


10 thoughts on “Kotlin SQLite example – CRUD operations with ListView | Android”

  1. It only builds, wenn I extend findViewById() to findViewById<TextView/ImageView>() in the ViewHolder Class.
    Otherwhise I get a “Error:(154, 34) Type inference failed: Not enough information to infer parameter T in fun <T : View!> findViewById(p0: Int): T!
    Please specify it explicitly.”

    The App stopps when I tap on the addNote in the menu and dunno where to debug. Sorry! Absolute Beginner.

    1. Hi Rafael,

      With API 26, we should change:

              init {
                  this.tvTitle = view?.findViewById(R.id.tvTitle) as TextView
                  this.tvContent = view?.findViewById(R.id.tvContent) as TextView
                  this.ivEdit = view?.findViewById(R.id.ivEdit) as ImageView
                  this.ivDelete = view?.findViewById(R.id.ivDelete) as ImageView
              }
      

      to:

              init {
                  this.tvTitle = view?.findViewById<TextView>(R.id.tvTitle) as TextView
                  this.tvContent = view?.findViewById<TextView>(R.id.tvContent) as TextView
                  this.ivEdit = view?.findViewById<ImageView>(R.id.ivEdit) as ImageView
                  this.ivDelete = view?.findViewById<ImageView>(R.id.ivDelete) as ImageView
              }
      

      Regards,
      JSA.

  2. I was going through the previous tutorials “by hand”. Due to the issues I had here and I’m not sure if I made a mistake I tried to import/load the downloadable sourcecode.
    Unfortunately, that did not work as intended. Is there a simple way to import the source als project I can work on?

    Thanks in advance.

    1. Hi Rafael,

      Please use:

              init {
                  this.tvTitle = view?.findViewById<TextView>(R.id.tvTitle) as TextView
                  this.tvContent = view?.findViewById<TextView>(R.id.tvContent) as TextView
                  this.ivEdit = view?.findViewById<ImageView>(R.id.ivEdit) as ImageView
                  this.ivDelete = view?.findViewById<ImageView>(R.id.ivDelete) as ImageView
              }
      

      Regards,
      JSA.

  3. It took my several hours to find, why my build App always crashes.
    Finally – after finding LogCat and using it – it gives me something like “Unable to find explicit activity class {}; have you declared this activity in your AndroidManifest.xml”
    The NoteActivity was not declared. All I had to do was a Alt+Enter on the class to declare it.
    The App is running – I’m improving 🙂

  4. How can I run the code? I’m new in Android, I downloaded the code but there is required create a configuration to run the code….Some body can help me. Thanks!

  5. I went through you tutorial and got it working, but I decidied to change all your references to Notes to League, to see if I understood it completely. However I am getting an Unresolved Reference to lvLeague in the MainActivity.kt.

    lvLeague.onItemClickListener = AdapterView.OnItemClickListener { adapterView, view, position, id ->
                Toast.makeText(this, "Click on " + listLeague[position].league_name, Toast.LENGTH_SHORT).show()
            }
        }
    
        override fun onCreateOptionsMenu(menu: Menu?): Boolean {
            menuInflater.inflate(R.menu.menu_main, menu)
    
            return super.onCreateOptionsMenu(menu)
        }
    
        override fun onOptionsItemSelected(item: MenuItem?): Boolean {
            if (item != null) {
                when (item.itemId) {
                    R.id.addLeague -> {
                        var intent = Intent(this, LeagueActivity::class.java)
                        startActivity(intent)
                    }
                }
            }
    
            return super.onOptionsItemSelected(item)
        }
    
        override fun onResume() {
            super.onResume()
            loadQueryAll()
        }
    
        fun loadQueryAll() {
    
            var dbManager = LeagueDBHelper(this)
            val cursor = dbManager.queryAll()
    
            listLeague.clear()
            if (cursor.moveToFirst()) {
    
                do {
                    val l_id = cursor.getInt(cursor.getColumnIndex("League_Id"))
                    val l_name = cursor.getString(cursor.getColumnIndex("League_Name"))
                    val l_create_date = cursor.getString(cursor.getColumnIndex("League_Create_Date"))
    
                    listLeague.add(League(l_id, l_name, l_create_date))
    
                } while (cursor.moveToNext())
            }
    
            var leagueAdapter = LeagueAdapter(this, listLeague)
            lvLeague.adapter = leagueAdapter
        }
    

    I have gone back through your tutorial and I am unable to find any where else lvNotes is being used.

    Any assistance would be helpful. Thanks!

  6. Hi nice tutorial but I always find tutorial inserting to database. Can you make tutorial on how to read sqlite database inside drawables asset folder into listview that would be great help.

Got Something To Say:

Your email address will not be published. Required fields are marked *

*