Open in app

Sign in

Write

Sign in

SQLite CRUD Kotlin-Android Studio

Usman Idzhami
6 min readApr 29, 2022

--

Using a database is a way to store structured data or repetitive data. The APIs that you later use databases on Android are available in the android.database.sqlite package.

After that specify the table name, column name and db name

companion object {
    val DATABASE_NAME = "Person.db"
    val TABLE_PERSON = "tblPerson"
    val COL_ID = "caller_id"
    val COL_NAME = "caller_name"
    val COL_ADDRESS = "caller_address"
    val COL_BIRTHDAY = "caller_birthday"
}

After you define a database view, you must implement the methods that will create and manage the database and tables.

SQLiteOpenHelper(context, DATABASE_NAME, null, 1) {
override fun onCreate(db: SQLiteDatabase) {
    db.execSQL("CREATE TABLE $TABLE_PERSON($COL_ID INTEGER PRIMARY KEY AUTOINCREMENT, $COL_NAME TEXT , $COL_ADDRESS TEXT , $COL_BIRTHDAY TEXT);")
}

private fun dropDb(db: SQLiteDatabase) {
    db.execSQL("DROP TABLE $TABLE_PERSON;")
}

override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
    this.dropDb(db)
    onCreate(db)
}

Then create a CRUD function

inner class TABLE_PERSON {
    fun ADD_DATA_PERSON(caller_name: String, caller_address: String, caller_birthday: String) {
        val db = writableDatabase;
        val newContentoffline = ContentValues()
        newContentoffline.put(COL_NAME, caller_name)
        newContentoffline.put(COL_ADDRESS, caller_address)
        newContentoffline.put(COL_BIRTHDAY, caller_birthday)
        db.insert(TABLE_PERSON, null, newContentoffline)
    }

    fun DELETE_DATA_PERSON(id: String): Int {
        val db = writableDatabase
        return db.delete(TABLE_PERSON, "caller_id = ?", arrayOf(id))
    }

    fun GET_DATA_PERSON(): ArrayList<Person> {
        val db = writableDatabase
        val res = db.rawQuery("SELECT * FROM ${TABLE_PERSON}", null)
        val useList = ArrayList<Person>()
        if (res.moveToFirst()) {
            while (!res.isAfterLast()) {
                val model =
                    Person(
                        res.getString(0),
                        res.getString(1),
                        res.getString(2),
                        res.getString(3)
                    )

                useList.add(model)
                res.moveToNext()
            }
        }
        res.close()
        return useList
    }

    fun UPDATE_DATA_PERSON(id: String, name: String, address: String, birthday: String) {
        val db = writableDatabase
        val newContentoffline = ContentValues()
        newContentoffline.put(COL_ID, id)
        newContentoffline.put(COL_NAME, name)
        newContentoffline.put(COL_ADDRESS, address)
        newContentoffline.put(COL_BIRTHDAY, birthday)
        db.update(TABLE_PERSON, newContentoffline, "$COL_ID = ?", arrayOf(id))
    }
}

for complete code

