Android SQLite Database

SQLite Database

SQLite Database:

Android SQLite Database is a RDBMS, lightweight and that comes shipped with the Android Operating System that offers a Local Data Storage solution to Andoird applications.

SQLite is C library that offers scalable and lightweight client-side database which requires no server and is easy to configure.

In the context of Android development, SQLite is a utility that provides the application with a database for structured data which could be written in a persistent memory in the device.

Example:

                                    
<!-- 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"
    tools:context=".MainActivity"
    android:padding="16dp"
    android:orientation="vertical">

    <ScrollView
        android:layout_width="match_parent"
        android:layout_height="match_parent"
        android:layout_marginTop="10dp"
        android:scrollbars="none">

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

            <EditText
                android:id="@+id/id"
                android:layout_width="match_parent"
                android:layout_height="wrap_content"
                android:hint="ID"
                android:layout_margin="10dp"/>

            <EditText
                android:id="@+id/name"
                android:layout_width="match_parent"
                android:layout_height="wrap_content"
                android:hint="Name"
                android:layout_margin="10dp"/>

            <EditText
                android:id="@+id/age"
                android:layout_width="match_parent"
                android:layout_height="wrap_content"
                android:hint="Age"
                android:layout_margin="10dp"
                android:inputType="number"/>

            <EditText
                android:id="@+id/city"
                android:layout_width="match_parent"
                android:layout_height="wrap_content"
                android:hint="City"
                android:layout_margin="10dp"/>

            <LinearLayout
                android:layout_width="match_parent"
                android:layout_height="wrap_content"
                android:orientation="horizontal"
                android:layout_marginTop="20dp"
                android:gravity="center_horizontal">

                <Button
                    android:id="@+id/save"
                    android:layout_width="wrap_content"
                    android:layout_height="wrap_content"
                    android:text="Save"
                    android:layout_weight="1"
                    android:textColor="@color/colorWhite"
                    android:background="@color/colorPrimaryDark"
                    android:layout_margin="10dp"/>

                <Button
                    android:id="@+id/view"
                    android:layout_width="wrap_content"
                    android:layout_height="wrap_content"
                    android:text="View"
                    android:layout_weight="1"
                    android:textColor="@color/colorWhite"
                    android:background="@color/colorPrimaryDark"
                    android:layout_margin="10dp"/>

            </LinearLayout>

            <LinearLayout
                android:layout_width="match_parent"
                android:layout_height="wrap_content"
                android:orientation="horizontal"
                android:layout_marginTop="6dp"
                android:gravity="center_horizontal">

                <Button
                    android:id="@+id/update"
                    android:layout_width="wrap_content"
                    android:layout_height="wrap_content"
                    android:text="Update"
                    android:layout_weight="1"
                    android:textColor="@color/colorWhite"
                    android:background="@color/colorPrimaryDark"
                    android:layout_margin="10dp"/>

                <Button
                    android:id="@+id/delete"
                    android:layout_width="wrap_content"
                    android:layout_height="wrap_content"
                    android:text="Delete"
                    android:layout_weight="1"
                    android:textColor="@color/colorWhite"
                    android:background="@color/colorPrimaryDark"
                    android:layout_margin="10dp"/>

            </LinearLayout>

            <LinearLayout
                android:layout_width="match_parent"
                android:layout_height="wrap_content"
                android:orientation="horizontal"
                android:layout_marginTop="6dp"
                android:gravity="center_horizontal">

                <Button
                    android:id="@+id/search"
                    android:layout_width="match_parent"
                    android:layout_height="wrap_content"
                    android:text="Search"
                    android:layout_weight="1"
                    android:textColor="@color/colorWhite"
                    android:background="@color/colorPrimaryDark"
                    android:layout_margin="10dp"/>
            </LinearLayout>

        </LinearLayout>
    </ScrollView>
</LinearLayout>


// activity_second.xml:

<?xml version="1.0" encoding="utf-8"?>
<ScrollView 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"
    tools:context=".SecondActivity"
    android:padding="16dp">

    <TextView
        android:id="@+id/viewSavedData"
        android:layout_width="match_parent"
        android:layout_height="match_parent"
        android:text="View Saved Data"
        android:textSize="18sp"
        android:padding="8dp"
        android:textColor="#000000"
        android:scrollbars="vertical"/>
</ScrollView>

                                    
                                        // main_activity.java
                                         
import android.content.Intent; // Intent for switching between activities
import android.os.Bundle; // Bundle for passing data between activities
import android.view.View; // View for UI elements
import android.widget.Button; // Button widget
import android.widget.EditText; // EditText widget
import android.widget.Toast; // Toast for displaying messages

