Vishal Anand's Blog

How to make the table and insert values in SQLite DB - Android Studio

How to make the table and insert values in SQLite DB - Android Studio

SQLite implementation project and fetch data into RecyclerView in Android Studio

Vishal Anand's photo
Vishal Anand
·

4 min read

Hello folks, in this article we will see how we can implement an SQLite database, make tables, insert data into tables, and fetch the table's data into RecyclerView using Kotlin.

In this article, we will make an app that will store the user data ( name, email ) in the SQLite Database and will show all the stored data inside RecyclerView

Step 1: First create a new Android Studio project

Create a new project in android studio (hashnode.dev)

Skip designing part

Step 2: activity_main.xml file designing part

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:orientation="vertical"
    tools:context=".MainActivity">

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

        <LinearLayout
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:orientation="horizontal"
            android:padding="5dp">

            <TextView
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:text="Name : "
                android:textColor="@color/black"
                android:textSize="20dp"
                android:textStyle="bold" />


            <EditText
                android:id="@+id/name"
                android:layout_width="match_parent"
                android:layout_height="wrap_content"
                android:hint="Enter name"
                android:textColor="@color/black" />


        </LinearLayout>

        <LinearLayout
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:orientation="horizontal"
            android:padding="5dp">

            <TextView
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:text="Email : "
                android:textColor="@color/black"
                android:textSize="20dp"
                android:textStyle="bold" />


            <EditText
                android:id="@+id/email"
                android:layout_width="match_parent"
                android:layout_height="wrap_content"
                android:hint="Enter email address"
                android:textColor="@color/black" />


        </LinearLayout>

        <Button
            android:id="@+id/insert"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:layout_margin="5dp"
            android:text="Insert"
            android:textSize="16dp" />

    </LinearLayout>

    <androidx.recyclerview.widget.RecyclerView
        android:id="@+id/recView"
        android:layout_width="match_parent"
        android:layout_margin="5dp"
        android:layout_height="match_parent"/>


</LinearLayout>

Screen design result :

Step 3: Create a custom layout file for RecyclerView

custom_user.xml

<?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="wrap_content"
    android:layout_margin="8dp"
    android:padding="5dp"
    android:background="@color/black"
    android:orientation="vertical">

    <TextView
        android:id="@+id/name"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="name"
        android:textColor="@color/white"
        android:textSize="18dp"
        android:textStyle="bold" />

    <TextView
        android:id="@+id/email"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="email"
        android:textColor="@color/white"
        android:textSize="20dp"
        android:textStyle="bold" />

</LinearLayout>

Screen design result :

Step 4: Create DatabaseHelper.kt class to perform all the implementations and operations of DB

create DatabaseHelper.kt the class which will extend SQLiteOpenHelper class

package com.tworoot2.sqlitedatabase.databaseClass

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

class DatabaseHelper(private val context: Context) : SQLiteOpenHelper(
    context, DATABASE_NAME, null,
    DATABASE_VERSION
) {

    // this function will create table inside our database
    override fun onCreate(sqLiteDatabase: SQLiteDatabase?) {
        sqLiteDatabase!!.execSQL("create Table UserDetails(ID INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT, email TEXT)")
    }

    // Called when the database needs to be upgraded.
    // The implementation should use this method to drop tables, add tables,
    // or do anything else it needs to upgrade to the new schema version.
    override fun onUpgrade(sqLiteDatabase: SQLiteDatabase?, oldVersion: Int, newVersion: Int) {
        sqLiteDatabase!!.execSQL("DROP TABLE IF EXISTS UserDetails")

    }

    // function for inserting data into Database
    fun insertData(name: String, email: String): Boolean {

        // Gets the data repository in write mode
        val db: SQLiteDatabase = this.writableDatabase
        val contentValues = ContentValues()
        contentValues.put("name", name)
        contentValues.put("email", email)

        val result = db.insert("UserDetails", null, contentValues)

        return !result.equals(-1)
    }

    // function for fetching all the data from database
    fun getData(): Cursor? {
        val db = this.writableDatabase
        return db.rawQuery("Select * from UserDetails", null)
    }


    // initialize here database name and database version
    companion object {
        const val DATABASE_NAME = "MyDatabase.db"
        const val DATABASE_VERSION = 1
    }


}

Now our SQLite Database Helper class is ready, and we can insert the values in the Database

Step 5: Make data class and adapter class for recycler view

UserData.kt class

package com.tworoot2.sqlitedatabase.dataClasses