class DbAdapterPerson(context: Context) :
    SQLiteOpenHelper(context, DATABASE_NAME, null, 1) {
    override fun onCreate(db: SQLiteDatabase) {
        db.execSQL("CREATE TABLE $TABLE_PERSON($COL_ID INTEGER PRIMARY KEY AUTOINCREMENT, $COL_NAME TEXT , $COL_ADDRESS TEXT , $COL_BIRTHDAY TEXT);")
    }

    private fun dropDb(db: SQLiteDatabase) {
        db.execSQL("DROP TABLE $TABLE_PERSON;")
    }

    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        this.dropDb(db)
        onCreate(db)
    }

    inner class TABLE_PERSON {
        fun ADD_DATA_PERSON(caller_name: String, caller_address: String, caller_birthday: String) {
            val db = writableDatabase;
            val newContentoffline = ContentValues()
            newContentoffline.put(COL_NAME, caller_name)
            newContentoffline.put(COL_ADDRESS, caller_address)
            newContentoffline.put(COL_BIRTHDAY, caller_birthday)
            db.insert(TABLE_PERSON, null, newContentoffline)
        }

        fun DELETE_DATA_PERSON(id: String): Int {
            val db = writableDatabase
            return db.delete(TABLE_PERSON, "caller_id = ?", arrayOf(id))
        }

        fun GET_DATA_PERSON(): ArrayList<Person> {
            val db = writableDatabase
            val res = db.rawQuery("SELECT * FROM ${TABLE_PERSON}", null)
            val useList = ArrayList<Person>()
            if (res.moveToFirst()) {
                while (!res.isAfterLast()) {
                    val model =
                        Person(
                            res.getString(0),
                            res.getString(1),
                            res.getString(2),
                            res.getString(3)
                        )

                    useList.add(model)
                    res.moveToNext()
                }
            }
            res.close()
            return useList
        }

        fun UPDATE_DATA_PERSON(id: String, name: String, address: String, birthday: String) {
            val db = writableDatabase
            val newContentoffline = ContentValues()
            newContentoffline.put(COL_ID, id)
            newContentoffline.put(COL_NAME, name)
            newContentoffline.put(COL_ADDRESS, address)
            newContentoffline.put(COL_BIRTHDAY, birthday)
            db.update(TABLE_PERSON, newContentoffline, "$COL_ID = ?", arrayOf(id))
        }

    }
    companion object {
        val DATABASE_NAME = "Person.db"
        val TABLE_PERSON = "tblPerson"
        val COL_ID = "caller_id"
        val COL_NAME = "caller_name"
        val COL_ADDRESS = "caller_address"
        val COL_BIRTHDAY = "caller_birthday"
    }

}

Wokayy, let’s continue creating views in activity_main.xml

<?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="match_parent"
    android:background="#E1ECF1"
    android:orientation="vertical"
    tools:context=".MainActivity">
    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_margin="24dp"
        android:layout_gravity="center"
        android:orientation="vertical">
        <TextView
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:textSize="14dp"
            android:text="Name"/>
        <EditText
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:textSize="14dp"
            android:id="@+id/ED_NAME"/>
        <TextView
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:text="Birthday"/>
        <EditText
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:inputType="datetime"
            android:id="@+id/ED_BIRTHDAY"/>
        <TextView
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:layout_marginTop="15dp"
            android:textSize="14dp"
            android:text="Address"/>
        <EditText
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:textSize="14dp"
            android:id="@+id/ED_ADDRESS"/>
        <Button
            android:layout_marginTop="15dp"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:textColor="@color/white"
            android:id="@+id/BTN_ADD"
            android:text="ADD"/>
        <androidx.recyclerview.widget.RecyclerView
            android:id="@+id/rv_main"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:layout_marginTop="24dp"
            android:scrollbars="vertical"
            tools:ignore="MissingConstraints" />
    </LinearLayout>

</LinearLayout>

then create a Person.kt file

data class Person(
    var id: String,
    var name: String,
    var address: String,
    var birthday: String
)

Next, we prepare the RecycleViewAdapter.kt file which is useful for displaying the data that we have successfully entered