import androidx.appcompat.app.AppCompatActivity; // Base class for activities

// DatabaseHandler class for managing SQLite database
public class MainActivity extends AppCompatActivity implements View.OnClickListener {

    DatabaseHandler db; // Database handler instance

    EditText eId, eName, eAge, eCity; // EditText widgets for user input
    Button bSave, bView, bUpdate, bDelete, bSearch; // Buttons for various actions

    String id, name, age, city; // String variables for data input

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        eId = findViewById(R.id.id); // Initialize EditText for ID
        eName = findViewById(R.id.name); // Initialize EditText for Name
        eAge = findViewById(R.id.age); // Initialize EditText for Age
        eCity = findViewById(R.id.city); // Initialize EditText for City

        bSave = findViewById(R.id.save); // Initialize Save Button
        bView = findViewById(R.id.view); // Initialize View Button
        bUpdate = findViewById(R.id.update); // Initialize Update Button
        bDelete = findViewById(R.id.delete); // Initialize Delete Button
        bSearch = findViewById(R.id.search); // Initialize Search Button

        bSave.setOnClickListener(this); // Set click listener for Save Button
        bView.setOnClickListener(this); // Set click listener for View Button
        bUpdate.setOnClickListener(this); // Set click listener for Update Button
        bDelete.setOnClickListener(this); // Set click listener for Delete Button
        bSearch.setOnClickListener(this); // Set click listener for Search Button

        db = new DatabaseHandler(this); // Initialize DatabaseHandler instance
    }

    @Override
    public void onClick(View v) {
        switch (v.getId()) {

            case R.id.save: // Save Button clicked
                name = eName.getText().toString();
                age = eAge.getText().toString();
                city = eCity.getText().toString();

                if (name.equals("") | age.equals("") | city.equals("")) {
                    Toast.makeText(this, "Please fill the fields.", Toast.LENGTH_SHORT).show();
                } else {
                    db.addEmployee(name, age, city);
                    eId.setText("");
                    eName.setText("");
                    eAge.setText("");
                    eCity.setText("");
                    Toast.makeText(this, "Data saved successfully.", Toast.LENGTH_SHORT).show();
                }
                break;

            case R.id.view: // View Button clicked
                String data = db.getEmployee();

                if (data.equals("")) {
                    Toast.makeText(this, "No data found.", Toast.LENGTH_SHORT).show();
                } else {
                    Intent intent = new Intent(this, SecondActivity.class);
                    startActivity(intent);
                }
                break;

            case R.id.update: // Update Button clicked
                id = eId.getText().toString().trim();
                name = eName.getText().toString().trim();
                age = eAge.getText().toString();
                city = eCity.getText().toString();

                if (id.equals("") | name.equals("") | age.equals("") | city.equals("")) {
                    Toast.makeText(this, "Please fill all the fields.", Toast.LENGTH_SHORT).show();
                } else {
                    long l = Long.parseLong(id);
                    db.updateEmployee(l, name, age, city);
                    eId.setText("");
                    eName.setText("");
                    eAge.setText("");
                    eCity.setText("");
                    Toast.makeText(this, "Data updated successfully.", Toast.LENGTH_SHORT).show();
                }
                break;

            case R.id.delete: // Delete Button clicked
                id = eId.getText().toString();

                if (id.equals("")) {
                    Toast.makeText(this, "Please fill the Id.", Toast.LENGTH_SHORT).show();
                } else {
                    long l = Long.parseLong(id);
                    db.deleteEmployee(l);
                    eId.setText("");
                    eName.setText("");
                    eAge.setText("");
                    eCity.setText("");
                    Toast.makeText(this, "Data deleted successfully.", Toast.LENGTH_SHORT).show();
                }
                break;

            case R.id.search: // Search Button clicked
                id = eId.getText().toString().trim();

                if (id.equals("")) {
                    Toast.makeText(this, "Please fill the Id.", Toast.LENGTH_SHORT).show();
                } else {
                    try {
                        long l1 = Long.parseLong(id);
                        name = db.getName(l1);
                        age = db.getAge(l1);
                        city = db.getCity(l1);

                        eName.setText(name);
                        eAge.setText(age);
                        eCity.setText(city);

                        Toast.makeText(this, "Data found successfully.", Toast.LENGTH_SHORT).show();

                    } catch (Exception e) {
                        Toast.makeText(this, "ID is not valid.", Toast.LENGTH_SHORT).show();
                    }

                }
                break;
        }
    }
}

// DatabaseHandler class for managing SQLite database
public class DatabaseHandler extends SQLiteOpenHelper {