data class UserData(val name: String, val email: String)

UserAdapter.kt class

package com.tworoot2.sqlitedatabase.adapters

import android.content.Context
import android.view.LayoutInflater
import android.view.View
import android.view.ViewGroup
import android.widget.Button
import android.widget.TextView
import androidx.recyclerview.widget.RecyclerView
import com.tworoot2.sqlitedatabase.R
import com.tworoot2.sqlitedatabase.dataClasses.UserData

class UserAdapter(var context: Context, var arrayList: ArrayList<UserData>) :
    RecyclerView.Adapter<UserAdapter.UserViewHolder>() {


    override fun onCreateViewHolder(parent: ViewGroup, viewType: Int): UserViewHolder {
        val view = LayoutInflater.from(parent.context)
            .inflate(R.layout.custom_user, parent, false)

        return UserViewHolder(view)
    }

    override fun onBindViewHolder(holder: UserViewHolder, position: Int) {
        holder.name.text = arrayList[position].name
        holder.email.text = arrayList[position].email
    }

    override fun getItemCount(): Int {
        return arrayList.size
    }


    inner class UserViewHolder(itemView: View) : RecyclerView.ViewHolder(itemView) {
        val name: TextView = itemView.findViewById(R.id.name)
        val email: TextView = itemView.findViewById(R.id.email)

    }


}

Step 6: Now it's the turn of MainActivity.kt file

In MainActivity.kt file we will have two parts, first will be the data inserting part and the second will be the data retrieving part inside the recycler view

package com.tworoot2.sqlitedatabase

import android.database.Cursor
import androidx.appcompat.app.AppCompatActivity
import android.os.Bundle
import android.widget.Button
import android.widget.EditText
import android.widget.Toast
import androidx.recyclerview.widget.LinearLayoutManager
import androidx.recyclerview.widget.RecyclerView
import com.tworoot2.sqlitedatabase.adapters.UserAdapter
import com.tworoot2.sqlitedatabase.dataClasses.UserData
import com.tworoot2.sqlitedatabase.databaseClass.DatabaseHelper

class MainActivity : AppCompatActivity() {

    lateinit var name: EditText
    lateinit var email: EditText
    private lateinit var insert: Button
    lateinit var recView: RecyclerView
    private lateinit var databaseHelper: DatabaseHelper
    lateinit var adapter: UserAdapter
    lateinit var arrayList: ArrayList<UserData>

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

        name = findViewById(R.id.name)
        email = findViewById(R.id.email)
        insert = findViewById(R.id.insert)
        recView = findViewById(R.id.recView)

        databaseHelper = DatabaseHelper(this)
        arrayList = ArrayList()

        addValuesInArrayList()

        // data retrieving part inside recyclerview
        recView.layoutManager =
            LinearLayoutManager(this@MainActivity, LinearLayoutManager.VERTICAL, false)
        adapter = UserAdapter(this@MainActivity, arrayList)
        recView.adapter = adapter


        // data inserting part
        insert.setOnClickListener {
            if (name.text.equals("") || name.text.isEmpty() ||
                email.text.isEmpty() || name.text == null ||
                email.text == null || email.text.equals("")
            ) {
                if (name.text.equals("") || name.text.isEmpty() ||
                    name.text == null
                )
                    name.error = "Please enter your name"
                else if (email.text.isEmpty() || email.text == null || email.text.equals("")) {
                    email.error = "Please enter your email"
                }
            } else {
                val inserted =
                    databaseHelper.insertData(name.text.toString(), email.text.toString())
                if (inserted) {
                    Toast.makeText(this@MainActivity, "Inserted successfully", Toast.LENGTH_SHORT)
                        .show()
                    arrayList.clear()
                    addValuesInArrayList()
                    adapter = UserAdapter(this@MainActivity, arrayList)
                    recView.adapter = adapter

                } else {
                    Toast.makeText(this@MainActivity, "Not inserted", Toast.LENGTH_SHORT).show()
                }
            }
        }


    }

    // this method will add all the data to arraylist from Database
    private fun addValuesInArrayList() {
        val cursor = databaseHelper.getData()
        while (cursor!!.moveToNext()) {
            arrayList.add(UserData(cursor.getString(1), cursor.getString(2)))
        }
    }
}

Video Output :

https://www.youtube.com/shorts/3zwYYsQm12o

All required comments are available in every code section

That's all for this

Thank you for reading this article, if you found this helpful please subscribe.

recycler-viewSQLiteAndroid#SQLtutorial Databases