class RecycleViewAdapter(

    private val context: Context,
    private var resultTransaction: List<Person>,
    private val activity: MainActivity

) : RecyclerView.Adapter<RecycleViewAdapter.ViewHolderTransaction>() {
    private val TAG = javaClass.simpleName

    companion object {
        private const val VIEW_TYPE_DATA = 0;
        private const val VIEW_TYPE_PROGRESS = 1;
    }

    override fun onCreateViewHolder(parent: ViewGroup, viewType: Int): ViewHolderTransaction {
        return when (viewType) {
            VIEW_TYPE_DATA -> {//inflates row layout
                val view = LayoutInflater.from(parent?.context)
                    .inflate(R.layout.item_list, parent, false)
                ViewHolderTransaction(view)
            }
            VIEW_TYPE_PROGRESS -> {//inflates progressbar layout
                val view = LayoutInflater.from(parent?.context)
                    .inflate(R.layout.support_simple_spinner_dropdown_item, parent, false)
                ViewHolderTransaction(view)
            }
            else -> throw IllegalArgumentException("Different View type")
        }
    }

    override fun getItemCount(): Int = resultTransaction.size

    fun refreshAdapter(resultTransaction: List<Person>) {
        this.resultTransaction = resultTransaction
        notifyItemRangeChanged(0, this.resultTransaction.size)
    }


    @SuppressLint("ResourceAsColor")
    override fun onBindViewHolder(
        holder: RecycleViewAdapter.ViewHolderTransaction,
        position: Int
    ) {
        val txtName: TextView
        val txtBirthday: TextView
        val txtAddress: TextView
        val BTN_DELETE: ImageView
        val BTN_UPDATE: Button
        txtName = holder.itemView.findViewById(R.id.TXT_NAME) as TextView
        txtBirthday = holder.itemView.findViewById(R.id.TXT_BIRTHDAY) as TextView
        txtAddress = holder.itemView.findViewById(R.id.TXT_ADDRESS) as TextView
        BTN_DELETE = holder.itemView.findViewById(R.id.BTN_DELETE) as ImageView
        BTN_UPDATE = holder.itemView.findViewById(R.id.BTN_UPDATE) as Button
        if (holder.itemViewType == VIEW_TYPE_DATA) {
            val resultItem = resultTransaction[position]
            txtName.text = resultItem.name
            txtBirthday.text = resultItem.birthday
            txtAddress.text = resultItem.address

            BTN_DELETE.setOnClickListener {
                try {
                    activity.DELETE_DATA(resultItem.id)
                } catch (e: Exception) {
                    Log.e("error : ", e.toString())
                }
            }
            BTN_UPDATE.setOnClickListener {
                try {
                    activity.DATA_FROM_RECYCLEVIEW(resultItem.id,resultItem.name,resultItem.address,resultItem.birthday)
                } catch (e: Exception) {
                    Log.e("error : ", e.toString())
                }
            }

        }
    }

    override fun getItemViewType(position: Int): Int {
        return if (resultTransaction[position] == null) {
            VIEW_TYPE_PROGRESS
        } else {
            VIEW_TYPE_DATA
        }
    }

    inner class ViewHolderTransaction(itemView: View?) : RecyclerView.ViewHolder(itemView!!)
}

create xml file with name item_list.xml

<?xml version="1.0" encoding="utf-8"?>
<androidx.cardview.widget.CardView xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="wrap_content"
    android:layout_marginTop="5dp"
    android:layout_marginBottom="2dp"
    android:layout_marginRight="2dp"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    app:cardCornerRadius="10dp">
    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal">
        <LinearLayout
            android:layout_width="100dp"
            android:layout_margin="10dp"
            android:layout_weight="1"
            android:layout_height="wrap_content"
            android:orientation="vertical"
            android:gravity="center">
            <TextView
                android:id="@+id/TXT_NAME"
                android:textStyle="bold"
                android:textSize="14dp"
                android:textColor="@color/black"
                android:layout_width="match_parent"
                android:layout_height="wrap_content"
                android:text="Idzhami"/>
            <TextView
                android:id="@+id/TXT_BIRTHDAY"
                android:textSize="14dp"
                android:layout_width="match_parent"
                android:layout_height="wrap_content"
                android:text="30-03-2021"/>
            <TextView
                android:id="@+id/TXT_ADDRESS"
                android:textSize="14dp"
                android:layout_width="match_parent"
                android:textStyle="italic"
                android:layout_height="wrap_content"
                android:text="Kel.Kecandran, kec.Sidomuti Kota Salatiga Jateng"/>
        </LinearLayout>
        <LinearLayout
            android:layout_weight="1"
            android:layout_width="wrap_content"
            android:layout_margin="10dp"
            android:layout_height="wrap_content"
            android:orientation="vertical"
            android:gravity="center">
            <ImageView
                android:id="@+id/BTN_DELETE"
                android:layout_width="40dp"
                android:layout_height="20dp"
                android:layout_gravity="right"
                android:background="@drawable/ic_delete"/>
            <Button
                android:layout_marginTop="10dp"
                android:layout_width="70dp"
                android:layout_height="33dp"
                android:textSize="8dp"
                android:layout_gravity="right"
                android:textColor="@color/white"
                android:id="@+id/BTN_UPDATE"
                android:text="update"/>

        </LinearLayout>

    </LinearLayout>