    private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_NAME = "employeeManager";
    private static final String TABLE_EMPLOYEE = "employee";
    private static final String KEY_ID = "id";
    private static final String KEY_NAME = "name";
    private static final String KEY_AGE = "age";
    private static final String KEY_CITY = "city";

    SQLiteDatabase db;

    public DatabaseHandler(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String query = "CREATE TABLE " + TABLE_EMPLOYEE + "("
                + KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + KEY_NAME + " TEXT,"
                + KEY_AGE + " TEXT,"
                + KEY_CITY + " TEXT " + ")";
        db.execSQL(query);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_EMPLOYEE);
        onCreate(db);
    }

    public long addEmployee(String name, String age, String city) {
        db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_NAME, name);
        values.put(KEY_AGE, age);
        values.put(KEY_CITY, city);

        return db.insert(TABLE_EMPLOYEE, null, values);
    }

    public String getEmployee() {
        db = this.getReadableDatabase();

        Cursor cursor = db.query(TABLE_EMPLOYEE, new String[]{KEY_ID, KEY_NAME, KEY_AGE, KEY_CITY},
                null, null, null, null, null);

        int eId = cursor.getColumnIndex(KEY_ID);
        int eName = cursor.getColumnIndex(KEY_NAME);
        int eAge = cursor.getColumnIndex(KEY_AGE);
        int eCity = cursor.getColumnIndex(KEY_CITY);

        String res = "";

        for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()) {
            res = res +
                    "Id: " + cursor.getString(eId) + "\n" +
                    "Name: " + cursor.getString(eName) + "\n" +
                    "Age: " + cursor.getString(eAge) + "\n" +
                    "City: " + cursor.getString(eCity) + "\n\n";
        }

        db.close();
        return res;
    }

                                      
                                        import android.content.Intent // Intent for switching between activities
import android.os.Bundle // Bundle for passing data between activities
import android.view.View // View for UI elements
import android.widget.Button // Button widget
import android.widget.EditText // EditText widget
import android.widget.Toast // Toast for displaying messages

import androidx.appcompat.app.AppCompatActivity // Base class for activities

// DatabaseHandler class for managing SQLite database
class MainActivity : AppCompatActivity(), View.OnClickListener {

    private lateinit var db: DatabaseHandler // Database handler instance

    private lateinit var eId: EditText // EditText for ID
    private lateinit var eName: EditText // EditText for Name
    private lateinit var eAge: EditText // EditText for Age
    private lateinit var eCity: EditText // EditText for City

    private lateinit var bSave: Button // Save Button
    private lateinit var bView: Button // View Button
    private lateinit var bUpdate: Button // Update Button
    private lateinit var bDelete: Button // Delete Button
    private lateinit var bSearch: Button // Search Button

    private var id: String = "" // String variable for ID
    private var name: String = "" // String variable for Name
    private var age: String = "" // String variable for Age
    private var city: String = "" // String variable for City

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

        eId = findViewById(R.id.id) // Initialize EditText for ID
        eName = findViewById(R.id.name) // Initialize EditText for Name
        eAge = findViewById(R.id.age) // Initialize EditText for Age
        eCity = findViewById(R.id.city) // Initialize EditText for City

        bSave = findViewById(R.id.save) // Initialize Save Button
        bView = findViewById(R.id.view) // Initialize View Button
        bUpdate = findViewById(R.id.update) // Initialize Update Button
        bDelete = findViewById(R.id.delete) // Initialize Delete Button
        bSearch = findViewById(R.id.search) // Initialize Search Button

        bSave.setOnClickListener(this) // Set click listener for Save Button
        bView.setOnClickListener(this) // Set click listener for View Button
        bUpdate.setOnClickListener(this) // Set click listener for Update Button
        bDelete.setOnClickListener(this) // Set click listener for Delete Button
        bSearch.setOnClickListener(this) // Set click listener for Search Button