</androidx.cardview.widget.CardView>

create two drawable files

ic_delete.xml

<vector xmlns:android="http://schemas.android.com/apk/res/android"
    android:width="25dp"
    android:height="16dp"
    android:viewportWidth="25"
    android:viewportHeight="16">
    <path
        android:pathData="M24.96,0H8.96L8.96,0L0,7.9995L8.96,16H24.96V0Z"
        android:fillColor="#F44336"
        android:fillType="evenOdd"/>
    <path
        android:pathData="M21.101,5.3539L19.7433,3.9963L17.0278,6.7118L14.3128,3.9968L12.9551,5.3544L15.6702,8.0695L12.9551,10.7845L14.3128,12.1421L17.0278,9.4271L19.7434,12.1427L21.101,10.785L18.3854,8.0695L21.101,5.3539Z"
        android:fillColor="#ffffff"
        android:fillType="evenOdd"/>
</vector>

ic_edit.xml

<vector xmlns:android="http://schemas.android.com/apk/res/android"
    android:width="14dp"
    android:height="14dp"
    android:viewportWidth="14"
    android:viewportHeight="14">
    <path
        android:pathData="M13.6158,2.2508C14.1309,1.7357 14.1311,0.9006 13.6161,0.3856C13.1011,-0.1294 12.266,-0.1293 11.7508,0.3859L1.3508,10.7859C0.8356,11.3011 0.8355,12.1362 1.3505,12.6512C1.8655,13.1662 2.7006,13.166 3.2157,12.6509L13.6158,2.2508Z"
        android:fillColor="#009E3D"/>
    <path
        android:pathData="M0,14.0002L0.8653,11.2695L2.7307,13.135L0,14.0002Z"
        android:fillColor="#009E3D"/>
    <path
        android:pathData="M3.8728,11.9919L2.0078,10.127L0.866,11.2688L2.731,13.1337L3.8728,11.9919Z"
        android:fillColor="#009E3D"/>
</vector>

After everything is fine,
please add the code below in the MainActivity.kt file

class MainActivity : AppCompatActivity() {
    private var adapterTransaction by Delegates.notNull<RecycleViewAdapter>()
    var dbhelper = DbAdapterPerson(this).TABLE_PERSON()
    var id_data = ""
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)
        try {
            GET_DATA_PERSONE()

            val edname = findViewById<EditText>(R.id.ED_NAME)
            val address = findViewById<EditText>(R.id.ED_ADDRESS)
            val birthday = findViewById<EditText>(R.id.ED_BIRTHDAY)
            val btn_add = findViewById<Button>(R.id.BTN_ADD)
            btn_add.setOnClickListener {
                try {
                    if (id_data == "") {
                        if (edname.text.toString().trim() != "") {
                            ADD_PERSON(
                                edname.text.toString().trim(),
                                address.text.toString().trim(),
                                birthday.text.toString().trim()
                            )
                            Handler().postDelayed({
                                val edname = findViewById<EditText>(R.id.ED_NAME).setText("")
                                val address = findViewById<EditText>(R.id.ED_ADDRESS).setText("")
                                val birthday = findViewById<EditText>(R.id.ED_BIRTHDAY).setText("")
                                Toast.makeText(
                                    this,
                                    "Added successfully",
                                    Toast.LENGTH_SHORT
                                ).show()
                                GET_DATA_PERSONE()
                            }, 1000)
                        } else {
                            Toast.makeText(
                                this,
                                "Please enter name",
                                Toast.LENGTH_SHORT
                            ).show()
                        }

                    } else {
                        if (edname.text.toString().trim() != "") {
                            UPDATE_DATA(
                                id_data,
                                edname.text.toString().trim(),
                                address.text.toString().trim(),
                                birthday.text.toString().trim()
                            )
                            Handler().postDelayed({
                                val edname = findViewById<EditText>(R.id.ED_NAME).setText("")
                                val address = findViewById<EditText>(R.id.ED_ADDRESS).setText("")
                                val birthday = findViewById<EditText>(R.id.ED_BIRTHDAY).setText("")
                                val btn_add = findViewById<Button>(R.id.BTN_ADD)
                                btn_add.setText("Add")
                                id_data = ""
                                Toast.makeText(
                                    this,
                                    "Update successfully",
                                    Toast.LENGTH_SHORT
                                ).show()
                                GET_DATA_PERSONE()
                            }, 1000)
                        } else {
                            Toast.makeText(
                                this,
                                "Please enter name",
                                Toast.LENGTH_SHORT
                            ).show()
                        }
                    }
                } catch (e: Exception) {
                    Toast.makeText(
                        this,
                        "e.toString()",
                        Toast.LENGTH_SHORT
                    ).show()
                }
            }
        } catch (e: Exception) {
            Log.d(" Data Error ", e.toString())
        }

    }

    private fun ADD_PERSON(
        name: String,
        address: String,
        birtday: String
    ) {
        var addperson = dbhelper.ADD_DATA_PERSON(name, address, birtday)
    }

    private fun GET_DATA_PERSONE() {
        try {
            var dataPerson = dbhelper.GET_DATA_PERSON()
            adapterTransaction = RecycleViewAdapter(
                this,
                dataPerson,
                this
            )
            val rvmain = findViewById<RecyclerView>(R.id.rv_main)

            rvmain.layoutManager = LinearLayoutManager(this@MainActivity)
            rvmain.adapter = adapterTransaction

        } catch (e: Exception) {
            Log.e("Error : ", e.toString())
        }
    }

    fun DELETE_DATA(id: String) {
        try {
            Log.d("id :", id)
            var dataPerson = dbhelper.DELETE_DATA_PERSON(id)
            Handler().postDelayed({
                Toast.makeText(
                    this,
                    "Delete successfully",
                    Toast.LENGTH_SHORT
                ).show()
                GET_DATA_PERSONE()
            }, 1000)
        } catch (e: Exception) {

        }
    }

    private fun UPDATE_DATA(
        id: String,
        name: String,
        address: String,
        birthday: String
    ) {
        var dataPerson = dbhelper.UPDATE_DATA_PERSON(id, name, address, birthday)
    }

    fun DATA_FROM_RECYCLEVIEW(
        id: String,
        name: String,
        address: String,
        birthday: String
    ) {

        id_data = id
        val btn_add = findViewById<Button>(R.id.BTN_ADD)
        btn_add.setText("Update")
        val edname = findViewById<EditText>(R.id.ED_NAME).setText(name)
        val address = findViewById<EditText>(R.id.ED_ADDRESS).setText(address)
        val birthday = findViewById<EditText>(R.id.ED_BIRTHDAY).setText(birthday)
    }

}

Wokayy….
Run project ~

Done ~

https://github.com/idzhami/CRUD-SQLite-Kotlin

Kotlin
Android Studio
Sqlite

--

--

Usman Idzhami

Written by Usman Idzhami

19 Followers

https://www.linkedin.com/in/usman-idzhami-763b47186

Help

Status

About

Careers

Press

Blog

Privacy

Terms

Text to speech

Teams