        db = DatabaseHandler(this) // Initialize DatabaseHandler instance
    }

    override fun onClick(v: View) {
        when (v.id) {

            R.id.save -> { // Save Button clicked
                name = eName.text.toString()
                age = eAge.text.toString()
                city = eCity.text.toString()

                if (name.isEmpty() || age.isEmpty() || city.isEmpty()) {
                    Toast.makeText(this, "Please fill the fields.", Toast.LENGTH_SHORT).show()
                } else {
                    db.addEmployee(name, age, city)
                    eId.setText("")
                    eName.setText("")
                    eAge.setText("")
                    eCity.setText("")
                    Toast.makeText(this, "Data saved successfully.", Toast.LENGTH_SHORT).show()
                }
            }

            R.id.view -> { // View Button clicked
                val data = db.getEmployee()

                if (data.isEmpty()) {
                    Toast.makeText(this, "No data found.", Toast.LENGTH_SHORT).show()
                } else {
                    val intent = Intent(this, SecondActivity::class.java)
                    startActivity(intent)
                }
            }

            R.id.update -> { // Update Button clicked
                id = eId.text.toString().trim()
                name = eName.text.toString().trim()
                age = eAge.text.toString()
                city = eCity.text.toString()

                if (id.isEmpty() || name.isEmpty() || age.isEmpty() || city.isEmpty()) {
                    Toast.makeText(this, "Please fill all the fields.", Toast.LENGTH_SHORT).show()
                } else {
                    val l: Long = id.toLong()
                    db.updateEmployee(l, name, age, city)
                    eId.setText("")
                    eName.setText("")
                    eAge.setText("")
                    eCity.setText("")
                    Toast.makeText(this, "Data updated successfully.", Toast.LENGTH_SHORT).show()
                }
            }

            R.id.delete -> { // Delete Button clicked
                id = eId.text.toString()

                if (id.isEmpty()) {
                    Toast.makeText(this, "Please fill the Id.", Toast.LENGTH_SHORT).show()
                } else {
                    val l: Long = id.toLong()
                    db.deleteEmployee(l)
                    eId.setText("")
                    eName.setText("")
                    eAge.setText("")
                    eCity.setText("")
                    Toast.makeText(this, "Data deleted successfully.", Toast.LENGTH_SHORT).show()
                }
            }

            R.id.search -> { // Search Button clicked
                id = eId.text.toString().trim()

                if (id.isEmpty()) {
                    Toast.makeText(this, "Please fill the Id.", Toast.LENGTH_SHORT).show()
                } else {
                    try {
                        val l1: Long = id.toLong()
                        name = db.getName(l1)
                        age = db.getAge(l1)
                        city = db.getCity(l1)

                        eName.setText(name)
                        eAge.setText(age)
                        eCity.setText(city)

                        Toast.makeText(this, "Data found successfully.", Toast.LENGTH_SHORT).show()

                    } catch (e: Exception) {
                        Toast.makeText(this, "ID is not valid.", Toast.LENGTH_SHORT).show()
                    }
                }
            }
        }
    }
}


// DatabaseHandler class for managing SQLite database

class DatabaseHandler(context: Context) : SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {

    companion object {
        private const val DATABASE_VERSION = 1
        private const val DATABASE_NAME = "employeeManager"
        private const val TABLE_EMPLOYEE = "employee"
        private const val KEY_ID = "id"
        private const val KEY_NAME = "name"
        private const val KEY_AGE = "age"
        private const val KEY_CITY = "city"
    }

    private lateinit var db: SQLiteDatabase

    override fun onCreate(db: SQLiteDatabase) {
        val query = "CREATE TABLE $TABLE_EMPLOYEE(" +
                "$KEY_ID INTEGER PRIMARY KEY AUTOINCREMENT," +
                "$KEY_NAME TEXT," +
                "$KEY_AGE TEXT," +
                "$KEY_CITY TEXT)"
        db.execSQL(query)
    }

    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        db.execSQL("DROP TABLE IF EXISTS $TABLE_EMPLOYEE")
        onCreate(db)
    }

    fun addEmployee(name: String, age: String, city: String): Long {
        db = this.writableDatabase

        val values = ContentValues()
        values.put(KEY_NAME, name)
        values.put(KEY_AGE, age)
        values.put(KEY_CITY, city)

        return db.insert(TABLE_EMPLOYEE, null, values)
    }

    fun getEmployee(): String {
        db = this.readableDatabase

        val cursor = db.query(
            TABLE_EMPLOYEE, arrayOf(KEY_ID, KEY_NAME, KEY_AGE, KEY_CITY),
            null, null, null, null, null
        )

        val eId = cursor.getColumnIndex(KEY_ID)
        val eName = cursor.getColumnIndex(KEY_NAME)
        val eAge = cursor.getColumnIndex(KEY_AGE)
        val eCity = cursor.getColumnIndex(KEY_CITY)

        var res = ""

        cursor.use {
            while (it.moveToNext()) {
                res += "Id: ${it.getString(eId)}\n" +
                        "Name: ${it.getString(eName)}\n" +
                        "Age: ${it.getString(eAge)}\n" +
                        "City: ${it.getString(eCity)}\n\n"
            }
        }

        db.close()
        return res
    }
}